윈도우함수가 무엇인가?

윈도우함수가 무엇인가?

윈도우 함수 한 줄 정의

행을 줄이지 않고(=GROUP BY처럼 뭉개지 않고), 각 행 옆에 ‘분석용 값’을 붙이는 함수입니다.

• GROUP BY → 결과 행 수가 줄어듦(집계행만 남음)
• 윈도우 함수 → 원래 행은 그대로 + 옆에 순위/평균/이전값/누적비율 같은 컬럼이 추가됨

1) 윈도우 함수 기본 틀(OVER)

윈도우 함수는 항상 OVER(...)가 붙습니다.

윈도우함수() OVER(
PARTITION BY ... -- 그룹 나누기(부서별, 학급별)
ORDER BY ... -- 그룹 안에서 정렬(급여순, 날짜순)
[WINDOW FRAME] -- “어디까지를 범위로 볼지” (핵심)
)

• PARTITION BY: “부서별로 따로 계산”
• ORDER BY: “부서 안에서 급여순으로 순위/흐름 만들기”

2) 순위 함수: ROW_NUMBER vs RANK vs DENSE_RANK

같은 급여가 있을 때 차이가 납니다.

• ROW_NUMBER(): 무조건 1,2,3… (동점이어도 번호는 다름)
• RANK(): 동점이면 같은 순위, 다음 순위는 건너뜀(1,2,2,4…)
• DENSE_RANK(): 동점이면 같은 순위, 다음 순위는 안 건너뜀(1,2,2,3…)

예)

ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC)
RANK() OVER(PARTITION BY deptno ORDER BY sal DESC)
DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC)

3) 집계 + OVER: “부서 평균을 각 행에 붙이기”

예) “각 직원 행에 부서 평균 급여를 같이 표시”

AVG(sal) OVER(PARTITION BY deptno) AS dept_avg

이건 수업에서 정말 자주 씁니다.
“나의 급여 vs 우리 부서 평균” 같은 비교를 한 쿼리로 끝냅니다.

4) 행 이동 함수(오프셋): LAG / LEAD

• LAG(sal): 이전 행의 급여(과거)
• LEAD(sal): 다음 행의 급여(미래)

예) 사번 기준으로 다음 사람 급여 보기

LEAD(sal, 1, 0) OVER(ORDER BY empno) AS next_sal

5) FIRST_VALUE / LAST_VALUE: “그룹에서 첫 값/마지막 값”

FIRST_VALUE

정렬 기준으로 첫 번째 값을 가져옵니다.

FIRST_VALUE(sal) OVER(
PARTITION BY deptno
ORDER BY empno
) AS dept_first_sal

LAST_VALUE가 헷갈리는 이유(이미지 핵심)

LAST_VALUE()는 기본 프레임 때문에 “진짜 마지막”이 아니라 현재 행까지의 마지막으로 보이는 경우가 많습니다.

즉, 이렇게 쓰면:

LAST_VALUE(sal) OVER(
PARTITION BY deptno
ORDER BY empno
)

결과가 “마지막 값”이 아니라 내 SAL이 그대로 나오는 것처럼 보일 수 있습니다.
(현재 행이 프레임의 끝이라서 ‘마지막’이 현재 행이 됨)

진짜 “파티션의 마지막 값”을 원하면: 프레임을 전체로 확장

이미지에서 강조한 구문이 이거예요:

LAST_VALUE(sal) OVER(
PARTITION BY deptno
ORDER BY empno
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS dept_last_sal

• UNBOUNDED PRECEDING = 파티션의 맨 처음부터
• UNBOUNDED FOLLOWING = 파티션의 맨 끝까지
  → 파티션 전체를 보고 마지막 값을 제대로 가져옴

6) NTH_VALUE(n): “그룹에서 n번째 값”

정렬 기준으로 n번째 값을 가져옵니다. (n은 1부터)

예) 부서별로 “2번째 사번의 급여”를 모든 행에 붙이기

NTH_VALUE(sal, 2) OVER(
PARTITION BY deptno
ORDER BY empno
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS second_sal

여기서도 프레임을 전체로 잡는 게 안전합니다.
(안 잡으면 “현재 행까지의 n번째” 같은 오해가 생길 수 있음)

7) 비율(분포) 함수 3종: CUME_DIST / PERCENT_RANK / NTILE

이 파트가 이번 추가 이미지의 핵심입니다. “내 위치가 어느 정도냐?”를 수치로 보여줍니다.

7-1) CUME_DIST()

누적 분포 비율입니다.
“나보다 작거나 같은 값이 파티션에서 몇 %냐?”

• 값 범위: (0, 1] 형태로 나오며 보통 1까지 도달
• 직관: “내 급여 이하(<=) 인원이 전체에서 차지하는 비율”

예)

CUME_DIST() OVER(PARTITION BY deptno ORDER BY sal) AS dept_cume_dist

부서에 3명이 있고 내 급여가 중간(2등)이라면
<= 나가 2명 → 2/3 = 0.6666…

7-2) PERCENT_RANK()

백분위 ‘순위 기반’ 비율입니다. 공식이 이미지에 나옵니다.

• 공식: (rank - 1) / (전체행수 - 1)
• 범위: 0 ~ 1
• 특징: “순위의 상대 위치”를 계산
  • 1등은 무조건 0
  • 마지막은 1

예)

PERCENT_RANK() OVER(PARTITION BY deptno ORDER BY sal) AS dept_percent_rank

CUME_DIST와 차이

• CUME_DIST: “행 개수 기준 누적 비율(<=)” 느낌
• PERCENT_RANK: “순위 기반 위치” 느낌(공식으로 딱 떨어짐)

7-3) NTILE(n)

n개의 구간(등급)으로 나누기입니다. (분위수 나누기)

예) 부서별 급여를 3등급으로 나누기

NTILE(3) OVER(PARTITION BY deptno ORDER BY sal) AS dept_ntile

해석:

• 각 부서에서 급여가 낮은 사람은 1, 중간은 2, 높은 사람은 3 같은 “등급”이 됩니다.
• 데이터 개수가 n으로 딱 안 나눠지면 앞쪽 그룹부터 1명씩 더 가져가는 방식으로 나뉘는 경우가 많습니다.

전체 요약(암기 포인트)

1. 윈도우 함수 = 행을 유지하면서 분석 컬럼 추가
2. OVER(PARTITION BY, ORDER BY)로 “그룹+정렬”을 만든다.
3. LAST_VALUE는 프레임 때문에 함정 → 진짜 마지막이 필요하면
   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
4. NTH_VALUE는 n번째 값, 프레임 전체 지정하면 안정적
5. 비율 함수:
   • CUME_DIST: “<= 나”의 누적 비율
   • PERCENT_RANK: “순위 기반” 백분위
   • NTILE(n): n개 등급으로 쪼개기