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

양승엽님의 프로필 이미지
양승엽

작성한 질문수

BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)

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

작성

·

33

0

연습 문제

(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의 뒷 내용이 생각보다 조금 복잡해 한번에 떠오르지는 않았음

    • OVERORDER 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)

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

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

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

  • 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 일 : 크리스마스

      • 등등…

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

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

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

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

시간대에 따른 이용자 수

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

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

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

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

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

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

     

     

답변 1

0

안녕하세요, 인프런 AI 인턴입니다. 양승엽님께서 윈도우 함수와 관련된 SQL 쿼리 작성에 대해 질문해 주셨습니다. 이를 통해 SQL의 다양한 기능을 활용하여 데이터를 분석하고자 하시는 것으로 보입니다. 아래와 같은 기존의 유사한 질의 응답을 통해 도움이 될 만한 정보를 제공해 드리겠습니다.

  1. LEAD와 LAG 함수 활용: LEAD와 LAG 함수는 미래 또는 과거의 데이터를 쉽게 조회할 수 있도록 도와줍니다. 각각의 함수를 사용하는 방법에 대한 다양한 예제가 포함된 3-7 리텐션 SQL 쿼리 작성하기 연습 (Weekly, Monthly) 게시물을 참고하시어, 위에서 언급하신 문제가 어떻게 해결되어가는지 확인해 보세요.

  2. 윈도우 함수의 다양한 활용 사례: 윈도우 함수는 데이터를 움직이면서 계산 결과를 낼 때 매우 유용합니다. 특히, 누적합, 이동 평균, 데이터의 갭 분석 등을 수행할 수 있습니다. 이에 대한 보다 자세한 사용 사례는 퍼널별 유저들의 새로운 세션 시작 횟수의 총합 _ 윈도우 함수 마지막 문제 응용 게시물에서 확인할 수 있습니다. 여기서는 사용자의 세션 데이터를 윈도우 함수를 통해 어떻게 분석할 수 있는지를 설명합니다.

  3. QUALIFY와 PARTITION BY 활용: PARTITION BY를 통해 데이터의 그룹별로 누적 합이나 순위를 계산할 수 있습니다. 또한 QUALIFY 절은 데이터 필터링 시 더욱 유용하게 쓸 수 있지요. 관련 예제는 Bigquery 활용편(PDF)파일 P.229 페이지 코드 오류에서 찾을 수 있으며, 이 자료에서 논의된 문법을 실습하며 이해를 깊이 하실 수 있습니다.

질문에 포함된 쿼리와 관련된 문제 해결에 도움이 되었기를 바랍니다. 양승엽님에게 좋은 학습의 기회가 되길 바라며, 추가적으로 궁금한 점이 있으시면 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏

양승엽님의 프로필 이미지
양승엽

작성한 질문수

질문하기