윈도우 함수 완벽 정리
RANK·ROW_NUMBER·SUM OVER부터 LAG/LEAD, ROWS vs RANGE까지 시험 빈출 포인트 총정리
1. 윈도우 함수 기본 구문
윈도우 함수란?
행과 행 간의 관계를 정의하여 계산하는 함수로, GROUP BY 없이도 집계 결과를 각 행에 함께 표시할 수 있다. OVER 절을 반드시 포함하며, 결과 집합을 줄이지 않고 원본 행을 유지한다.
기본 구문
함수명(컬럼) OVER (
[PARTITION BY 컬럼]
[ORDER BY 컬럼 [ASC|DESC]]
[ROWS|RANGE BETWEEN ... AND ...]
)
| 구성 요소 | 설명 | 필수 여부 |
|---|---|---|
| PARTITION BY | 그룹(파티션) 나누기 | 선택 |
| ORDER BY | 파티션 내 정렬 기준 | 순위 함수는 필수 |
| ROWS/RANGE | 윈도우 프레임(범위) 지정 | 선택 |
2. 순위 함수
RANK vs DENSE_RANK vs ROW_NUMBER
| 함수 | 동작 | 동점 처리 |
|---|---|---|
| RANK() | 동순위 허용, 다음 순위 건너뜀 | 1, 2, 2, 4 |
| DENSE_RANK() | 동순위 허용, 다음 순위 연속 | 1, 2, 2, 3 |
| ROW_NUMBER() | 동순위 없이 무조건 연속 번호 | 1, 2, 3, 4 |
같은 데이터로 결과 비교
SAL 기준 내림차순 정렬, 동일 급여(3000)가 2명인 경우:
| ENAME | SAL | RANK | DENSE_RANK | ROW_NUMBER |
|---|---|---|---|---|
| KING | 5000 | 1 | 1 | 1 |
| SCOTT | 3000 | 2 | 2 | 2 |
| FORD | 3000 | 2 | 2 | 3 |
| JONES | 2975 | 4 | 3 | 4 |
JONES의 순위가 각각 4, 3, 4로 다른 점이 핵심 출제 포인트!
사용 예시
SELECT ename, sal,
RANK() OVER (ORDER BY sal DESC) AS rk,
DENSE_RANK() OVER (ORDER BY sal DESC) AS dr,
ROW_NUMBER() OVER (ORDER BY sal DESC) AS rn
FROM emp;
3. 집계 윈도우 함수
집계 함수 + OVER
일반 집계 함수에 OVER 절을 붙이면 GROUP BY 없이 각 행에 집계 결과를 함께 표시할 수 있다.
| 함수 | 설명 | 예시 |
|---|---|---|
| SUM() OVER | 합계 | 부서별 급여 합계를 각 행에 표시 |
| AVG() OVER | 평균 | 부서별 평균 급여 |
| COUNT() OVER | 건수 | 부서별 인원수 |
| MAX() OVER | 최댓값 | 부서별 최고 급여 |
| MIN() OVER | 최솟값 | 부서별 최저 급여 |
누적합(Running Total) 패턴
ORDER BY와 함께 사용하면 현재 행까지의 누적 합계를 계산할 수 있다. 기본 윈도우 프레임은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
SELECT ename, sal,
SUM(sal) OVER (ORDER BY sal) AS running_total
FROM emp;
PARTITION BY를 추가하면 그룹별 누적합도 가능:
SUM(sal) OVER (PARTITION BY deptno
ORDER BY sal) AS dept_running_total
4. 행 순서 함수
LAG / LEAD
| 함수 | 설명 | 기본값 |
|---|---|---|
| LAG(col, n) | 현재 행 기준 n행 이전 값 | n 생략 시 1, 없으면 NULL |
| LEAD(col, n) | 현재 행 기준 n행 이후 값 | n 생략 시 1, 없으면 NULL |
SELECT ename, hiredate,
LAG(hiredate, 1) OVER (ORDER BY hiredate) AS prev_hire,
LEAD(hiredate, 1) OVER (ORDER BY hiredate) AS next_hire
FROM emp;
3번째 인자로 기본값 지정 가능: LAG(sal, 1, 0) → 이전 행 없으면 0 반환
FIRST_VALUE / LAST_VALUE
| 함수 | 설명 | 주의사항 |
|---|---|---|
| FIRST_VALUE(col) | 파티션의 첫 번째 행 값 | ORDER BY 기준 첫 행 |
| LAST_VALUE(col) | 파티션의 마지막 행 값 | 기본 프레임이 CURRENT ROW까지이므로 주의 |
LAST_VALUE를 제대로 쓰려면 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING을 명시해야 파티션 전체의 마지막 값을 얻을 수 있다.
5. ROWS vs RANGE 차이
윈도우 프레임 키워드
| 키워드 | 의미 |
|---|---|
| UNBOUNDED PRECEDING | 파티션의 첫 번째 행 |
| CURRENT ROW | 현재 행 |
| UNBOUNDED FOLLOWING | 파티션의 마지막 행 |
| n PRECEDING / FOLLOWING | 현재 행 기준 n행 이전/이후 |
ROWS vs RANGE 비교
| 구분 | ROWS | RANGE |
|---|---|---|
| 기준 | 물리적 행 수 기준 | 논리적 값 범위 기준 |
| 동일값 처리 | 각각 별도 행으로 취급 | 동일 값은 같은 그룹으로 취급 |
| 기본값 | - | ORDER BY 지정 시 기본값 |
ORDER BY만 지정하면 기본 프레임은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW이다. 동일값이 있을 때 ROWS와 RANGE 결과가 달라질 수 있다.
자주 쓰는 프레임 패턴
- 누적합: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- 전체 합: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
- 이동평균 (3행): ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
6. PARTITION BY vs GROUP BY
핵심 차이
| 구분 | GROUP BY | PARTITION BY |
|---|---|---|
| 결과 행 수 | 그룹 수만큼 줄어듦 | 원본 행 수 유지 |
| 개별 행 조회 | 불가 (집계된 결과만) | 가능 (개별 행 + 집계 함께) |
| 사용 위치 | FROM/WHERE 이후 | OVER() 내부 |
-- GROUP BY: 부서별 1행
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;
-- PARTITION BY: 모든 행 + 부서별 평균
SELECT ename, deptno, sal,
AVG(sal) OVER (PARTITION BY deptno) AS dept_avg
FROM emp;
7. 시험 빈출 포인트
자주 출제되는 핵심 포인트
| 포인트 | 설명 |
|---|---|
| 순위 함수 결과 비교 | 동점자 있을 때 RANK(4건너뜀), DENSE_RANK(연속), ROW_NUMBER(고유번호) 차이 |
| WHERE절 사용 불가 | 윈도우 함수는 SELECT절에서만 사용 가능. WHERE절 필터링은 인라인 뷰로 감싸야 함 |
| 실행 순서 | 윈도우 함수는 WHERE, GROUP BY, HAVING 이후에 실행됨 |
| LAST_VALUE 함정 | 기본 프레임이 CURRENT ROW까지여서 기대와 다른 결과. UNBOUNDED FOLLOWING 필요 |
| ROWS vs RANGE | 동일 값이 있을 때 결과 차이. ORDER BY 기본은 RANGE |
WHERE절에서 윈도우 함수 사용하기
윈도우 함수는 WHERE절에서 직접 사용할 수 없다. 인라인 뷰(서브쿼리)로 감싸서 필터링해야 한다.
-- 에러! WHERE절에 윈도우 함수 직접 사용
SELECT * FROM emp
WHERE RANK() OVER (ORDER BY sal DESC) <= 3;
-- 정상! 인라인 뷰로 감싸기
SELECT * FROM (
SELECT ename, sal,
RANK() OVER (ORDER BY sal DESC) AS rk
FROM emp
) WHERE rk <= 3;
개념을 확인했다면 문제로 실력을 검증해보세요