SQL 윈도우 함수 완벽 가이드

SQL 윈도우 함수는 데이터를 그룹별로 나누어 집계하면서도 각 행의 정보를 유지하는 강력한 기능입니다. ROW_NUMBER, RANK, LAG/LEAD, PARTITION BY 등 실무에서 자주 사용되는 윈도우 함수들을 초급자도 쉽게 이해할 수 있도록 설명합니다.

---

목차

1. 윈도우 함수 개념
2. ROW_NUMBER로 순번 매기기
3. RANK와 DENSE_RANK 차이
4. LAG/LEAD로 이전/다음 행 참조
5. PARTITION BY로 그룹별 집계
6. SUM OVER로 누적 합계

---

1. 윈도우 함수 개념

시작하며

여러분이 쇼핑몰의 주문 데이터를 분석할 때 이런 상황을 겪어본 적 있나요? "각 고객별로 주문 금액이 높은 순서대로 순위를 매기고 싶은데, 모든 주문 내역은 그대로 보고 싶어요." 일반적인 GROUP BY를 사용하면 집계는 되지만 상세 정보가 사라지고, 그냥 조회하면 순위를 매길 수 없죠.

이런 문제는 실제 개발 현장에서 정말 자주 발생합니다. 판매 순위, 성적 순위, 시계열 데이터 분석 등 "그룹별로 집계는 하고 싶지만 원본 데이터도 유지하고 싶은" 상황이 매일 발생하거든요.

전통적인 SQL만으로는 이런 요구사항을 처리하기가 정말 복잡하고 어렵습니다. 바로 이럴 때 필요한 것이 윈도우 함수입니다.

윈도우 함수를 사용하면 데이터를 그룹별로 나누어 계산하면서도, 각 행의 상세 정보를 그대로 유지할 수 있습니다. 마치 창문(Window)을 통해 특정 범위의 데이터만 보면서 계산하는 것처럼 작동합니다.

개요

간단히 말해서, 윈도우 함수는 테이블의 각 행마다 "특정 범위(윈도우)"의 데이터를 참조하여 계산을 수행하는 함수입니다. GROUP BY처럼 데이터를 그룹화하지만, 결과에서 각 행이 사라지지 않고 그대로 유지됩니다.

왜 이 개념이 필요한지 실무 관점에서 설명하면, 데이터 분석에서는 "집계와 상세 정보를 동시에" 보고 싶은 경우가 정말 많습니다. 예를 들어, 각 부서별 평균 급여를 계산하면서도 모든 직원의 급여 정보를 함께 보고 싶을 때, 또는 제품별 판매 순위를 매기면서도 각 판매 기록의 상세 정보를 유지하고 싶을 때 매우 유용합니다.

전통적인 방법과 비교해볼까요? 기존에는 서브쿼리나 조인을 여러 번 사용해서 복잡하게 구현했다면, 이제는 윈도우 함수 하나로 간단하고 명확하게 처리할 수 있습니다.

성능도 훨씬 좋고, 코드도 읽기 쉬워집니다. 윈도우 함수의 핵심 특징은 크게 세 가지입니다.

첫째, OVER 절을 사용하여 "윈도우(계산 범위)"를 정의합니다. 둘째, PARTITION BY로 데이터를 그룹으로 나눕니다.

셋째, ORDER BY로 그룹 내 정렬 순서를 지정합니다. 이러한 특징들이 함께 작동하여 강력하고 유연한 데이터 분석을 가능하게 만듭니다.

코드 예제

-- 직원 테이블에서 부서별 평균 급여와 함께 각 직원 정보 조회
SELECT
    employee_name,
    department,
    salary,
    -- 윈도우 함수: 부서별 평균 급여 계산
    AVG(salary) OVER (PARTITION BY department) as dept_avg_salary,
    -- 전체 평균 급여 계산
    AVG(salary) OVER () as company_avg_salary
FROM employees
ORDER BY department, salary DESC;

설명

이것이 하는 일: 윈도우 함수는 SELECT 문에서 각 행마다 실행되면서, OVER 절에 정의된 "윈도우(범위)" 내의 데이터를 참조하여 계산을 수행합니다. 마치 엑셀에서 특정 셀 범위를 참조하여 계산하는 것과 비슷합니다.

첫 번째로 이해해야 할 부분은 OVER 절입니다. AVG(salary) OVER (PARTITION BY department)에서 OVER 절이 "어떤 범위의 데이터를 볼 것인가"를 정의합니다.

PARTITION BY department는 "부서별로 나누어서" 평균을 계산하라는 의미입니다. 이렇게 하면 각 행마다 해당 직원이 속한 부서의 평균 급여가 계산되어 표시됩니다.

두 번째로, AVG(salary) OVER ()처럼 PARTITION BY 없이 사용하면 전체 데이터를 하나의 윈도우로 보고 계산합니다. 즉, 모든 직원의 평균 급여가 각 행마다 동일하게 표시됩니다.

이렇게 하면 한 번의 쿼리로 "개별 급여", "부서별 평균", "전체 평균"을 모두 볼 수 있습니다. 세 번째로 중요한 점은, GROUP BY와의 차이입니다.

