2. 핵심 원리
2.1 분석 함수의 모양: 함수() OVER (...)
분석 함수는 함수() OVER (PARTITION BY 그룹 컬럼 ORDER BY 정렬 컬럼) 형태로 씁니다. OVER 안의 PARTITION BY 는 그룹을 나누고, ORDER BY 는 그 그룹 안의 순서를 정합니다. PARTITION BY 를 생략하면 전체가 하나의 그룹입니다.
OVER 안의 ORDER BY 는 순위를 매기는 기준일 뿐이고, 결과 행의 출력 순서를 정하는 것이 아닙니다. 결과 순서는 문장 맨 끝의 ORDER BY 로 따로 정합니다.
2.2 순위 함수 4종
| 함수 | 동점 처리 | 예: 700, 700, 600 |
|---|---|---|
ROW_NUMBER() |
동점이어도 겹치지 않게 번호 부여 | 1, 2, 3 |
RANK() |
동점은 같은 순위, 다음은 건너뜀 | 1, 1, 3 |
DENSE_RANK() |
동점은 같은 순위, 다음은 안 건너뜀 | 1, 1, 2 |
NTILE(n) |
행을 n 개 묶음으로 균등 분할 | 묶음 번호 |
RANK 는 스포츠 순위처럼 공동 1등이 둘이면 다음이 3등입니다. DENSE_RANK 는 다음이 2등입니다. ROW_NUMBER 는 동점이라도 서로 다른 번호를 주므로 "정확히 N행"이 필요할 때 씁니다.
NTILE(n) 은 정렬된 행을 n 개로 나눠 묶음 번호를 붙입니다. 나누어떨어지지 않으면 앞쪽 묶음이 1행씩 더 많습니다. 8행을 NTILE(3) 으로 나누면 3행, 3행, 2행입니다.
2.3 실행 순서와 WHERE 금지
핵심분석 함수는
SELECT와ORDER BY에만 쓸 수 있고,WHERE·GROUP BY·HAVING에는 쓸 수 없습니다. 분석 함수는WHERE·GROUP BY·HAVING이 끝난 뒤 계산되기 때문입니다.
실행 순서는 FROM → WHERE → GROUP BY → HAVING → 분석 함수(SELECT) → ORDER BY 입니다. WHERE 를 처리하는 시점에는 순위가 아직 없습니다. 그래서 순위로 거르려면 순위를 계산하는 쿼리를 인라인 뷰나 CTE 로 감싸고, 바깥 쿼리의 WHERE 에서 거릅니다.
2.4 DB별 지원 시점
| DB | 순위 함수(ROW_NUMBER·RANK·DENSE_RANK·NTILE) |
|---|---|
| Oracle | 8i 부터 |
| Tibero | 지원(Oracle 호환) |
| MySQL | 8.0 부터 |
| MSSQL | 2005 부터 |
MySQL 5.7 이하에는 분석 함수가 없어 사용자 변수(@rn := @rn + 1)로 순번을 흉내 냈습니다. 이 방식은 평가 순서가 보장되지 않아 8.0 이상에서는 분석 함수를 씁니다.