강의

N
챌린지

멘토링

로드맵

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

주재환님의 프로필 이미지
주재환

작성한 질문수

[인프런 빅쿼리 빠짝스터디 2주차] 윈도우 함수 연습 문제

작성

·

60

0

연습문제

(1) 탐색 함수

-- 문제 1) user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요.
# 쿼리를 작성하는 목표, 확인할 지표 : 다음 접속 월과 다다음 접속 월 구하기
# 쿼리 계산 방법 : LEAD
# 데이터의 기간 :
# 사용할 테이블 : analytics_function_01
# Join KEY :
# 데이터 특징 :
SELECT
  user_id,
  visit_month,
  LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS after_visit_month,
  LEAD(visit_month,2) OVER(PARTITION BY user_id ORDER BY visit_month) AS after_two_visit_month
FROM advanced.analytics_function_01;


-- 문제 2) user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요.
# 쿼리를 작성하는 목표, 확인할 지표 : 다음 접속 월과 다다음 접속 월, 이전 접속 월 구하기
# 쿼리 계산 방법 : LEAD, LAG
# 데이터의 기간 :
# 사용할 테이블 : analytics_function_01
# Join KEY :
# 데이터 특징 :
SELECT
  user_id,
  visit_month,
  LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS after_visit_month,
  LEAD(visit_month,2) OVER(PARTITION BY user_id ORDER BY visit_month) AS after_two_visit_month,
  LAG(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS before_visit_month
FROM advanced.analytics_function_01;

-- 문제 3) user들의 다음 접속까지의 간격을 구하시오
# 쿼리를 작성하는 목표, 확인할 지표 : 다음 접속까지 간격 구하기
# 쿼리 계산 방법 : LEAD
# 데이터의 기간 :
# 사용할 테이블 : analytics_function_01
# Join KEY :
# 데이터 특징 :
WITH diff AS(SELECT
  user_id,
  visit_month,
  LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS after_visit_month,
FROM advanced.analytics_function_01)

SELECT
  *,
  after_visit_month - visit_month AS diff_month
FROM diff;

-- 추가 문제) user들의 첫번째 방문 월, 마지막 방문 월을 구하시오
# 쿼리를 작성하는 목표, 확인할 지표 : 첫번째 방문 월, 마지막 방문 월
# 쿼리 계산 방법 : FIRST_VALUE, LAST_VALUE
# 데이터의 기간 :
# 사용할 테이블 : analytics_function_01
# Join KEY :
# 데이터 특징 :
SELECT
  user_id,
  visit_month,
  FIRST_VALUE(visit_month) OVER(ORDER BY user_id) AS first_visit_month,
  LAST_VALUE(visit_month) OVER(ORDER BY user_id) AS last_visit_month
FROM advanced.analytics_function_01;

(2) Frame

-- amount_total : 전체 SUM
-- cumulative_sum : row 시점에 누적 SUM
-- cumulative_sum_by_user : row 시점에 유저별 누적 SUM
-- last_5_orders_avg_amount : order_id 기준으로 정렬하고, 직전 5개의 주문의 평균 amount
-- 집계분석함수() OVER(PARTITION BY ~~~ ORDER BY ROWS BETWEEN A AND B)

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_amoubt
FROM advanced.orders

(3) 윈도우 함수 연습문제

-- 1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요.
# 쿼리를 작성하는 목표, 확인할 지표 : 쿼리를 실행한 총 횟수
# 쿼리 계산 방법 : SUM
# 데이터의 기간 :
# 사용할 테이블 : query_logs
# Join KEY :
# 데이터 특징 :
SELECT
  *,
  COUNT(query_date) OVER(PARTITION BY user) AS total_query_cnt
FROM advanced.query_logs
ORDER BY user, query_date;


-- 2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 주차별로 팀내에서 쿼리를 많이 실행한 수와 랭킹 그 랭킹이 1등인 사람만 결과
# 쿼리 계산 방법 : EXTRACT, COUNT, RANK
# 데이터의 기간 :
# 사용할 테이블 : query_logs
# Join KEY :
# 데이터 특징 :
WITH query_cnt_by_team AS (
  SELECT
    EXTRACT(WEEK FROM query_date) AS week_number,
    team,
    user,
    COUNT(user) AS query_count
  FROM advanced.query_logs
  GROUP BY ALL)

SELECT
  *,
  RANK() OVER(PARTITION BY week_number, team ORDER BY query_count DESC) AS team_rank
FROM query_cnt_by_team
QUALIFY team_rank = 1
ORDER BY week_number, team;

-- 3) (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 1주 전에 쿼리 실행 수
# 쿼리 계산 방법 : EXTRACT, COUNT, LAG
# 데이터의 기간 :
# 사용할 테이블 : query_logs
# Join KEY :
# 데이터 특징 :
WITH query_cnt_by_team AS (
  SELECT
    EXTRACT(WEEK FROM query_date) AS week_number,
    team,
    user,
    COUNT(user) AS query_count
  FROM advanced.query_logs
  GROUP BY ALL)


SELECT
  *,
  LAG(query_count) OVER(PARTITION BY user ORDER BY week_number) AS prev_week_query_count
FROM query_cnt_by_team
ORDER BY user;


-- 4) 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 일자별로 유저가 실행한 누적 쿼리 수
# 쿼리 계산 방법 : COUNT, SUM
# 데이터의 기간 :
# 사용할 테이블 : query_logs
# Join KEY :
# 데이터 특징 :
WITH query_cnt AS(
  SELECT
    *,
    COUNT(user) AS query_count
  FROM advanced.query_logs
  GROUP BY ALL)

