윈도우 프레임과 분석 함수 상세
PARTITION BY, ORDER BY, ROWS/RANGE 프레임과 순위·집계·순서·비율 함수를 결과 예측 기준으로 정리합니다.
출제 빈도 ★★★★★ 관련 문제 11개 |
OVERRANKLAGFIRST_VALUENTILE 윈도우 함수는 “행을 유지하는 분석”입니다
GROUP BY는 여러 행을 한 행으로 줄입니다. 반면 윈도우 함수는 원래 행을 유지하면서 옆에 순위, 누적합, 이전값 같은 분석 결과를 붙입니다.
SELECT empno,
deptno,
sal,
SUM(sal) OVER (PARTITION BY deptno) AS dept_sal_sum
FROM emp;
이 쿼리는 사원 행을 유지하면서 각 사원이 속한 부서의 급여 합계를 함께 보여줍니다.
OVER 절 구성
함수() OVER (
PARTITION BY 그룹기준
ORDER BY 정렬기준
ROWS BETWEEN 시작점 AND 끝점
)
| 요소 | 의미 | 생략 가능? |
|---|---|---|
PARTITION BY | 계산 범위를 나누는 그룹 (없으면 전체가 한 파티션) | ○ |
ORDER BY | 파티션 안에서 계산 순서 결정 | △ (순위·LAG 등에는 필수) |
ROWS 또는 RANGE | 현재 행을 기준으로 볼 행 범위 | ○ (기본값 있음) |
ROWS와 RANGE — 프레임의 두 방식
ROWS (물리적 행 개수)
- “현재 행 기준 몇 행 앞/뒤” 개념
- 정렬 값이 같아도 각각 다른 행으로 셈
- 누적합·이동평균 계산에 주로 사용
ROWS BETWEEN
1 PRECEDING AND 1 FOLLOWING
-- 앞 1행 + 현재 + 뒤 1행 = 3행
RANGE (정렬 값의 범위)
- “현재 정렬 값 ± 범위” 개념
- 같은 정렬 값의 행은 한 그룹으로 묶임
- 동점이 있으면 ROWS와 결과 달라짐
RANGE BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW
-- 기본값 (ORDER BY 있을 때)
-- 첫 행부터 현재 행까지 누적합
SUM(sal) OVER (
ORDER BY empno
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
순위 함수
| 함수 | 동점 처리 | 예 (50, 40, 40, 30) |
|---|---|---|
RANK | 동점 후 순위 건너뜀 | 1, 2, 2, 4 |
DENSE_RANK | 동점 후 순위 이어짐 | 1, 2, 2, 3 |
ROW_NUMBER | 동점에도 고유 번호 | 1, 2, 3, 4 |
SELECT ename, sal,
RANK() OVER (ORDER BY sal DESC) r,
DENSE_RANK() OVER (ORDER BY sal DESC) dr,
ROW_NUMBER() OVER (ORDER BY sal DESC) rn
FROM emp;
순서 함수
| 함수 | 의미 |
|---|---|
LAG(col, n, default) | 이전 n번째 행 값 (없으면 default) |
LEAD(col, n, default) | 다음 n번째 행 값 |
FIRST_VALUE(col) | 프레임 첫 값 |
LAST_VALUE(col) | 프레임 마지막 값 (프레임 주의!) |
-- 월별 매출 증감 (전월 대비)
SELECT month, sales,
sales - LAG(sales, 1, 0) OVER (ORDER BY month) AS diff
FROM monthly_sales;
비율 함수
| 함수 | 의미 | 첫 행 | 마지막 행 |
|---|---|---|---|
CUME_DIST | 누적 분포 (≤ 현재까지의 비율) | 1/N | 1 |
PERCENT_RANK | 순위 기반 백분율 (rank-1)/(N-1) | 0 | 1 |
NTILE(n) | n개 구간으로 균등 분할 | 1 | n |
RATIO_TO_REPORT | 전체 합 대비 비율 | — | — |
-- 급여 상위 25% / 25~50% / 50~75% / 하위 25%
SELECT ename, sal,
NTILE(4) OVER (ORDER BY sal DESC) AS quartile
FROM emp;
PARTITION BY 단독 사용
PARTITION BY만 쓰고 ORDER BY가 없으면 파티션 전체를 하나의 프레임으로 보고 집계합니다.
-- 각 사원의 급여와 그 부서 평균을 한 행에
SELECT ename, deptno, sal,
AVG(sal) OVER (PARTITION BY deptno) AS dept_avg,
sal - AVG(sal) OVER (PARTITION BY deptno) AS diff
FROM emp;
이 패턴은 “개별 행 값과 그룹 평균을 함께 보기”의 정석입니다. GROUP BY를 쓰면 원본 행이 사라져 비교할 수 없습니다.
윈도우 함수 위치 규칙
시험 풀이 기준
- 행 수가 유지되면 윈도우 함수입니다.
- 누적합 문제는 프레임 시작점과 끝점을 확인합니다.
- 동점 순위는
RANK/DENSE_RANK/ROW_NUMBER를 구분합니다. LAST_VALUE는 프레임이 어디까지인지 반드시 확인합니다.WHERE·HAVING에 윈도우 함수를 직접 사용할 수 없습니다.
윈도우 함수는 파티션·정렬·프레임 세 가지를 읽어야 결과를 정확히 예측할 수 있습니다.