CODEWITHSUNDEEP
sqlgroup-bywindow
AetherMass
AetherMass

Reputation: 147

Sessionize time sequence data in SQL

I have some time sequence data in SQL. I am attempting to "sessionize" this data. New sessions occur when a break larger than n time units occurs.

Input in CSV form:

Time, TimeDiffFromLast
0,0
1,1
2,1
17,15
18,1
19,1
32,13
33,1
34,1

For this example, a new session should be created if the time difference from the last row is greater than n=10 time units.

Desired output in CSV form:

Time, TimeDiffFromLast, SessionLabel
0,0,a
1,1,a
2,1,a
17,15,b
18,1,b
19,1,b
32,13,c
33,1,c
34,1,c

Is there any way to do this in SQL in general? Or it is not possible and I need to sequentially iterate over the data?

Upvotes: 0

Views: 634

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use a case expression to specify the start of a group when the diff > 10. Then use a running sum to classify session labels.

select time,diff_from_last,sum(col) over(order by time) as session_label
from (
select time, time-lag(time,1,time) over(order by time) as diff_from_last,
case when time-lag(time,1,time) over(order by time) > 10 then 1 else 0 end as col
from tbl
) t

This assumes the dbms you are using supports window functions.

Upvotes: 1

Related Questions