시스매

SQL 윈도우 함수 완벽 정복 핸드북 데이터 분석의 차원을 높이는 비밀 병기

18 min read

2025-08-29 20:53

SQL 윈도우 함수 완벽 정복 핸드북 데이터 분석의 차원을 높이는 비밀 병기

데이터를 다루다 보면 종종 이런 질문에 부딪히게 됩니다.

  • “각 직원들의 급여가 자신이 속한 부서의 평균 급여와 얼마나 차이가 날까?”

  • “월별 매출액과 함께, 올해 초부터 현재까지의 누적 매출액을 함께 보고 싶어.”

  • “각 제품 카테고리별로 가장 많이 팔린 상위 3개 제품은 무엇일까?”

이러한 질문들은 언뜻 간단해 보이지만, 전통적인 GROUP BY 문법만으로는 해결하기 까다롭습니다. GROUP BY는 행을 집계하여 하나의 결과로 요약해버리기 때문에, 개별 행의 고유한 속성과 집계 결과를 동시에 보여주기 어렵습니다. 이 문제를 해결하기 위해 서브쿼리나 복잡한 SELF JOIN을 사용해야 했고, 쿼리는 길고 복잡해지며 성능 저하의 원인이 되기도 했습니다.

바로 이 지점에서 **윈도우 함수(Window Functions)**가 등장합니다. 윈도우 함수는 데이터 분석과 리포팅을 위한 SQL의 ‘비밀 병기’와도 같습니다. 행들을 요약하지 않고, 각 행의 컨텍스트를 유지하면서 집계, 순위, 비율 등 다차원적인 분석을 가능하게 해줍니다.

이 핸드북에서는 윈도우 함수가 왜 필요한지부터 시작하여, 그 구조와 종류, 그리고 실무에서 마주할 수 있는 다양한 문제들을 윈도우 함수로 얼마나 우아하게 해결할 수 있는지 구체적인 예시와 함께 깊이 있게 탐구할 것입니다. 이제, 데이터의 숨겨진 패턴과 인사이트를 발견하는 여정을 시작하겠습니다.

1. 윈도우 함수란 무엇인가? (개념 이해하기)

윈도우 함수를 이해하는 가장 좋은 방법은 ‘창문(Window)‘이라는 단어에 집중하는 것입니다.

윈도우 함수는 ‘현재 행’을 기준으로 관련된 행들의 집합(마치 창문을 통해 특정 범위를 내다보듯이)에 대해 계산을 수행하는 함수입니다.

GROUP BY가 여러 행을 하나로 ‘압축’하여 결과를 보여준다면, 윈도우 함수는 각 행을 그대로 유지한 채, 계산된 값을 새로운 열에 ‘추가’하는 방식으로 동작합니다.

위 그림처럼, 각 행(집)은 자신의 위치를 유지하면서, 특정 범위(창문) 안에 있는 다른 행(집)들의 정보를 참고하여 새로운 값을 계산할 수 있습니다. 예를 들어, 3번 집에 사는 사람은 창문을 통해 1, 2, 3, 4, 5번 집을 모두 보고 “우리 동네 5가구의 평균 소득”을 계산해서 자신의 집에 그 정보를 추가할 수 있는 셈입니다.

윈도우 함수의 기본 구조: OVER() 절

모든 윈도우 함수는 OVER()라는 특별한 절과 함께 사용됩니다. 이 OVER() 절이 바로 ‘창문’의 범위와 기준을 정의하는 역할을 합니다.

SELECT
    column_name(s),
    WINDOW_FUNCTION_NAME(argument(s)) OVER (
        [PARTITION BY column_name(s)]
        [ORDER BY column_name(s)]
        [ROWS or RANGE BETWEEN start AND end]
    ) AS new_column_name
FROM
    table_name;

OVER() 절 안에는 세 가지 주요 구성 요소가 있습니다.

  1. PARTITION BY: 창문을 통해 내다볼 ‘그룹’을 나눕니다. GROUP BY와 유사하지만, 행을 합치지 않습니다. 예를 들어 PARTITION BY department는 부서별로 별개의 창문을 만드는 것과 같습니다.
  2. ORDER BY: 파티션(그룹) 내에서 어떤 순서로 행을 정렬할지 결정합니다. 순위나 누적 값을 계산할 때 필수적입니다.
  3. ROWS / RANGE (프레임 절): 파티션 내에서 현재 행을 기준으로 계산에 포함할 구체적인 행의 범위를 지정합니다. 예를 들어 ‘현재 행과 이전 2개 행’ 또는 ‘현재 행부터 파티션의 마지막 행까지’와 같이 매우 세밀한 범위 설정이 가능합니다.

이 세 가지 요소를 어떻게 조합하느냐에 따라 윈도우 함수의 무궁무진한 활용이 가능해집니다.

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

윈도우 함수는 크게 세 가지 유형으로 나눌 수 있습니다. 각 유형이 어떤 역할을 하는지, 대표적인 함수들과 함께 살펴보겠습니다.

가. 집계 윈도우 함수 (Aggregate Window Functions)