GROUP BY를 사용하면 그룹당 하나의 행만 결과에 나타나지만, 윈도우 함수는 모든 원본 행을 유지합니다. 예를 들어, 10명의 직원이 있고 3개 부서가 있다면, GROUP BY는 3개 행을 반환하지만 윈도우 함수는 10개 행을 모두 반환하면서 각 행에 집계 값을 추가합니다.

여러분이 이 코드를 사용하면 데이터 분석이 훨씬 쉬워집니다. 복잡한 서브쿼리나 조인 없이도 "개별 정보와 집계 정보를 동시에" 볼 수 있고, 직원이 자신의 급여가 부서 평균이나 전체 평균과 어떻게 비교되는지 한눈에 파악할 수 있습니다.

또한 쿼리 성능도 여러 번 조인하는 것보다 훨씬 빠르고, 코드의 가독성도 크게 향상됩니다.

실전 팁

💡 윈도우 함수는 WHERE 절이 실행된 후에 적용됩니다. 따라서 윈도우 함수의 결과로 필터링하려면 서브쿼리나 CTE를 사용해야 합니다.

💡 OVER () 절에 아무것도 명시하지 않으면 전체 테이블이 하나의 윈도우가 됩니다. 이는 전체 집계를 각 행에 표시할 때 유용합니다.

💡 성능을 위해 윈도우 함수에 사용되는 컬럼(PARTITION BY, ORDER BY)에 인덱스를 생성하면 좋습니다. 특히 대용량 데이터에서 큰 차이가 납니다.

💡 같은 OVER 절을 여러 번 사용한다면 WINDOW 절로 재사용할 수 있습니다: WINDOW w AS (PARTITION BY department) 그리고 OVER w로 사용.

💡 윈도우 함수는 SELECT, ORDER BY 절에서만 사용 가능합니다. WHERE, GROUP BY, HAVING 절에서는 직접 사용할 수 없으니 주의하세요.

---

2. ROW_NUMBER로 순번 매기기

시작하며

여러분이 게시판을 만들 때 이런 요구사항을 받아본 적 있나요? "각 카테고리별로 최신 글 3개씩만 보여주세요." 또는 "각 사용자가 작성한 글 중에서 가장 최근 글만 가져오세요." 이런 상황에서 어떻게 해야 할까요?

일반적인 방법으로는 정말 복잡합니다. 서브쿼리를 여러 개 중첩하거나, 임시 테이블을 만들어야 하죠.

코드도 길어지고 성능도 나빠집니다. "그룹 내에서 순서대로 번호를 매기고, 그 번호로 필터링"하는 간단한 작업인데 말이죠.

바로 이럴 때 ROW_NUMBER 함수가 완벽한 해결책입니다. ROW_NUMBER는 각 행에 고유한 순번을 자동으로 매겨주는 윈도우 함수입니다.

PARTITION BY로 그룹을 나누고, ORDER BY로 정렬 기준을 정하면, 각 그룹 내에서 1번부터 순차적으로 번호가 매겨집니다.

개요

간단히 말해서, ROW_NUMBER()는 결과 집합의 각 행에 고유한 순번을 부여하는 함수입니다. 중복된 값이 있어도 무조건 다른 번호가 매겨지며, 1부터 시작해서 1씩 증가합니다.

이 함수가 왜 필요한지 실무 관점에서 설명하면, 데이터 중복 제거, Top N 쿼리, 페이지네이션 구현 등에서 정말 자주 사용됩니다. 예를 들어, 각 고객별로 가장 최근 주문 3건만 조회하거나, 각 제품 카테고리에서 판매량 상위 5개 제품만 선택하는 경우에 매우 유용합니다.

또한 웹 개발에서 페이지네이션(1페이지: 1-10번, 2페이지: 11-20번)을 구현할 때도 필수적입니다. 전통적인 방법과 비교해볼까요?

기존에는 변수를 사용하거나(@rownum := @rownum + 1), 복잡한 서브쿼리로 순번을 매겼다면, ROW_NUMBER를 사용하면 한 줄로 깔끔하게 해결됩니다. 코드도 명확하고, 다른 개발자가 봐도 의도를 바로 이해할 수 있습니다.

ROW_NUMBER의 핵심 특징은 세 가지입니다. 첫째, 항상 고유한 번호를 부여합니다(같은 값이라도 다른 번호).

둘째, PARTITION BY로 그룹을 나누면 각 그룹마다 1부터 다시 시작합니다. 셋째, ORDER BY는 필수이며, 이것이 순번을 매기는 기준이 됩니다.

이러한 특징들 덕분에 정확하고 예측 가능한 순번 부여가 가능합니다.

코드 예제

-- 카테고리별 최신 게시글 3개씩 조회
WITH ranked_posts AS (
    SELECT
        post_id,
        category,
        title,
        created_at,
        -- 카테고리별로 작성일 기준 내림차순 순번 부여
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY created_at DESC
        ) as row_num
    FROM posts
)
-- 각 카테고리에서 순번 1~3번만 선택
SELECT post_id, category, title, created_at
FROM ranked_posts
WHERE row_num <= 3
ORDER BY category, row_num;

설명

이것이 하는 일: 이 쿼리는 CTE(Common Table Expression)를 사용하여 먼저 모든 게시글에 카테고리별 순번을 매기고, 그 다음 각 카테고리에서 상위 3개만 필터링합니다. 두 단계로 나뉘어 있지만 실제로는 하나의 쿼리로 실행됩니다.

