업무 보고서를 만들다 보면 부서별 순위, 전월 대비 증감, 누적 매출, 각 그룹의 최근 주문처럼 행을 유지하면서 집계 결과도 함께 보고 싶은 요구가 자주 생깁니다. GROUP BY는 여러 행을 하나의 결과 행으로 줄이기 때문에 원본 열을 그대로 보여 주기 어렵습니다. 이때 오라클과 Microsoft SQL Server에서 지원하는 윈도우 함수를 사용하면 결과 행을 접지 않고도 그룹별 계산을 수행할 수 있습니다.
GROUP BY와 윈도우 함수의 차이
GROUP BY는 부서별 총매출처럼 그룹당 한 행이 필요할 때 적합합니다. 윈도우 함수는 각 주문 행 옆에 해당 고객의 총 주문액이나 순위를 함께 표시할 때 사용합니다. 기본 형태는 함수() OVER (PARTITION BY 그룹열 ORDER BY 정렬열)입니다. PARTITION BY는 계산 그룹을 나누고 ORDER BY는 그룹 안의 순서를 정합니다. 두 절을 생략할 수 있지만 계산 의미가 달라지므로 요구사항에 맞게 명시해야 합니다.
ROW_NUMBER로 그룹별 최신 행 찾기
고객마다 가장 최근 주문 한 건을 찾기 위해 고객 테이블과 주문 테이블을 복잡하게 다시 조인하는 경우가 많습니다. ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC, order_id DESC)로 번호를 만든 뒤 바깥 쿼리에서 번호가 1인 행을 선택하면 의도가 분명해집니다. 같은 주문 시간이 있을 수 있으므로 고유한 order_id를 두 번째 정렬 조건에 넣어 결과를 안정적으로 만듭니다.
ROW_NUMBER, RANK, DENSE_RANK는 동점 처리 방식이 다릅니다. ROW_NUMBER는 동점에도 서로 다른 번호를 주고, RANK는 동점 다음 순위를 건너뛰며, DENSE_RANK는 건너뛰지 않습니다. 화면의 순위 규칙을 먼저 결정한 뒤 함수를 선택해야 합니다.
LAG와 LEAD로 이전·다음 행 비교하기
LAG는 현재 행보다 앞선 행의 값을, LEAD는 다음 행의 값을 가져옵니다. 월별 매출에서 전월 값을 가져와 증감액을 계산하거나 상태 변경 기록에서 다음 상태까지 걸린 시간을 구할 수 있습니다. 첫 행에는 이전 값이 없어 NULL이 반환되므로 COALESCE로 무조건 0으로 바꾸기 전에 ‘비교 대상 없음’과 실제 0의 의미가 같은지 확인해야 합니다.
시계열 비교 체크포인트
- 월이 빠진 데이터가 있다면 바로 이전 행이 전월인지 확인합니다.
- 날짜와 고유키를 함께 정렬해 순서를 확정합니다.
- 시간대와 월 마감 기준을 통일합니다.
- NULL을 0으로 바꾸기 전에 업무 의미를 검토합니다.
SUM OVER로 누적 합계 계산하기
누적 매출은 SUM(amount) OVER (PARTITION BY customer_id ORDER BY ordered_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)처럼 표현할 수 있습니다. 여기서 프레임 절이 중요합니다. 일부 DB의 기본 프레임은 같은 정렬값을 가진 행을 함께 묶는 RANGE가 될 수 있어 같은 날짜의 누적값이 예상과 다르게 보일 수 있습니다. 행 단위 누적이 목적이라면 ROWS 프레임과 안정적인 정렬키를 명시합니다.
핵심 원칙: 윈도우 함수의 ORDER BY는 단순한 출력 순서가 아니라 계산 결과를 결정하므로 동점까지 해소하는 열을 포함해야 합니다.
이동 평균과 구간 프레임
최근 7개 행의 평균은 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW로 구할 수 있습니다. 하지만 행이 하루에 하나라는 보장이 없다면 ‘최근 7행’과 ‘최근 7일’은 다릅니다. 날짜 범위를 기준으로 계산할 때는 DB별 RANGE 지원 문법을 확인하거나 날짜 차원을 먼저 만들어 누락된 날짜를 채우는 방법을 고려합니다. 보고서에서 숫자만 맞추는 것보다 구간의 정의를 문서화하는 것이 중요합니다.
CTE로 계산 단계를 읽기 쉽게 나누기
윈도우 함수 결과는 같은 SELECT의 WHERE 절에서 바로 필터링하기 어렵기 때문에 공통 테이블 표현식인 CTE나 서브쿼리를 사용합니다. 첫 단계에서 필요한 행을 필터링하고, 두 번째 단계에서 순위를 계산하고, 마지막 단계에서 순위 조건을 적용하면 의도를 추적하기 쉽습니다. CTE가 항상 결과를 임시 저장하거나 성능을 높이는 것은 아닙니다. 오라클과 SQL Server의 옵티마이저가 쿼리를 재구성할 수 있으므로 실제 실행 계획으로 확인합니다.
오라클과 SQL Server에서 확인할 차이
핵심 윈도우 함수 문법은 비슷하지만 NULL 정렬 기본값, 날짜 연산, 문자열 및 별칭 처리에 차이가 있습니다. 오라클은 NULLS FIRST와 NULLS LAST를 명시할 수 있고 SQL Server에서는 CASE 표현식 등으로 정렬 의도를 표현할 수 있습니다. 페이징과 함께 쓸 때도 DB 버전별 OFFSET/FETCH 지원 조건을 확인합니다. 이식성이 필요하다면 공통 테스트 데이터를 두 DB에 넣고 결과 행과 순서를 자동 비교하는 것이 안전합니다.
성능을 위한 필터링과 인덱스
윈도우 함수는 파티션과 정렬을 위해 많은 메모리와 임시 공간을 사용할 수 있습니다. 필요한 기간과 업무 범위를 먼저 WHERE에서 줄이고, 파티션 및 정렬 열을 고려한 인덱스를 검토합니다. 그렇다고 모든 열을 하나의 거대한 인덱스에 넣으면 쓰기 비용이 커집니다. 실행 계획에서 정렬, 스필, 실제 행 수를 확인하고 통계 정보를 최신 상태로 유지합니다.
검증 순서
- 작은 표로 기대 결과를 손으로 먼저 계산합니다.
- 동일 정렬값과 NULL이 포함된 데이터를 추가합니다.
- ROW_NUMBER와 RANK의 동점 결과를 비교합니다.
- ROWS와 RANGE 프레임의 차이를 시험합니다.
- 누락된 날짜가 있는 시계열을 확인합니다.
- 실제 데이터 규모로 실행 계획과 임시 공간 사용을 봅니다.
- 오라클과 SQL Server 양쪽에서 결과 순서를 비교합니다.
윈도우 함수는 복잡한 자기 조인과 반복 서브쿼리를 줄여 SQL을 읽기 쉽게 만들 수 있습니다. 다만 PARTITION, ORDER BY, 프레임의 의미가 정확해야 올바른 결과를 얻습니다. 먼저 결과 행의 단위와 동점 규칙, 시간 구간을 정의한 뒤 함수를 선택하면 분석 쿼리와 운영 보고서를 더 안정적으로 작성할 수 있습니다.
한 줄 요약: SQL 윈도우 함수는 행을 유지한 채 순위·전후 비교·누적 집계를 수행하되 정렬과 프레임 규칙을 명확히 해야 합니다.