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

윈도우 함수 - 탐색함수 연습 문제

1번

-- 1번. user들의 다음 접속 월과 다다음 접속 월을 구해라.
SELECT
  user_id,
  visit_month,
  LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_after_one_month,
  LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_after_two_month
FROM advanced.analytics_function_01
ORDER BY user_id;
-- LEAD 함수는 항상 ORDER BY가 필요함!=> LEAD는 순서를 알아야 동작하는 함수이기 때문에~!!

2번

-- 2번. user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구해라.
SELECT
  user_id,
  visit_month,
  LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_after_one_month,
  LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_after_two_month,
  LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_before_one_month
FROM advanced.analytics_function_01
ORDER BY user_id;

3번

-- 3번. user가 접속했을 때, 다음 접속까지의 간격을 구해라.
-- LAG으로 이전 월을 구한 뒤, 현재 행과 뺄셈 진행하기.. (방문 텀을 구하는 것이므로.. 현재 달에 이전 월을 빼는게 더 맞는 방향이라 생각함....!)
WITH base AS(
  SELECT
    *,
    LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS before_visit_month
  FROM advanced.analytics_function_01
)

SELECT 
  *,
  (visit_month - before_visit_month) AS visit_interval
FROM base;
-- 피연산자에 null값이 포함되어 있으면 해당 연산의 결과는 NULL이 된다.

-- 3번을 서브쿼리 없이 짠다면..
-- SELECT 
--   *,
--   LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS before_visit_month,
--   visit_month - LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS gap
-- FROM advanced.analytics_function_01
-- 하지만!! 여기서 만약 before_visit_month에 대한 내용을 수정한다고하면.. before_visit_month와 gap 두 부분을 수정해야한다... => 과연 괜찮은 쿼리일까?
-- 쿼리가 길어지는 것을 무서워하지 말고, 쿼리를 덜 수정할 수 있는 구조를 만드는게 더 좋음!!!

추가문제

-- 추가문제
-- 이 데이터셋을 기준으로 user_id의 첫 번째 방문 월, 마지막 방문 월을 구해라.
SELECT 
  *, 
  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

윈도우 함수 - FRAME 연습 문제

1번

-- 1)우리회사의모든주문량은?
--  amount_total
SELECT 
  *,
  SUM(amount) OVER (ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total
FROM advanced.orders;

2번

--  2)특정주문시점에서누적주문량은?
--  cumulative_sum
SELECT 
  *,
  SUM(AMOUNT) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
FROM advanced.orders;

3번

--  3)고객별주문시점에서누적주문량은?
--  cumulative_sum_by_uesr
SELECT 
  *,
  SUM(AMOUNT) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum_by_uesr
FROM advanced.orders;

4번

