[SQL] Yammer 검색 기능 개선방향 분석(Log, Session 구간 설정)

1️⃣ Percent of Sessions with Search Runs and Autocompletes

Q1. https://app.mode.com/benn/reports/c1039e799f77/runs/21a44c0d302d/embed

https://app.mode.com/benn/reports/c1039e799f77/runs/21a44c0d302d/embed

 

# 쿼리 한 줄씩 해석해보기 
# bounds 라는 서브쿼리 생성 > 
   # last_event : user_id로 구분 & 발생순으로 주어질 때, 해당 로그의 이전 이벤트 발생시간을 현 발생시간에서 뺀 시간 
   # next_event : user_id로 구분 & 발생순으로 주어질 때, 해당 로그의 이후 이벤트 발생시간에서 현 발생시간을 뺀 시간 
   # id : row_number 을 통해서 유니크값 부여 

SELECT user_id,event_type,event_name,occurred_at,
occurred_at - LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event,
ROW_NUMBER() OVER () AS id
FROM tutorial.yammer_events e
WHERE e.event_type = 'engagement'
ORDER BY user_id,occurred_at


# 세션의 기준 정하기
  # session : 10분이 넘어가거나 첫 이벤트인 경우 (last_event is null)  id 
  # 세션 기준이 잘 이해가 안감 > 10분이 넘지 않거나, 첫 이벤트 외에는 이전 id - 1?
  # WHERE last_event >= INTERVAL '10 MINUTE' OR next_event >= INTERVAL '10 MINUTE' OR last_event IS NULL
  # 이런 조건을 넣으면, 해당 세션의 첫 이벤트와 마지막 이벤트 > 세션 당 두개의 로그만 남음 

SELECT bounds.*,
CASE WHEN last_event >= INTERVAL '10 MINUTE' THEN id
     WHEN last_event IS NULL THEN id
     ELSE LAG(id,1) OVER (PARTITION BY user_id ORDER BY occurred_at) END AS session
  FROM (
					SELECT user_id,event_type,event_name,occurred_at,
					occurred_at - LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
					LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event,
					ROW_NUMBER() OVER () AS id
					FROM tutorial.yammer_events e
					WHERE e.event_type = 'engagement'
					ORDER BY user_id,occurred_at
          ) bounds
WHERE last_event >= INTERVAL '10 MINUTE' OR next_event >= INTERVAL '10 MINUTE' OR last_event IS NULL


# 유저아이디, 세션 번호별 시작 세션 시간 / 끝난 시간 구하기

SELECT user_id, session, MIN(occurred_at) AS session_start, MAX(occurred_at) AS session_end
  FROM (
SELECT bounds.*,
CASE WHEN last_event >= INTERVAL '10 MINUTE' THEN id
     WHEN last_event IS NULL THEN id
     ELSE LAG(id,1) OVER (PARTITION BY user_id ORDER BY occurred_at) END AS session
  FROM (
					SELECT user_id,event_type,event_name,occurred_at,
					occurred_at - LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
					LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event,
					ROW_NUMBER() OVER () AS id
					FROM tutorial.yammer_events e
					WHERE e.event_type = 'engagement'
					ORDER BY user_id,occurred_at
          ) bounds
WHERE last_event >= INTERVAL '10 MINUTE' OR next_event >= INTERVAL '10 MINUTE' OR last_event IS NULL
) final 
GROUP BY 1,2 

# AND e.occurred_at >= session.session_start AND e.occurred_at <= session.session_end
# 로그 발생시간이 해당 세션 시간 시작과 끝사이일 때, 이벤트에 대한 전체 내용과 세션번호, 세션 시작 시작 

SELECT e.*,
       session.session,
       session.session_start
  FROM tutorial.yammer_events e
  LEFT JOIN (
SELECT user_id, session, MIN(occurred_at) AS session_start, MAX(occurred_at) AS session_end
  FROM (
SELECT bounds.*,
CASE WHEN last_event >= INTERVAL '10 MINUTE' THEN id
     WHEN last_event IS NULL THEN id
     ELSE LAG(id,1) OVER (PARTITION BY user_id ORDER BY occurred_at) END AS session
  FROM (
					SELECT user_id,event_type,event_name,occurred_at,
					occurred_at - LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
					LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event,
					ROW_NUMBER() OVER () AS id
					FROM tutorial.yammer_events e
					WHERE e.event_type = 'engagement'
					ORDER BY user_id,occurred_at
          ) bounds
WHERE last_event >= INTERVAL '10 MINUTE' OR next_event >= INTERVAL '10 MINUTE' OR last_event IS NULL
) final 
GROUP BY 1,2 
) session
    ON e.user_id = session.user_id
   AND e.occurred_at >= session.session_start
   AND e.occurred_at <= session.session_end
 WHERE e.event_type = 'engagement'