[Day 21] solvesql | Advent of SQL 2024 | 세션 유지 시간을 10분으로 재정의하기⭐💡

세션 유지 시간을 10분으로 재정의하기 

 

✔️0214 복습

문제 조건

1. 세션을 종료하는 기존의 기준(30분 이상)을 사용자가 10분 이상 행동하지 않을 때로 수정

2. 사용자 'a8Xu9GO6TB’의 세션을 재정의해 세션 ID를 계산하는 쿼리를 작성

3. 재정의한 세션 ID는 1부터 시작해 세션 시작 시간이 빠른 순서대로 1씩 증가하는 자연수일 것

4. 이벤트 발생 시각이 빠른 순서대로 정렬

 

Solution

WITH step1 AS (
  SELECT user_pseudo_id
       , event_timestamp_kst
       , LAG(event_timestamp_kst, 1) OVER (ORDER BY event_timestamp_kst) AS last_event -- 이후에 전체 유저에 확장시킬 것을 대비하여 partition by절 적어주면 꼭 1,2행 순서가 바뀜🤔
       , event_name
       , ga_session_id
  FROM ga
  WHERE user_pseudo_id = 'a8Xu9GO6TB'
  ORDER BY user_pseudo_id, event_timestamp_kst, last_event

), step2 AS (
  SELECT *
       , CASE WHEN TIMESTAMPDIFF(SECOND, last_event, event_timestamp_kst) >= 600 THEN 1
              ELSE 0
         END AS diff
  FROM step1

)
SELECT user_pseudo_id
     , event_timestamp_kst
     , event_name
     , ga_session_id
     , SUM(diff) OVER (ORDER BY event_timestamp_kst ROWS UNBOUNDED PRECEDING) + 1 AS new_session_id
FROM step2
ORDER BY event_timestamp_kst

/*
-- SQLite 버전
WITH T1 AS (
  SELECT event_timestamp_kst,
         COALESCE(LAG(event_timestamp_kst, 1) OVER(ORDER BY event_timestamp_kst), event_timestamp_kst) as lag_1,
         user_pseudo_id,
         ga_session_id,
         event_name
  FROM ga
  WHERE user_pseudo_id = 'a8Xu9GO6TB'
  ), T2 AS (
  SELECT user_pseudo_id,
         event_timestamp_kst,
         event_name,
         ga_session_id,
         CASE WHEN CAST(STRFTIME('%s', event_timestamp_kst) AS INTEGER) - CAST(STRFTIME('%s', lag_1) AS INTEGER) >= 600 THEN 1
           ELSE 0 END as diff
   FROM T1
  )
  SELECT user_pseudo_id,
         event_timestamp_kst,
         event_name,
         ga_session_id,
         SUM(diff) OVER(ORDER BY event_timestamp_kst ROWS UNBOUNDED PRECEDING) + 1 as new_session_id -- 누적합
  FROM T2
  ORDER BY 2
  */

 

✅step1 CTE:

사용자 'a8Xu9GO6TB’의 세션을 필터링한다.

이벤트 발생 시각을 기준으로 직전 발생 시각을 반환하며, NULL인 경우에 기존의 값을 반환하도록 한다.

 

✅step2 CTE:

이벤트 발생 시각과 직전 발생 시각 간의 차이를 초 단위로 계산하여 이 값이 600초(=10분)를 넘길 경우 1, 그렇지 않은 경우 0을 반환한다. -> 재정의한 세션 기준

 

✅최종 SELECT:

SUM 함수와 윈도우 함수를 사용하여 diff 컬럼의 누적합을 계산한다.

단, 재정의한 세션 ID는 1부터 시작하므로 기존 diff 값에 1을 더한다. 

 

(예시)

아래 이미지의 형광색 부분을 집중해보자!

 

왼쪽 : 기존 세션 ID인 'ga_session_id' 컬럼을 보면 10분이 넘지만 30분을 넘지 않는 경우에 같은 세션 ID를 갖고 있다.

오른쪽 : 10분 이상 행동하지 않은 경우 세션을 재정의하도록 가공했기 때문에 다른 세션 ID를 갖고 있다.