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

数据

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 小时内的不良状态。我怎样才能做到这一点？ 我正在尝试这样的事情:

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(实际间隔天到秒)

最佳答案

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

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;

https://stackoverflow.com/questions/54233225/