Advented of SQL 2024 : 세션 유지 시간을 10분으로 재정의하기 (DAY 21)

Hyeon·2024년 12월 23일

SQL 문제 풀이

목록 보기

60/61

문제 탐구

GA는 30분 이상 기간 동안 사용자의 행동 로그가 없을 때 기존 세션을 종료하고 새 세션을 생성하는데 이 기준을 10분으로 줄이고 싶습니다. 세션을 종료하는 기준을 사용자가 10분 이상 행동하지 않을 때로 수정하여, 사용자 'a8Xu9GO6TB’의 세션을 재정의해 세션 ID를 계산하는 쿼리를 작성해주세요.

재정의한 세션 ID는 1부터 시작해 세션 시작 시간이 빠른 순서대로 1씩 증가하는 자연수여야하고 new_session_id 컬럼에 출력되어야 합니다.
이벤트 발생 시각이 빠른 순서대로 정렬되어야 합니다.

출력컬럼
user_pseudo_id: 사용자 아이디 (’a8Xu9GO6TB’)
event_timestamp_kst: 이벤트가 발생한 시각
event_name: 이벤트 이름
ga_session_id: 기존 세션 아이디
new_session_id: 재정의한 세션 아이디

진행 과정

1번째 시도

처음에는 재귀함수로 구해야한다고 생각해서 recursive cte 을 이용했다. but 오류 뜸

with cte_1 as (select event_timestamp_kst,event_name,ga_session_id,lag(event_timestamp_kst,1) over (order by event_timestamp_kst) as lag_1
from ga order by 1 limit 100),
cte_2 as (select strftime('%s',event_timestamp_kst)-strftime('%s',lag_1) as time_diff, event_timestamp_kst,event_name, ga_session_id
from cte_1),
recursive cte_3 as (
  SELECT *,new_session_id as 1
  FROM cte_2
  where time_diff >=600

  union ALL

  SELECT new_session_id + 1, c2.event_timestamp_kst,c2.event_name,c2.ga_session_id
  FROM cte_3 c
  inner JOIN cte_2 c2
  on c.event_name=c2.event_name and c.ga_session_id=c2.ga_session_id and c.user_pseudo_id=c2.user_pseudo_id 
)

select * from cte_3;

그래서 cte_2값 확인해봤더니 다음과 같이 떴지만 어떻게 적용해야할지 감이 안잡혔다.

2번째 시도

역시 재귀함수로 구하려니까 중간에 오류가 났었다.

with cte_1 as (select event_timestamp_kst,event_name,ga_session_id,lag(event_timestamp_kst,1) over (order by event_timestamp_kst) as lag_1
from ga order by 1 limit 100),
cte_2 as (select strftime('%s',event_timestamp_kst)-strftime('%s',lag_1) as time_diff, event_timestamp_kst,event_name, ga_session_id
from cte_1),
recursive cte_3 as (
  SELECT new_session_id as 1

  union ALL

  SELECT new_session_id + 1 as new_session_id, c2.event_timestamp_kst,c2.event_name,c2.ga_session_id
  FROM cte_2 
  where time_diff>=600
 )

select * from cte_3;

3번째 시도

이번에는 time_diff에서 제대로 적용이 안되어서 발생한 문제다.
원래 1시-> 12시면 최소 00분 이어야하는데, 0분 차이 난다고 떴다.
그래서 어떤 함수를 적용해야할지 고민을 많이 했었다.

with cte_1 as (select event_timestamp_kst,event_name,ga_session_id,lag(event_timestamp_kst,1) over (order by event_timestamp_kst) as lag_1
from ga
where user_pseudo_id = 'a8Xu9GO6TB' ),
cte_2 as (select 1 as new_session_id , strftime('%s',datetime(event_timestamp_kst))-strftime('%s',datetime(lag_1)) as time_diff, event_timestamp_kst,event_name, ga_session_id
from cte_1),
cte_3 as (select row_number() over (order by event_timestamp_kst) as row_n,case when time_diff>=600 then 1 else 0 end as time_diff_1, *
from cte_2)
select sum(time_diff_1) over (order by time_diff_1 rows between unbounded preceding and current row) + 1 as rows_b ,* from cte_3;

4번째 시도 (정답)

구글링하다가 주댕이의 공부기록 velog 을 보았다.
https://juyoungeeya.tistory.com/entry/SQL-solvesql-Advent-of-SQL-2024-21%EC%9D%BC%EC%B0%A8
'처음부터 datetime으로 만들고' 값 차이를 만드신게 key point였다.
그러면 1시 -> 12시 데이터도 00분으로 적용되는 것을 볼 수 있다.

그리고 sum() over() & case when을 통해서 재귀함수 없이 충분히 구할 수 있다.
STEP1. case when 을 통해서 분 차이가 10분 이상이면 1, 아니면 0을 출력
STEP2. sum() over ()을 통해 누적함수 구하기 , 10분 이상일 경우 1->2->3 순차적으로 변경

with cte_1 as (
select user_pseudo_id,datetime(event_timestamp_kst) as event_timestamp_kst,event_name,ga_session_id,
lag(datetime(event_timestamp_kst),1) over (order by datetime(event_timestamp_kst)) as lag_1
from ga
where user_pseudo_id ='a8Xu9GO6TB'),
cte_2 as (select (strftime('%s',event_timestamp_kst) - strftime('%s',lag_1)) / 60  as time_diff,*
from cte_1),
cte_3 as (select case when time_diff >= 10 then 1 else 0 end  as time_diff_1 , * from cte_2 )
select user_pseudo_id,event_timestamp_kst,event_name,ga_session_id,
sum(time_diff_1) over (order by event_timestamp_kst) + 1 as new_session_id 
from cte_3;