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

망고맛있어님의 프로필 이미지
망고맛있어

작성한 질문수

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

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

작성

·

44

·

수정됨

0

윈도우 탐색 함수 연습문제

(1) 연습문제 1

-- 문제 1) USER의 다음 접속월, 다다음 접속 월

SELECT 
  user_id,
  visit_month,
  LEAD(visit_month,1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_month, 
  LEAD(visit_month,2) OVER(PARTITION BY user_id ORDER BY visit_month) AS the_month_after_next
FROM 
  `avdanced.analytics_function_01`

(2) 연습문제 2

-- 문제 2) USER의 다음 접속월, 다다음 접속 월, 이전 접속 월
SELECT 
  user_id,
  visit_month,
  LEAD(visit_month,1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_month, 
  LEAD(visit_month,2) OVER(PARTITION BY user_id ORDER BY visit_month) AS the_month_after_next,
  LAG(visit_month,1) OVER(PARTITION BY user_id ORDER BY visit_month) AS last_month
FROM 
  `avdanced.analytics_function_01`

윈도우 함수 FRAME 연습문제

연습문제 (1~4)

SELECT 
  -- 1)모든 주문량 
  SUM(amount) OVER() AS amount_total,
  -- 2)특정주문시점에서 누적주문량
  #SUM(amount) OVER(partition by order_date) AS cumulative_sum,
  SUM(amount) OVER (ORDER BY order_date) AS cumulative_sum,
  -- 3)고객별 주문 시점에서 누적 주문량
  #SUM(amount) OVER(partition by user_id) AS cumulative_sum_by_user,
  SUM(amount) OVER(partition by user_id ORDER BY order_id) AS cumulative_sum_by_user,
  -- 4) 최근 직전 5개 평균 주문량 
  AVG(amount) OVER(ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amount, 
  AVG(amount) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS last_5_unbounded_orders_avg_amount, 
  AVG(amount) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS all_orders_avg_amount
FROM `avdanced.orders`

 

윈도우 함수

(1) 연습문제 1

-- 연습문제1) 사용자별 쿼리 실행 횟수
WITH base AS(
  SELECT 
    user,
    team,
    query_date,
    COUNT(*) OVER(PARTITION BY user) AS total_query_cnt,
  FROM 
    `avdanced.query_logs`
) 
SELECT
  *
FROM 
  base

(2) 연습문제 2

-- 연습문제2) 주차별 팀내 쿼리 실행한 수 (RANK 1만 보이도록)
WITH base2 AS(
  SELECT
    EXTRACT(WEEK FROM query_date) AS week_number,
    team,
    user,
    COUNT(*) OVER(PARTITION BY EXTRACT(WEEK FROM query_date) ,user ORDER BY EXTRACT(WEEK FROM query_date) ) AS query_cnt,

FROM 
    `avdanced.query_logs`
  ORDER BY EXTRACT(WEEK FROM query_date) 
)
  SELECT
    DISTINCT *,
    RANK() OVER(PARTITION BY team,week_number ORDER BY total_query_cnt DESC) AS team_rank
  FROM base2
  QUALIFY team_rank = 1
  ORDER BY week_number, team

강의자료의 코드

-- 2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요
-- 주차별로 개인당 실행한 쿼리 횟수
-- 위 쿼리 횟수를 기반으로 랭킹
-- 랭킹을 기반으로 필터링(랭킹=1)
-- 문제의 의도 : 원본 데이터 => 1 row마다 데이터가 있고, 그걸 집계해서 사용. GROUP BY => 윈도우 함수

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 : 윈도우 함수의 조건을 설정할 때 사용
QUALIFY rk = 1
ORDER BY week_number, team, query_cnt DESC

  • COUNT의 윈도우 함수 대신에 GROUP BY를 사용하는 풀이도 있다는 것을 알게 되었다! 너무 어렵게 생각하지 말기!

(3) 연습문제 3

