liminfoSQL Window Functions Reference
SQL 윈도우 함수 레퍼런스 - ROW_NUMBER, RANK, LAG 등
SQL Window Functions Reference 소개
SQL 윈도우 함수 레퍼런스는 고급 분석에 사용되는 모든 주요 SQL 윈도우 함수를 다루는 검색 가능한 빠른 참조입니다. 순위 함수(ROW_NUMBER, RANK, DENSE_RANK, NTILE), 집계 윈도우 함수(SUM OVER, AVG OVER, COUNT OVER, MAX/MIN OVER), 값 접근 함수(LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE), 분포 함수(PERCENT_RANK, CUME_DIST)를 포함합니다. 각 항목은 OVER 절, PARTITION BY 그룹화, ORDER BY 정렬이 포함된 실용적인 SQL 예제를 제공합니다.
윈도우 함수는 GROUP BY처럼 결과 집합을 축소하지 않고 현재 행과 관련된 행 집합에 대해 연산을 수행합니다. 이를 통해 누적 합계, 이동 평균, 그룹 내 순위, 이전/다음 행과의 값 비교, 백분위 분포 계산에 필수적입니다. 이 레퍼런스는 주가의 7일 이동 평균, 누적 매출 합계, 부서별 급여 순위, 행간 가격 비교 같은 실제 패턴을 보여줍니다.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW와 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 같은 프레임 절의 차이를 이해하는 것은 정확한 윈도우 함수 동작을 위해 중요합니다. 이 레퍼런스에는 프레임 사양이 LAST_VALUE에 어떻게 영향을 미치는지(실제 마지막 값을 반환하려면 UNBOUNDED FOLLOWING이 필요)와 이동 윈도우 프레임이 롤링 집계를 어떻게 생성하는지를 보여주는 예제가 포함되어 있습니다. 다루는 모든 윈도우 함수는 PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite 3.25+에서 작동합니다.
주요 기능
- 순위 함수: 순차 번호를 위한 ROW_NUMBER, 건너뛰는 RANK, 연속적인 DENSE_RANK, 균등 그룹 분할을 위한 NTILE
- 집계 윈도우 함수: SUM OVER를 활용한 누적 합계, AVG OVER를 활용한 7일 이동 평균, COUNT OVER를 활용한 파티션 카운트
- 값 접근 함수: 이전 행을 위한 LAG, 다음 행을 위한 LEAD, 윈도우 극값을 위한 FIRST_VALUE와 LAST_VALUE
- 윈도우 파티션 내 특정 위치의 값에 접근하기 위한 NTH_VALUE
- 분포 함수: 상대적 순위(0-1)를 위한 PERCENT_RANK와 누적 분포를 위한 CUME_DIST
- 부서, 카테고리 또는 임의 컬럼 기준 계산 그룹화를 위한 PARTITION BY 예제
- ROWS BETWEEN, UNBOUNDED PRECEDING, CURRENT ROW를 활용한 프레임 절 데모
- PostgreSQL, MySQL 8+, SQL Server, Oracle에서 작동하는 크로스 데이터베이스 호환 구문
자주 묻는 질문
RANK와 DENSE_RANK의 차이점은 무엇인가요?
둘 다 동일한 값을 가진 행에 같은 순위를 부여하지만 다음 순위 처리 방식이 다릅니다. RANK는 동점 이후 번호를 건너뜁니다: 두 행이 순위 2에서 동점이면 다음 순위는 4입니다. DENSE_RANK는 건너뛰지 않습니다: 2에서 동점 후 다음 순위는 3입니다. 위에 있는 행 수를 반영하는 위치가 필요할 때(백분위 계산에 유용) RANK를, 연속적인 순위 번호가 필요할 때 DENSE_RANK를 사용하세요.
ROW_NUMBER는 동점을 RANK와 어떻게 다르게 처리하나요?
ROW_NUMBER는 값이 동일해도 항상 고유한 순차 번호(1, 2, 3, 4...)를 할당합니다. 동점 행의 순서는 ORDER BY에 타이브레이커 컬럼을 추가하지 않으면 비결정적입니다. RANK는 동점 행에 같은 번호를 할당합니다. 위치당 정확히 하나의 행이 필요할 때(예: CTE를 사용한 중복 제거) ROW_NUMBER를, 동일한 값이 같은 위치를 공유해야 할 때 RANK를 사용하세요.
SUM OVER로 누적 합계를 어떻게 계산하나요?
SUM(amount) OVER (ORDER BY date)를 사용하여 날짜 순서로 누적 합계를 계산합니다. 기본 프레임은 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW이며, 첫 번째 행부터 현재 행까지 합산합니다. 카테고리별로 분할하려면 PARTITION BY를 추가합니다: SUM(amount) OVER (PARTITION BY category ORDER BY date). 이렇게 하면 각 카테고리에 대해 누적 합계가 초기화됩니다.
LAG와 LEAD의 차이점은 무엇인가요?
LAG(column, n)는 현재 행에서 n행 이전의 값에 접근합니다(기본 n=1). LEAD(column, n)는 n행 이후의 값에 접근합니다. 둘 다 오프셋이 윈도우 경계를 넘을 때의 기본값으로 세 번째 인수를 받습니다. 일반적인 사용 사례로는 전일 대비 가격 변화 계산(price - LAG(price, 1) OVER (ORDER BY date))과 현재 지표와 다음 기간 비교가 있습니다.
LAST_VALUE가 현재 행 값을 자주 반환하는 이유는 무엇인가요?
기본적으로 윈도우 프레임이 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW이므로 LAST_VALUE는 현재 행(기본 프레임의 마지막 행)을 반환합니다. 파티션의 실제 마지막 값을 얻으려면 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING을 반드시 지정해야 합니다. 이것은 흔한 함정입니다. FIRST_VALUE는 첫 번째 행이 항상 기본 프레임에 포함되므로 이 문제가 없습니다.
NTILE은 행을 그룹으로 어떻게 나누나요?
NTILE(n)은 행을 n개의 거의 동일한 크기의 그룹으로 나누고 1부터 n까지의 그룹 번호를 할당합니다. 행이 균등하게 나누어지지 않으면 앞쪽 그룹이 하나씩 더 많은 행을 받습니다. 예를 들어 10개 행에 NTILE(4)를 적용하면 3, 3, 2, 2행의 그룹이 생성됩니다. 이는 통계 분석을 위한 사분위수, 십분위수 또는 백분위 기반 그룹화를 만드는 데 유용합니다.
ROWS BETWEEN 프레임 절이란 무엇이며 어떻게 작동하나요?
프레임 절은 파티션 내에서 윈도우 계산에 포함되는 행을 정의합니다. UNBOUNDED PRECEDING은 파티션의 첫 번째 행을 의미합니다. CURRENT ROW는 현재 행입니다. n PRECEDING/FOLLOWING은 앞/뒤 n행을 의미합니다. 예를 들어 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW는 이동 평균을 위한 7행 슬라이딩 윈도우를 만듭니다. RANGE BETWEEN은 유사하지만 동일한 ORDER BY 값을 가진 행을 함께 그룹화합니다.
PERCENT_RANK와 CUME_DIST의 차이점은 무엇인가요?
PERCENT_RANK는 (rank - 1) / (total_rows - 1)을 계산하여 첫 번째 행에 0, 마지막 행에 1을 부여합니다. CUME_DIST는 현재 행 이하의 값을 가진 행의 비율을 계산합니다: count(값 <= 현재) / total_rows. CUME_DIST는 항상 0보다 크고 마지막 행은 항상 1입니다. 상대적 위치에는 PERCENT_RANK를, 특정 값 이하 데이터셋의 비율에는 CUME_DIST를 사용하세요.