첫 번째 단계를 자세히 살펴보면, ROW_NUMBER() OVER (PARTITION BY category ORDER BY created_at DESC)가 핵심입니다. PARTITION BY category는 "카테고리별로 그룹을 나누라"는 의미입니다.

예를 들어 '개발', '디자인', '마케팅' 카테고리가 있다면, 각 카테고리가 독립적인 그룹이 되어 각각 1번부터 순번이 매겨집니다. ORDER BY created_at DESC는 "작성일 기준으로 최신 것부터 순번을 매기라"는 뜻입니다.

두 번째 단계에서는 WHERE row_num <= 3 조건으로 필터링합니다. 여기서 주의할 점은 윈도우 함수는 WHERE 절에서 직접 사용할 수 없기 때문에, CTE나 서브쿼리로 먼저 순번을 계산한 다음 필터링해야 한다는 것입니다.

이렇게 하면 각 카테고리마다 1, 2, 3번 게시글만 선택됩니다. 마지막으로 ORDER BY category, row_num으로 정렬하면, 결과가 카테고리별로 묶이고 그 안에서 순번 순서대로 정리됩니다.

실제 화면에서 보면 '개발 카테고리 최신 3개', '디자인 카테고리 최신 3개' 이런 식으로 깔끔하게 표시됩니다. 여러분이 이 코드를 사용하면 복잡한 그룹별 Top N 쿼리를 간단하게 작성할 수 있습니다.

페이지네이션 구현도 쉬워지고(OFFSET, LIMIT 대신 순번으로 제어), 중복 데이터 제거도 정확하게 할 수 있습니다. 특히 여러 그룹에서 동시에 상위 N개를 뽑아야 할 때, 서브쿼리를 여러 번 작성하는 대신 이 방법 하나로 해결됩니다.

실전 팁

💡 ROW_NUMBER는 같은 값이 있어도 다른 번호를 부여합니다. 만약 동점자에게 같은 순위를 주고 싶다면 RANK나 DENSE_RANK를 사용하세요.

💡 페이지네이션 구현 시 OFFSET/LIMIT 대신 ROW_NUMBER를 사용하면 더 정확합니다. 특히 데이터가 계속 추가되는 환경에서 페이지 중복/누락을 방지할 수 있습니다.

💡 중복 데이터 삭제 시 ROW_NUMBER()를 사용하여 각 중복 그룹에서 하나만 남기고 삭제할 수 있습니다: DELETE FROM table WHERE row_num > 1.

💡 ORDER BY 절에 여러 컬럼을 사용할 수 있습니다. 예: ORDER BY created_at DESC, post_id DESC로 날짜가 같으면 ID로 구분.

💡 성능 최적화: PARTITION BY와 ORDER BY에 사용된 컬럼에 복합 인덱스를 생성하면 대용량 데이터에서도 빠르게 작동합니다.

---

3. RANK와 DENSE_RANK 차이

시작하며

여러분이 시험 성적 순위를 매기는 프로그램을 만든다고 상상해보세요. 90점이 3명, 85점이 1명, 80점이 2명이 있을 때, 순위를 어떻게 매겨야 할까요?

90점은 모두 1등이지만, 다음 85점은 2등일까요, 4등일까요? 이런 문제는 순위 시스템을 구현할 때 항상 마주치는 고민입니다.

스포츠 경기 순위, 판매 실적 순위, 게임 랭킹 등 실무에서 순위를 다루는 경우가 정말 많은데, "동점자를 어떻게 처리할 것인가"에 따라 사용자 경험이 완전히 달라집니다. ROW_NUMBER는 무조건 다른 번호를 주기 때문에 진정한 순위가 아닙니다.

바로 이럴 때 필요한 것이 RANK와 DENSE_RANK입니다. 이 두 함수는 같은 값에 같은 순위를 부여하지만, 다음 순위를 계산하는 방식이 다릅니다.

RANK는 "건너뛰기" 방식(1, 1, 1, 4, 5), DENSE_RANK는 "연속" 방식(1, 1, 1, 2, 3)입니다.

개요

간단히 말해서, RANK와 DENSE_RANK는 동점자에게 같은 순위를 부여하는 순위 함수입니다. 둘의 차이는 동점 이후 다음 순위를 계산하는 방식에 있습니다.

이 개념이 왜 필요한지 실무 관점에서 설명하면, 공정한 순위 시스템을 구현하기 위해서입니다. 예를 들어, 학생 성적 순위에서 같은 점수를 받은 학생들은 당연히 같은 순위를 받아야 합니다.

판매 실적 순위에서도 동일한 금액을 판매한 직원들은 같은 등수여야 공정하죠. 또한 "상위 10%"나 "Top 100" 같은 기준을 적용할 때도 정확한 순위가 필요합니다.

전통적인 방법과 비교해볼까요? 기존에는 동점자 처리를 위해 복잡한 로직(COUNT, GROUP BY 등)을 여러 단계로 구현했다면, RANK/DENSE_RANK를 사용하면 단 한 줄로 해결됩니다.

코드가 간결해질 뿐만 아니라, 의도도 명확하게 드러납니다. 핵심 차이점을 정리하면: RANK는 동점자 수만큼 다음 순위를 건너뜁니다(3명이 1등이면 다음은 4등).

