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

DA유치원님의 프로필 이미지
DA유치원

작성한 질문수

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

[빠짝스터디 2주차 과제] 윈도우 함수 연습 문제

작성

·

24

·

수정됨

0

1. 윈도우 함수 연습문제 풀이

문제 1)

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

SELECT  
  user,
  team,
  query_date,
  COUNT(user) OVER (PARTITION BY user) AS total_query_cnt
FROM advanced.query_logs
ORDER BY query_date, team, user
;

문제 2)

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

WITH weekly_team_query_cnt 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
  week_number,
  team,
  user,
  query_cnt,
  ROW_NUMBER() OVER (PARTITION BY week_number, team ORDER BY query_cnt DESC) AS team_rank
FROM weekly_team_query_cnt
WHERE 1=1
QUALIFY team_rank = 1
ORDER BY week_number, team
;

문제 3)

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

WITH weekly_team_query_cnt 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 
  user,
  team,
  week_number,
  query_cnt,
  LAG(query_cnt, 1) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt
FROM weekly_team_query_cnt
ORDER BY user, team, week_number
;

문제 4)

  • 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성

WITH timeseries_user_query_count AS (
  SELECT
    user,
    team,
    query_date,
    COUNT(user) AS query_count
  FROM advanced.query_logs
  GROUP BY ALL
)
SELECT
  user,
  team,
  query_date,
  query_count,
  SUM(query_count) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_query_count
FROM timeseries_user_query_count

문제 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
)
SELECT 
  date,
  LAST_VALUE(number_of_orders IGNORE NULLS) OVER (ORDER BY date) AS number_of_orders
FROM raw_data
;

문제 6)

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

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
)
SELECT
  date,
  number_of_orders,
  AVG(number_of_orders) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM (
  SELECT 
    * EXCEPT(number_of_orders),
    LAST_VALUE(number_of_orders IGNORE NULLS) OVER (ORDER BY date) AS number_of_orders
  FROM raw_data
)
;

문제 7)** 새로운 Session 부여하기

  1. 본인 해결 쿼리)

     

WITH create_new_session_app_logs_raw_data AS (
  SELECT
    *,
    LAG(event_datetime) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS before_event_datetime
  FROM (
    SELECT
      event_date,
      event_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-18'
      AND user_pseudo_id = '1997494153.8491999091'
  )
), 
create_new_session_app_logs AS (
  SELECT
    *,
    IF(second_diff is null OR second_diff > 20, 1, null) AS session_start
  FROM (
    SELECT
      *,
      EXTRACT(second FROM event_datetime - before_event_datetime) AS second_diff
    FROM create_new_session_app_logs_raw_data
  )
)
SELECT
  *,
  SUM(session_start) OVER (ORDER BY event_timestamp) AS session_id
FROM create_new_session_app_logs
;

  1. 카일스쿨님 해결 쿼리)

  • 본인 해결 쿼리와의 차이점

     

    • second_diff 새로운 컬럼 생성 시 DATETIME_DIFF(col1, col2, 시간단위)라는 함수 적용

    • CASE WHEN문을 적용하여 직관적이고 수정하기 용이한 형태의 쿼리 사용


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
), diff_data AS (
  SELECT
    *,
    DATETIME_DIFF(event_datetime, prev_event_datetime, SECOND) AS second_diff
    # second_diff 기반으로 새로운 세션의 시작일지, 아닐지를 판단할 수 있음
  FROM (
    SELECT
      *,
      LAG(event_datetime, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS prev_event_datetime,
      # event_datetime이랑 prev_event_datetime을 뺴서 20초가 넘으면 새로운 세션으로 정의
      # 20초가 넘지 않으면 기존 세션
    FROM base
  )
)
SELECT
  *,
  # 누적합을 사용해서 session_number를 만들었다!
  SUM(session_start) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_num
  # session을 구할 때 쿼리가 길어질 수 있음. 하루에 접속을 여러번 하는 서비스 => session기반이 좋을 수 있고, 아니라고 한다면 일자별 집계가 나을 수 있음
FROM (
  SELECT
    *,
    CASE
      WHEN prev_event_datetime IS NULL THEN 1
      WHEN second_diff >= 20 THEN 1 # session을 나누는 기준 초. 데이터를 탐색하면서 결정
      ELSE NULL
      END AS session_start
      # session이 시작됨을 알리는 session_start
  FROM diff_data
  )
ORDER BY event_datetime
;

 

sqlGoogle-Analyticsfirebasegoogle-sheetsbigquery

답변 1

0

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

안녕하세요, 인프런 AI 인턴입니다. DA유치원님.

윈도우 함수는 데이터베이스 내에서 데이터를 집계하거나 정렬할 때 매우 유용한 도구입니다. 특히 윈도우 함수를 사용하면 GROUP BY 없이도 데이터를 처리할 수 있어 더욱 강력합니다.

문제 1에 대한 접근 방법

윈도우 함수를 활용하여 사용자별 쿼리 실행 횟수를 구하는 방법을 알아보겠습니다. 윈도우 함수 중 COUNT()를 사용하여, 각 사용자가 실행한 쿼리의 횟수를 같은 테이블 내에서 계산하고자 합니다.

SELECT 
  *, 
  COUNT(*) OVER (PARTITION BY user_id) AS total_queries
FROM 
  query_logs;

위 쿼리에서 OVER 절을 사용하면 전체 테이블에서 윈도우 함수가 작동하는 방법을 지정할 수 있습니다. PARTITION BY를 사용함으로써 각 user_id를 기준으로 데이터를 분리하여 계산할 수 있습니다. 이렇게 하면 각 사용자의 총 쿼리 실행 횟수가 total_queries라는 새로운 컬럼에 추가됩니다.

추가 참고 자료

  • 윈도우 함수의 활용: 기본 윈도우 함수의 사용법과 활용 사례를 다룬 자료를 참고하시기 바랍니다.
  • 윈도우 함수 탐색 함수 연습 문제 - 기존 질문 링크: 윈도우 함수의 다양한 활용 예제가 포함되어 있습니다. 특히 LAST_VALUE와 관련한 실습 문제도 확인해 보세요.

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

DA유치원님의 프로필 이미지
DA유치원

작성한 질문수

전체 Q&A
질문하기