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

1. 탐색함수 연습문제

문제 1. user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리

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 lead_visit_month2
FROM `advanced.analytics_function_01`
ORDER BY user_id

문제2. user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리

SELECT user_id, visit_month,
lead(visit_month,1) 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 lead_visit_month2,
lag(visit_month,1) OVER (PARTITION BY user_id ORDER BY visit_month) AS lag_visit_month
FROM `advanced.analytics_function_01`
ORDER BY user_id, visit_month

# LAG 함수를 사용할 때 NULL 이면 그 값은 처음,
# LEAD 함수를 사용할 때 NULL 이면 그 값은 마지막

문제3. 유저가 접속했을 때, 다음 접속까지의 간격을 구하기

SELECT user_id, visit_month,
lead(visit_month,1) OVER (PARTITION BY user_id ORDER BY visit_month) AS lead_visit_month,
((lead(visit_month,1) OVER (PARTITION BY user_id ORDER BY visit_month) ) - visit_month) AS diff_month
# 별칭쓴거는 select 안에서 못함
FROM `advanced.analytics_function_01`
ORDER BY user_id, visit_month

# 이 경우에는 쿼리 많은경우에 수정할 경우 헷갈리기 시작한다. 따라서 서브쿼리로 묶어서 하면 더 편하고 실수 적어짐
# 쿼리 길어진다고 해도 무서워하지 말고 쿼리 덜 수정할 수 있는 구조를 만들자.
# 윈도우 함수 쓰면  줄이 쿼리 길어짐. 감안하고 쓰자.

문제 4. 이 데이터셋을 기준으로 user_id의 첫번째 접근 월을 구하는 쿼리를 작성하기

# 마지막 추가 문제
SELECT *,
FIRST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY  visit_month) AS First_Value,
LAST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY  visit_month) AS Last_Value
FROM (
        SELECT *,
        lead(visit_month,1) OVER (PARTITION BY user_id ORDER BY visit_month) AS lead_visit_month
        FROM `advanced.analytics_function_01`
        ORDER BY user_id, visit_month ) 
ORDER BY user_id, visit_month

2. Frame 연습문제

문제 . 회사의 모든 주문량, 누적 주문량, 최근 직전5개 평균 주문량 구하기

SELECT * ,
  SUM(amount) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total,
# OVER 안에 아무것도 없으면 전체 출력이다!
  SUM(amount) OVER (ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 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_avg
order by order_id

3. 연습문제

문제 1. 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성하기. 단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들기

SELECT 
   *,
   COUNT(user) OVER (PARTITION BY user ) AS cnt
FROM advanced.query_logs
ORDER BY query_date, user

문제 2. 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구하기. 단, 랭킹이 1등인 사람만 결과가 보이도록 하기

# query_date를 바탕으로 주차별로 구분하여 WITH함수로 묶기
WITH week_number_table AS (
  SELECT 
  *,
  EXTRACT(WEEK FROM query_date) AS week_number,
  # 하루에 유저별 쿼리 이용수 추출
  COUNT(*) AS query_cnt,
  FROM advanced.query_logs
  GROUP BY ALL
)

# 팀 내에서 유저별로 랭킹 구하기
SELECT  week_number, team, query_date, query_cnt,
RANK() OVER (PARTITION BY week_number,team ORDER BY query_cnt DESC) AS team_rank
FROM week_number_table
# 1등인 유저만 출력
QUALIFY team_rank = 1
ORDER BY week_number, team

문제 3. (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성하기

# 2번쿼리 
WITH week_number_table AS (
  SELECT 
  *,
  EXTRACT(WEEK FROM query_date) AS week_number,
  COUNT(*) AS query_cnt,
  FROM advanced.query_logs
  GROUP BY ALL
)

SELECT 
  *,
  # 1주전의 실행수 이므로 LAG를 이용해서 전 week_number의 실행수 구하기 
  LAG(query_cnt, 1) OVER(PARTITION BY user ORDER BY week_number) AS prev_week_query_count
  FROM week_number_table 

문제 4. 시간의 흐름에 따라 일자별로 유저가 실행한 누적 쿼리 수를 작성하기

# 시간의 흐름에 따라 일자별로 쿼리수 묶기
WITH user_query AS (
  SELECT 
    *,
    COUNT(*) AS query_count
  FROM advanced.query_logs
  GROUP BY ALL )

# 유저별로 누적 쿼리수 구하기
SELECT *,
SUM(query_count) OVER (PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_count
FROM user_query
ORDER BY user

# Frame 의 Default 값은  UNBOUNDED PRECEDING ~ CURRENT ROW 이다
# 데이터 정합성 확인 할때 2가지 값이 모두 같은지 비교하면 편리하다 ( WHERE, QUALIFY절에 활용 )

문제 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
)

# CASE문을 이용해서 주문량 NULL 값이면 전 날짜의 주문량으로 대치
SELECT *,
  (CASE WHEN number_of_orders IS NULL THEN LAG(number_of_orders,1) OVER (ORDER BY date) 
       ELSE number_of_orders END ) AS number_of_orders2
FROM raw_data 

# BUT 맨 마지막값 NULL이 연속2번이라 NULL이 나옴
# LAST_VALUE의 IGNORE NULLS 쓰기

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

문제 6. 5번 문제에서 NULL을 채운 후, 2일전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성하기(이동평균)

SELECT 
  date,
  number_of_orders2,
  AVG(number_of_orders2) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_agv
  # NULL값 처리한 테이블 서브쿼리로 묶은 다음에 윈도우 함수써서 2틀전~현재 평균 출력하기
  
  FROM (
    SELECT 
      *,  
      LAST_VALUE(number_of_orders IGNORE NULLS) OVER (ORDER BY date) AS number_of_orders2,
    FROM raw_data ) 

문제 7. app_logs 테이블에서 custom session을 만들기. 이전 이벤트 로그와 20초가 지나면 새로운 session을 만들기. session은 숫자로 (1, 2, 3 …) 표시해도 됨.

WITH START AS (
  SELECT
    event_date,
# 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-16'
),

# 기존 시간의 차이 구하기

DIFF_DATE AS (
  SELECT
    *,
    DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff
  FROM (
    SELECT
  # 전(LAG) 시간을 불러오기 위해 서브쿼리로 묶음
      *,
      LAG(event_datetime, 1 ) OVER ( PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_event_datetime
    FROM START)
  ORDER BY event_datetime)

# session_start를 누적합 이용하여 session_number 구하
SELECT 
  *,
  SUM(session_start) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_number
FROM (
SELECT 
  *,
# CASE문을 써서 처음 시작부분 (NULL) 1로 바꾸기
  (CASE WHEN second_diff IS NULL THEN 1 
#  second_diff 가 20초 이상이면 1 아니면 0 
       WHEN second_diff >= 20 THEN 1
       ELSE 0 END) AS session_start
        FROM DIFF_DATE)