DENSE_RANK는 연속된 순위를 유지합니다(3명이 1등이어도 다음은 2등). 어떤 것을 선택할지는 비즈니스 요구사항에 따라 다릅니다.

올림픽 메달 순위처럼 실제 경쟁 순서를 보여주려면 RANK, 등급(S, A, B, C)처럼 구간을 나누려면 DENSE_RANK가 적합합니다.

코드 예제

-- 학생 성적 순위를 RANK와 DENSE_RANK로 비교
SELECT
    student_name,
    score,
    -- RANK: 동점 이후 순위를 건너뜀 (1, 1, 1, 4, 5...)
    RANK() OVER (ORDER BY score DESC) as rank_position,
    -- DENSE_RANK: 연속된 순위 유지 (1, 1, 1, 2, 3...)
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank_position,
    -- 비교를 위한 ROW_NUMBER (1, 2, 3, 4, 5...)
    ROW_NUMBER() OVER (ORDER BY score DESC) as row_position
FROM students
ORDER BY score DESC;

-- 결과 예시:
-- 이름      점수  RANK  DENSE_RANK  ROW_NUMBER
-- 철수      95    1     1           1
-- 영희      95    1     1           2
-- 민수      90    3     2           3
-- 지영      90    3     2           4
-- 동현      85    5     3           5

설명

이것이 하는 일: 이 쿼리는 학생들의 점수를 기준으로 세 가지 방식의 순번/순위를 동시에 계산하여 그 차이를 명확하게 보여줍니다. 같은 데이터에 세 가지 함수를 적용함으로써 각각의 특성을 한눈에 비교할 수 있습니다.

첫 번째로, RANK() OVER (ORDER BY score DESC)를 살펴봅시다. 이 함수는 점수가 높은 순서대로 순위를 매기는데, 같은 점수를 가진 학생들에게는 모두 같은 순위를 부여합니다.

예를 들어 95점인 학생이 2명이면 둘 다 1등입니다. 그런데 중요한 점은 다음 90점 학생은 2등이 아니라 3등이 된다는 것입니다.

"앞에 2명이 있으니 나는 3번째"라는 논리죠. 이것이 스포츠나 시험에서 사용하는 전통적인 순위 방식입니다.

두 번째로, DENSE_RANK() OVER (ORDER BY score DESC)는 조금 다르게 작동합니다. 95점 2명이 1등인 것은 같지만, 다음 90점 학생은 바로 2등이 됩니다.

순위가 건너뛰지 않고 연속적으로 이어지는 것이죠. 이 방식은 "몇 개의 서로 다른 점수 그룹이 있는가"를 세는 것과 같습니다.

등급 시스템(금, 은, 동)이나 점수 구간을 나눌 때 유용합니다. 세 번째로 참고용으로 추가한 ROW_NUMBER는 동점을 무시하고 무조건 1, 2, 3, 4, 5로 매깁니다.

같은 점수라도 다른 번호를 받죠. 이것은 순위가 아니라 단순한 행 번호입니다.

여러분이 이 코드를 실제로 사용할 때는 상황에 맞게 선택하면 됩니다. 경쟁 시스템이나 시험 순위처럼 "내가 실제로 몇 번째인가"가 중요하면 RANK를 사용하세요.

반면 "몇 개의 등급이 있는가" 또는 "Top 10 구간 안에 드는가"처럼 구간이 중요하면 DENSE_RANK를 사용하면 됩니다. 예를 들어 "상위 5개 등급" 안에 드는 사용자를 찾을 때 DENSE_RANK <= 5를 사용하면 정확합니다.

실전 팁

💡 "Top N" 필터링 시 주의: RANK로 상위 10명을 뽑으면 동점자 때문에 10명 이상이 나올 수 있습니다. 정확히 10명이 필요하면 ROW_NUMBER를 사용하세요.

💡 등급 시스템 구현: DENSE_RANK로 순위를 매긴 후 CASE 문으로 등급 변환 (1-10: S등급, 11-30: A등급 등).

💡 PARTITION BY와 함께 사용하면 그룹별 순위를 매길 수 있습니다: RANK() OVER (PARTITION BY department ORDER BY score DESC)로 부서별 순위.

💡 NULL 값 처리: ORDER BY에서 NULLS FIRST 또는 NULLS LAST를 명시하여 NULL의 순위 위치를 제어할 수 있습니다.

💡 성능 팁: 순위 함수들은 정렬이 필요하므로, ORDER BY 컬럼에 인덱스를 생성하면 대용량 데이터에서 성능이 크게 향상됩니다.

---

4. LAG/LEAD로 이전/다음 행 참조

시작하며

여러분이 주식 거래 시스템을 만든다고 상상해보세요. 오늘 주가와 어제 주가를 비교하여 등락률을 계산해야 합니다.

또는 쇼핑몰에서 각 고객의 "이번 구매"와 "이전 구매" 사이의 간격을 분석해야 하는 상황이죠. 어떻게 구현하시겠어요?

전통적인 방법은 정말 복잡합니다. 자기 자신과 조인(Self Join)을 하거나, 서브쿼리를 여러 개 작성해야 합니다.

"바로 이전 행"이나 "바로 다음 행"의 데이터를 가져오는 단순한 작업인데, 코드가 길고 복잡해지며 성능도 나빠집니다. 특히 시계열 데이터 분석에서 이런 요구사항은 정말 자주 발생합니다.