--  4)최근직전5개의평균주문량은?
--  last_5_orders_avg_amount
SELECT 
  *,
  SUM(AMOUNT) OVER (ORDER BY order_date ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amount
FROM advanced.orders;

-- 정석 답
SELECT
  *,
  SUM(amount) OVER() AS amount_total,
  SUM(amount) OVER(ORDER BY order_id) AS cumulatvie_sum,
  SUM(amount) OVER(PARTITION BY user_id ORDER BY order_id) AS cumulatvie_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 user_id, order_date;

윈도우 함수 연습 문제

1번

# 1. 사용자별 쿼리를 실행한 총 횟수를 구해라.
SELECT
  *,
  COUNT(*) OVER (PARTITION BY user) AS total_query_cnt
FROM advanced.query_logs;

2번

# 2. 주차별로 팀 내에게 쿼리를 가장 많이 사용한 사람들 보여주세요.
-- week_number | team | user | query_cnt | team_rank
-- week_number는 format_date("%U", query_date) 이용 
-- 팀 내에서 쿼리를 가장 많이 사용한 사람..
-- 한 사람은 하나의 팀에만 속해있음
-- => Rank구할 때 PARTITION으로 팀으로 나누고 ORDER BY 에 query_cnt한 다음에.. 근데 이때 주차로도 나눠져야함.... PARTITION에 두 가지가 가능할까? => 애초에 주차별로 나눠서 count했어야 함..!
-- 혼자 풀어본 쿼리
WITH base AS (
  SELECT 
    *,
    FORMAT_DATE("%U", query_date) AS week_number,
    COUNT(*) OVER (PARTITION BY user, FORMAT_DATE("%U", query_date)) AS query_cnt
  FROM advanced.query_logs
)

SELECT
  DISTINCT
  week_number,
  team,
  user,
  query_cnt,
  RANK() OVER (PARTITION BY team ORDER BY query_cnt DESC) AS team_rank
FROM base
QUALIFY team_rank = 1
ORDER BY week_number, team;

-- 문제 의도.. 에 철저하게 당했(?)다
-- window연습문제니까 window만 생각하게 됨...! 

-- 2번 정석 정답
-- 팀, 주차별 실행 횟수 count하기
-- 위의 결과를 바탕으로 랭킹 구한 뒤, QUALFY로 1등만 뽑기
-- WITH count_of_week AS (
--   SELECT
--     EXTRACT(WEEK FROM query_date) AS week_number,
--     team,
--     user,
--     COUNT(query_date) 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 count_of_week
-- QUALIFY team_rank = 1
-- ORDER BY week_number, team

3번

# 3번. 2번 문제에서 사용한 주차별 쿼리를 사용하여 쿼리 실행시점 기준 1주 전 쿼리 실행 수를 별도의 컬럼을 확인할 수 있는 쿼리 작성하기
-- user | team | week_number | query_count | prev_week_query_count
-- from count_of_week
-- LAG(query_count) OVER (PARTITION BY user ORDER BY week_number)
SELECT 
  user,
  team,
  week_number,
  query_cnt,
  LAG(query_cnt) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_qeury_cnt
FROM count_of_week
ORDER BY user, week_number;

4번

# 4번. 시간의 흐름에 따라 일자별로 유저가 실행한 누적 쿼리수를 작성해라.
-- count
-- frame설정 - ROWS UNBOUNDED PRECEDING AND CURRENT ROW
-- 혼자 풀어본 리
SELECT 
  *,
  COUNT(user) OVER (PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_cnt
FROM advanced.query_logs
ORDER BY user, query_date;
-- 일자별 흐름. 일자별 누적합.. 먼저 "일자별"로 합을 구해뒀어야했음.

-- 정석 쿼리
-- SELECT 
--   *,
--   SUM(query_count) OVER (PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_cnt
-- FROM (
--   SELECT
--     *,
--     COUNT(*) AS query_count
--   FROM `advanced.query_logs`
--   GROUP BY ALL)
-- ORDER BY user, query_date 

5번

# 5번. 다음 데이터는 주문 횟수를 나타낸 데이터임. 만약 주문 횟수가 없으면 NULL이 기록된다. NULL로 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해라.

-- 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
-- ), filled_null AS (SELECT
--   date,
--   IF(number_of_orders IS NULL, LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), number_of_orders) AS number_of_orders
-- FROM raw_data)
-- frame을 위와 이 설정해주지 않아도 됐음!!
-- 어짜피 null값을 제외한 마지막 값을 리턴하니까..!!!!!

6번

# 6번. 5번 문제에서 null을 채운 후, 2일 전~ 현재 데이터의 평균을 구하는 쿼리를 작성해라.
SELECT
  *,
  AVG(number_of_orders) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM filled_null

7번

# 7번. app_logs 테이블에서 custom session을 만들어라.이전 이벤트 로그와 20초가 지나면 새로운 session을 만들 것. 
-- event_date | event_timestamp | event_datetime | event_name | user_id | user_pesudo_id | before_event_datetime | second_diff | session_start | session_id
-- event_datetime, before_event_datetime, second_diff, session_start, session_id 컬럼을 생성해야한다.
-- timestamp_micros()이용하여 timestamp만들고 datetime으로 'Asia/Seoul'
-- LAG로 before_datetime
-- datetime_diff 함수로 secound_diff
-- session_start가 1 아니면 null
-- 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,
--     DATETIME(timestamp_micros(LAG(event_timestamp) OVER (ORDER BY event_timestamp)), 'Asia/Seoul') AS before_event_datetime,
--   from advanced.app_logs  
--   where event_date = '2022-08-18'
--     AND user_pseudo_id = 
--     '1997494153.849199901'
-- )
-- SELECT
--   *,
--   datetime_diff(event_datetime, before_event_datetime, second) AS second_diff,
--   IF(datetime_diff(event_datetime, before_event_datetime, second) >= 20, 1, NULL) AS session_start
-- from base
-- ORDER BY event_timestamp
-- 첫 번째 값의 NULL값 처리 못함
-- session_id 설정 못함..

-- 정석 쿼리
WITH base AS (
    SELECT
    event_date,
    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'
), diff_data AS(
  SELECT 
    *,
    DATETIME_DIFF(event_datetime, prev_event_datetime, SECOND) AS second_diff
  FROM (
    SELECT
      *,
      LAG(event_datetime, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS prev_event_datetime,

    FROM base
  )
)

SELECT
  *,
  SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_id
FROM (
  SELECT
      *,
    CASE
      WHEN prev_event_datetime IS NULL THEN 1
      WHEN second_diff >= 20 THEN 1
      ELSE NULL
      END AS session_start
  FROM diff_data
)
ORDER BY event_datetime

기억할 것

• 윈도우 함수에 ORDER BY를 설정한다고 쿼리 결과값이 정렬되는 것은 아니다.(단지 함수를 위한 정렬일 뿐!) FROM 다음에 ORDER BY를 써줘야 쿼리 결과가 정렬되어 나타난다.

• frame 설정 없이도 기본값은 UNBOUNDED PRECEDING AND CURRENT ROW와 값이 같다. 하지만, 명시적 표현으로 해주는 것이 더 좋다

• LAG의 옵션으로 IGNORE NULLS는 없다. NULL을 구분하여 처리하고싶다면 FIRST_VALUE, LAST_VALUE 등의 함수를 이용해야 한다.

• 세션을 구하려면 쿼리가 많아지는건 어쩔 수 없는 일!
  (쿼리가 많아진다고 겁 먹지 말고 일단 단계별로 진행해보자)

• 하루 방문 횟수가 많다 하면 세션 기반이 좋을 수 있고 아니라면 그냥 일자별 유저 집계가 나을 수도 있다. (session 범위 기준은 데이터를 탐색하며 결정)

• time_diff 함수 사용할 때, 입력되는 데이터 타입에 맞춰 함수 이름을 써줘야 한다.
  (입력되는 데이터 타입이 timestamp라면 timestamp_diff 함수를 사용해야 하고, datetime이라면 datetime_diff 함수를 사용해야 한다.)

• '어떤 타이밍에 서브쿼리를 이용하는 것이 좋은 타이밍일까...' 를 고민했는데, 그냥 써야할 때 쓰면 되겠다고 생각했다. (코드가 길어지는 것을 두려워하지 말자~!)