우리가 이미 GROUP BY와 함께 사용하던 익숙한 집계 함수들(SUM, AVG, COUNT, MAX, MIN)을 윈도우 함수로 사용할 수 있습니다.

이 함수들은 파티션 전체에 대한 집계 값을 각 행에 추가해 줍니다.

함수설명예시 사용 사례
SUM()파티션 내 값들의 합계를 구합니다.월별 매출과 함께 누적 매출 계산
AVG()파티션 내 값들의 평균을 구합니다.직원별 급여와 함께 부서 평균 급여 비교
COUNT()파티션 내 행의 개수를 셉니다.각 게시글과 함께 해당 작성자가 쓴 총 게시글 수 표시
MAX()파티션 내 최댓값을 구합니다.학생별 점수와 함께 해당 반의 최고 점수 표시
MIN()파티션 내 최솟값을 구합니다.제품별 판매가와 함께 해당 카테고리의 최저가 표시

예시: 직원 급여와 부서 평균 급여 비교

SELECT
    employee_name,
    department,
    salary,
    -- 각 부서(파티션)별 평균 급여를 계산
    AVG(salary) OVER (PARTITION BY department) AS avg_salary_in_dept
FROM
    employees;

결과:

employee_namedepartmentsalaryavg_salary_in_dept
AliceSales70007500.00
BobSales80007500.00
CharlieEngineering90009500.00
DavidEngineering100009500.00

GROUP BY를 사용했다면 부서와 평균 급여만 볼 수 있었겠지만, 윈도우 함수를 사용함으로써 각 직원의 정보는 그대로 유지한 채, 부서 평균이라는 컨텍스트 정보를 추가할 수 있었습니다.

나. 순위 윈도우 함수 (Ranking Window Functions)

파티션 내에서 각 행의 순위를 매기는 데 사용됩니다. 순위를 매기는 방식에 따라 여러 함수가 제공됩니다.

함수설명특징
ROW_NUMBER()파티션 내에서 고유한 순위를 부여합니다. (1, 2, 3, 4, …)동점자가 있어도 무조건 다른 순위를 매깁니다.
RANK()일반적인 순위 방식입니다. (1, 2, 2, 4, …)동점자에게는 같은 순위를 부여하고, 다음 순위는 동점자 수를 건너뛰어 매깁니다.
DENSE_RANK()RANK()와 유사하지만, 순위를 건너뛰지 않습니다. (1, 2, 2, 3, …)동점자가 있어도 다음 순위는 바로 이어집니다.
NTILE(n)파티션의 행들을 n개의 그룹(버킷)으로 나눕니다.상위 25%, 50% 등 백분위수를 계산할 때 유용합니다.

예시: 부서별 급여 순위 매기기

SELECT
    employee_name,
    department,
    salary,
    -- 부서별로 급여가 높은 순서대로 순위 부여
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM
    employees;

결과:

employee_namedepartmentsalarysalary_rank
BobSales80001
AliceSales70002
DavidEngineering100001
CharlieEngineering90002

이 쿼리 하나만으로 “각 부서의 급여 1위는 누구인가?”라는 질문에 대한 답을 쉽게 찾을 수 있습니다.

다. 값/오프셋 윈도우 함수 (Value/Offset Window Functions)

파티션 내에서 현재 행을 기준으로 특정 위치에 있는 행의 값을 가져오는 데 사용됩니다. 시계열 데이터 분석에 특히 강력합니다.

함수설명예시 사용 사례
LAG(col, n, default)현재 행보다 n번째 앞에 있는 행의 col 값을 가져옵니다.어제의 매출과 오늘의 매출을 비교
LEAD(col, n, default)현재 행보다 n번째 뒤에 있는 행의 col 값을 가져옵니다.오늘의 매출과 내일의 예상 매출을 비교
FIRST_VALUE(col)파티션의 첫 번째 행의 col 값을 가져옵니다.각 직원의 급여와 해당 부서에서 가장 먼저 입사한 직원의 급여 비교
LAST_VALUE(col)파티션의 마지막 행의 col 값을 가져옵니다.각 직원의 급여와 해당 부서에서 가장 최근에 입사한 직원의 급여 비교

예시: 전월 대비 매출 성장률 계산

SELECT
    sale_month,
    monthly_revenue,
    -- 1개월 전(LAG) 매출을 가져옴
    LAG(monthly_revenue, 1, 0) OVER (ORDER BY sale_month) AS previous_month_revenue
FROM
    monthly_sales;

결과:

sale_monthmonthly_revenueprevious_month_revenue
2023-0110000
2023-0212001000
2023-0311001200
2023-0415001100

이제 (monthly_revenue - previous_month_revenue) / previous_month_revenue 공식을 적용하면, 복잡한 SELF JOIN 없이도 손쉽게 월별 성장률을 계산할 수 있습니다.

3. 실전 활용: 윈도우 함수로 문제 해결하기

개념과 종류를 익혔으니, 이제 실제 비즈니스 문제들을 윈도우 함수로 어떻게 해결하는지 살펴보겠습니다.

