OVER – dsgnmstr; Design Monster

데이터 분석가나 SQL을 다루는 개발자라면 누구나 한 번쯤은 이런 요구사항에 머리를 싸맨 경험이 있을 것입니다. “각 부서별로 직원들의 급여 순위를 매겨주세요.”, “월별 매출액과 함께 누적 매출액을 보여주세요.”, “각 직원의 급여를 바로 이전 입사자의 급여와 비교해주세요.” 기존의 GROUP BY를 활용한 집계 방식으로는 개별 행의 정보가 사라지기 때문에 이러한 요구사항을 해결하기가 매우 까다롭습니다. 여러 번의 서브쿼리나 복잡한 셀프 조인(Self-Join)을 사용해야만 겨우 원하는 결과를 얻을 수 있었죠. ‘윈도우 함수(Window Function)’는 바로 이러한 분석 쿼리의 한계를 극복하기 위해 탄생한 강력한 SQL 기능입니다.

윈도우 함수는 행과 행 간의 관계를 쉽게 정의하고, 각 행의 위치에 기반하여 연산한 결과를 반환하는 함수입니다. 마치 데이터의 특정 범위(파티션)를 ‘창문(Window)’을 통해 들여다보며 계산하는 것과 같다고 해서 이런 이름이 붙었습니다. OLAP(Online Analytical Processing, 온라인 분석 처리) 환경에서 복잡한 분석 및 리포팅 쿼리를 작성하는 데 특화되어 있어 ‘OLAP 함수’라고도 불립니다. 이 글에서는 SQL 데이터 분석의 필수 스킬로 자리 잡은 윈도우 함수의 기본 개념부터 종류, 그리고 실전 활용법까지 체계적으로 알아보겠습니다.

윈도우 함수의 핵심: OVER() 절 해부하기

윈도우 함수의 모든 마법은 OVER()라는 키워드 안에서 이루어집니다. OVER() 절은 함수가 계산될 행의 집합, 즉 ‘윈도우’를 정의하는 역할을 합니다. 이 OVER() 절은 세 가지 주요 구성 요소로 나뉩니다.

1. PARTITION BY: 창문의 구획 나누기

PARTITION BY 절은 전체 데이터를 특정 기준에 따라 여러 개의 논리적인 그룹, 즉 ‘파티션’으로 분할합니다. 이는 GROUP BY 절과 역할이 유사하지만 결정적인 차이가 있습니다. GROUP BY는 그룹별로 하나의 결과 행만 반환하지만, PARTITION BY는 원본 데이터의 각 행은 그대로 유지한 채, 함수 계산을 위한 논리적인 경계만 설정합니다.

예시: PARTITION BY department_id 라고 지정하면, 윈도우 함수는 각 부서(department_id) 안에서만 독립적으로 계산을 수행합니다. A 부서의 계산은 B 부서에 영향을 주지 않습니다.

2. ORDER BY: 창문 안에서 순서 정하기

ORDER BY 절은 파티션 내에서 어떤 순서로 데이터를 정렬하여 함수를 적용할지 결정합니다. 순위(Ranking)를 매기거나, 순서에 기반한 누적(Running Total) 값을 계산하는 등 순서가 중요한 윈도우 함수에서는 필수적인 요소입니다.

예시: ORDER BY salary DESC 라고 지정하면, 파티션 내에서 급여(salary)가 높은 순서대로 정렬한 뒤, 그 순서에 따라 함수 계산이 이루어집니다.

3. ROWS / RANGE (Frame): 계산 범위 지정하기

ROWS 또는 RANGE 절은 파티션 내에서 현재 행을 기준으로 함수 계산에 포함될 구체적인 행의 범위를 지정합니다. 이를 ‘프레임(Frame)’이라고 부릅니다. 이 프레임은 동적으로 이동하며 계산을 수행합니다.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 파티션의 첫 번째 행부터 현재 행까지를 계산 범위로 지정합니다. (누적 합계를 구하는 데 주로 사용)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 현재 행의 바로 이전 행, 현재 행, 바로 다음 행, 이렇게 3개의 행을 계산 범위로 지정합니다. (이동 평균을 구하는 데 사용)

이 세 가지 요소를 조합하여 함수() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE ...) 형태로 윈도우 함수를 사용하게 됩니다.

윈도우 함수의 종류: 무엇을 할 수 있는가?

윈도우 함수는 크게 순위 함수, 집계 함수, 행 순서 함수 세 가지 그룹으로 나눌 수 있습니다.

1. 순위 함수 (Ranking Functions)

파티션 내에서 각 행의 순서를 결정하는 함수들입니다.

RANK(): 일반적인 순위 함수. 동일한 값이면 같은 순위를 부여하고, 다음 순위는 공동 순위의 개수만큼 건너뜁니다. (예: 1, 2, 2, 4)
DENSE_RANK(): RANK()와 유사하지만, 공동 순위가 있어도 다음 순위를 건너뛰지 않고 연속적으로 부여합니다. (예: 1, 2, 2, 3)
ROW_NUMBER(): 동일한 값이라도 고유한 순위를 부여합니다. 공동 순위가 없습니다. (예: 1, 2, 3, 4)
NTILE(n): 파티션의 전체 행을 지정된 n개의 그룹(버킷)으로 나누고 각 행이 몇 번째 그룹에 속하는지 나타냅니다. (예: 고객을 매출 상위 4분위로 나눌 때 사용)