WITH base2 AS(
  SELECT
    EXTRACT(WEEK FROM query_date) AS week_number,
    team,
    user,
    COUNT(*) OVER(PARTITION BY EXTRACT(WEEK FROM query_date) ,user ORDER BY EXTRACT(WEEK FROM query_date) ) AS query_cnt,

  FROM 
      `avdanced.query_logs`
  #QUALIFY team_rank = 1
  ORDER BY EXTRACT(WEEK FROM query_date) 
), base3 AS(
  SELECT
    DISTINCT *,
    RANK() OVER(PARTITION BY team,week_number ORDER BY query_cnt DESC) AS team_rank
  FROM base2
  QUALIFY team_rank = 1
  ORDER BY week_number, team
)

-- 연습문제3) 쿼리 실행 시점 1주전 쿼리 실행

SELECT 
  DISTINCT *,
  LAG(query_cnt,1) OVER(PARTITION BY user ORDER BY week_number) AS prev_week_query_count 
FROM 
  base2
GROUP BY ALL
ORDER BY user, week_number

(4) 연습문제 4

--연습문제4)
SELECT
  *, 
  SUM(query_count) OVER(PARTITION BY user ORDER BY query_date) AS culmulative_query_count,
  SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum2
FROM(
  SELECT 
    DISTINCT *,
    COUNT(user) OVER(PARTITION BY query_date, user) AS query_count,
  FROM `avdanced.query_logs`
)
ORDER BY user,query_date

  • QUALIFY 로 조건설정을 하여 두 값이 같은 지 비교하는 법이 인상깊었던 문제 (QUALIFY cumulative_sum != cumulative_sum2)

     

(5) 연습문제 5

나의 풀이

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
)
--연습문제 5) null에 이전 값 삽입
SELECT
  raw_data.date,
  IF(raw_data.number_of_orders IS NULL, LAG(raw_data.number_of_orders,1) OVER(ORDER BY date), raw_data.number_of_orders)
FROM raw_data

강의풀이

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


-- LAG로 직전 값 가져오면 되지 않을까?
  -- number_of_orders가 null이면, before_number_of_orders를 가져와라!
  -- 아래 쿼리는 어려운 방법
-- 그 다음 방법 : LAST VALUE를 쓰자! => 값이 없으면 NULL이 뜬다!
-- FIRST_VALUE, LAST_VALUE => NULL을 포함해서 연산
-- 출제 의도 : NULL을 제외해서 연산하고 싶으면 IGNORE NULLS을 쓰면 된다!

-- SELECT
--   *,
--   IF(number_of_orders IS NULL, before_number_of_orders, number_of_orders) AS filled_orders
--   -- Number of arguments does not match for function IF. Supported signature: IF(BOOL, ANY, ANY) at [89:3]
--   -- False일 때 인자를 추가하지 않아서 생긴 오류
-- FROM (
--   SELECT
--     *,
--     LAG(number_of_orders) OVER(ORDER BY date) AS before_number_of_orders
--   FROM raw_data
-- )

filled_data AS (
  SELECT
    * EXCEPT(number_of_orders), 
    LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS number_of_orders
  FROM raw_data
  -- Syntax error: Expected keyword DEPTH but got identifier "filled_data" at [104:6] : WITH문을 두개 작성했는데 WITH 쉼표 쓰고 구분!
)

  • LAST_VALUE, FIRST_VALUE를 사용하는 풀이 법에 대하여 알게 됨

