[인프런 빅쿼리 빠짝스터디 2주차] 윈도우 함수, FRAME 설정, QUALIFY

연습 문제

(1) 윈도우 함수

-- 1) user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요.
SELECT
  user_id,
  visit_month,
  LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month_1,
  LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month_2,
FROM `advanced.analytics_function_01`
ORDER BY user_id

• LEAD 함수와 적절한 수를 이용해 작성

• LEAD 함수에 들어가는 인자에 따라 다음인지 다다음인지 정할 수 있음

  • 따로 정해주지 않을 경우 1로 인식

• OVER의 뒷 내용이 생각보다 조금 복잡해 한번에 떠오르지는 않았음

  • OVER의 ORDER BY의 기본은 오름차순

     

-- 2) user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요.
SELECT
  user_id,
  visit_month,
  LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month_1,
  LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month_2,
  LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS previous_visit_month_1,
FROM `advanced.analytics_function_01`
ORDER BY user_id

• LAG 사용해 이전 값 구해봄

• LAG 안에 특별한 숫자를 주지않으니 1로 인식하는 것을 확인함

• LEAD 값이 NULL → 해당 값이 마지막 값

• LAG 값이 NULL → 해당 값이 첫번째 값

-- 3) user의 다음 접속까지의 간격을 구하시오
SELECT
  user_id,
  visit_month,
  LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month_1,
  (LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) - visit_month) AS diff_month,
FROM `advanced.analytics_function_01`
ORDER BY user_id

• SELECT 절에서 만들어진 컬럼은 그대로 사용할 수는 없음

• 하지만 위와 같이 할 경우 너무 길어지고 복잡해짐(중복됨) → 서브쿼리 사용

   

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

• 쿼리를 최대한 덜 수정하는 방향으로 작성해 볼 것

(2) QUALIFY

-- amount_total : 전체 SUM
-- cumulative_sum : row 시점에 누적 SUM
-- cumulative_sum_by_user : row 시점에 유저별 누적 SUM
-- last_5_orders_avg_amount : order_id 기준으로 정렬하고, 직전 5개 주문의 평균 amount

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 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

• BETWEEN 앞에 ROWS를 빼먹어서 계속 오류를 냈음

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

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

• 데이터에 NULL값이 없으므로 어떤 열을 세던지 상관 없음

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

SELECT
  *,
  RANK() OVER(PARTITION BY team, week_key ORDER BY cnt_by_user DESC) AS rnk
FROM (
  SELECT
    user,
    team,
    IF(query_date < '2024-05-01', 1, 2) AS week_key,
    COUNT(user) AS cnt_by_user
  FROM advanced.query_logs
  GROUP BY ALL
)
QUALIFY rnk = 1
ORDER BY team, week_key

• '주차별’에 대한 아이디어가 잘 떠오르지 않았음
  → 데이터의 범위가 좁기 때문에 일단은 IF를 통해 주차를 구분해줌
  → 날짜 범위가 넓어지면 어떻게 할지 아직은 모르겠음

• 서브 쿼리 사용해봄

• PARTITION이 2개임(주차별, 팀별)

• QUALIFY 사용할 것(생각 못하고 LIMIT 쓰려다 막힘)

-- 강의 코드
WITH query_cnt_by_team 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 rk
FROM query_cnt_by_team
QUALIFY rk = 1
ORDER BY week_number, team, query_cnt

• EXTRACT 함수 통해 ‘주차’ 추출

• GROUP BY 후 윈도우 함수 사용 → 유연하게 사용할 것

-- 3) (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요
SELECT
  *,
  LAG(query_cnt) OVER(PARTITION BY user ORDER BY week_number) AS previous_week_cnt
FROM query_cnt_by_team
ORDER BY user

• WITH AS로 만든 테이블 그대로 사용

-- 4) 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요

SELECT
  *,
  SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumul_cnt
FROM (
  SELECT
    user,
    team,
    query_date,
    COUNT(*) AS query_cnt
  FROM advanced.query_logs
  GROUP BY ALL
)
ORDER BY user, query_date

• GROUP BY한 서브쿼리 사용

• FRAME 사용이 그렇게 까다롭진 않았음

• FRAME의 defalut 값 → UNBOUNDED PRECEDING ~ CURRENT ROW

-- 5) 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 
--    이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요

SELECT 
  date,
  IF(number_of_orders IS NULL, LAG(raw_data.number_of_orders, 1) OVER(ORDER BY date), number_of_orders) AS number_of_orders
FROM raw_data

• 조건문 사용해서 IS NULL인 값들만 LAG 사용

• 기존의 number_of_orders가 사라지는 문제가 있음

