문제

advent S21 세션 유지 시간을 10분으로 재정의하기
(자세한 문제는 생략)

풀이 과정

문제/데이터 이해

event가 발생한 시간들이 기록되어 있다.

기존에는 시간의 차이가 30분 이상일 때 session_id가 변경되었는데
이걸 10분 이상 차이가 날 때 변경되도록 하고자 한다.
new_session_id는 1부터 step=1로 커지도록 한다

LAG 함수로 diff_time 구하기

LAG 함수를 사용하여 직전 timestamp를 가져오고
현재 timestamp와 직전 timestamp의 차인 diff_time을 구한다

MySQL이었다면 함수 하나로 뚝딱이 되었겠지만,
SQLite의 경우에는 율리시안 단위로 변환한 뒤 차를 구하고, 이걸 다시 '초'로 계산하기 위해 연산을 해주어야 한다
[SQLite] Date Diff를 구하는 방법

WITH now_next_time AS (
  SELECT
    user_pseudo_id
    , ga_session_id
    , event_timestamp_kst
    , event_name
    , LAG(event_timestamp_kst, 1) over() as "previous_time_stamp"
  FROM
    ga
  WHERE
    user_pseudo_id = 'a8Xu9GO6TB'
)
SELECT
  *  
  , CAST(
      (JULIANDAY(event_timestamp_kst)
      - JULIANDAY(previous_time_stamp))
      *24 *60 *60
    as INTEGER) as "diff_time"
FROM
  now_next_time
;

10분 이상일 때 1(True)로 인코딩

코드의 완성을 위해 2가지 하위 목표를 달성해야 한다
(1) diff_time이 10분(600초) 이상일 때마다 표시를 해줘야 한다
(2) diff_time이 True일 때마다 1씩 증가해야 한다

위의 2가지 하위 목표를 동시에 해결할 방법을 고민하던 중, 최근에 윈도우 함수를 사용했던 문제가 떠올랐다
참고: 이동평균 그 문제

윈도우 함수의 활용법 중 range도 있지만, "첫 행부터 현재 행까지"라는 조건을 거는 것이 가능하다
참고: [SQL][SQLD][SQLP] 윈도우 함수
이를 활용하여 첫 행부터 현재 행까지의 누적합을 계산한다면, 자연스럽게 diff_time이 발생할 때마다 1씩 증가하도록 코드를 구성할 수 있다.

그 첫 단계로, diff_time이 true일 때마다 1을 출력하도록 코드를 작성했다.

WITH now_next_time AS (
  SELECT
    user_pseudo_id
    , ga_session_id
    , event_timestamp_kst
    , event_name
    , LAG(event_timestamp_kst, 1) over() as "previous_time_stamp"
  FROM
    ga
  WHERE
    user_pseudo_id = 'a8Xu9GO6TB'
),
make_difftime AS (
  SELECT
    *  
    , CAST(
        (JULIANDAY(event_timestamp_kst)
        - JULIANDAY(previous_time_stamp))
        *24 *60 *60
      as INTEGER) as "diff_time"
  FROM
    now_next_time
)
SELECT
  user_pseudo_id
  , event_timestamp_kst
  , event_name
  , ga_session_id
  , CASE
      when diff_time >= 600 then 1
      else 0
    END as "check_ten_min"
FROM
  make_difftime
;

윈도우 함수로 합계 계산 : unbounded

이제 rows unbounded PRECEDING를 통해 누적합을 계산하도록 하자

WITH now_next_time AS (
  SELECT
    user_pseudo_id
    , ga_session_id
    , event_timestamp_kst
    , event_name
    , LAG(event_timestamp_kst, 1) over() as "previous_time_stamp"
  FROM
    ga
  WHERE
    user_pseudo_id = 'a8Xu9GO6TB'
),
make_difftime AS (
  SELECT
    *  
    , CAST(
        (JULIANDAY(event_timestamp_kst)
        - JULIANDAY(previous_time_stamp))
        *24 *60 *60
      as INTEGER) as "diff_time"
  FROM
    now_next_time
),
check_ten_minute AS (
  SELECT
    user_pseudo_id
    , event_timestamp_kst
    , event_name
    , ga_session_id
    , CASE
        when diff_time >= 600 then 1
        else 0
      END as "check_ten_min"
  FROM
    make_difftime
)
SELECT
  user_pseudo_id
  , event_timestamp_kst
  , event_name
  , ga_session_id
  , (sum(check_ten_min) over (rows unbounded PRECEDING))+1 as "new_session_id"
FROM
  check_ten_minute
;

정렬 조건 적용

사실 가장 먼저 했어야 하는 부분인데, 문제에서 “이벤트 발생 시각이 빠른 순서대로 정렬되어야 합니다”라는 정렬 조건을 언급하고 있다
맨 처음 QTE에서 ORDER BY를 추가해주도록 한다

WITH now_next_time AS (
  SELECT
    user_pseudo_id
    , ga_session_id
    , event_timestamp_kst
    , event_name
    , LAG(event_timestamp_kst, 1) over() as "previous_time_stamp"
  FROM
    (SELECT * FROM ga ORDER BY event_timestamp_kst) aa
  WHERE
    user_pseudo_id = 'a8Xu9GO6TB'
),
make_difftime AS (
  SELECT
    *  
    , CAST(
        (JULIANDAY(event_timestamp_kst)
        - JULIANDAY(previous_time_stamp))
        *24 *60 *60
      as INTEGER) as "diff_time"
  FROM
    now_next_time
),
check_ten_minute AS (
  SELECT
    user_pseudo_id
    , event_timestamp_kst
    , event_name
    , ga_session_id
    , CASE
        when diff_time >= 600 then 1
        else 0
      END as "check_ten_min"
  FROM
    make_difftime
)
SELECT
  user_pseudo_id
  , event_timestamp_kst
  , event_name
  , ga_session_id
  , (sum(check_ten_min) over (rows unbounded PRECEDING))+1 as "new_session_id"
FROM
  check_ten_minute
;