sql - 查找时间序列的运行总和高于给定阈值的位置

Question

我有一些时间序列数据。例如，查看以下值(假设此处的时间为分钟):

User Time Value
a   0      10
b   1      100
c   2      200
a   3      5
e   4      7
a   5      999
a   6      8
b   7      10
a   8      10
a   9      10
a   10     10
a   11     10
a   12     100

现在我想知道在任何给定的 5 分钟间隔内，总和是否达到了 1000 以上。

例如，在上面的示例中，我应该得到诸如用户 a，第 5、6、8、9 分钟的输出。

Answer 1

这对于窗口函数来说是一项简单的任务:

select *
from 
 (
   select t.*
     ,sum("Value") -- cumulative sum over the previous five minutes
      over (partition by "user"
            order by "Time"
            range 4 preceding) as sum_5_minutes
   from Table1 t
 ) dt
where sum_5_minutes > 1000

参见 fiddle

编辑:SQLFiddle 又下线了，不过你还可以搜索接下来的 5 分钟。

Edit2:SQLFiddle 离线，但如果数据类型是 TimeStamp 或 Date，则必须使用间隔而不是整数:

select *
from 
 (
  select t.*
     ,sum("Value") 
      over (partition by "User"
            order by "Time"
            range interval '4' minute preceding) as sum_prev5_minutes
      ,sum("Value") 
      over (partition by "User"
            order by "Time"
            range between interval '0' minute preceding -- or "current row" if there are no duplicate timestamps
            and interval '4' minute following) as sum_next5_minutes

   from Table1 t
 ) dt
where sum_prev5_minutes > 1000 
   or sum_next5_minutes > 1000