[빠짝스터디 2주차 과제] 윈도우 함수, FRAME 설정, QUALIFY

2-4. 윈도우 함수 탐색 함수 연습 문제

• 문제 1) User들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요

   

select 
  *,
  lead(visit_month) over (partition by user_id order by visit_month) as next_visit_month,
  lead(visit_month, 2) over (partition by user_id order by visit_month) as next_visit_month2  
from advanced.analytics_function_01
; 

결과

• 문제 2) User들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리 작성

select 
  *,
  lead(visit_month, 1) over (partition by user_id order by visit_month) as next_visit_month,
  lead(visit_month, 2) over (partition by user_id order by visit_month) as next_visit_month2,
  lag(visit_month, 1) over (partition by user_id order by visit_month) as pre_visit_month

from advanced.analytics_function_01
order by user_id, visit_month

결과

• 문제 3) user가 접속했을 때, 다음접속까지의 간격을 구하시오

   

select
  *,
  next_visit_month - visit_month as term
From (
select
  *,
  lead(visit_month, 1) over (partition by user_id order by visit_month) as next_visit_month
from advanced.analytics_function_01)
order by user_id, visit_month
;

결과

• 추가문제 : 이 데이터셋을 User_id의 첫번째 방문 월, 마지막 방문월을 구하는 쿼리 작성

   

select
  *,
  first_value(visit_month) over (partition by user_id order by visit_month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as first_visit_month,
  last_value(visit_month) over (partition by user_id order by visit_month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_visit_month

from advanced.analytics_function_01
order by user_id, visit_month
;

결과

2-8. 윈도우 함수 Frame연습 문제

• Frame 연습문제

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_user, -- 누적
  avg(amount) over(order by order_id rows between 5 preceding and 1 preceding) as last_5_avg

from `advanced.orders`
order by order_date, user_id
;

결과

2-11. 윈도우 함수 연습 문제(1번)

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

select
  *,
  count(query_date) over() as total_cnt, # 전체
  count(query_date) over(partition by user) as total_query_cnt
from advanced.query_logs
order by user, query_date
;

결과

2-11. 윈도우 함수 연습 문제(2번~6번)

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

with query_cnt_by_team 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 
  *,
  rank() over(partition by week_number, team order by query_cnt desc) as team_rank
from query_cnt_by_team
qualify team_rank = 1 
order by week_number, team, query_cnt desc
;

결과

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

with query_cnt_by_team as (
  select 
    user,
    team,
    extract(WEEK from query_date) as week_number,
    count(user) as query_cnt
  from advanced.query_logs
  group by all)

select 
  *,
  LAG(query_cnt, 1) OVER (partition by user ORDER BY week_number) AS prev_week_query_count
  -- user 단위, 전주차 week_number
from query_cnt_by_team
-- order by user -- team, query_cnt desc
;

결과

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

select 
  *,
  sum(query_cnt) over(partition by user order by query_date) as cumulative_sum,
  -- 왜되는거지? : frame의 default값 : unbounded preceding ~current row  
  sum(query_cnt) over(partition by user order by query_date rows between unbounded preceding and current row) as cumulative_sum2
from (select 
  user,
  team,
  query_date,
  count(*) as query_cnt
from advanced.query_logs
group by all)
-- 검증
-- qualify cumulative_sum != cumulative_sum2
order by user, query_date

;

결과

• 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 
  *,
  LAG(number_of_orders) over(order by date) as number_of_orders2,
  ifnull(number_of_orders, LAG(number_of_orders) over(order by date)) as number_of_orders3,
  LAST_VALUE(number_of_orders) over(order by date) as last_value_orders,
  LAST_VALUE(number_of_orders ignore nulls) over(order by date) as last_value_orders2

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 * from raw_data;
select 
  * ,
  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)
;

결과

2-11. 윈도우 함수 연습 문제(7번)

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 1=1
    and event_date = "2022-08-18"
    and user_pseudo_id = '1997494153.8491999091'), diff_data as(
select 
  *,
  datetime_diff(event_datetime, prev_event_datetime, second) as second_diff -- second로 간격설정
  # 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
  *,
  sum(session_start) over(partition by user_pseudo_id order by event_datetime) as session_num
from(select 
  *,
  case
    when prev_event_datetime is null then 1
    when second_diff >= 20 then 1
    else 0
    end as session_start
    # session이 시작됨을 알리는 session_start
from diff_data)
order by event_datetime
limit 20;

결과

느낀점

• 마지막 문제같은 실무에 사용 할 법한 예제 문제들을 더 만들어서 풀어봐야 할 것 같다.

• session기준에 대한 리서치를 더 해봐야 할 것 같다.