CodeGeek123 CodeGeek123 - 2 months ago 6
SQL Question

Finding where a running sum of a time series is above given threshold

I have some time series data. For example look at the following values (Lets assume time here is minutes):

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


Now I want to find out if within any given 5 minute intervals a total SUM of more than 1000 is achieved.

For example in the above example I should get an output such as user a, minute 5,6,8,9.

Answer

That's an easy task for Window Function:

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

See fiddle

Edit: SQLFiddle is offline again, but you can also search the next 5 minutes.

Edit2: SQLFiddle offline, but if the datatype is a TimeStamp or Date you must use intervals instead of integers:

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