인프런 커뮤니티 질문&답변
작성한 질문수
[빠짝스터디 2주차 과제] 윈도우 함수 연습 문제
작성
·
17
0
집계 함수
Aggregation Function
GROUP BY와 함께 사용한 COUNT나 AVG는 컬럼의 특정 Value를 기준으로 집계하면서 하나의 값을 반환합니다.
윈도우 함수
다음과 같은 경우 유용한 함수
A유저의 전 주문 수량은 얼마나 될까?
B유저가 물건을 구입하기 전에 상세보기 페이지를 얼마나 방문했을까?
C유저의 특정 구매 시점별 누적 구매 횟수는?
특정 카테고리 내에서 제일 많이 방문한 제품은?
이런 경우 모두 윈도우 함수(분석 함수)를 사용하면 편리함
윈도움 함수를 사용하지 않으면 서브 쿼리와 JOIN 등을 사용해서 만들어야 함
집계 함수와 다르게 윈도우 함수는 각행마다 단일 값을 반환함
종류
탐색 함수 : LEAD, LAG, FIRST_VALUE, LAST_VALUE
번호 지정 함수 : RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTITLE
집계 분석 함수: 집계 함수들, AVG, COUNT, SUM, MAX, MIN
문법
함수 이름(컬럼, OFFSET) OVER (PARTITION BY 파티션_컬럼 ORDER BY 정렬_컬럼)
OFFSET : 값을 가져올 행의 위치, 기본 값은 1이고 생략 가능
함수 이름 (컬럼) OVER (PARTITON BY 파티션_컬럼 ORDER BY 정렬_컬럼)
필요에 따라 PARTITION BY는 생략 가능
#문제1
SELECT
user_id,
visit_month,
lead(visit_month, 1) over(partition by user_id order by visit_month asc) as next_visit,
lead(visit_month, 2) over(partition by user_id order by visit_month asc) as next_next_visit
FROM workspace.analytics_function_01
ORDER BY user_id, visit_month
;
#문제2
SELECT
user_id,
visit_month,
lead(visit_month, 1) over(partition by user_id order by visit_month asc) as next_visit,
lead(visit_month, 2) over(partition by user_id order by visit_month asc) as next_next_visit,
lag(visit_month, 1) over(partition by user_id order by visit_month asc) as prev_visit
FROM workspace.analytics_function_01
ORDER BY user_id, visit_month
;
#문제3
SELECT
user_id,
visit_month,
lead(visit_month, 1) over(partition by user_id order by visit_month asc) as next_visit_month,
lead(visit_month, 1) over(partition by user_id order by visit_month asc) - visit_month as next_visit_month_diff
FROM workspace.analytics_function_01
ORDER BY user_id, visit_month
;
#추가문제
SELECT DISTINCT
user_id,
first_value(visit_month) over(partition by user_id order by visit_month asc rows between unbounded preceding and unbounded following) as first_visit_month,
last_value(visit_month) over(partition by user_id order by visit_month asc rows between unbounded preceding and unbounded following) as last_visit_month
FROM workspace.analytics_function_01
ORDER BY user_id
;
#문제4
SELECT
*,
sum(amount) over() as total_amount,
sum(amount) over(order by order_id asc rows between unbounded preceding and current row) as cumulative_sum,
sum(amount) over(partition by user_id order by order_id asc rows between unbounded preceding and current row) as cumulative_sum_by_user,
avg(amount) over(order by order_id asc rows between 5 preceding and 1 preceding) as last_five_orders_avg_amount
FROM workspace.orders
ORDER BY order_id
;
#연습문제1
SELECT
*,
count(*) over(partition by user) as query_count_by_users
FROM workspace.query_logs
;
#연습문제2
SELECT
query_weeknum,
team,
user,
query_count,
rank() over(partition by query_weeknum, team order by query_count desc) as query_rank
FROM
(
SELECT
extract(week from query_date) as query_weeknum,
team,
user,
count(1) as query_count
FROM workspace.query_logs
GROUP BY ALL
)
QUALIFY query_rank = 1
ORDER BY query_weeknum
;
#연습문제3
SELECT
team,
user,
query_weeknum,
query_count,
lag(query_count, 1) over(partition by team, user order by query_weeknum asc) as prev_week_query_count
FROM
(
SELECT
team,
user,
extract(week from query_date) as query_weeknum,
count(1) as query_count
FROM workspace.query_logs
GROUP BY ALL
)
#연습문제4
SELECT
team,
user,
query_date,
query_count,
sum(query_count) over(partition by team, user order by query_date asc rows between unbounded preceding and current row) as cumulative_sum
FROM
(
SELECT
team,
user,
query_date,
count(1) as query_count
FROM workspace.query_logs
GROUP BY ALL
)
ORDER BY team, user, query_date
;
#연습문제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
)
SELECT
date,
ifnull(number_of_orders, real_prev_number_of_orders) as number_of_orders
FROM
(
SELECT
date,
number_of_orders,
last_value(number_of_orders ignore nulls) over(order by date asc rows between unbounded preceding and 1 preceding) as real_prev_number_of_orders
FROM raw_data
)
ORDER BY date asc
;
#연습문제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
)
SELECT
date,
number_of_orders,
avg(number_of_orders) over(order by date asc rows between 2 preceding and current row) as moving_avg
FROM
(
SELECT
date,
ifnull(number_of_orders, real_prev_number_of_orders) as number_of_orders
FROM
(
SELECT
date,
number_of_orders,
last_value(number_of_orders ignore nulls) over(order by date asc rows between unbounded preceding and 1 preceding) as real_prev_number_of_orders
FROM raw_data
)
)
ORDER BY date asc
;
#연습문제7
WITH total_logs AS (
SELECT
user_pseudo_id,
event_name,
timestamp_micros(event_timestamp) as event_datetime
FROM workspace.app_logs
)
SELECT
user_pseudo_id,
event_name,
event_datetime,
prev_event_datetime,
second_diff,
sum(session_change) over(partition by user_pseudo_id order by event_datetime asc) as session_id
FROM
(
SELECT
*,
case when event_datetime = first_event_datetime then 1 end as session_id,
case when second_diff is null or second_diff >= 20 then 1
else 0 end as session_change
FROM
(
SELECT
*,
datetime_diff(event_datetime, prev_event_datetime, second) as second_diff
FROM
(
SELECT
user_pseudo_id,
event_name,
event_datetime,
lag(event_datetime, 1) over(partition by user_pseudo_id order by event_datetime asc) as prev_event_datetime,
first_value(event_datetime) over(partition by user_pseudo_id order by event_datetime asc) as first_event_datetime
FROM total_logs
)
)
)
ORDER BY user_pseudo_id, event_datetime
;
답변 1
0
안녕하세요, 변혜지님. 인프런 AI 인턴입니다.
윈도우 함수에 대해 궁금해 하신 점과 다양한 실습 문제에 대해 작성해주셨네요. 말씀하신 윈도우 함수는 데이터 분석에서 매우 유용한 도구로, 각 사용자의 행동 패턴을 이해하고 중요한 통계를 계산하는 데 많이 사용됩니다. 특히, 특정 행의 앞이나 뒤 데이터를 비교하거나 누적 값을 계산할 때, 이러한 함수들은 유용합니다.
아래에 유사한 질문과 답변이 제공된 글들을 추천 드립니다. 여러분의 질문이 여러 부분을 포함하고 있기 때문에, 각각의 연습문제에 대한 이해를 돕기 위해 여기에 소개된 글들을 참조하시면 좋겠습니다.
윈도우 함수 기본 사용 사례 - 이 글에서는 기본적인 윈도우 함수 사용 방법과 관련된 설명이 되어 있습니다.
리텐션 분석을 위한 SQL 활용법 - 리텐션 데이터 분석에서 윈도우 함수의 구체적인 활용 사례를 다루고 있습니다.
퍼널 분석에 대한 윈도우 함수 활용 - 퍼널 분석 측면에서 윈도우 함수가 어떻게 사용되는지 설명되어 있습니다.
각 질문 페이지에는 해당 케이스들이 다루어진 Q&A와 설명이 담겨 있으니, 여러분의 쿼리 작성에 큰 도움이 될 것입니다. 윈도우 함수를 사용함으로써 데이터를 어떻게 더 잘 분석할 수 있을지 이해하는 데에 더욱 유용할 것입니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.