CodeGeek123 - 25 days ago 4x

SQL Question

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
```

Source (Stackoverflow)

Comments