바로 이럴 때 LAG와 LEAD 함수가 완벽한 해결책입니다. LAG는 "이전 행"의 값을, LEAD는 "다음 행"의 값을 현재 행에서 직접 참조할 수 있게 해줍니다.

마치 타임머신처럼 과거나 미래의 데이터를 현재 시점에서 볼 수 있는 것이죠.

개요

간단히 말해서, LAG는 현재 행 기준으로 이전 행의 값을 가져오고, LEAD는 다음 행의 값을 가져오는 함수입니다. 몇 번째 이전/다음 행인지(offset), 값이 없을 때 기본값(default)도 지정할 수 있습니다.

이 함수들이 왜 필요한지 실무 관점에서 설명하면, 시계열 데이터 분석에서 필수적이기 때문입니다. 예를 들어, 매출 증감률 계산(이번 달 vs 지난달), 사용자 행동 패턴 분석(이전 페이지 → 현재 페이지), 재고 변동 추적(입고 전 수량 vs 입고 후 수량) 등에서 매일 사용됩니다.

웹 로그 분석에서 "사용자가 A 페이지 다음에 어떤 페이지로 이동했는가"를 분석할 때도 LEAD가 핵심입니다. 전통적인 방법과 비교해볼까요?

기존에는 Self Join으로 구현했습니다: FROM table t1 LEFT JOIN table t2 ON t1.id = t2.id - 1 이런 식이죠. 하지만 이 방법은 코드가 복잡하고, 성능이 나쁘며(조인 비용), 의도를 파악하기 어렵습니다.

LAG/LEAD를 사용하면 한 줄로 명확하게 표현됩니다. 핵심 특징을 정리하면: 첫째, offset 파라미터로 몇 칸 이전/이후를 볼지 지정합니다(기본값 1).

둘째, default 파라미터로 값이 없을 때(첫 행의 LAG, 마지막 행의 LEAD) 기본값을 설정합니다. 셋째, PARTITION BY와 함께 사용하면 그룹 내에서만 이전/다음을 참조합니다.

이러한 특징들이 유연하고 강력한 시계열 분석을 가능하게 만듭니다.

코드 예제

-- 일별 매출과 전일 대비 증감률 계산
SELECT
    sale_date,
    daily_revenue,
    -- 이전 날짜의 매출 가져오기 (없으면 0)
    LAG(daily_revenue, 1, 0) OVER (ORDER BY sale_date) as prev_revenue,
    -- 다음 날짜의 매출 가져오기 (미래 예측과 비교용)
    LEAD(daily_revenue, 1, 0) OVER (ORDER BY sale_date) as next_revenue,
    -- 전일 대비 증감률 계산 (%)
    ROUND(
        (daily_revenue - LAG(daily_revenue, 1, daily_revenue) OVER (ORDER BY sale_date))
        / LAG(daily_revenue, 1, daily_revenue) OVER (ORDER BY sale_date) * 100,
        2
    ) as growth_rate
FROM daily_sales
ORDER BY sale_date;

설명

이것이 하는 일: 이 쿼리는 일별 매출 데이터에서 각 날짜의 매출과 함께 전날 매출, 다음 날 매출, 그리고 전일 대비 증감률을 계산합니다. 복잡한 조인 없이 LAG/LEAD 함수만으로 시계열 비교 분석을 수행하는 좋은 예시입니다.

첫 번째로 LAG(daily_revenue, 1, 0) OVER (ORDER BY sale_date)를 살펴봅시다. 이 함수는 sale_date 순서대로 정렬된 상태에서, 현재 행의 바로 이전 행(offset=1)의 daily_revenue 값을 가져옵니다.

만약 이전 행이 없다면(첫 번째 날짜), 기본값 0을 반환합니다. 이렇게 하면 매출 데이터의 첫 날에도 에러 없이 안전하게 작동합니다.

두 번째로 LEAD(daily_revenue, 1, 0) OVER (ORDER BY sale_date)는 반대로 작동합니다. 현재 날짜의 다음 날 매출을 가져오는 것이죠.

이것은 주로 "예측값과 실제값 비교" 같은 분석에 사용됩니다. 예를 들어, 어제 내일 매출을 100만원으로 예측했는데 실제로는 얼마였는지 비교할 때 유용합니다.

세 번째로 growth_rate 계산 부분이 핵심입니다. (오늘 매출 - 어제 매출) / 어제 매출 * 100으로 증감률을 계산하는데, 여기서 LAG를 사용하여 어제 매출을 가져옵니다.

주의할 점은 첫 날의 경우 이전 매출이 없으므로, 기본값으로 현재 매출을 사용하여 0으로 나누는 에러를 방지합니다. ROUND 함수로 소수점 2자리까지 표시하여 가독성을 높였습니다.

여러분이 이 코드를 사용하면 일별/월별 트렌드 분석이 정말 쉬워집니다. 복잡한 Self Join 없이도 "전년 동기 대비", "전월 대비", "전일 대비" 같은 비교 분석을 한 번의 쿼리로 수행할 수 있습니다.

특히 대시보드에서 "오늘 매출: 500만원 (▲ 15%)" 같은 표시를 만들 때, 이 쿼리 하나면 모든 데이터가 준비됩니다. 성능도 조인보다 훨씬 빠르고, 코드도 읽기 쉽습니다.

실전 팁

