📌 윈도우 함수(Window)

• ORACLE에서는 분석 함수(Analytics)라고도 불림
• 창을 통해 풍경의 일부분을 볼 수 있듯이, 윈도우 함수를 통해 일부분을 계산할 수 있도록 돕는 함수
• 그룹 내 집계함수, 순위 등을 계산할 때 사용

**윈도우함수 OVER(PARTITION BY 그룹을 나눌 칼럼 ORDER BY 정렬 칼럼) AS 별칭**

SUM() OVER(PARTITION BY col ORDER BY col2 ) AS name

⭐ 윈도우 함수 종류

윈도우 함수는 크게 탐색함수, 순위함수, 집계분석함수로 나뉨.

1. 탐색함수

• LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()
• PARTITION으로 나눈 특정 그룹에서 이전행의 값, 후속행의 값, 첫번째 값, 마지막값을 볼 수 있음
• LAG, LEAD의 경우 column 뿐 아니라, 번호를 지정하여 몇 번째 값을 볼 것인지 지정할 수 있음

SELECT 
    user_id,
    date,
    cnt,
    LAG(cnt, 2) OVER (PARTITION BY user_id ORDER BY date) AS cnt_lag_2,
    LEAD(cnt, 2) OVER (PARTITION BY user_id ORDER BY date) AS lead_cnt_2,
    FIRST_VALUE(cnt) OVER (PARTITION BY user_id ORDER BY date) AS first_cnt,
    LAST_VALUE(cnt) OVER (PARTITION BY user_id ORDER BY date 
                          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_cnt
FROM 
    your_table;

2. 순위함수

• ROW_NUMBER(), RANK(), DENSE_RANK()가 있음
• 데이터의 순위를 매겨주는 함수

SELECT 
    user_id,
    score,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score DESC) AS row_num,
    RANK() OVER (PARTITION BY user_id ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY score DESC) AS dense_rank
FROM 
    your_table;

• ROW_NUMER는 중복 순위 값에 랜덤으로 값 부여. 중복되는 순위값이 없음
• RANK는 중복 순위값에 중복값 부여. 다음 점수는 이전 순위 수 만큼 건너 뛰고 순위 부여 (이전순위 데이터 갯수+1)
• DENSE_RANK는 중복 순위값에 중복값 부여. 다음점수는 그 다음 순위 부여

3. 집계분석함수

• AVG, SUM, COUNT, MIN,MAX
• 집계함수와 기능은 동일하나, 집계함수는 값만 도출해내는 반면, 집계분석함수는 새로운 COLUMN에 값을 도출

SELECT 
    user_id,
    score,
    SUM(score) OVER (PARTITION BY user_id ORDER BY date) AS cumulative_score,
    AVG(score) OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average,
    COUNT(score) OVER (PARTITION BY user_id ORDER BY date) AS cumulative_count,
    MIN(score) OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS min_score,
    MAX(score) OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS max_score
FROM 
    your_dataset.your_table;

📌 FRAME 설정

• 윈도우 함수와 사용되며, 범위를 지정해주는 역할

FRAME 종류

• 크게 ROW, RANGE로 종류가 나뉨
  1. ROW
• 데이터의 행 수를 기준으로 범위 설정
• OVER() 내부에서 ROWS BETWEEN A AND B (A,B는 범위를 나타내는 구문)
• 아래 예시는 현재 행을 기준으로 1이전행을 포함(1이전행 ~ 현재행)

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (ORDER BY employee_id 
                      ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS running_total
FROM 
    employees;

2. RANGE

• 값의 범위를 기준으로 범위 설정
• OVER()안에 RANGE BETWEEN A AND B (A,B는 범위를 나타내는 구문)
• 아래 예시는 현재 날짜를 기준으로 이전 하루 동안의 모든 행을 포함(현재날짜 기준 하루 전~현재날짜)
• 수치와 날짜 데이터에 주로 사용됨

SELECT 
    order_id,
    order_date,
    SUM(amount) OVER (ORDER BY order_date 
                      RANGE BETWEEN INTERVAL '1 DAY' PRECEDING AND CURRENT ROW) AS total_sales
FROM 
    orders;

⭐ FRAME 범위 설정 구문

• ROW, RANGE의 범위 설정 구문은 거의 동일

1. UNBOUNDED PRECEDING: 현재 행을 포함하여, 모든 이전 행. (모든 이전행 <=현재)
2. n PRECEDING: 현재 행의 n개 이전 행. (이전 N행<현재)
3. CURRENT ROW: 현재 행.
4. UNBOUNDED FOLLOWING: 현재 행 이후의 모든 행.(현재<=모든 이후행)
5. n FOLLOWING: 현재 행의 n개 이후 행.(현재<=이후 N 행)

• INTERVAL은 RANGE절에서 주로 사용됨
• 아래 예시는 이전 7일 범위 내의 모든 행 포함. 현재 값이 14이면 7~14인 모든 데이터.

SELECT 
    order_id,
    order_date,
    SUM(order_amount) OVER (
        ORDER BY order_date 
        RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
    ) AS weekly_sales
FROM orders;

⭐📌 QUALIFY

• 윈도우 함수로 나온 값을 필터링

• WHERE는 데이터 실행 전 필터링 실행.

• QUALIFY는 윈도우 함수 사용시 적용되며, 윈도우 함수 결과를 바탕으로 필터링되는 차이점

• 급여 랭크가 1인 사람 조회하는 예시. RANK를 사용한 윈도우 함수 이후에 QUALIFY문이 실행됨.

SELECT 
    employee_id,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees
QUALIFY salary_rank = 1;

---

⭐ 중요 개념 및 함수

• 윈도우 함수
• FRAME
• QULIFY

⭐ 습관들이기

• ORDER BY를 사용하여 특정시점까지의 누적값을 구할 수 있다.
• FRAME에는 순서가 중요하다
• USER_ID와 USER_PSEUDO_ID 차이 유의
• CTE를 사용하는 것도 좋지만 간단한 코드는 서브쿼리를 사용해 보자
• IF문은 행 단위로 작동, SUM은 특정 파티션에 대한 누적합을 계산
• QUALIFY 로 조건설정을 하여 두 값이 같은 지 비교가능
• COUNT의 윈도우 함수 대신 GROUP BY를 사용하여 간략하게 코드를 작성할 수 있다.

---

참고

• ChatGPT : 각 함수 예시, ROW와 RANGE 예시, INTERVAL
• BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
  : https://www.inflearn.com/course/bigquery-%ED%99%9C%EC%9A%A9%ED%8E%B8