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

🔐 이번주차 중요 키워드 : 윈도우 함수, FRAME, QUALIFY

✅ 윈도우 함수 연습문제 1번

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

select *,
count(query_date) over (partition by user) as query_cnt
from advanced.query_logs
order by query_cnt desc

✅ 윈도우 함수 연습문제 2번

2) 주차별로 팀 내에서 쿼리를 많이 실행한 수 구하기 
2-1) 실행한 수를 활용해 랭킹 구하기 -- 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요. 
week_number | team | user | query_cnt | team_rank 

select * ,
rank() over(partition by week_number, team order by query_cnt) as team_rank
from (
  select 
    EXTRACT(week FROM query_date) as week_number, 
    team, 
    user, 
    count(query_date) as query_cnt
  from advanced.query_logs
  group by all 
) 
qualify team_rank = 1
order by week_number, team, query_cnt DESC

❗ 새롭게 알게된 함수 : EXTRACT(week FROM query_date) as week_number
→ 기존의 알고 있던 함수와 같은 결과값 : DATE_TRUNC(query_date, WEEK) AS week_number
❗ qualify team_rank = 1 : QUALIFY 덕분에 서브쿼리 없이 바로 조건에 사용가능함!

✅ 윈도우 함수 연습문제 3번

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

select * ,
LAG(query_cnt, 1) over (partition by user order by week_number) as pre_week_query_cnt
from (
  select 
    EXTRACT(week FROM query_date) as week_number, 
    team, 
    user, 
    count(query_date) as query_cnt
  from advanced.query_logs
  group by all 
) 

✅ 윈도우 함수 연습문제 4번

4) 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리수 작성
## FRAME의 default값은 UNBOUNDED PRECEDING AND CURRENT ROW 

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

select *, 
sum(query_cnt) over (partition by user order by query_date ASC) as cumulative_SUM1, 
sum(query_cnt) over (partition by user order by query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_SUM2, 
from query_cnt_by_team 

❗FRAME의 default값은 UNBOUNDED PRECEDING AND CURRENT ROW

✅ 윈도우 함수 연습문제 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 * , 
ifnull(number_of_orders, LAST_VALUE(number_of_orders IGNORE NULLS) over(order by date)) as filled_orders
from raw_data 

-- 조건절 ifnull 사용할 수 있음. 
-- LAG()를 사용하면 마지막 NULL값인 경우 채우는 값도 NULL! 
-- 그렇기 떄문에, LAST_VALUE()인데, NULL은 무시하라는 IGNORE NULLS! 

❗️LAST_VALUE에서 IGNORE NULLS 안하면 값은 NULL

✅ 윈도우 함수 연습문제 6번

6) 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 * , 
AVG(filled_orders) over (order by date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_orders
from (
  select * , 
  ifnull(number_of_orders, LAST_VALUE(number_of_orders IGNORE NULLS) over(order by date)) as filled_orders
  from raw_data 
) 

✅ 윈도우 함수 연습문제 7번

7) app_logs 테이블에서 custom_session을 만들어 주세요:) 
이전 이벤트 로그와 20초가 지나면 새로운 session을 만들어 주세요. 
event_date | event_timestamp | event_datetime | evnet_name | user_id | 
user_pseudo_id | before_event_datetime | second_diff | session_start | session_id

with base as (
  select event_date, 
  DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') as event_time, 
  event_name, 
  user_id, 
  user_pseudo_id, 
  from advanced.app_logs 
  where 
    event_date = "2022-08-18"
    and user_pseudo_id = "1997494153.8491999091"
  order by event_timestamp
) 

select *, 
  sum(session_start) over (partition by user_pseudo_id order by event_time) as session_num
from ( 
  select 
    *, 
    CASE 
      WHEN before_event_datetime IS NULL THEN 1 
      WHEN second_diff >=20 THEN 1 ELSE 0 END AS session_start

  from (
  select 
    *, 
    DATETIME_DIFF(event_time, before_event_datetime, SECOND) AS second_diff
  from (
    select *, 
    LAG(event_time,1) over (partition by user_pseudo_id order by event_time) as before_event_datetime
    from base
    order by event_time 
  ) 
  ) 
) 

❗새롭게 알게 된 함수 DATETIME_DIFF : 처음에 날짜-시간 차이를 단순히 (-)로만 생각했다가 잘못된 결과 도출 → DATETIME_DIFF 함수로 정답도출!