[지표] 세션 시간 연장하기

SET @seq := 1; -- 변수 선언

with base as (
	select t.*
		  ,timestampdiff(second, t.last_stamp, t.event_timestamp_kst) as last_diff
		  ,timestampdiff(second, t.event_timestamp_kst, t.next_stamp) as next_diff
	from (
	select user_pseudo_id
		  ,ga_session_id
		  ,lag(event_timestamp_kst, 1) over (partition by user_pseudo_id order by event_timestamp_kst) as last_stamp
		  ,event_timestamp_kst
		  ,lead(event_timestamp_kst, 1) over (partition by user_pseudo_id order by event_timestamp_kst) as next_stamp
		  ,row_number() over () as id
	from ga
	) t
)
select t2.*
from (
	select t.user_pseudo_id
		  ,ga_session_id
          ,last_stamp
          ,event_timestamp_kst
          ,next_stamp
          ,id
          ,last_diff
          ,next_diff
		  ,case when last_diff is null then t.id
				when last_diff >= 3600 then t.id
				else lag(id, 1) over (partition by user_pseudo_id order by id)
				end as new_session_id
		  ,case when last_diff >= 3600 then (@seq := @seq + 1)
				else @seq
				end as new_session_id2
	from base t
	where 1=1
	order by t.event_timestamp_kst
) t2
where 1=1
and (t2.last_diff is null
	 or t2.last_diff >= 3600);