window function

윈도우 함수란?

• OVER() 절과 함께 사용하며, 행을 그룹화(Partition), 정렬(Order by), 범위 설정(Rows/Range)하여 다양한 함수들을 제공.
• 윈도우 함수의 기본 문법

SELECT 윈도우함수(컬럼1) OVER (
    [PARTITION BY 컬럼2] -- 그룹화
    [ORDER BY 컬럼3 ASC|DESC] -- 정렬
    [ROWS|RANGE BETWEEN A AND B] -- 계산 범위
) AS 결과
FROM 테이블;

윈도우 함수의 진행 순서

1. Partition 그룹으로 그룹화
2. Order by로 그룹 별 데이터 정렬
3. Rows/Range로 정렬된 데이터 내에서 연산 범위 설정
4. 지정된 범위와 정렬에 따라 윈도우 함수 값 계산

특징

• 집계 함수의 확장 - SUM, AVG 등의 집계 결과를 개별 행에 표시
  • 기존 집계함수는 그룹 단위로 함수가 적용. 윈도우 함수는 각 행의 데이터와 함께 집계 결과 유지
• 원본 데이터 유지
• 다양한 기능의 함수 제공

윈도우 함수 개념과 문법

실행 순서

• FROM - JOIN - WHERE - GROUP BY - HAVING - WINDOW FUNCTION - SELECT - DISTINCT - ORDER BY - LIMIT

윈도우 함수의 종류

순위 함수

RANK()

• 순위를 매기되, 동일한 값에 대해 같은 순위 부여

SELECT
  RANK() OVER(ORDER BY ranking_score) AS rank_number,
  name,
  category,
  ranking_score
FROM product;

• PARTITION BY를 활용해 그룹화를 한 경우

SELECT
  RANK() OVER(PARTITION BY category ORDER BY ranking_score) AS rank_number,
  name,
  category,
  ranking_score
FROM product;

• 공동 RANK가 있다면 그 다음 RANK는 이전 등수의 개수만큼 건너뜀

DENSE_RANK()

• 동일한 값에 같은 순위를 부여. 다음 순위 건너뛰지 않음.

SELECT
  DENSE_RANK() OVER(ORDER BY ranking_score DESC) AS dense_rank_number,
  name,
  category,
  ranking_score
FROM product;

ROW_NUMBER()

• 동일한 값에도 고유한 순위 부여. 따라서 RANK라기보다는 ROW_NUMBER

SELECT
  ROW_NUMBER() OVER(ORDER BY ranking_score) AS row_number,
  name,
  category,
  ranking_score
FROM product;

집계 함수

SUM()

• 그룹화된 파티션에 대해 누적합 계산

SELECT
  toy_name,
  month,
  sale_value,
  SUM(sale_value) OVER(PARTITION BY toy_name ORDER BY month)
    AS total_toy_value
FROM toys_sale;

행 참조 함수

LAG()

• 이전 행 참조. 하나씩 뒤로 끌어 옴.

SELECT
  toy_name,
  month,
  sale_value,
  LAG(sale_value) OVER(PARTITION BY toy_name ORDER BY month)
    AS prev_month_value,
  LAG(sale_value) OVER(PARTITION BY toy_name ORDER BY month) - sale_value
    AS difference
FROM toys_sale;

LEAD()

• 다음 행 참조. 하나씩 앞으로 당겨 옴.

SELECT
  toy_name,
  month,
  sale_value,
  LEAD(sale_value) OVER(PARTITION BY toy_name ORDER BY month)
    AS next_month_value
FROM toys_sale;

비율 함수

PERCENT_RANK()

• 파티션 데이터에 대한 해당 데이터의 백분위 순위 계산

파티션이 없는 경우:

SELECT 
    IdiotName,
    IQ,
    PERCENT_RANK() OVER ( ORDER BY IQ ) AS "Percentage Rank"
FROM Idiots;

결과:

+-------------------+------+--------------------+
| IdiotName         | IQ   | Percentage Rank    |
+-------------------+------+--------------------+
| Dumbest           |   30 |                  0 |
| Homer             |   40 | 0.1111111111111111 |
| Patrick Star      |   40 | 0.1111111111111111 |
| Ed                |   40 | 0.1111111111111111 |
| Dumber            |   50 | 0.4444444444444444 |
| Peter Griffin     |   50 | 0.4444444444444444 |
| Cosmo             |   55 | 0.6666666666666666 |
| Dumb              |   60 | 0.7777777777777778 |
| Ralph Wiggum      |   65 | 0.8888888888888888 |
| Richard Watterson |   70 |                  1 |
+-------------------+------+--------------------+

파티션이 있는 경우:

SELECT 
    DogName,
    Activity,
    Score,
    PERCENT_RANK() OVER ( PARTITION BY Activity ORDER BY Score ) AS "Percentage Rank"
FROM Dogs;

결과:

+---------+-------------+-------+-----------------+
| DogName | Activity    | Score | Percentage Rank |
+---------+-------------+-------+-----------------+
| Bruno   | Fetch Stick |    43 |               0 |
| Cooper  | Fetch Stick |    67 |             0.5 |
| Max     | Fetch Stick |    91 |               1 |
| Bruno   | Keep Quiet  |     1 |               0 |
| Cooper  | Keep Quiet  |     8 |             0.5 |
| Max     | Keep Quiet  |    12 |               1 |
| Bruno   | Wag Tail    |    51 |               0 |
| Cooper  | Wag Tail    |    51 |               0 |
| Max     | Wag Tail    |    87 |               1 |
+---------+-------------+-------+-----------------+

파티션 범위 지정

• 윈도우 함수의 범위는 ROWS 혹은 RANGE를 사용하여 지정

범위 옵션

• UNBOUNDED PRECEDING: 윈도우의 시작(맨 처음)부터 현재 행까지 (기본값)
• CURRENT ROW: 현재 행까지
• N PRECEDING: 현재 행에서 N개 이전 행까지
• N FOLLOWING: 현재 행에서 N개 이후 행까지
• BETWEEN A AND B: A에서 B까지
• UNBOUNDED FOLLOWING: 윈도우 끝(맨 마지막)까

• ROWS: 행 단위로 적용.
• RANGE: 값 단위로 적용. 행의 개수가 몇 개인지에 상관 없이 지정된 값을 가지는 모든 ROW가 RANGE에 포함.

SELECT 
    고객ID,
    결제ID,
    결제금액,
    AVG(결제금액) OVER (
        PARTITION BY 고객ID 
        ORDER BY 결제ID
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS avg_rec_paid --최근(2가지) 평균 과금액 
FROM payments;