윈도우 함수

1. 윈도우 함수란

윈도우 함수는 테이블의 행을 유지한 상태에서 순위, 누적합, 이전값·다음값 같은 값을 계산하는 함수다.
GROUP BY처럼 여러 행을 하나로 줄여버리는 함수가 아니라, 원본 행을 그대로 남겨두면서 각 행 옆에 계산 결과를 추가하는 함수라고 볼수 있다

윈도우 함수의 큰 분류로는 순위 함수, 그룹 내 집계 함수, 그룹 내 행 순서 함수, 그룹 내 비율 함수로 정리할수 있다

2. 윈도우 함수 기본 문법

윈도우 함수는 반드시 OVER()와 함께 사용해야 한다.
함수 옆에 OVER()가 없다면 윈도우 함수가 아니다. OVER() 안에는 다음 요소가 들어갈 수 있다.

WINDOW_FUNCTION() OVER (
[PARTITION BY 컬럼]
[ORDER BY 컬럼]
[WINDOWING 절]
)

• PARTITION BY : 그룹을 나누는 기준
• ORDER BY : 각 그룹 안에서 정렬하는 기준
• WINDOWING : 현재 행을 기준으로 어디까지 계산할지 정하는 범위

어떤 함수를 쓸지 정하고, OVER() 안에서 그룹 기준, 정렬 기준, 계산 범위를 정하는 방식이다.

즉 윈도우 함수는 단순 계산 함수가 아니라, 행의 맥락을 보고 계산하는 함수라고 이해하면 된다.

3. 그룹 내 순위 함수

그룹 내 순위 함수로 RANK, DENSE_RANK, ROW_NUMBER가 있다
이 함수들은 비슷해 보이지만 동점 처리 방식이 서로 다르다.

RANK()

RANK는 정렬 기준에 따라 순위를 매기는데, 동점이면 같은 순위를 부여하고 다음 순위를 건너뛴다.

예시) 매출이 같은 두 상품이 2등이면, 그다음 순위는 3등이 아니라 4등으로 표시하기

SELECT BRANCH_ID, PRODUCT_NAME, SALES_AMOUNT,
RANK() OVER (ORDER BY SALES_AMOUNT DESC) AS RANKING
FROM SALES_DATA;

 

1→2=2 → 4

따라서 RANK는 경쟁 순위처럼 생각하면 이해하기 쉽다.

DENSE_RANK()

DENSE_RANK도 동점이면 같은 순위를 부여한다.
다만 RANK와 다른 점은 다음 순위를 건너뛰지 않는다는 것이다.

예시) 두 명이 공동 2등이면 그다음은 4등이 아니라 3등으로 표시하기

SELECT BRANCH_ID, PRODUCT_NAME, SALES_AMOUNT,
DENSE_RANK() OVER (ORDER BY SALES_AMOUNT DESC) AS RANKING
FROM SALES_DATA;

1→2=2 → 3

 

따라서 DENSE_RANK는 촘촘한 순위라고 보면 된다.

ROW_NUMBER()

ROW_NUMBER는 동점 여부와 상관없이 모든 행에 고유한 번호를 순서대로 부여한다.
따라서 같은 값이 있어도 순위를 공유하지 않고, 무조건 1, 2, 3, 4처럼 나간다. 

예시)

SELECT BRANCH_ID, PRODUCT_NAME, SALES_AMOUNT,
ROW_NUMBER() OVER (ORDER BY SALES_AMOUNT DESC) AS RANKING
FROM SALES_DATA;

 

1→2→3→4

ROW_NUMBER는 순위라기보다 행 번호 매기기에 더 가깝다.

PARTITION BY를 함께 쓰는 ROW_NUMBER

ROW_NUMBER에 PARTITION BY를 함께 쓰는 예시
이 경우 전체 데이터를 한 번에 순위 매기는 것이 아니라, 지점별로 그룹을 나눠서 각 지점 안에서 다시 순위를 매기게 된다.

SELECT BRANCH_ID, PRODUCT_NAME, SALES_AMOUNT,
ROW_NUMBER() OVER (
PARTITION BY BRANCH_ID
ORDER BY SALES_AMOUNT DESC
) AS RN
FROM SALES_DATA;

