SQL (LAG(), MAX(), TIMESTAMPDIFF, DENSE_RANK()) - 스터디 카페의 '집중 타임' 세션 정의하기

문제

당신은 무인 스터디 카페의 데이터 분석가입니다. 이 카페의 키오스크와 좌석에는 센서가 있어 회원이 어떤 행동(입실, 퇴실, 외출, 좌석 이동 등)을 할 때마다 로그가 기록됩니다.

 

하지만 로그 데이터만으로는 회원이 실제로 **'연속해서 공부한 시간(집중 타임)'**이 언제부터 언제까지인지 파악하기 어렵습니다. 회원이 잠깐 화장실을 다녀오거나(5분 이내), 물을 마시고 오는 등 짧은 시간 내에 다시 활동이 감지되면 같은 공부 세션으로 간주하고 싶습니다.

 

하지만 마지막 활동 이후 '1시간(60분)' 이상 기록이 없다면, 이전 공부는 끝났고 새로운 공부 세션이 시작된 것으로 정의하려고 합니다.

 

주어진 access_logs 테이블을 이용하여, 각 회원의 활동 기록을 '집중 타임 세션(session_id)' 별로 구분하는 SQL 쿼리를 작성해 보세요.

user_id	VARCHAR	회원 고유 ID
action_time	DATETIME	행동이 발생한 시각
action_type	VARCHAR	행동 유형 (입실, 외출, 복귀, 퇴실)

 

[요구 사항]

1. 세션 기준: 이전 action_time과 현재 action_time의 차이가 1시간(60분) 이상이면 새로운 세션으로 간주합니다. (1시간 미만이면 같은 세션)
2. 세션 ID 생성: 각 회원(user_id)별로 첫 번째 활동부터 시간 순서대로 1부터 시작하는 고유한 session_id를 부여하세요.
3. 결과 출력: user_id, session_id, action_time, action_type을 출력하고, user_id와 action_time 오름차순으로 정렬하세요.

 

[예시 데이터]

(user_id가 'A001'인 경우)

A001	2024-03-01 09:00:00	입실
A001	2024-03-01 09:50:00	외출
A001	2024-03-01 10:05:00	복귀 (이전과 15분 차이 -> 같은 세션)
A001	2024-03-01 13:00:00	입실 (이전과 약 3시간 차이 -> 새 세션)
A001	2024-03-01 13:30:00	퇴실

 

[예상 결과]

A001	1	2024-03-01 09:00:00	입실
A001	1	2024-03-01 09:50:00	외출
A001	1	2024-03-01 10:05:00	복귀
A001	2	2024-03-01 13:00:00	입실
A001	2	2024-03-01 13:30:00	퇴실

 

목표/접근법/사용함수

• 마지막 활동 이후 1시간 이상 기록이 없다면 새로운 세션으로 정의
  • LAG , TIMESTAMP(SECOND, 시작시각, 종료시각)
• 회원의 활동기록을 집중 타임 세션별로 구분
  • ROW_NUMVER(), CASE WEHN, MAX()

코드

WITH stats1 AS (
    SELECT user_id
            , action_time 
            , LAG(action_time, 1) OVER (PARTITION BY user_id ORDER BY action_time) AS prior
            , TIMESTAMPDIFF(SECOND, LAG(action_time, 1) OVER (PARTITION BY user_id ORDER BY action_time), action_time) AS seconds
            , ROW_NUMBER () OVER (PARTITION BY user_id ORDER BY action_time) AS id
            , action_type
    FROM access_logs),

stats2 AS (
    SELECT user_id
                    , MAX(CASE WHEN seconds >= 3600 THEN id 
                                         WHEN prior IS NULL THEN id
                                END) OVER (PARTITION BY user_id ORDER BY action_time)  AS new_session
                    , action_time
                    , action_type
    FROM stats1 )
			
SELECT user_id
		 , DENSE_RANK() OVER (PARTITION BY user_id ORDER BY new_session) AS session_id
		 , action_time
		 , action_type
FROM stats2
ORDER BY user_id, action_time

상세풀이

1. 각 레코드별 소요시간 계산: 직전 타임 - 현재 타임
2. 각 레코드별 고유 아이디 맵핑 (향후 세션별 구간을 나누기 위해)
3. 소요시간이 다음 조건을 만족하면 새로운 세션으로 정의
   - 60초 X 60분 = 3600초 이상이거나
   - prior 값이 NULL 인 경우 ( 그 전 시각 기록이 없다는 뜻이니까)
4. MAX() OVER ()를 활용해 각 session_id로 구획
5. 이를 DENSE_RANK()를 활용해 1씩 증가하는 정수로 변환

주의사항

• MAX() OVER (PARTITION BY ____ ORDER BY _____) : 누적 기법으로 세션 ID 부여 ⇒ 구획을 나눌 수 있게 됨
• DENSE_RANK() OVER(PARTITION BY ____ ORDER BY ____) : 동순위 처리, 연속 증가ID MAX DENSE_RANK 
• ID MAX DENSE_RANK

  ID	MAX	DENSE_RANK
  1	1	1
  	1	1
  	1	1
  4	4	2
  	4	2
  5	5	3