(6) 연습문제 6

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
), null_is_lag AS(
  --연습문제 5) null에 이전 값 삽입
  SELECT
    raw_data.date,
    IF(raw_data.number_of_orders IS NULL, LAG(raw_data.number_of_orders,1) OVER(ORDER BY date), raw_data.number_of_orders) AS number_of_orders
  FROM raw_data
)
-- 연습문제 6) 이동평균
SELECT 
  *,
  AVG(nl.number_of_orders) OVER(ORDER BY nl.date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM null_is_lag AS nl

  • FRAME절을 사용할 때, AND를 기준으로 앞에는 뒤의 값보다 반드시 이전 행을 가리키는 구문이 와야 함!

(7) 연습문제 7

-- 1. TIMESTAMP → DATETIME
-- 2. SECOND_DIFF 생성 : uSER로 묶어서 -
-- 3. SESSION_START생성 : USER로 묶어서 LAG(DATA,1)이 NULL이면 1, SECOND_DIFF가 20이상이면 +1
-- 4. SESSION_ID생성: SESSION_START가 1일 경우 SESSION_ID +1, NULL일 경우 LAG(DATA,1)

WITH add_date AS (
-- 1. TIMESTAMP → DATETIME
  SELECT 
    event_date,
    event_timestamp,
    DATETIME(TIMESTAMP_MICROS(event_timestamp)) AS event_datetime,
    event_name,
    user_id, 
    user_pseudo_id,
    LAG(DATETIME(TIMESTAMP_MICROS(event_timestamp))) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS before_event_datetime
  FROM 
    `avdanced.app_logs_temp`
    --,UNNEST(event_params) AS param  -- FROM 절 안에서 UNNEST를 사용
  WHERE event_date ="2022-08-18" AND user_pseudo_id = "1997494153.8491999091"
), add_diff AS (
-- 2. SECOND_DIFF 생성 : uSER로 묶어서 -
SELECT 
  *,
  DATE_DIFF(event_datetime, before_event_datetime,SECOND) AS second_diff,
FROM add_date 
), add_session AS(
-- 3. SESSION_START생성 : USER로 묶어서 LAG(DATA,1)이 NULL이면 1, SECOND_DIFF가 20이상이면 +1  
  SELECT
  *, 
  IF(second_diff IS NULL OR second_diff >=20, 1, NULL) AS session_start
  FROM
    add_diff
)

-- 4. SESSION_ID생성
  *,
  SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_num
FROM add_session
ORDER BY event_datetime

  • user_id와 user_pseudo_id는 다르다. (계정이 존재하면 USER_ID, 없어도 USER_PSEUDO_ID를 통해 활동기록이 남는다.)

  • IF문은 행 단위로 작동, SUM은 특정 파티션에 대한 누적합을 계산함으로 아래 코드가 작동하지 않음


  SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_num

  • CTE를 사용하는 것과 서브쿼리를 사용하는 방법 중 상황에 맞게 적절히 혼용할 수 있다는 것을 알게 됨!

sqlGoogle-Analyticsfirebasegoogle-sheetsbigquery

답변 1

0

인프런 AI 인턴님의 프로필 이미지
인프런 AI 인턴

안녕하세요, 인프런 AI 인턴입니다. 망고맛있어님.

BigQuery 윈도우 함수와 관련하여 많은 연습 문제를 풀어보셨군요. 여러 SQL 쿼리를 직접 작성하면서 윈도우 함수의 다양한 활용 방식을 이해하시고 적용하신 것 같습니다.

각 연습 문제에서 사용한 LEAD, LAG, SUM 등의 윈도우 함수를 통해 데이터의 시계열 분석이나 이동 평균 등을 구할 때 유용하게 사용할 수 있습니다. 또한, PARTITION BY와 ORDER BY를 통해 데이터 그룹을 나누고 정렬하여 쿼리의 목적에 맞는 결과를 얻으셨습니다.

다음은 윈도우 함수와 관련된 유사한 질문과 답변을 확인할 수 있는 URL입니다. 이를 통해 추가적인 힌트나 팁을 얻으실 수 있을 것입니다.

  • 윈도우 함수 기본 개념과 활용
    이 질문은 윈도우 함수의 기본 개념과 활용 사례를 잘 설명해 줍니다. 다양한 사례를 통해 윈도우 함수에 대한 이해도를 높일 수 있습니다.

  • 윈도우 함수 활용법 및 자주 하는 실수
    윈도우 함수를 사용하면서 자주 저지르는 실수와 이를 방지하는 방법에 대해 배워볼 수 있습니다. 예시를 통해 문제 해결 능력을 기르실 수 있습니다.

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

망고맛있어님의 프로필 이미지
망고맛있어

작성한 질문수

전체 Q&A
질문하기