💡 0으로 나누기 에러 방지: 증감률 계산 시 LAG 값이 0일 수 있으니 NULLIF(LAG(...), 0)으로 감싸면 안전합니다.

💡 여러 칸 이전/이후 참조: LAG(value, 3)처럼 offset을 조정하여 3일 전, 7일 전(주간 비교) 데이터를 가져올 수 있습니다.

💡 PARTITION BY 활용: LAG(...) OVER (PARTITION BY category ORDER BY date)로 카테고리별로 독립적인 시계열 분석 가능.

💡 NULL 처리 전략: default 값을 NULL로 두고 COALESCE나 CASE로 처리하면 더 유연한 로직 구현 가능.

💡 성능 최적화: ORDER BY에 사용되는 날짜 컬럼에 인덱스를 생성하면 정렬 속도가 크게 향상됩니다.

---

5. PARTITION BY로 그룹별 집계

시작하며

여러분이 대시보드를 만들 때 이런 요구사항을 받아본 적 있나요? "각 직원의 정보와 함께 그 직원이 속한 부서의 평균 급여를 함께 보여주세요." 또는 "각 제품의 판매 금액과 그 제품 카테고리의 총 판매액을 같이 표시해주세요." 일반적인 방법으로는 GROUP BY로 부서별 평균을 계산한 테이블을 만들고, 원본 테이블과 조인해야 합니다.

쿼리가 길어지고 복잡해지며, 서브쿼리나 CTE를 여러 개 작성해야 하죠. "개별 데이터와 그룹 집계를 동시에 보여주는" 간단한 요구사항인데 구현은 복잡합니다.

바로 이럴 때 PARTITION BY가 진가를 발휘합니다. PARTITION BY는 윈도우 함수의 핵심 옵션으로, 데이터를 그룹으로 나누어 각 그룹 내에서 독립적으로 계산을 수행합니다.

GROUP BY처럼 그룹화하지만, 모든 행을 유지한다는 점이 결정적 차이입니다.

개요

간단히 말해서, PARTITION BY는 윈도우 함수의 OVER 절 안에서 사용되며, 전체 데이터를 여러 파티션(그룹)으로 나누는 역할을 합니다. 각 파티션 내에서 윈도우 함수가 독립적으로 계산되며, 모든 원본 행은 그대로 유지됩니다.

이 개념이 왜 필요한지 실무 관점에서 설명하면, "개별 상세 정보와 그룹 통계를 동시에" 보여줘야 하는 경우가 정말 많기 때문입니다. 예를 들어, 직원 목록에서 각 직원의 급여가 부서 평균보다 높은지 낮은지 표시하거나, 제품 목록에서 각 제품의 매출이 카테고리 전체에서 차지하는 비율을 계산할 때 필수입니다.

이런 분석을 조인 없이 한 번의 쿼리로 처리할 수 있습니다. 전통적인 방법과 비교해볼까요?

기존에는 이렇게 작성했습니다: "WITH group_stats AS (SELECT ... GROUP BY ...) SELECT * FROM table JOIN group_stats ON ...".

두 단계를 거치고 조인이 필요했죠. PARTITION BY를 사용하면 이 모든 것이 한 줄로 해결됩니다: AVG(salary) OVER (PARTITION BY department).

코드가 간결해지고, 성능도 개선되며, 의도도 명확해집니다. PARTITION BY의 핵심 특징은 세 가지입니다.

첫째, 여러 컬럼으로 파티션을 나눌 수 있습니다(PARTITION BY dept, region). 둘째, 각 파티션은 완전히 독립적으로 계산됩니다(부서 A의 순위와 부서 B의 순위는 별개).

셋째, PARTITION BY를 생략하면 전체가 하나의 파티션이 됩니다. 이러한 특징들이 유연한 그룹별 분석을 가능하게 만듭니다.

코드 예제

-- 직원 정보와 부서별 통계를 동시에 조회
SELECT
    employee_name,
    department,
    salary,
    -- 부서별 평균 급여
    ROUND(AVG(salary) OVER (PARTITION BY department), 2) as dept_avg,
    -- 부서별 최고 급여
    MAX(salary) OVER (PARTITION BY department) as dept_max,
    -- 부서 내 급여 순위
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    -- 전체 평균 급여 (비교용)
    ROUND(AVG(salary) OVER (), 2) as company_avg,
    -- 부서 평균 대비 차이 계산
    ROUND(salary - AVG(salary) OVER (PARTITION BY department), 2) as diff_from_dept_avg
FROM employees
ORDER BY department, salary DESC;

설명

이것이 하는 일: 이 쿼리는 직원 테이블에서 각 직원의 정보를 표시하면서, 동시에 부서별 통계(평균, 최댓값, 순위)와 전사 통계를 계산합니다. 조인이나 서브쿼리 없이 PARTITION BY만으로 복잡한 분석을 한 번에 수행하는 강력한 예시입니다.

첫 번째 핵심은 AVG(salary) OVER (PARTITION BY department)입니다. 이 부분이 "부서별로 파티션을 나누어, 각 파티션 내에서 평균 급여를 계산하라"는 의미입니다.

예를 들어 개발팀 직원들을 조회할 때, dept_avg 컬럼에는 개발팀 전체의 평균 급여가 표시됩니다. 같은 부서의 모든 직원에게 동일한 평균값이 표시되지만, 각 직원의 개별 급여 정보는 그대로 유지됩니다.

