인프런 커뮤니티 질문&답변
작성한 질문수
[인프런 빅쿼리 빠짝스터디 2주차] 윈도우 함수, FRAME설정, QUALITY
작성
·
63
0
-- 윈도우 함수 집계 및 frame 연습 문제
-- My answer
SELECT
*,
sum(amount) over (order by order_id rows between unbounded preceding and
unbounded following) as total_amount,
-- 전체 합을 구하고 싶을 때, over () 안에 아무것도 작성 안해도 됨
sum(amount) over (order by order_date, order_id rows between unbounded preceding and current row) as cumulative_sum,
-- sum(amount) over(order by order_id) 로만 해도 ok
sum(amount) over (partition by user_id order by user_id, order_date
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 avg_5_last
FROM
`advanced.orders`
ORDER BY
order_id;
-- MEMO --
-- OVER() 안에 아무것도 들어가지 않는 경우도 있음
-- 연습 문제 1번
-- 사용자 별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요.
-- 단,GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요.
SELECT
*,
COUNT(*) OVER(PARTITION BY user) as use_query
FROM
`advanced.query_logs`;
-- # data 확인
SELECT
*,
COUNT(*) OVER(PARTITION BY user) as use_query
FROM
`advanced.query_logs`
order by
user;
-- 연습 문제 2번
-- 주차 별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요.
-- 단,랭킹이 1등인 사람만 결과가 보이도록 해주세요.
-- Q) 주 차를 뽑는 함수..? : extract 활용
-- partiton by column 잘 생각하기!
with add_weeknum as(
select
*,
extract(week from query_date) as week_number
from
`advanced.query_logs`),
count_query as(
select
*,
count(user) over(partition by week_number, team, user order by week_number,
team, user rows between unbounded preceding and current row) as query_total
from
add_weeknum)
select
user, team, week_number,
rank() over(partition by week_number, team order by query_total desc) as rank_query
from
count_query
qualify
rank_query=1
order by
week_number;
-- # 간단한 답
-- # 윈도우 함수 대신, GROUP BY를 쓰면 절차 줄일 수 있음!
with query_total_cnt as(
select
extract(week from query_date) as week_number,
team,
user,
count(query_date) as query_total
from
`advanced.query_logs`
group by all
)
select
*,
rank() over(partition by week_number, team order by query_total desc) as rank_query
from
query_total_cnt
qualify
rank_query=1
order by
week_number;
-- 연습 문제 3번
-- (2번 문제에서 사용한 주차 별 쿼리 사용)쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요.
-- partiton by column 잘 생각하기!
with add_weeknum as(
select
*,
extract(week from query_date) as week_number
from
`advanced.query_logs`),
count_query as(
select
*,
count(user) over(partition by week_number, user order by week_number, team, user
rows between unbounded preceding and current row)
as query_total
from
add_weeknum
order by
user, week_number),
week_query as(
select
distinct user, team, week_number,
last_value(query_total) over (partition by user, week_number order by
week_number, team, user) as week_total_cnt
from
count_query
order by
user, week_number)
select
*,
lag(week_total_cnt,1) over (partition by user order by week_number) as last_query
from
week_query
order by
user, week_number;
-- 연습 문제 4번
-- 시간의 흐름에 따라, 일자 별로 유저가 실행한 누적 쿼리수를 작성해 주세요.
-- # WITH 구문에서 윈도우 함수 대신, group by를 사용하는 것이 더 간단할 듯
with daily_query as(
select
distinct user, team, query_date,
count(*) over (partition by user, query_date) as query_count
from
`advanced.query_logs`
order by
user, query_date
)
select
*,
sum(query_count) over (partition by user order by user, query_date rows between
unbounded preceding and current row) as cumulative_query_count
-- # order by 구문이 있고, frame 구문은 없을 때 frame의 default 값 : rows between unbounded preceding and current row
from
daily_query
order by
user, query_date;
-- 연습 문제 5번
-- 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다.
-- 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해 주세요.
-- Q) 5월 12일은 어떻게 처리? / 이틀 전 주문량으로 대체?
-- # LAST_VALUE & IGNORE NULLS 활용
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
*,
case when number_of_orders is null then
lag(number_of_orders,1) over(order by date)
else number_of_orders end as number_of_orders_re
FROM
raw_data
order by
date;
-- 답안
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 number_of_orders_re
FROM
raw_data
order by
date;
-- 연습 문제 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
),
newdata as(
SELECT
*,
LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) as
number_of_orders_re -- 수정한 부분
FROM
raw_data
order by
date)
select
date, number_of_orders_re,
avg(number_of_orders_re) over (order by date rows between 2 preceding and
current row) as moving_avg
from
newdata
order by
date;
-- 연습 문제 7번
-- app_logs 테이블에서 CustomSession을 만들어 주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요.
-- Session은 숫자로(1,2,3…) 표시해도 됩니다. 2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다.
-- Q) session_start id 어떻게....????????
-- # 처음에는 datetime diff의 누적 합을 생각했음
with event_data 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_log`
where
user_pseudo_id='1997494153.8491999091'
order by user_pseudo_id, event_datetime
),
before_eventtime_add as(
select
*,
lag(event_datetime,1) over (partition by user_pseudo_id order by user_pseudo_id,
event_datetime) as before_datetime
from
event_data
order by
user_pseudo_id, event_datetime
)
select
*,
datetime_diff(event_datetime,before_datetime,second) as second_diff
from
before_eventtime_add
order by
user_pseudo_id, event_datetime;
-- 답안
-- # event_datetime이랑 before_datetime을 빼서 20초가 넘으면 새로운 세션으로 정의한다.
with event_data 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_log`
where
user_pseudo_id='1997494153.8491999091'
order by user_pseudo_id, event_datetime
),
before_eventtime_add as(
select
*,
lag(event_datetime,1) over (partition by user_pseudo_id order by user_pseudo_id, event_datetime) as before_datetime
from
event_data
order by
user_pseudo_id, event_datetime
),
diff_data as(
select
*,
datetime_diff(event_datetime,before_datetime,second) as second_diff
from
before_eventtime_add
order by
user_pseudo_id, event_datetime
)
select
*,
sum(start_id) over (partition by user_pseudo_id order by event_datetime) as session_num
from(
select
*,
case when second_diff is null then 1
when second_diff>=20 then 1
else 0 end as start_id
from
diff_data);답변 1
0
안녕하세요, 인프런 AI 인턴입니다. 이은경님!
BigQuery를 사용한 SQL 쿼리에서 윈도우 함수와 관련된 다양한 연습 문제를 풀어주셨네요. 지금 제공해주신 쿼리들 중에서 몇 가지 중요한 부분들을 짚어보도록 하겠습니다.
윈도우 함수 및 FRAME 설정:
OVER절을 사용할 때,ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW는 기본값이므로 명시적으로 쓰지 않아도 가능합니다.
- 누적 집계 시, 정렬 기준의 중요성을 꼭 인지해야 합니다.
집계 없이 사용자 별 쿼리 실행 횟수 계산:
COUNT(*) OVER(PARTITION BY user)를 통해 사용자의 쿼리 실행 횟수를 계산하는 방식은 유용합니다. 특히GROUP BY를 사용하지 않아도 개별 레코드에 연관된 집계값을 얻을 수 있습니다.
시간에 따른 세션 계산:
- 이벤트 로그 간의 시간 차이를 계산하는 방식으로 새로운 세션을 구분하는 것은 일반적인 접근 방식입니다.
LAG함수와DATETIME_DIFF를 활용하여 시간 차이를 계산했습니다.
- 최종적으로
CASE WHEN을 사용하여 새로운 세션 판별 후,SUM함수로 세션 번호를 계산한 것은 잘 작성되었습니다.
- 이벤트 로그 간의 시간 차이를 계산하는 방식으로 새로운 세션을 구분하는 것은 일반적인 접근 방식입니다.
NULL 처리 후 이동평균 계산:
- NULL 값을 이전 값으로 대체한 후 이동평균을 계산하셨습니다. 여기서는
LAST_VALUE함수의IGNORE NULLS옵션이 효과적입니다. 이동평균 계산에서도ROWS BETWEEN 2 PRECEDING AND CURRENT ROW를 통해 적절히 이전 두 일의 평균을 구하는 방식도 적절합니다.
- NULL 값을 이전 값으로 대체한 후 이동평균을 계산하셨습니다. 여기서는
제시된 다양한 예제를 통해 윈도우 함수를 어떻게 활용할 수 있는지 잘 보여주셨습니다. 추가적으로 궁금한 점이 있으면 언제든지 말씀해 주세요! 도와드릴 수 있는 부분이 있으면 기쁘게 설명해 드리겠습니다.
더 많은 학습을 위해 Spring Data JDBC 문서도 참고해 보시면 좋겠습니다. Happy Coding! 😊