윈도우 함수

윈도우 함수(분석 함수)는 행을 합치지 않으면서 각 행 옆에 그룹 통계나 순위를 붙입니다. GROUP BY로 묶으면 부서 평균만 남고 사원 이름이 사라지지만, 윈도우 함수는 사원 행은 그대로 두고 옆에 부서 평균·순위를 더합니다. 결과 행 수는 원본과 같습니다.

형태는 함수() OVER (PARTITION BY 그룹 ORDER BY 정렬 프레임)입니다. PARTITION BY는 계산할 그룹을 나누고(생략하면 전체가 한 그룹), ORDER BY는 그룹 안의 순서를, 프레임(ROWS·RANGE)은 계산에 넣을 행 범위를 정합니다. 윈도우 함수는 SELECT·ORDER BY 절에서만 쓸 수 있어서, 결과로 거르려면 인라인 뷰로 감싼 뒤 바깥 WHERE에서 거릅니다.

윈도우 함수는 WHERE·GROUP BY·HAVING이 끝난 뒤 남은 행을 대상으로 계산합니다. WHERE DEPT_ID = 'D1'을 걸면 전체 합계 SUM(SAL) OVER ()도 D1 두 명의 합 8000이 됩니다. 또 OVER 안의 ORDER BY는 계산 순서일 뿐이라 화면에 나오는 순서는 보장하지 않습니다. 출력 순서는 맨 끝의 ORDER BY로 정합니다.

순위 함수는 OVER (ORDER BY ...)가 반드시 필요합니다. RANK는 동점에 같은 순위를 주고 다음 순위를 건너뜁니다(1, 1, 3). DENSE_RANK는 같은 순위를 주되 건너뛰지 않습니다(1, 1, 2). ROW_NUMBER는 동점이어도 모두 다른 번호를 줍니다(1, 2, 3). 동점끼리 누가 먼저 번호를 받는지는 정해지지 않으므로 필요하면 정렬 열을 하나 더 둡니다.

'상위 N명(정확히 N행)'은 ROW_NUMBER, '상위 N위(동점 포함, 결번 있음)'는 RANK, '상위 N개 수준(동점 포함, 결번 없음)'은 DENSE_RANK입니다. NTILE(n)은 정렬된 행을 n개 그룹으로 연속 블록으로 나누고, 나누어떨어지지 않으면 앞 그룹부터 한 개씩 더 줍니다. 8행을 NTILE(3)하면 3, 3, 2개입니다.

동점을 정리하려고 RANK의 ORDER BY에 이름 같은 고유 열을 덧붙이면 동점이 사라져 공동 순위도 없어집니다. 고유 열을 덧붙이는 것은 ROW_NUMBER의 번호를 고정할 때만 씁니다.

NULL이 섞이면 위치에 주의합니다. Oracle은 NULL을 가장 큰 값으로 정렬하므로 ORDER BY SAL DESC에서는 NULL이 1위가 됩니다. 맨 뒤로 보내려면 DESC NULLS LAST를 씁니다. SQL Server는 반대로 NULL을 가장 작은 값으로 봅니다(참고).

순위로 거를 때는 함수 선택이 결과 행 수를 바꿉니다. 부서별 1위를 뽑으면 D2의 박·최가 4500 동점이라 RANK = 1은 3행(이, 박, 최), ROW_NUMBER = 1은 부서마다 정확히 1행(이, 박 또는 최)입니다.

PARTITION BY를 쓰면 그룹마다 순위와 집계를 따로 계산합니다. 부서별 순위를 매기면 각 부서에서 1위가 나오고, 부서별 평균도 그 부서 사원만으로 구합니다. 같은 SELECT 안에서 함수마다 OVER를 다르게 줄 수도 있습니다.

집계 함수(SUM·AVG·COUNT·MAX·MIN)도 OVER와 함께 쓰면 행을 합치지 않고 그룹 통계를 붙입니다. 이때 OVER 안에 ORDER BY가 없으면 그룹 전체 값이, ORDER BY가 있으면 기본 프레임 때문에 첫 행부터 현재 행까지의 누적 값이 나옵니다.

NULL 처리는 일반 집계와 같습니다. COUNT(*) OVER (...)는 NULL 행도 세지만 COUNT(SAL) OVER (...)는 NULL을 빼고 셉니다. AVG도 NULL을 0으로 보지 않고 제외한 뒤 나눕니다.

LAG(열, n)은 정렬 순서상 n행 앞(이전) 값, LEAD(열, n)은 n행 뒤(다음) 값을 가져옵니다. n을 생략하면 1이고, 앞이나 뒤에 행이 없으면 NULL(세 번째 인자로 기본값 지정 가능)입니다. 급여 오름차순에서 '바로 직전 급여'는 LAG(SAL) OVER (ORDER BY SAL)입니다. 정렬을 DESC로 바꾸면 방향이 뒤집힙니다.

FIRST_VALUE는 프레임의 첫 값, LAST_VALUE는 프레임의 마지막 값입니다. ORDER BY만 쓰면 기본 프레임이 '처음 ~ 현재 행'이라 FIRST_VALUE는 모든 행에서 첫 값이 같지만, LAST_VALUE는 현재 행 값이 그대로 나옵니다. 그룹의 진짜 마지막 값을 원하면 프레임을 UNBOUNDED FOLLOWING까지 넓힙니다. 비율 함수로는 RATIO_TO_REPORT(전체 합 대비 비율), PERCENT_RANK(백분위 순위), CUME_DIST(누적 분포)가 있습니다.

LAST_SAL이 '마지막 사원 최의 4500'이 아니라 각 행 자기 값인 이유는 기본 프레임이 현재 행에서 끝나기 때문입니다. LAST_VALUE(SAL) OVER (ORDER BY HIRE_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)이면 모든 행이 4500입니다.

박·최는 RANK가 2라 PERCENT_RANK = (2 − 1) / (4 − 1) ≈ 0.33입니다. CUME_DIST는 동점인 최까지 함께 세어 3 / 4 = 0.75가 됩니다. RATIO는 5000 / 17000처럼 전체 합계에 대한 비율입니다.

프레임은 현재 행 기준으로 어디부터 어디까지 계산할지 정합니다. UNBOUNDED PRECEDING(처음부터), n PRECEDING(n행 앞), CURRENT ROW(현재 행), n FOLLOWING(n행 뒤), UNBOUNDED FOLLOWING(끝까지)을 조합합니다. 누적합은 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW이고 줄여서 ROWS UNBOUNDED PRECEDING으로도 씁니다. 시작 경계에 UNBOUNDED FOLLOWING을 둘 수는 없습니다.

ROWS는 물리적인 행 개수로, RANGE는 정렬 값으로 범위를 잡습니다. 정렬 값이 같은 행이 있으면 RANGE는 그 행들을 한 묶음으로 함께 넣어 결과가 달라집니다. 프레임을 생략하면 OVER에 ORDER BY가 있을 때 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(누적), ORDER BY가 없을 때 그룹 전체가 기본입니다.

프레임은 집계 함수(SUM·AVG·COUNT·MAX·MIN)와 FIRST_VALUE·LAST_VALUE에 씁니다. RANK·ROW_NUMBER 같은 순위 함수와 LAG·LEAD에는 프레임을 붙이지 않습니다.

이동합은 앞 1행 + 현재 + 뒤 1행입니다. 첫 행(김)은 앞 행이 없어 3000 + 5000 = 8000, 마지막 행(최)은 뒤 행이 없어 4500 + 4500 = 9000입니다.