두 번째로 RANK() OVER (PARTITION BY department ORDER BY salary DESC)를 보면, 순위 함수와 PARTITION BY를 결합한 예시입니다. 이것은 "각 부서 내에서" 급여가 높은 순서대로 순위를 매깁니다.

개발팀의 1등과 마케팅팀의 1등은 완전히 별개입니다. 각 부서마다 1등부터 시작하는 독립적인 순위가 매겨지는 것이죠.

이런 식으로 부서별 top performer를 쉽게 찾을 수 있습니다. 세 번째로 주목할 부분은 AVG(salary) OVER ()처럼 PARTITION BY 없이 사용한 경우입니다.

이렇게 하면 전체 직원의 평균이 계산되어 모든 행에 동일하게 표시됩니다. 이를 부서 평균과 비교하면 "우리 부서가 전사 평균보다 높은가?"를 알 수 있습니다.

마지막으로 diff_from_dept_avg 컬럼은 실용적인 계산 예시입니다. 각 직원의 급여에서 부서 평균을 빼서 "부서 평균 대비 얼마나 많이/적게 받는가"를 계산합니다.

양수면 평균 이상, 음수면 평균 이하입니다. 이런 정보는 급여 조정이나 성과 평가에서 유용하게 사용됩니다.

여러분이 이 코드를 사용하면 대시보드나 리포트 작성이 정말 쉬워집니다. 한 번의 쿼리로 "개인 정보", "소속 그룹 통계", "전체 통계"를 모두 가져올 수 있어서, 프론트엔드에서 추가 계산이 필요 없습니다.

또한 조인이 없어 성능이 좋고, 데이터가 추가되어도 쿼리를 수정할 필요가 없습니다. 특히 여러 그룹의 통계를 비교할 때(부서별, 지역별, 연령대별 등) PARTITION BY를 바꾸기만 하면 되어 재사용성이 뛰어납니다.

실전 팁

💡 여러 컬럼으로 파티션 나누기: PARTITION BY department, job_title처럼 여러 기준을 조합할 수 있습니다.

💡 PARTITION BY와 ORDER BY 함께 사용: PARTITION BY로 그룹을 나누고, ORDER BY로 그룹 내 정렬을 지정하여 누적 합계 같은 계산 가능.

💡 성능 최적화: PARTITION BY에 사용되는 컬럼에 인덱스를 생성하면 대용량 데이터에서도 빠르게 작동합니다.

💡 NULL 처리: PARTITION BY에서 NULL은 하나의 독립된 그룹으로 취급됩니다. 필요시 COALESCE로 NULL을 다른 값으로 치환하세요.

💡 중복 계산 방지: 같은 PARTITION BY를 여러 번 사용한다면 CTE로 한 번만 계산하고 재사용하면 성능이 향상됩니다.

---

6. SUM OVER로 누적 합계

시작하며

여러분이 재무 대시보드를 만들 때 이런 요구를 받아본 적 있나요? "일별 매출과 함께 연초부터 현재까지의 누적 매출을 보여주세요." 또는 "월별 가입자 수와 함께 총 가입자 수(누적)를 그래프로 표시해주세요." 이런 누적 계산은 비즈니스 분석에서 정말 자주 등장합니다.

전통적인 방법으로 누적 합계를 계산하려면 정말 복잡합니다. Self Join으로 "자기 자신보다 이전 날짜의 모든 매출을 합산"하거나, 프로그래밍 언어로 데이터를 가져와서 루프를 돌며 계산해야 하죠.

SQL만으로는 어렵고, 성능도 나쁘고, 코드도 복잡합니다. 바로 이럴 때 SUM OVER with ORDER BY가 완벽한 해결책입니다.

윈도우 함수에 ORDER BY를 추가하면, "첫 행부터 현재 행까지"를 자동으로 범위로 인식하여 누적 합계를 계산해줍니다. 한 줄의 코드로 누적 매출, 누적 가입자, 이동 평균 등 다양한 누적 계산이 가능합니다.

개요

간단히 말해서, SUM() OVER (ORDER BY ...)는 정렬된 순서대로 처음부터 현재 행까지의 합계를 계산하는 누적 합계 함수입니다. 각 행마다 "여기까지의 총합"이 계산되어 시계열 분석에 필수적입니다.

이 기능이 왜 필요한지 실무 관점에서 설명하면, 비즈니스 지표의 대부분이 누적 개념이기 때문입니다. 예를 들어, YTD(Year-To-Date) 매출, 누적 가입자 수, 재고 잔량(입고 누적 - 출고 누적), 은행 잔액(입금/출금의 누적) 등은 모두 누적 계산입니다.

또한 성장 추세를 보여주는 차트에서 누적 그래프는 가장 직관적인 시각화 방법입니다. 전통적인 방법과 비교해볼까요?

기존에는 이렇게 작성했습니다: "SELECT date, (SELECT SUM(amount) FROM table t2 WHERE t2.date <= t1.date) as cumulative FROM table t1". 상관 서브쿼리(Correlated Subquery)로 매 행마다 합계를 다시 계산하니 성능이 정말 나쁩니다.

SUM OVER를 사용하면 SUM(amount) OVER (ORDER BY date) 한 줄로 해결되고, 성능도 훨씬 빠릅니다. SUM OVER의 핵심 특징은 네 가지입니다.

