인프런 커뮤니티 질문&답변
작성한 질문수
[빠짝스터디 2주차 과제] 윈도우 함수 연습 문제
작성
·
60
·
수정됨
0
--윈도우함수(Analytics Function)
- 탐색 함수 : LEAD, LAG, FIRST_VALUE(유저들의 첫 방문일은 언제인가요?), LAST_VALUE
- 탐색 함수 연습 문제
- 번호 지정 함수 : ROW_NUMBER, RANK
- 집계 분석 함수 : AVG, SUM
- 데이터의 범위를 제한하고 싶은 경우 : Frame 설정
- ROWS BETWEEN UNBOUNDED PRECEDING AND FOLLOWING - 윈도우 함수 조건 설정 : QUALIFY
- 연습 문제
RANK() OVER (PARTITION BY 학년 ORDER BY 키 DESC) AS 학년 별 키 순위
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_visit_month
, FIRST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) --얘는 NULL을 포함해서 값을 가져옴
, FIRST_VALUE(numbers IGNORE NULLS) OVER(ORDER BY date) AS first_num
, LAST_VALUE(numbers IGNORE NULLS) OVER(ORDER BY date) AS last_num
, RANK() OVER (PARTITION BY product_type ORDER BY revenue DESC) AS rank --중복이 있으면 1위 같이 부여하고 3위를 줌 --상위 30%를 뽑고 싶을땐 이거 사용
, DENSE_RANK() OVER (PARTITION BY product_type ORDER BY revenue DESC) AS rank --중복이 있으면 1위 같이 부여하고 2위를 줌
, ROW_NUMBER() OVER (PARTITION BY product_type ORDER BY revenue DESC, id) AS rank --중복이 있으면 랜덤으로 숫자 1,2를 줌
, AVG(revenue) OVER(PARTITION BY product_type) AS avg_revenue
, SUM(revenue) OVER(PARTITION BY product_type) AS sum_revenue
FROM Table
Frame:
1) 우리 회사의 모든 주문량은?
2) 특정 주문 시점에서 누적 주문량은?
3) 고객별 주문 시점에서 누적 주문량은?(누적 100만원 이상 회원 => 골드 등급!)
4) 최근 직전 5개의 평균 주문량은?
- PRECEDING : 현재 행 기준으로 이전 행
- CURRENT ROW : 현재 행
- FOLLOWING : 현재 행 기준으로 이후 행
- UNBOUNDED : 처음부터 또는 끝까지(사전적 의미 : 묶이지 않고 제한되지 않음)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING : default)파티션내 모든 값을 가져오겠다는 의미
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재 행과 그 앞뒤 한 행씩을 포함해서
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : 파티션의 처음부터 현재 행을 포함한 평균
SELECT
order_id,
order_date,
user_id,
amount,
SUM(amount) OVER (PARTITION BY user_id) AS amount_total
FROM advanced.orders WHERE 1=1
QUALIFY amount_total >= 500 --이게 이제는 가능
문제 1) user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요.
SELECT
user_id
, visit_month
, LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_1step_visit_month
, LEAD(visit_month,2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_2step_visit_month
FROM advanced.analytics_fuction_01
ORDER BY user_id, visit_month
문제 2) user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요.
WITH raw as (
SELECT
user_id
, visit_month
, LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_1step_visit_month
, LEAD(visit_month,2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_2step_visit_month
, LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS before_1step_visit_month
FROM advanced.analytics_fuction_01
ORDER BY user_id, visit_month
)
--LEAD는 다음, LAG는 이전(밀리고 당긴다고 생각하니 살짝 헷갈림)
--3) 간격구하는 쿼리
SELECT
user_id
, visit_month
, after_1step_visit_month - visit_month as diff
FROM raw
ORDER BY user_id, visit_month
-- 4)첫번째, 마지막 방문월
SELECT
user_id
, visit_month
, FIRST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) as first_visit_month
, LAST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) as last_visit_month
FROM advanced.analytics_fuction_01
ORDER BY user_id, visit_month
--Frame 연습문제
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING : default)파티션내 모든 값을 가져오겠다는 의미
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재 행과 그 앞뒤 한 행씩을 포함해서
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : 파티션의 처음부터 현재 행을 포함한 평균
select
SUM(amount) OVER () AS amount_total
--ORDER BY는 적는 순간 위에서부터 현재행까지 되는것 같음
, SUM(amount) OVER (ORDER BY order_date, order_id ) AS cumulative_sum
, SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id) AS cumulative_sum_user
--same
SUM(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total
, SUM(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
, SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum_user
, AVG(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING AS last_5_avg --본인 값은 포함 안함
FROM advanced.analytics_fuction_01
--연습문제 7개
--업로드 예정
1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요.
단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요.
select
user
, team
, query_date
, count(query_date) over(partition by user) as total_query_cnt
, count(*) over(partition by user) as total_query_cnt --이것도 되는지 확인
from advanced.query_logs
2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요.
단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요
with raw as (
select
user
, team
, extract(week from query_date) as weeks --week() 작동안됨
, count(*) as total_query_cnt
--, count(user) as total_query_cnt --명시해주기
from advanced.query_logs
group by 1,2,3
)
select
*
, rank() over(partition by weeks, team order by total_query_cnt) as team_rank
from
qualify rn=1
3) (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의
컬럼으로 확인할 수 있는 쿼리를 작성해주세요
with raw as (
select
user
, team
, week(query_date) as weeks
, count(*) as query_cnt
from advanced.query_logs
group by 1,2,3
)
select
*
, lag(query_cnt) over(partition by team, user order by weeks) as prev_week_query_cnt
-- , lag(query_cnt,1) --디폴트
from raw
4) 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요
with raw as (
select
user
, team
, query_date as query_date
, count(*) as query_cnt
from advanced.query_logs
group by 1,2,3
--group by all 해줘도 됨
)
select
*
, sum(query_cnt) over(partition by team, user order by query_date) as prev_week_query_cnt
--default frame: unbounded preceding and current row
from raw
--qualify로 프레임 넣은것과 아닌것이 실제로 차이가 나는지를 더블체크 해보는 습관을 들이자
--정말 데이터가 내 생각대로 생겼는지 확인해보는 작업은 중요하다는 생각
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
*
, last_value(number_of_orders ignore nulls) over(order by date) as new_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
)
, total as (
SELECT
* except(number_of_orders)
, last_value(number_of_orders ignore nulls) over(order by date) as new_number_of_orders --요 접근법 생각해낸거 강의 덕분
FROM raw_data
)
select
avg(new_number_of_orders) over(order by date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
from total
7) app_logs 테이블에서 Custom Session을 만들어 주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요. Session은 숫자로 (1, 2, 3 ...) 표시해도 됩니다
2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다
with raw as (
select
event_date
, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime --new
, event_name
, user_id
, user_pseudo_id
from advanced.app_logs
where event_date = '2022-08-16'
)
, date_diff as (
select
*,
lag(event_datetime) over(partition by user_pseudo_id order by event_datetime) as prev_event_datetime
from raw
)
, total as (
select
*
, DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) as second_diff
, case
when second_diff is null then 1
when second_diff >= 20 then 1 --20 seconds
else 0 end AS session_start
from date_diff
)
select
*
, sum(session_start) over(partition by user_pseudo_id order by event_datetime) as session_id
from total답변 1
0
안녕하세요, 인프런 AI 인턴입니다. Luna Chae님.
빅쿼리 SQL의 윈도우 함수를 활용한 다양한 쿼리 연습 문제를 풀고 계신 것 같아 반갑습니다. 윈도우 함수는 데이터 분석에서 자주 쓰이는 강력한 도구입니다. 자세한 질문을 주셔서 감사합니다.
윈도우 함수는 ‘다음’이나 ‘이전’ 값, 또는 특정 범위 내의 집계를 구하기에 유용합니다. 예를 들어 LEAD와 LAG를 사용하여 사용자의 다음 및 이전 접속 월을 구할 수 있고, FIRST_VALUE 및 LAST_VALUE로 사용자의 첫 번째 및 마지막 값을 가져올 수 있습니다. 또한, RANK, DENSE_RANK, ROW_NUMBER 등을 통해 주어진 조건 내에서 순위를 매기거나 집계를 할 수 있습니다.
기본적으로 윈도우 함수의 FRAME은 “RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”가 기본값으로 설정됩니다. UNBOUNDED는 처음부터 끝까지라는 의미를 가진 중요한 키워드입니다. 이와 관련된 상세한 내용은 인프런의 윈도우 함수 강의를 참고하시면 더 깊이 있는 이해를 하실 수 있습니다.
다음은 관련해서 도움이 될 수 있는 기존 답변 링크들입니다:
BigQuery 활용편(PDF)파일 P.229 페이지 코드 오류
- 윈도우 함수의 FRAME 설정 및 LAST_VALUE에 대한 이해를 돕습니다.
- 윈도우 함수의 FRAME 설정 및 LAST_VALUE에 대한 이해를 돕습니다.
windows function default 값에 대하여 range between과 rows between의 차이
- RANGE와 ROWS의 차이점에 대해 알고 싶으시다면 이 질문을 참고해보세요.
- RANGE와 ROWS의 차이점에 대해 알고 싶으시다면 이 질문을 참고해보세요.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.