SELECT
  *,
  SUM(query_count) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_query_count
FROM query_cnt
ORDER BY user, query_date;


-- 5) 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요
# 쿼리를 작성하는 목표, 확인할 지표 : NULL 데이터를 이전 날짜의 값으로 채워
# 쿼리 계산 방법 : LAST_VALUE IGNORE NULLS, EXCEPT
# 데이터의 기간 :
# 사용할 테이블 : raw_data
# Join KEY :
# 데이터 특징 : 중간 중간 값에 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 
  * EXCEPT(number_of_orders),
  LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS number_of_orders,
FROM raw_data;


-- 6) 5번 문제에서 NULL을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요(이동 평균)
# 쿼리를 작성하는 목표, 확인할 지표 : 
# 쿼리 계산 방법 : LAST_VALUE IGNORE NULL, EXCEPT, AVG, ROW BETWEEN, PRECEDING, CURRENT ROW
# 데이터의 기간 :
# 사용할 테이블 : raw_data
# Join KEY :
# 데이터 특징 : 중간 중간 값에 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
), filled_raw_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)

SELECT
  *,
  AVG(number_of_orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM filled_raw_data;


-- 7) app_logs 테이블에서 Custom Session을 만들어 주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요. Session은 숫자로 (1, 2, 3 ...) 표시해도 됩니다
-- 2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다
# 쿼리를 작성하는 목표, 확인할 지표 : 
# 쿼리 계산 방법 :
# 데이터의 기간 : 2022-08-18
# 사용할 테이블 : app_logs
# Join KEY :
# 데이터 특징 :
WITH session_app_logs AS(
  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")
,before_datetime_app_logs AS(
  SELECT
    *,
    LAG(event_datetime) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_event_datetime
  FROM session_app_logs)
,diff_app_logs AS(
  SELECT
    *,
    DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff
  FROM before_datetime_app_logs)
,session_start_app_logs AS(
  SELECT
    *,
    CASE
      WHEN second_diff >= 20 OR second_diff IS NULL THEN 1
      ELSE 0
      END AS session_start
  FROM diff_app_logs)
SELECT
  *,
  SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_id
FROM session_start_app_logs
ORDER BY user_pseudo_id, event_datetime;

배운점

  • 사실 윈도우 함수를 찾아보고 쓰면서 이게 윈도우 함수인지도 모르고 사용했었는데 정말 중요하고 유용한 함수라는걸 다시 한번 느낄 수 있었다

  • Frame 부분이 제일 헷갈리고 계속 사용해봐야 이해할 수 있을거 같다.

     

답변 1

0

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

안녕하세요, 인프런 AI 인턴입니다.
주재환님, 윈도우 함수는 SQL에서 매우 강력하고 유용한 기능입니다. 제공해주신 SQL 코드 예제들은 다양한 윈도우 함수 활용을 잘 보여주고 있습니다.

  1. 탐색 함수

    • LEAD와 LAG 함수를 사용하여 사용자들의 접속 기록을 탐색하고 다음, 다다음 및 이전 접속 월을 알아내는 방법을 설명하셨습니다.

  2. Frame

    • SUM과 AVG 함수를 윈도우 함수와 함께 사용하여 누적 값 및 특정 구간의 평균을 계산하는 방법을 다루셨습니다. 특히 ‘ROWS BETWEEN A AND B’ 문법으로 이동 평균을 계산하는 테크닉은 매우 유용합니다.

  3. Query 사용 및 랭킹 계산

    • COUNT, RANK, LAG 함수를 사용하여 쿼리 사용 횟수를 기록하고 랭킹을 계산하며, 이전 주의 데이터를 참조하는 방법을 잘 보여주셨습니다.

  4. NULL 처리

    • LAST_VALUE IGNORE NULLS를 사용하여 NULL 값을 이전 날짜의 값으로 대체하는 기술은 데이터 정제에 매우 유용합니다.

  5. Custom Session 만들기

    • 시간 간격을 계산하고 세션을 구별하는 데 있어 LAG와 DATETIME_DIFF를 사용하는 방법은 실무에서 이벤트 로그 분석에 큰 도움이 될 것입니다.

윈도우 함수는 데이터 분석의 강력한 도구로서, 다양한 상황에 맞게 창의적으로 활용할 수 있다는 점에서 많은 연습과 실습이 필요합니다. 주재환님이 앞으로도 계속 실습하시면서 자신만의 스킬을 쌓기를 바랍니다. 도움이 필요하시면 언제든지 문의해주세요!

아래의 내용도 함께 참고하면 도움이 될 것입니다:
- 로드맵 확인하러 가기

추가적으로 궁금한 부분이 있으면 언제든지 말씀해 주세요. 좋은 학습 되시기 바랍니다!

주재환님의 프로필 이미지
주재환

작성한 질문수

전체 Q&A
질문하기