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

윈도우 함수의 탐색 함수 : LEAD, LAG, FIRST_VALUE, LAST_VALUE

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


-- SELECT 
--       user_id
--     , visit_month
--     , LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_month
--     , LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_next_month
-- FROM `advanced.analytics_function_01`
-- ORDER BY user_id


## 문제2) user들의 다음 접속 월과 다다음 접속 월,이전 접속 월을 구하는 쿼리를 작성해주세요.

-- LAG 함수를 사용할 때 NULL이 나온다 => 그 값은 처음이다!
-- LEAD 함수를 사용할 때 NULL이 나온다 => 그 값은 마지막이다!

-- SELECT 
--       user_id
--     , visit_month
--     , LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_month
--     , LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_next_month
--     , LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS before_month
-- FROM `advanced.analytics_function_01`
-- ORDER BY user_id


## 3번 : 유저가 접속했을 때, 다음 접속까지의 간격을 구하시오
-- user_id | visit_month | after_visit_month | diff_month

-- SELECT 
--       user_id
--     , visit_month
--     , LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_month
--     , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) - visit_month AS diff_month
-- FROM `advanced.analytics_function_01`
-- ORDER BY user_id

## 윈도우 함수를 이렇게 쓰는게 좋을까? => 중복된 쿼리는 줄이는 것이 좋을 수 있음
-- 쿼리를 수정할 상황이 생김 => 2번 수정 => 굉장히 많아지면 복잡해지고, 실수하기 좋음 
-- 쿼리가 길어지는 것을 무서워하지 말고, 쿼리를 덜 수정할 수 있는 구조를 만들면 좋겠다!
-- 윈도우 함수 쓰다보면 쿼리 줄이 길어짐. 감안하고 사용하면 좋겠다
-- -- 그래서 서브쿼리로 만들어보면,,

-- SELECT
--       *
--     , (after_month - visit_month) AS diff_month
-- FROM (
--   SELECT
--         *
--       , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_month
--   FROM `advanced.analytics_function_01`
-- )
-- ORDER BY user_id


## 추가 문제 : 이 데이터셋을 기준으로 user_id의 첫번째 방문 월, 마지막 방문 월을 구하는 쿼리를 작성해주세요

SELECT
      user_id
    , visit_month
    , FIRST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS first_visit
    , LAST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS last_visit
FROM `advanced.analytics_function_01`
ORDER BY user_id, visit_month

윈도우 함수 Frame 연습문제

-- amount_total : 전체 SUM
-- cumulative_sum : row 시점에 누적 SUM
-- cumulative_sum_by_user : row 시점에 유저별 누적 SUM
-- last_5_orders_avg_amount : order_id 기준으로 정렬하고, 직전 5개의 주문의 평균 amount
-- 집계분석함수() OVER(PARTITION BY ~~~ ORDER BY ~~~ ROWS BETWEEN A and B)

SELECT
      *
    , SUM(amount) OVER() AS amount_total    ## OVER 안에 아무것도 들어가지 않는 경우도 있구나!
    , SUM(amount) OVER(ORDER BY order_id) AS cumulative_sum
    , SUM(amount) OVER(PARTITION BY user_id ORDER BY order_id) AS cumulative_sum_by_user
    , AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amount
    -- , AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    -- , AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
FROM advanced.orders
ORDER BY order_id

윈도우 함수의 연습문제

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


-- 사용자별 쿼리를 실행한 총 횟수 : COUNT() 전체 실행.
-- OVER(PARTITION BY user)
-- SELECT
--       * 
--     , COUNT(query_date) OVER(PARTITION BY user) AS total_query_cnt
-- FROM `advanced.query_logs`
-- ORDER BY query_date

## 2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요.
-- 주차별로 개인당 실행한 쿼리 횟수
-- 위 쿼리 횟수를 기반으로 랭킹
-- 랭킹을 기반으로 필터링(랭킹=1)
-- 문제의 의도 : 원본 데이터 => 1 row마다 데이터가 있고, 그걸 집계해서 사용. GROUP BY => 윈도우 함수 

-- SELECT 
--       *
--     , RANK() OVER(PARTITION BY week_number, team ORDER BY query_cnt DESC) 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


-- ## ## WITH 문 풀이
-- WITH query_cnt_by_team AS (
--   SELECT
--       EXTRACT(week from query_date) AS week_number,
--       team,
--       user, 
--       COUNT(query_date) AS query_cnt
--   FROM `advanced.query_logs`
--   GROUP BY ALL
-- )