함수	설명	예시 (점수: 100, 90, 90, 80)
RANK()	공동 순위 다음 순위 건너뜀	1, 2, 2, 4
DENSE_RANK()	공동 순위 있어도 순위 연속적	1, 2, 2, 3
ROW_NUMBER()	고유 순위 부여 (동점 없음)	1, 2, 3, 4

활용 예시: 각 부서(dept_no) 내에서 직원들의 급여(salary)가 높은 순으로 순위를 매기기

SQL

SELECT
    emp_name,
    dept_no,
    salary,
    RANK() OVER (PARTITION BY dept_no ORDER BY salary DESC) AS "부서별 급여 순위"
FROM
    employees;

2. 집계 함수 (Aggregate Functions)

SUM(), AVG(), COUNT(), MAX(), MIN() 등 기존의 집계 함수들도 OVER() 절과 함께 사용되면 윈도우 함수로 작동합니다. 이 경우, GROUP BY와 달리 각 행의 원래 정보는 유지되면서 파티션별 집계 결과가 모든 행에 추가됩니다.

활용 예시: 각 직원의 급여와 함께, 해당 직원이 속한 부서의 평균 급여를 함께 보여주기

SQL

SELECT
    emp_name,
    dept_no,
    salary,
    AVG(salary) OVER (PARTITION BY dept_no) AS "부서 평균 급여"
FROM
    employees;

활용 예시 2: 월별 매출과 함께 누적 매출(Running Total) 계산하기

SQL

SELECT
    sale_month,
    monthly_revenue,
    SUM(monthly_revenue) OVER (ORDER BY sale_month) AS "누적 매출"
FROM
    sales;

3. 행 순서 함수 (Value Functions)

파티션 내에서 현재 행을 기준으로 특정 위치에 있는 행의 값을 가져오는 함수들입니다.

LAG(column, n, default): 현재 행을 기준으로 이전 n번째 행의 column 값을 가져옵니다.
LEAD(column, n, default): 현재 행을 기준으로 이후 n번째 행의 column 값을 가져옵니다.
FIRST_VALUE(column): 파티션 내에서 정렬 순서상 가장 첫 번째 행의 column 값을 가져옵니다.
LAST_VALUE(column): 파티션 내에서 정렬 순서상 가장 마지막 행의 column 값을 가져옵니다.

활용 예시: 각 월의 매출을 바로 전월 매출과 비교하기

SQL

SELECT
    sale_month,
    monthly_revenue,
    LAG(monthly_revenue, 1, 0) OVER (ORDER BY sale_month) AS "전월 매출"
FROM
    sales;

이 쿼리를 통해 monthly_revenue - LAG(...) 와 같은 간단한 연산으로 전월 대비 매출 성장률을 쉽게 계산할 수 있습니다.

윈도우 함수 vs. GROUP BY: 결정적 차이

윈도우 함수와 GROUP BY는 데이터를 그룹화하여 집계한다는 점에서 유사해 보이지만, 그 결과와 목적은 완전히 다릅니다.

GROUP BY: 원본 데이터의 여러 행을 그룹별로 ‘요약’하여 그룹당 하나의 행만 남깁니다. 개별 행의 정보는 집계 과정에서 사라집니다. (예: 부서별 평균 급여)
윈도우 함수: 원본 데이터의 모든 행을 그대로 유지하면서, 각 행에 대한 ‘추가 정보’를 계산하여 보여줍니다. (예: 각 직원의 급여와 그가 속한 부서의 평균 급여)

즉, 개별 데이터의 상세 정보와 그룹 전체의 통계 정보를 함께 보고 싶을 때, 윈도우 함수는 그 진가를 발휘합니다. GROUP BY를 사용했다면, 부서별 평균 급여를 구한 뒤 원래 직원 테이블과 다시 조인해야 하는 번거로운 과정을 거쳐야 했을 것입니다.

결론: 복잡한 분석 쿼리를 위한 우아한 해결책

윈도우 함수는 현대적인 데이터 분석 환경에서 더 이상 선택이 아닌 필수 기능입니다. 복잡한 서브쿼리와 조인을 사용해야만 가능했던 데이터 순위, 누적 합계, 이동 평균, 기간별 비교 등의 분석 작업을 단 몇 줄의 직관적인 코드로 해결할 수 있게 해줍니다. 이는 SQL 쿼리의 가독성을 높이고 유지보수를 용이하게 만들 뿐만 아니라, 데이터베이스 시스템 내부에서 더 효율적으로 처리되어 성능상의 이점을 가져다주기도 합니다.

처음에는 OVER (PARTITION BY ... ORDER BY ...) 구문이 다소 복잡하게 느껴질 수 있지만, 각 요소가 ‘어떤 그룹에서’, ‘어떤 순서로’, ‘어떤 범위를’ 계산할지 정의하는 논리적 흐름이라는 것을 이해하면 금방 익숙해질 수 있습니다. 데이터로부터 더 깊이 있는 인사이트를 빠르고 우아하게 추출하고 싶다면, 윈도우 함수라는 강력한 무기를 반드시 당신의 것으로 만드시길 바랍니다.

[태그:] OVER

데이터 분석의 지평을 넓히다, SQL 윈도우 함수(Window Function) 완벽 정복