Presto SQL 窗口聚合回顾 x 小时/分钟/秒 [英] Presto SQL window aggregate looking back x hours/minutes/seconds

问题描述

我想通过回顾 x 小时/分钟/秒前来对 presto sql 进行聚合.

I want to do aggregate on presto sql by looking back x hours/minutes/seconds ago.

数据

id    |       timestamp       |    status
-------------------------------------------
A     |   2018-01-01 03:00:00 |     GOOD
A     |   2018-01-01 04:00:00 |     BAD
A     |   2018-01-01 05:00:00 |     GOOD
A     |   2018-01-01 09:00:00 |     BAD
A     |   2018-01-01 09:15:00 |     BAD
A     |   2018-01-01 13:00:00 |     GOOD
A     |   2018-01-01 14:00:00 |     GOOD
B     |   2018-02-01 09:00:00 |     GOOD
B     |   2018-02-01 10:00:00 |     BAD

结果:

id    |       timestamp       |    status    | bad_status_count
----------------------------------------------------------------
A     |   2018-01-01 03:00:00 |     GOOD     |       0 
A     |   2018-01-01 04:00:00 |     BAD      |       1
A     |   2018-01-01 05:00:00 |     GOOD     |       1
A     |   2018-01-01 09:00:00 |     BAD      |       1
A     |   2018-01-01 09:15:00 |     BAD      |       2
A     |   2018-01-01 13:00:00 |     GOOD     |       0 
A     |   2018-01-01 14:00:00 |     GOOD     |       0
B     |   2018-02-01 09:00:00 |     GOOD     |       0
B     |   2018-02-01 10:00:00 |     BAD      |       1

我正在按业务统计过去 3 小时内的不良状态.我怎样才能做到这一点?我正在尝试这样的事情:

I am counting bad status over the period of last 3 hours by business. How can I do that? I am trying something like this:

SELECT
  id,
  timestamp,
  status
  count(status) over(partition by id order by timestamp range between interval '3' hour and current_row) as bad_status_count
from table

当然还不行，我还得过滤掉状态不好的东西.我收到此错误:运行查询出错:第 7:1 行:窗口框架起始值类型必须是 INTEGER 或 BIGINT(实际间隔天到秒)

Of course it doesnt work yet and I still have to filter out for bad status. I got this error: Error running query: line 7:1: Window frame start value type must be INTEGER or BIGINT(actual interval day to second)

推荐答案

我不是 100% 如何在 PrestoDB 中表达这一点，但关键思想是将时间戳转换为小时:

I'm not 100% how to represent express this in PrestoDB, but the key idea is to convert the timestamps to hours:

select t.*,
       sum(case when status = 'Bad' then 1 else 0 end) over
           (partition by id
            order by hours
            range between -3 and current row
           ) as bad_status
from (select t.*,
             date_diff(hour, '2000-01-01', timestamp) as hours
      from t
     ) t;

这篇关于Presto SQL 窗口聚合回顾 x 小时/分钟/秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！