-- SELECT
--   *,
--   RANK() OVER(PARTITION BY week_number, team ORDER BY query_cnt DESC) AS rk 
-- FROM query_cnt_by_team
-- -- QUALIFY : 윈도우 함수의 조건을 설정할 때 사용
-- QUALIFY rk = 1
-- ORDER BY week_number, team, query_cnt DESC
-- -- 16주차 - AI팀의 케이피, 16주차 - 코칭팀의 카일, 16주차 - 데이터 사이언스팀의 샘


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

-- WITH query_cnt_by_team AS (
--   SELECT
--       EXTRACT(week from query_date) AS week_number,
--       team,
--       user, 
--       COUNT(query_date) AS query_cnt
--   FROM `advanced.query_logs`
--   GROUP BY ALL
-- )

-- SELECT
--   user,
--   team,
--   week_number,
--   query_cnt,
--   LAG(query_cnt) OVER(PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt
-- FROM query_cnt_by_team
-- ORDER BY user

-- -- ans T)

-- WITH query_cnt_by_team AS (
--   SELECT
--       EXTRACT(week from query_date) AS week_number,
--       team,
--       user, 
--       COUNT(query_date) 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_cnt
-- FROM query_cnt_by_team


## 4) 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요.
-- 누적 쿼리 : 과거의 시간(UNBOUNDED PRECEDING)부터 current row까지
-- 출제 의도 : Default Frame에 대해 알려드리고 싶었음.
-- For aggregate analytic functions, if the ORDER BY clause is present but the window frame clause is not, the following window frame clause is used by default:
-- SELECT
--   *,
--   SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_sum
-- FROM (
--       SELECT
--             user,
--             team,
--             query_date,
--             COUNT(query_date) AS query_cnt
--       FROM `advanced.query_logs`
--       GROUP BY ALL
-- )

-- -- ans T)

-- SELECT
--   *,
--   SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_sum,
--   SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum2
-- -- Frame의 Default 값 : UNBOUNDED PRECEDING ~ CURRENDT ROW
-- FROM (
--       SELECT 
--       query_date,
--       team,
--       user,
--       COUNT(user) AS query_cnt
--       FROM `advanced.query_logs`
--       GROUP BY ALL
-- )
-- -- QUALIFY cumulative_sum != cumulative_sum2
-- -- -- WHERE, QUALIFY 조건 설정해서 2가지 값이 모두 같은지 비교 => 모두 같으면 != 연산 결과에 반환하는 값이 없을 
-- ORDER BY user, query_date


## 5) 다음 데이터는 주문횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요.

-- LAG로 직전 값 가져오면 되지 않을까?
  -- number_of_orders가 null 이면, before_number_of_orders를 가져와라!
  -- 아래 쿼리는 어려운 방법
-- 그 다음 방법 : LAST VALUE를 쓰자! => 값이 없으면 NULL이 뜬다 ! 
-- FIRST_VALUE, LAST_VALUE => NULL 을 포함해서 연산
--  출제 의도 : NULL 을 제외해서 연산하고 싶으면 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
-- --   date,
-- --   IFNULL(number_of_orders, last_value_orders) AS numbers_of_orders
-- -- FROM (
-- --       SELECT
-- --             *,
-- --             -- LAG(number_of_orders) OVER(ORDER BY date) AS prev_orders,      ## 마지막 값  NULL !! 
-- --             LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS last_value_orders
-- --       FROM raw_data
-- -- )

-- -- -- ans T)

-- filled_data AS(
--   SELECT 
--     * EXCEPT(number_of_orders),
--     LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS number_of_orders
--   FROM raw_data
-- )


-- ## 6) 5번 문제에서 NULL을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요 (이동평균)
-- -- Frame : 2일 전 => BTWEEN 2 PRECEDING AND CURRENT ROW
-- -- 출제 의도 : Frame을 지정할 수 있는가?

-- SELECT
--   *,
--   AVG(number_of_orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
-- FROM filled_data


## 7) app_logs 테이블에서 CustomSession을 만들어주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어주세요. Session은 숫자로(1,2,3…) 표시해도 됩니다.
## 2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다


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'
),

base2 as (
  SELECT
    *,
    LAG(event_datetime, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) as before_event_datetime
  FROM base
)

SELECT
  *,
  SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) as session_id
FROM (
  SELECT
    *,
    IF(second_diff is NULL or second_diff > 20, 1, NULL) as session_start
  FROM (
    SELECT
      *,
      DATETIME_DIFF(event_datetime, before_event_datetime, second) as second_diff
    FROM base2
  )
)
ORDER BY event_timestamp