Presto SQL 窗口聚合回溯 x 小时/分钟/秒

Question

我想通过回顾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

当然，它还不起作用，我仍然必须过滤掉不良状态。我收到此错误： Error running query: line 7:1: Window frame start value type must be INTEGER or BIGINT(actual interval day to second)

Answer 1

我不是 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;