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

<윈도우 함수>

연습문제 1

SELECT
  user_id,
  visit_month,
  LEAD(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) as next_visit_month,
  LEAD(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) as two_next_visit_month,
FROM advanced.analytics_function_01
ORDER BY user_id

연습문제 2

SELECT
  user_id,
  visit_month,
  LAG(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) as prev_visit_month,
  LEAD(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) as next_visit_month,
  LEAD(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) as two_next_visit_month,
FROM advanced.analytics_function_01
ORDER BY user_id

추가문제 - 유저의 첫번째 방문월과 마지막 방문월 구하기

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
ORDER BY user_id

<FRAME 설정>

advanced.order 문제

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 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sum_by_user,
  AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) as last_5_orders_avg_amount,
FROM advanced.orders
ORDER BY order_id, user_id

<연습문제>

연습문제 1

SELECT
  *,
  COUNT(*) OVER(PARTITION BY user) as total_query_cnt
FROM advanced.query_logs
ORDER BY user

연습문제 2

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

연습문제 3

WITH base as (
  SELECT
    user,
    team,
    EXTRACT(WEEK FROM query_date) as week_number,
    COUNT(user) as query_cnt,
  FROM advanced.query_logs
  GROUP BY ALL
)
SELECT 
  *,
  LAG(query_cnt, 1) OVER(PARTITION BY user ORDER BY week_number) as prev_week_query_count
FROM base

연습문제 4

WITH base as (
  SELECT
    user,
    team,
    query_date,
    COUNT(user) as query_count,
  FROM advanced.query_logs
  GROUP BY ALL
)
SELECT
  *,
  SUM(query_count) OVER(PARTITION BY user ORDER BY query_date) as cumulative_query_count
FROM base
ORDER BY user

연습문제 5

-- SELECT
--   *,
--   IF(number_of_orders is NULL, prev_number_of_orders, number_of_orders) as result_number_of_orders,
-- FROM (
--   SELECT
--     *,
--     LAG(number_of_orders, 1) OVER(ORDER BY date) as prev_number_of_orders
--   FROM raw_data
-- )
-- 이 방법은 전의 값들이 Null이 여러개면 제대로 안나옴...!

SELECT
  *,
  IF(number_of_orders is NULL, last_number_of_orders, number_of_orders) as result_number_of_orders,
FROM (
  SELECT
    *,
    LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) as last_number_of_orders
  FROM raw_data
)

연습문제 6

SELECT
  *,
  AVG(result_number_of_orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING and CURRENT ROW) as moving_avg
FROM (
  SELECT
    *,
    IF(number_of_orders is NULL, last_number_of_orders, number_of_orders) as result_number_of_orders,
  FROM (
    SELECT
      *,
      LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) as last_number_of_orders
    FROM raw_data
  )
)

연습문제 7

WITH base as (
  SELECT
    event_date,
    event_timestamp,
    DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') as event_datetime,
    event_name,
    user_id,
    user_pseudo_id
  FROM advanced.app_logs
  WHERE event_date = '2022-08-18' and user_pseudo_id = '1997494153.8491999091'
),

base2 as (
  SELECT
    *,
    LAG(event_datetime, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) as before_event_datetime
  FROM base
)

SELECT
  *,
  SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) as session_id
FROM (
  SELECT
    *,
    IF(second_diff is NULL or second_diff > 20, 1, NULL) as session_start
  FROM (
    SELECT
      *,
      DATETIME_DIFF(event_datetime, before_event_datetime, second) as second_diff
    FROM base2
  )
)
ORDER BY event_timestamp

배운점&느낀점

1. EXTRACT(WEEK FROM query_date) as week_number : date 정보에서 week 정보 등을 추출할 수 있는 함수

2. DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') as event_datetime : timestamp 형태의 데이터를 날짜로 바꿀 수 있는 함수

3. LAST_VALUE(number_of_orders IGNORE NULLS)... 그냥 마지막 값은 LAST_VALUE, 이전 데이터 중 null값이 아닌 마지막 값을 가져오고 싶을 때는 IGNORE NULLS를 사용해야 함. (5번 연습문제에서 처음에는 LAG 함수를 사용해서 null이 여러 개 연속인 경우가 있어 제대로 값을 가져오지 못했고, LAST_VALUE 윈도우 함수에서IGNORE NULLS 를 까먹어서 null 처리가 또 안됐었다.)

4. With절과 서브쿼리 절을 어떤 상황에 사용할지 아직 잘 모르겠다.

5. CASE WHEN절 사용법이 익숙치 않았다. (문제를 풀 때 IF절을 사용했던 부분에서 강의에서는 CASE WHEN을 사용)

6. 윈도우 함수를 제대로 처음 배워봤는데, 어려웠지만 유용한 부분이 많아 사용할 일이 많을 것 같다.