• 마지막 날짜는 안채워짐(연속으로 NULL이라)

   

-- 강의 코드

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

• LAST_VALUE + IGNORE NULLS 사용

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

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

• 서브쿼리 사용했지만 WITH로 정의해도 됨
  → 문제에서는 WITH가 연속 두번 나오는데, WITH는 한번만 작성해도 됨(, 로 구분)

-- 7) app_logs 테이블에서 Custom Session을 만들어 주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요. 
--    Session은 숫자로 (1, 2, 3 ...) 표시해도 됩니다

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")
  ORDER BY event_timestamp
)

SELECT
  *,
  SUM(diff_classification) OVER(ORDER BY event_datetime) + 1 AS session_id
FROM (
  SELECT
    *,
    IF(DATETIME_DIFF(event_datetime, before_datetime, second) > 20, 1, 0) AS diff_classification
  FROM (
    SELECT
      *,
      LAG(event_datetime) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_datetime,
    FROM base
  )
)
ORDER BY event_datetime

• timestamp와 1초의 관계 : timestamp 1당 1초 아닌가? → 그렇다면 굳이 datetime으로 바꿀 필요가 있나

• 서브쿼리 2번 중첩해서 사용

• IF문 사용해 20초 차이남 → 1
  차이 안남 → 0 으로 파생해 누적합 + 1로 session_id 도출

• 첫 행 before_datetime에 예외처리 해줄 것

• 그런데 PARTITION BY 를 매 OVER 안에 무조건 써야 하나?

EDA(2)

요일별 접속자 수 + 기간 내 이벤트날

• 요일별 접속자 평균을 내보자

• 일 > 토 > 수 > 금 > 목 > 화 > 월
  → 역시 쉬는날이 더 배달 수요가 많은건가?
  → 수요일은 왜 일까?

• 2022-08-01(월) ~ 2023-01-20(금) 의 데이터

  • 주말(토, 일)이 아닌 공휴일 목록 → 네이버 캘린더 참조

    • 2022-08-15 월 : 광복절

    • 2022-09-09 금 : 추석연휴

    • 2022-09-12 월 : 추석연휴

    • 2022-10-03 월 : 개천절

    • 2023-01-23 월 : 설 연휴

    • 2023-01-24 화 : 설 연휴(대체공휴일)

  • 주요 이벤트 → 특정 사건이 있을 경우 배달 수요가 늘거나 줄지 않을까? / 위키 사이트 참조

    • 2022-08-02 화 : 코로나19 누적 감염자 2천만 명 돌파

    • 2022-08-08 월 : 수도권 기록적인 폭우 및 홍수

    • 2022-10-31 월 : 할로윈데이 / 2022-10-29 토에 이태원 압사 사고 발생

    • 2022-11-08 화 : 한국시리즈

    • 2022-11-17 목 : 2023학년도 대학수학능력시험

    • 2022-11-24 목 : 카타르 월드컵 vs 우루과이

    • 2022-11-28 월 : 카타르 월드컵 vs 가나

    • 2022-12-03 토 : 카타르 월드컵 vs 포르투갈

    • 2022-12-19 월 : 카타르 월드컵 결승

    • 2022-12-25 일 : 크리스마스

    • 등등…

• “특정 사건”의 중요도를 정성적으로 평가하기에는 어렵다고 느낌
  → 오히려 반대로 갑자기 이용자 수의 변화가 급격하게 나타나는 날짜를 위주로 봐야하나?
  → 그런데 앱 이용자 수 성장 시기에는 항상 상승만해서 보기 애매할 듯
  → 요일별 경향을 따져야 할 수도

• 일요일 < 월요일(개천절) < 화요일 → 앱 이용자 수 상승 예시

• 월드컵에 따른 이용자 수 변화는 뚜렷하지 않음

• 수요일에 딱히 뭔가 보이진 않음
  → 평일의 절반이 지남에 따른 보상 심리가 원인일수도?

시간대에 따른 이용자 수

• 마찬가지로 시간대별 평균 이용자 수 구해봄

• 저녁 시간대(19시 ~ 22시)가 가장 이용자 수 많음

• 점심 시간대(12시 ~ 14시)가 그 다음

• 당연하게도 식사 시간에 배달 앱 수요가 많음

• 시간대 별로 food_id 에 따른 수요가 다른지 확인해볼 것 → 시간대 별 이용자에게 추천해주는 음식 다르게 설정할 수 있음

• 하루의 기준을 0시로 잡아도 되나?에 대한 의문이 생김(야식 수요) → 이거에 대한 기준을 다시 잡고 일별 이용자 수 다시 구해야 할수도