활용 사례 1: 누적 합계 (Running Total) 계산

가장 대표적인 활용 사례입니다. 시간의 흐름에 따른 값의 누적 변화를 추적하는 데 매우 유용합니다.

문제: 일별 접속자 수 데이터가 있을 때, 특정 기간 동안의 누적 접속자 수를 계산하고 싶습니다.

SELECT
    access_date,
    daily_visitors,
    SUM(daily_visitors) OVER (ORDER BY access_date) AS cumulative_visitors
FROM
    daily_logs;

ORDER BY를 통해 날짜 순으로 정렬하고, 프레임 절을 생략하면 기본값인 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(처음부터 현재 행까지)가 적용되어 자연스럽게 누적 합계가 계산됩니다.

활용 사례 2: 그룹 내 상위 N개 추출 (Top-N per Group)

“각 카테고리별 판매량 상위 3개 제품” 또는 “각 부서별 성과 상위 2명”과 같은 요구사항은 매우 흔합니다.

문제: 각 영화 장르별로 평점이 가장 높은 2개의 영화를 찾고 싶습니다.

WITH RankedMovies AS (
    SELECT
        title,
        genre,
        rating,
        ROW_NUMBER() OVER (PARTITION BY genre ORDER BY rating DESC) as rn
    FROM
        movies
)
SELECT
    title,
    genre,
    rating
FROM
    RankedMovies
WHERE
    rn <= 2;

ROW_NUMBER() (또는 RANK())를 사용하여 장르별로 순위를 매긴 후, CTE(Common Table Expression)로 묶어 순위가 2 이하인 영화만 필터링하는 것이 일반적인 패턴입니다.

활용 사례 3: 이동 평균 (Moving Average) 계산

주가 분석, 사용자 활동 추이 분석 등에서 단기적인 변동을 완화하고 장기적인 추세를 파악하기 위해 이동 평균을 자주 사용합니다.

문제: 최근 7일간의 매출 이동 평균을 계산하여 일별 매출 변동의 추세를 보고 싶습니다.

SELECT
    sale_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7_days
FROM
    daily_sales;

여기서 바로 프레임 절(ROWS BETWEEN ...)이 강력한 힘을 발휘합니다. 6 PRECEDING AND CURRENT ROW는 ‘이전 6개 행과 현재 행’, 즉 총 7개 행을 프레임(창문)으로 삼아 평균을 계산하라는 의미입니다.

4. 심화 학습: 프레임 절 (ROWS vs RANGE)

윈도우 함수의 디테일을 완성하는 마지막 퍼즐은 프레임 절입니다. 프레임 절은 ORDER BY가 사용되었을 때, 파티션 내에서 계산에 포함될 행의 범위를 더욱 정밀하게 제어합니다.

  • ROWS: 현재 행을 기준으로 물리적인 행의 위치(오프셋)를 지정합니다. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING은 ‘이전 1개 행, 현재 행, 다음 1개 행’을 의미합니다.

  • RANGE: 현재 행의 ‘값’을 기준으로 논리적인 범위를 지정합니다. ORDER BY 컬럼에 중복된 값이 있을 때 ROWS와 차이가 발생합니다. 예를 들어, RANGE BETWEEN 1 PRECEDING AND CURRENT ROW는 현재 행의 값보다 1 작은 값부터 현재 행의 값과 같은 값을 가진 모든 행을 포함합니다.

일반적으로는 ROWS가 더 직관적이고 명확하여 자주 사용됩니다. ORDER BY된 컬럼에 동점자가 많고, 그 동점자들을 모두 하나의 단위로 묶어 계산하고 싶을 때 RANGE를 고려할 수 있습니다.

결론: 분석의 새로운 창을 열다

윈도우 함수는 처음에는 그 문법이 다소 복잡하게 느껴질 수 있습니다. OVER, PARTITION BY, ORDER BY, 그리고 프레임 절까지, 고려해야 할 요소가 많기 때문입니다.

하지만 이 핸드북에서 살펴본 것처럼, 윈도우 함수의 기본 구조와 각 구성 요소의 역할을 이해하고 나면, 이전에는 여러 단계의 서브쿼리와 JOIN으로 복잡하게 작성해야 했던 분석 쿼리를 단 하나의 우아하고 직관적인 쿼리로 해결할 수 있게 됩니다.

  • 가독성 향상: 쿼리의 의도가 명확하게 드러납니다.

  • 성능 개선: 여러 번 테이블에 접근해야 했던 작업을 한 번으로 줄여 성능상 이점을 가질 수 있습니다.

  • 분석의 깊이 확장: 개별 데이터의 맥락을 유지하면서 집계, 순위, 비율, 시계열 비교 등 고차원적인 분석을 손쉽게 수행할 수 있습니다.

이제 여러분의 데이터에 윈도우 함수라는 새로운 ‘창’을 내어보세요. 그 창을 통해 이전에는 보지 못했던 새로운 인사이트와 패턴을 발견하게 될 것입니다.

레퍼런스(References)

윈도우 함수

그래프 뷰

백링크