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

[1~2번]윈도우 함수(탐색 함수) 연습 문제

1번

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 two_next_visit_month
from `advanced.analytics_function_01

2번

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 two_next_visit_month,
  lag(visit_month, 1) over(partition by user_id order by visit_month) as before_visit_month
from `advanced.analytics_function_01`

[1~4번] 윈도우 함수 Frame 연습 문제

1번

/*
전체 계산 할 때 왜 over()를 써야 하는지 몰랐습니다.

그러나 일반 집계 함수의 방식과 비교해보니, 사용 이유를 알게 됐습니다.

집계함수를 사용할 때 over()를 사용했다는 건 '윈도우 함수가 적용된 결과값(각 행마다 결과 값이 반환 되는 것)으로 보겠다'는 의미인 걸 알게 됐습니다.

같은 집계함수를 사용하더라도 '어떤 방식(결과를 1개 행으로만 받아볼지, 여러개 값으로 받아볼지)으로 결과 값을 받아 볼 지' 생각해 봐야 한다는 걸 알았습니다.
*/

select
  *,
  sum(amount) over() as amount_total
from `advanced.orders`

2번

/*
처음에는 누적합을 구하는 건데 왜 over() 안에 order by를 써야하는지 이해하지 못했습니다.

그러나 누적합도 "어떤 방식으로 계산할지 '계산 방식'"을 지정해 줘야 한다는 걸 알게 됐습니다.
/*

select
  *,
  sum(amount) over(order by order_id) as cumulative_sum
from `advanced.orders`

3번

select
  *,
  sum(amount) over(partition by user_id order by order_id) as cumulative_sum_by_user 
from `advanced.orders`

4번

/*
over() 안에 사용하는 order by와 over() 밖에서 사용하는 order by를 명확히 이해했습니다.

윈도우 함수에서 사용하는 order by는 '계산 순서'를 정하는 것이고, 윈도우 함수 밖에서 사용한 order by는 '눈에 보이는 정렬'을 컨트롤 한다는 걸 이해했습니다.

이해할 때 '쿼리 작동 순서'를 생각했더니, 큰 도움이 됐습니다.
*/

select
  *,
  avg(amount) over(order by order_id rows between 5 preceding and 1 preceding) as last_5_orders_avg_amount
from `advanced.orders`
order by
  order_id

[1~7번] 윈도우 함수 연습 문제

1번

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

2번

/*
- date 형식 데이터를 extract()로 주 형식으로 바꾸는 걸 알게 됐습니다
- partition by 내에 2개 이상 컬럼을 넣어도 된다는 걸 알게 됐습니다.

*/
with user_query_cnt 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 user_query_cnt
qualify team_rank = 1
order by
  1,2

3번

/*
- lag()와 함께 쓰는 over() 사용법이 헷갈렸는데, 실행 후 수정하는 방향으로 쿼리를 작성해 보라고 하신 점이 인상 깊게 와닿았습니다.
*/
with base as (
select
  user,
  team,
  extract(week FROM query_date) as week_number,
  count(user) as query_count
from advanced.query_logs
group by all
)

select
  *,
  lag(query_count, 1) over(partition by user order by week_number) as pre_query_cnt
from base

4번

/*
qualify cumulative_query_cnt != cumulative_query_cnt2

위 쿼리로 정합성을 체크하는 방식을 배우게 됐습니다.
*/
## 4번
with query_cnt as (
select
  *,
  count(user) as query_count
from advanced.query_logs
group by all
)

select
  *,
  sum(query_count) over(partition by user order by query_date) as cumulative_query_cnt,
  sum(query_count) over(partition by user order by query_date rows between unbounded preceding and current row) as cumulative_query_cnt2
from query_cnt
-- qualify cumulative_query_cnt != cumulative_query_cnt2

5번

/*
- 4번에서 배운 정합성 체크하는 쿼리를 이용해서 컬럼 내 값이 있는지 테스트 해봤습니다.
*/

## 정합성 테스트
-- select
--   *
-- from (
-- select
--   *,
--   last_value(number_of_orders) over(order by date) as last_values_order
-- from raw_data
-- ) as test1
-- where
--   number_of_orders != last_values_order


select
  *,
  last_value(number_of_orders ignore nulls) over(order by date) as last_values_order
from raw_data

6번

/*
처음에는 1. 평균을 구하고 2. 해당 테이블을 서브쿼리로 두고, lag(컬럼, 2)를 해야 하는줄 알았습니다.

그러나 Frame 사용 방법이 생각났고, 조건에 맞게 범위를 설정해서 문제를 해결했습니다.
*/

## 6번

, base2 as (
select
  *,
  last_value(number_of_orders ignore nulls) over(order by date) as last_values_order,
from raw_data
)

select
  *,
  avg(last_values_order) over(order by date rows between 2 preceding and current row) as moving_avg
from base2

7번

/*
- 타임스탬프 데이터를 다루는 다양한 방법을 알게 됐습니다.
- 실무에서는 세션 기준과 보고 싶은 유저 혹은 이벤트를 지정해서 사용해도 된다는 걸 알게 됐습니다.
*/


with base 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_logs`
where 
  event_date = '2022-08-18'
  and user_pseudo_id = '1997494153.8491999091'
)


select
  *,
  sum(session_start) over(order by event_datetime) as session_id
from (
select
  *,
  case
    when second_diff >= 20 then 1
    when second_diff is null then 1
    else 0
  end session_start
from (
select
  *,
  timestamp_diff(event_datetime, before_event_datetime, second) as second_diff
from (
select
  *,
  lag(event_datetime, 1) over(partition by user_pseudo_id order by event_datetime) as before_event_datetime
from base
) as base2
) as base3
) as base4