인프런 커뮤니티 질문&답변

1. 윈도우 함수 탐색 함수 연습 문제

1-1) 유저의 다음 접속 월과 다다음 접속 월 구하기

SELECT
  user_id
  , visit_month
  , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS lead_visit_month
  , LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS lead2_visit_month
FROM advanced.analytics_function_01

1-2) 이전 접속월 구하기

SELECT
  user_id
  , visit_month
  , LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS before_visit_month
FROM advanced.analytics_function_01

1-3) 다음 접속 월까지의 간격 구하기

SELECT 
  *
  , after_visit_month - visit_month AS diff_month
FROM (
  SELECT
    user_id
    , visit_month
    , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month
  FROM advanced.analytics_function_01
)

1-4) 첫번째와 마지막 방문 월 구하기

SELECT
  user_id
  , visit_month
  , FIRST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS first_visit_month
  , LAST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS last_visit_month
FROM advanced.analytics_function_01

2. 윈도우 함수 Frame 연습 문제

SELECT 
  *
  , SUM(amount) OVER () AS amount_total
  , SUM(amount) OVER (ORDER BY order_id) AS cumulative_sum
  , SUM(amount) OVER (PARTITION BY user_id ORDER BY order_id) AS user_cumulative_sum
  , AVG(amount) OVER (ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_avg
FROM advanced.orders

3. 윈도우 함수 연습 문제

1-1) 사용자 별 쿼리를 실행한 총 횟수 구하기

SELECT 
  user
  , team
  , query_date
  , COUNT(*) OVER(PARTITION BY user) AS total_query_cnt
FROM advanced.query_logs
ORDER BY user, query_date. -- 검증

1-2) 주차 별로 팀 내에서 쿼리를 많이 실행한 수, 단 랭킹이 1등인 경우만 구하기

SELECT
  week_number
  , team
  , user
  , query_cnt
  , RANK() OVER(PARTITION BY week_number, team ORDER BY query_cnt DESC) AS team_rank 
FROM (
  SELECT
    EXTRACT(WEEK FROM query_date) AS week_number
    , team
    , user
    , COUNT(query_date) AS query_cnt
  FROM advanced.query_logs
  GROUP BY ALL
)
QUALIFY team_rank=1
ORDER BY week_number

1-3) 쿼리 실행 시점 기준 1주 전 쿼리 실행 수 구하기

WITH query_cnt_by_team AS (
  SELECT
    EXTRACT(WEEK FROM query_date) AS week_number
    , team
    , user
    , COUNT(query_date) AS query_cnt
  FROM advanced.query_logs
  GROUP BY
    EXTRACT(WEEK FROM query_date) 
    , team
    , user
)
SELECT
  *
  , LAG(query_cnt) OVER(PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt 
FROM query_cnt_by_team
ORDER BY user

1-4) 시간의 흐름에 따라 일자 별 유저가 실행한 누적 쿼리 수 구하기

WITH query_cnt_by_user AS (
  SELECT
    user
    , team
    , query_date
    , COUNT(query_date) AS query_count
  FROM advanced.query_logs
  GROUP BY ALL
)
SELECT
  user
  , team
  , query_date
  , query_count
  , SUM(query_count) OVER(PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_cnt  
  -- , SUM(query_count) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_query_cnt2
FROM query_cnt_by_user
-- 검증
-- QUALIFY cumulative_query_cnt != cumulative_query_cnt2
ORDER BY user, query_date

*집계 분석 함수를 사용하고 ORDER BY가 있는 경우 Frame의 디폴트 값은 UNBOUNDED PRECEDING ~ CURRENT ROW 이다.

1-5) 값이 null인 경우 직전 값으로 채우기

null 값이 2번 연속 있는 경우가 있어 COALESCE를 사용했습니다.

WITH raw_data AS (
  SELECT DATE '2024-05-01' AS date, 15 AS number_of_orders UNION ALL
  SELECT DATE '2024-05-02', 13 UNION ALL
  SELECT DATE '2024-05-03', NULL UNION ALL
  SELECT DATE '2024-05-04', 16 UNION ALL
  SELECT DATE '2024-05-05', NULL UNION ALL
  SELECT DATE '2024-05-06', 18 UNION ALL
  SELECT DATE '2024-05-07', 20 UNION ALL
  SELECT DATE '2024-05-08', NULL UNION ALL
  SELECT DATE '2024-05-09', 13 UNION ALL
  SELECT DATE '2024-05-10', 14 UNION ALL
  SELECT DATE '2024-05-11', NULL UNION ALL
  SELECT DATE '2024-05-12', NULL
)
SELECT
  date
  -- , number_of_orders
  , COALESCE(
      number_of_orders
      , LAG(number_of_orders, 1) OVER(ORDER BY date)
      , LAG(number_of_orders, 2) OVER(ORDER BY date)
    ) AS number_of_orders
FROM raw_data

성윤님의 풀이를 보고 LAST_VALUE + IGNORE NULLS를 사용하면 연속되는 null의 수에 상관없이 직전 값을 채울 수 있어 깔끔하다는 생각이 들었습니다!

SELECT
  *
  , LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS last_value_orders
FROM raw_data

1-6) 같은 데이터 셋에서 1일 전 부터 현재까지의 평균 구하기

SELECT
  *
  , AVG(last_value_orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM (
  SELECT
    date
    , LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS last_value_orders
  FROM raw_data
)

1-7) 20초를 기준으로 세션 아이디 구하기

WITH base AS (
  SELECT 
    event_date
    , user_pseudo_id
    , event_name
    , DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM advanced.app_logs
  WHERE user_pseudo_id='1997494153.8491999091'
    AND event_date="2022-08-18"
  ORDER BY event_timestamp
)
, base_second_dff AS (
  SELECT
  *
  , DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff
  FROM (
    SELECT 
      *
    , LAG(event_datetime) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) before_event_datetime
    FROM base
  )
)
, result AS(
SELECT 
  *
  -- CASE 문 사용 가능
  , IF(second_diff - before_second_diff > 20 OR second_diff IS NULL, 1, null) AS session_start
FROM (
  SELECT
    *
    , LAG(second_diff) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_second_diff
  FROM base_second_dff
)
)
SELECT
  *
  , SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_id
FROM result

✔ 느낀 점

• 프레임은 처음 보는 개념이라 초반에는 어색했지만 문제를 풀면서 익숙해질 수 있었다. 누적합을 구할 때 등 유용하게 사용할 것 같다.

• "쿼리가 길어지는 것을 무서워하지말고 쿼리를 잘 수정할 수 있는 구조를 만들자" 일할 때에도 동료들이 수정하고 테스트하기 쉬운 구조인가?를 생각해보자.

• CTE의 이름을 짓는건 언제나 고민된다. 서브쿼리를 적절하게 사용하자!

✔ 새롭게 알게된 점

• ROW_NUMBER를 사용할 때 중복값이 있으면 순서가 보장되지 않을 수 있다. 이때는 OVER 안에서 id를 사용하자.

• FIRST_VALUE는 MIN과 같이 동작하지만, LAST_VALUE는 MAX가 아니다.

• 전체 집계 값을 구하고 싶을 때에는 OVER 안에 아무것도 안 쓰면 된다.

• 윈도우 함수에 조건을 걸고 싶을 땐 QUALIFY를 사용하자.

   

• 연속된 NULL값을 채우는 경우 LAST_VALUE와 IGNORE NULLS조합을 사용하자.