이 쿼리는 BRANCH_ID별로 그룹을 나누고, 각 지점 안에서 매출이 높은 순으로 1, 2, 3 순번을 붙인다.
지점 001 안에서는 1~3, 지점 002 안에서도 다시 1~3처럼 번호가 새로 시작한다.

따라서 PARTITION BY는 GROUP BY처럼 행을 줄이는 것이 아니라, 계산 단위를 끊어주는 역할이라고 이해하면 된다.

4. 그룹 내 집계 함수

윈도우 함수에서는 SUM, AVG 같은 집계 함수도 사용할 수 있다.
GROUP BY는 결과 행이 줄어들지만, 윈도우 함수는 행을 유지한 채로 각 행 옆에 누적합이나 평균 같은 값을 추가한다.

예시)

SELECT BRANCH_ID, PRODUCT_NAME, SALES_AMOUNT, MONTH,
SUM(SALES_AMOUNT) OVER (
PARTITION BY BRANCH_ID
ORDER BY MONTH
) AS 누적합
FROM SALES_DATA;

 

이 경우 각 지점별로 월 순서에 따라 누적합을 계산할 수 있다.
따라서 지점 안에서 1월 값, 1월+2월 값, 1월+2월+3월 값이 순서대로 붙는 식이다.

WINDOWING 절

윈도우 함수의 핵심 중 하나는 WINDOWING 절이다.
이 절은 현재 행을 기준으로 어디까지를 계산 범위로 볼지 정하는 역할을 한다.

WINDOWING 절을 생략하면 기본 범위가 적용된다

대표 표현은 다음과 같다.

• UNBOUNDED PRECEDING : 그룹 내 첫 행부터
• UNBOUNDED FOLLOWING : 그룹 내 마지막 행까지
• CURRENT ROW : 현재 행
• RANGE : 값의 범위를 기준으로
• ROWS : 물리적인 행 기준으로

WINDOWING 절은 현재 행을 중심으로 어느 구간까지 합계를 낼 것인가를 정하는 규칙이라고 보면 된다

ROWS 기반 누적합 예시

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 는 현재 행이 어디에 있든, 파티션의 첫 행부터 마지막 행까지를 모두 포함해서 합계를 계산한다. 그래서 같은 지점 안의 모든 행에 동일한 총합이 붙는다.

SELECT BRANCH_ID, PRODUCT_NAME, SALES_AMOUNT, MONTH,
SUM(SALES_AMOUNT) OVER (
PARTITION BY BRANCH_ID
ORDER BY MONTH
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS 합계
FROM SALES_DATA;

 

반면 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW로 바꾸면 누적합이 된다.
즉 첫 행부터 현재 행까지만 더하기 때문에, 행이 내려갈수록 값이 커지는 형태가 된다

RANGE 기반 범위 합계

RANGE BETWEEN 5000 PRECEDING AND 10000 FOLLOWING 일때 ROWS는 행의 개수를 기준으로 범위를 정하지만, RANGE는 정렬 기준 컬럼의 값 차이를 기준으로 범위를 정한다.

예를 들어 SALES_AMOUNT를 기준으로 RANGE를 잡으면, 현재 행의 매출액 기준 ±범위 안에 들어오는 값들을 함께 계산하게 된다.

따라서 RANGE는 물리적 위치가 아니라 값의 범위로 묶는 방식이라는 점이 중요하다.

5. 그룹 내 행 순서 함수

그룹 내 행 순서 함수로 LAG, LEAD가 있다
이 함수들은 이전 행, 다음 행 값을 가져올 때 사용한다. 따라서 같은 그룹 안에서 “바로 전 값과 비교”하거나 “다음 값 확인” 같은 작업을 할 수 있다.

LAG()

LAG는 현재 행을 기준으로 이전 행의 값을 가져오는 함수다.

• 컬럼 : 이전 행의 값을 가져올 대상
• 오프셋 : 몇 번째 이전 행인지 지정
• 디폴트 : 값이 없을 경우 반환할 기본값

SELECT BRANCH_ID, PRODUCT_NAME, SALES_AMOUNT, MONTH,
LAG(SALES_AMOUNT, 1) OVER (
PARTITION BY BRANCH_ID
ORDER BY MONTH
) AS PREV_SALES
FROM SALES_DATA;

 

이 쿼리는 같은 지점 안에서 바로 이전 월의 매출액을 현재 행 옆에 보여준다.
첫 번째 행은 이전 값이 없으므로 NULL이 붙는다.

LEAD()

LEAD는 현재 행을 기준으로 다음 행의 값을 가져오는 함수다.
LAG와 구조는 같지만 방향만 반대라고 보면 된다. 

SELECT BRANCH_ID, PRODUCT_NAME, SALES_AMOUNT, MONTH,
LEAD(SALES_AMOUNT, 2, 999) OVER (
PARTITION BY BRANCH_ID
ORDER BY MONTH
) AS NEXT_SALES
FROM SALES_DATA;

 

이 예시는 현재 행 기준 두 번째 이후 행 값을 가져오고, 값이 없으면 999를 반환한다.
따라서 LEAD는 “다음 달 예측값 보기”, “다음 행과 비교하기” 같은 상황에서 유용하다.

6. 그룹 내 비율 함수

그룹 내 비율 함수로 RATIO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTILE가 있다
이 함수들은 현재 행이 그룹 안에서 차지하는 비율이나 상대적 위치를 계산할 때 사용한다.

RATIO_TO_REPORT()

RATIO_TO_REPORT는 현재 행의 값이 그룹 전체 합계에서 차지하는 비율을 계산한다.
예시)지점별 총매출 대비 각 상품 매출이 몇 %인지 구하기