첫째, ORDER BY가 있으면 자동으로 "첫 행부터 현재 행까지"가 윈도우 범위가 됩니다(Running Total). 둘째, PARTITION BY와 함께 사용하면 그룹별 누적 합계를 계산합니다.

셋째, ROWS BETWEEN으로 윈도우 범위를 명시적으로 지정할 수 있습니다(이동 평균 등). 넷째, COUNT, AVG 등 다른 집계 함수도 같은 방식으로 누적 계산 가능합니다.

이러한 특징들이 다양한 시계열 분석을 간단하게 만들어줍니다.

코드 예제

-- 일별 매출과 누적 매출 계산
SELECT
    sale_date,
    daily_revenue,
    -- 연초부터 현재까지 누적 매출 (Running Total)
    SUM(daily_revenue) OVER (ORDER BY sale_date) as cumulative_revenue,
    -- 최근 7일 이동 합계 (Moving Sum)
    SUM(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_7day_sum,
    -- 최근 7일 이동 평균 (Moving Average)
    ROUND(AVG(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) as moving_7day_avg,
    -- 누적 매출 목표 달성률 (%)
    ROUND(
        SUM(daily_revenue) OVER (ORDER BY sale_date) / 10000000 * 100,
        2
    ) as target_achievement_rate
FROM daily_sales
ORDER BY sale_date;

설명

이것이 하는 일: 이 쿼리는 일별 매출 데이터에서 단순 매출뿐만 아니라 누적 매출, 7일 이동 합계, 7일 이동 평균, 목표 달성률까지 한 번에 계산합니다. 복잡한 서브쿼리나 Self Join 없이 윈도우 함수만으로 모든 시계열 분석을 수행하는 실전 예시입니다.

첫 번째 핵심은 SUM(daily_revenue) OVER (ORDER BY sale_date)입니다. 이것이 바로 누적 합계(Running Total)를 계산하는 기본 패턴입니다.

ORDER BY sale_date로 날짜 순서대로 정렬한 상태에서, 각 행마다 "첫 날부터 오늘까지"의 매출 합계가 계산됩니다. 1월 1일에는 1일 매출만, 1월 2일에는 1일+2일 매출, 1월 3일에는 1일+2일+3일 매출이 표시되는 식입니다.

이렇게 하면 "연초부터 현재까지 총 얼마를 벌었는가"를 한눈에 볼 수 있습니다. 두 번째로 주목할 부분은 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW입니다.

이것은 윈도우 범위를 명시적으로 지정하는 방법으로, "현재 행 기준으로 이전 6개 행부터 현재 행까지"를 범위로 삼습니다. 즉, 총 7일치 데이터(오늘 포함)를 의미하죠.

이 범위 내에서 SUM을 계산하면 "최근 7일 매출 합계"가 되고, AVG를 계산하면 "최근 7일 평균 매출"이 됩니다. 이런 이동 평균(Moving Average)은 일시적인 변동을 제거하고 추세를 파악하는 데 정말 유용합니다.

세 번째로 이해해야 할 개념은 윈도우 범위의 기본 동작입니다. ORDER BY만 있고 ROWS BETWEEN이 없으면, 기본적으로 "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"가 적용됩니다.

이것이 "첫 행부터 현재 행까지" 전체를 범위로 하는 누적 합계를 만드는 원리입니다. 반면 ROWS BETWEEN으로 명시하면 정확히 N개 행만큼만 범위를 제한할 수 있습니다.

마지막으로 실전 활용 예시인 target_achievement_rate를 보면, 누적 매출을 목표액(1천만원)으로 나누어 달성률을 계산합니다. 이런 식으로 누적 합계와 다른 계산을 결합하면 "현재 진행률", "예상 달성 시점" 등 다양한 비즈니스 지표를 만들 수 있습니다.

여러분이 이 코드를 사용하면 재무 리포트나 대시보드 작성이 정말 간단해집니다. 일별 데이터만 있으면 누적, 이동 평균, 추세 분석을 모두 한 번의 쿼리로 처리할 수 있습니다.

프론트엔드에서 추가 계산이 필요 없고, 차트 라이브러리에 바로 연결할 수 있는 형태로 데이터가 나옵니다. 특히 주식 거래, 재고 관리, 회원 가입 추이 같은 시계열 데이터를 다룰 때 이 패턴은 필수입니다.

실전 팁

💡 이동 평균 기간 조정: ROWS BETWEEN N PRECEDING으로 N을 바꾸면 원하는 기간의 이동 평균 가능 (30일, 90일 등).

💡 월별 누적: PARTITION BY YEAR(date), MONTH(date)를 추가하면 매월 1일부터 누적되는 월별 누적 합계 계산 가능.

💡 ROWS vs RANGE: ROWS는 물리적 행 개수, RANGE는 값의 범위 기준. 날짜가 중복될 수 있으면 ROWS가 더 정확합니다.

💡 NULL 처리: SUM에서 NULL은 무시됩니다. 0으로 계산하고 싶다면 COALESCE(column, 0)으로 미리 변환하세요.

💡 성능 최적화: ORDER BY 컬럼(날짜 등)에 인덱스를 생성하면 정렬 비용이 줄어들어 대용량 데이터에서도 빠릅니다.

---