윈도우 함수 ③ 행 순서·비율
쉽게 말하면
이 함수들은 '다른 줄의 값'을 옆으로 가져와요. LAG는 바로 앞 줄 값, LEAD는 바로 뒤 줄 값, FIRST_VALUE는 맨 앞 줄 값이에요. 지난달보다 매출이 얼마나 늘었는지 계산할 때 많이 써요. NTILE은 줄을 몇 조로 나눠 몇 조인지 번호를 붙여요.
시험용 정의
행 순서 함수는 윈도우 안의 다른 위치 행 값을 가져온다. FIRST_VALUE / LAST_VALUE(범위의 첫 값 / 마지막 값), LAG / LEAD(n행 앞 / n행 뒤 값). 비율 함수는 위치나 비율을 계산한다. RATIO_TO_REPORT(합계 대비 비율), PERCENT_RANK(백분위 순위, 0~1), CUME_DIST(누적 분포, 현재 값 이하 비율), NTILE(n)(n개 그룹으로 나눈 번호).
비유로 이해하기
줄 서 있는 사람들이다. LAG는 "바로 앞사람 키", LEAD는 "바로 뒷사람 키", FIRST_VALUE는 "맨 앞사람", NTILE(4)는 "줄을 네 조로 나눠 몇 조인지", CUME_DIST는 "나보다 작거나 같은 사람이 전체의 몇 %"다.
자세히
먼저 알아 둘 말
| 말 | 뜻 |
|---|---|
| 오프셋 | 현재 행에서 몇 칸 떨어졌는지. LAG(sal, 2)의 2 |
| 기본값 | LAG·LEAD에서 가리킨 행이 없을 때 대신 돌려줄 값. 생략하면 NULL |
| 백분위 | 전체를 100으로 봤을 때의 위치 |
행 순서 함수
| 함수 | 뜻 | 주의 |
|---|---|---|
| FIRST_VALUE(열) | 윈도우 범위의 첫 행 값 | 기본 범위에서도 첫 행은 파티션 첫 행이라 직관대로 |
| LAST_VALUE(열) | 윈도우 범위의 마지막 행 값 | 기본 범위는 현재 행까지라 전체 마지막을 원하면 UNBOUNDED FOLLOWING |
| LAG(열, n, 기본값) | n행 앞 값 (n 생략 시 1) | 첫 행은 앞 행이 없어 기본값(NULL) |
| LEAD(열, n, 기본값) | n행 뒤 값 | 마지막 행은 기본값(NULL) |
비율 함수 (n = 파티션 행 수)
| 함수 | 계산 | 범위 |
|---|---|---|
| RATIO_TO_REPORT(열) | 값 ÷ 파티션 합계 | 0 ~ 1 (ORDER BY 쓰지 않음) |
| PERCENT_RANK() | (RANK - 1) ÷ (n - 1) | 첫 행 0, 마지막 1 |
| CUME_DIST() | 현재 값 이하인 행 수 ÷ n | 0 초과 ~ 1 |
| NTILE(k) | 행을 k개 그룹으로 고르게 나눈 번호 | 나머지는 앞 그룹부터 하나씩 더 받는다 (10행 NTILE(3) → 4, 3, 3) |
시험에 이렇게 나와요
- LAG·LEAD 결과값 (오프셋, 기본값, 첫·마지막 행 NULL)
- LAST_VALUE 기본 범위 함정 (UNBOUNDED FOLLOWING 필요)
- NTILE 그룹 크기 계산 (나머지는 앞 그룹부터)
- PERCENT_RANK·CUME_DIST·RATIO_TO_REPORT 값 계산
출제 범위·공식 안내 기반
확인 문제
Q1. LAST_VALUE(ename) OVER (ORDER BY sal)은 보통 무엇을 돌려줄까요?
- NULL
- 현재 행(과 동점 행) 중 마지막 이름
- 급여가 가장 높은 사원 이름
- 급여가 가장 낮은 사원 이름
정답 보기
2번 현재 행(과 동점 행) 중 마지막 이름
ORDER BY만 쓰면 기본 범위가 처음 ~ 현재 행이라, 마지막 값은 대개 현재 행 자신이다. 전체의 마지막 값을 원하면 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING을 써야 한다. 자주 나오는 함정이다.
Q2. 10행을 NTILE(4)로 나누면 각 그룹 행 수는?
- 3, 2, 3, 2
- 4, 2, 2, 2
- 2, 2, 3, 3
- 3, 3, 2, 2
정답 보기
4번 3, 3, 2, 2
10 ÷ 4 = 2 나머지 2. 나머지 2를 앞 그룹부터 하나씩: 3, 3, 2, 2.
Q3. 급여가 (100, 200, 300) 순일 때 LEAD(sal, 1, 0) OVER (ORDER BY sal)의 마지막 행 값은?
- NULL
- 0
- 300
- 100
정답 보기
2번 0
0이다. LEAD(sal, 1, 0)은 다음 행 값을 가져오는데 마지막 행은 다음 행이 없어 세 번째 인수인 기본값 0이 나온다.
Q4. 5행에서 3번째 순위 행의 PERCENT_RANK()는? (동점 없음)
- 0.4
- 0.5
- 0.6
- 0.75
정답 보기
2번 0.5
(3 - 1) / (5 - 1) = 0.5.
Q5. 급여가 (100, 200, 300) 순일 때 LAG(sal) OVER (ORDER BY sal)의 첫 행 값은?
- 0
- 300
- 100
- NULL
정답 보기
4번 NULL
앞 행이 없으면 NULL이다. 기본값을 주려면 LAG(sal, 1, 0).
Q6. LAST_VALUE(sal) OVER (ORDER BY sal)이 파티션의 진짜 마지막 값을 주지 않는 이유는?
- NULL을 빼서
- PARTITION BY가 없어서
- 기본 범위가 현재 행까지라서
- 정렬이 반대라서
정답 보기
3번 기본 범위가 현재 행까지라서
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING을 줘야 한다.
헷갈리기 쉬운 O·X
LAG 함수는 현재 행보다 뒤에 있는 행의 값을 가져온다.
정답 보기
X LAG는 앞 행, LEAD가 뒤 행.
OVER (ORDER BY sal)만 쓴 LAST_VALUE는 대부분 현재 행 값을 돌려준다.
정답 보기
O 기본 범위가 현재 행까지라서다.
CUME_DIST의 첫 행 값은 항상 0이다.
정답 보기
X CUME_DIST는 자기 이하 비율이라 0보다 크다(첫 행이 동점 없으면 1/n). 첫 행이 0인 것은 PERCENT_RANK.