[TIL156] solvesql - 세션 재정의하기

세션 종료 기준 변경 (30분 → 1시간)

문제 설명

GA(Google Analytics)는 기본적으로 사용자가 일정 시간 동안 아무 행동도 하지 않으면 세션을 종료한다.

기존에는 30분 동안 행동이 없으면 세션이 종료되었지만, 블로그 기능이 추가되면서 사용자의 페이지 체류 시간이 길어졌기 때문에 세션 종료 기준을 1시간(60분)으로 변경하려고 한다.

사용자 S3WDQCqLpK의 이벤트 로그를 기준으로 다음 작업을 수행해야 한다.

• 이전 이벤트와 현재 이벤트의 시간 차이를 계산한다.
• 60분 이상 차이가 발생하면 새로운 세션으로 간주한다.
• 새롭게 정의된 세션별 시작 시각과 종료 시각을 구한다.
• 결과는 세션 시작 시각 기준 오름차순 정렬한다.

---

최종 정답 코드

-- 세션 종료 기준 : 1시간 행동
-- 사용자 'S3WDQCqLpK’의 세션을 재정의하고 로그 내 모든 세션의 시작 시각과 종료 시각을 출력하는 쿼리를 작성해주세요. 
-- 세션 시작 시각 기준으로 정렬
with a as (SELECT user_pseudo_id, event_timestamp_kst,
lag(event_timestamp_kst) over(partition by user_pseudo_id order by event_timestamp_kst) as prev_time
from ga
where user_pseudo_id = 'S3WDQCqLpK'
),
b as (SELECT *,
case when prev_time is null then 1 
when timestampdiff(minute, prev_time, event_timestamp_kst) >= 60 then 1 else 0 end as new_session
from a 
),
c as (SELECT *,
sum(new_session) over( partition by user_pseudo_id
  order by event_timestamp_kst) as new_session_cnt
from b 
)
SELECT user_pseudo_id, 
min(event_timestamp_kst) as session_start,
max(event_timestamp_kst) as session_end
from c 
group by user_pseudo_id, new_session_cnt
order by session_start

---

생각 흐름

Step 1. 이전 이벤트 시각 가져오기

현재 이벤트만 가지고는 세션이 끊겼는지 알 수 없다.

따라서 바로 이전 행동 시각을 가져와야 한다.

LAG(event_timestamp_kst)

---

Step 2. 이전 행동과의 시간 차이 계산

이전 행동과 현재 행동 사이가

60분 이상

이면 새로운 세션으로 간주한다.

 

사용 함수

TIMESTAMPDIFF(
    MINUTE,
    prev_time,
    current_time
)

 

 

Step 3. 새로운 세션 시작 여부 표시

CASE
    WHEN prev_time IS NULL THEN 1
    WHEN TIMESTAMPDIFF(...) >= 60 THEN 1
    ELSE 0
END

---

결과

new_session

09:00	1
09:10	0
09:25	0
11:00	1

---

의미

1 = 새로운 세션 시작
0 = 기존 세션 유지

---

Step 4. 세션 번호 생성

현재는

새 세션 시작 여부

만 알 수 있다.

실제 세션 번호를 만들기 위해 누적합을 사용한다.

SUM(new_session)
OVER(
    ORDER BY event_timestamp_kst
)

---

결과

시각new_sessionsession_no

09:00	1	1
09:10	0	1
09:25	0	1
11:00	1	2
11:30	0	2

---

핵심

새 세션이 시작될 때마다
세션 번호가 1 증가

---

Step 5. 세션별 시작/종료 시각 계산

이제 세션 번호가 생겼으므로

GROUP BY session_no

를 수행할 수 있다.

---

세션 시작

MIN(event_timestamp_kst)

---

세션 종료

MAX(event_timestamp_kst)

---

최종

GROUP BY user_pseudo_id,
         new_session_cnt

---

핵심 개념 정리

1. 이전 값 가져오기

LAG(컬럼)

---

2. 세션 분리 조건 만들기

CASE WHEN 시간차 >= 기준시간
THEN 1
ELSE 0
END

---

3. 누적합으로 그룹 번호 만들기

SUM(flag)
OVER(ORDER BY ...)

 

 

세션화(Sessionization) 문제에서 매우 자주 등장

---

기억할 패턴

LAG(timestamp)

→ 이전 이벤트

TIMESTAMPDIFF()

→ 시간 차이 계산

CASE WHEN diff >= 기준시간

→ 새 세션 표시

SUM(flag) OVER()

→ 세션 번호 생성

GROUP BY session_id

→ 세션 집계

---

한 줄 요약

이 문제는 Sessionization(세션 재구성) 문제이다. LAG()로 이전 행동 시각을 구하고, TIMESTAMPDIFF()로 시간 차이를 계산한 뒤, 60분 이상 공백이 발생하면 새로운 세션으로 표시한다. 이후 SUM(flag) OVER() 누적합으로 세션 번호를 생성하고 세션별 시작·종료 시각을 집계한다.