SELECT BRANCH_ID, PRODUCT_NAME, SALES_AMOUNT,
RATIO_TO_REPORT(SALES_AMOUNT) OVER (
PARTITION BY BRANCH_ID
) AS SALES_RATIO
FROM SALES_DATA;

따라서 이 함수는 “이 상품이 지점 매출에서 얼마나 비중을 차지하는가”를 볼 때 유용하다.

PERCENT_RANK()

PERCENT_RANK는 전체 데이터 안에서 현재 행이 어느 정도 백분위 위치에 있는지 계산한다.
예시)각 지점 안에서 몇 번째 백분위에 해당하는지 구하시오

0부터 1 사이 값으로 상대적 순위를 나타낸다고 이해하면 된다.

CUME_DIST()

CUME_DIST는 현재 행까지의 누적된 분포 비율을 계산한다.
예시) 현재 값 이하의 행이 전체에서 몇 %인가요?

NTILE()

NTILE은 데이터를 N개의 그룹으로 나눈다.

NTILE(4)를 사용하면 4분위처럼 데이터를 나눌 수 있다. 순위를 단순히 숫자로 주는 것이 아니라, 몇 번째 구간에 속하는지를 알려주는 함수다.

예시) 학생 점수를 4개의 분위로 나누시오

 

SELECT STUDENT_ID, SCORE,
NTILE(4) OVER (ORDER BY SCORE DESC) AS QUARTILE
FROM STUDENT_SCORE;

7. PARTITION BY와 GROUP BY의 차이

이 둘은 이름이 비슷해서 많이 헷갈리지만 결과가 다르다.

• PARTITION BY : 원본 행을 유지한 상태에서 그룹별 계산 결과를 각 행에 붙인다.
• GROUP BY : 같은 그룹끼리 묶어서 행 수가 줄어든 집계 결과를 만든다.

예시) 아래 쿼리는 원본 행을 유지하면서 지점별 합계를 각 행에 붙인다.

SELECT BRANCH_ID, PRODUCT_NAME, SALES_AMOUNT,
SUM(SALES_AMOUNT) OVER (PARTITION BY BRANCH_ID) AS BRANCH_TOTAL
FROM SALES_DATA;

 

예시) 반면 아래 쿼리는 지점별로 하나의 행만 남긴다.

SELECT BRANCH_ID, SUM(SALES_AMOUNT) AS BRANCH_TOTAL
FROM SALES_DATA
GROUP BY BRANCH_ID;

 

따라서 PARTITION BY는 붙이기, GROUP BY는 줄이기라고 생각하면 이해하기 쉽다.