SQL 윈도우 함수와 CTE: 동점 순위·누적합·재귀 조직도 실습

1장. 같은 매출인데 왜 공동 1위가 사라졌을까#

팀 A의 판매 실적이 다음과 같다고 하겠습니다.

판매ID 날짜 금액
1 1월 100
2 2월 120
3 3월 120
4 4월 80

2월과 3월 매출은 둘 다 120입니다.

따라서 금액 기준으로는 공동 1위라고 볼 수 있습니다.

그런데 다음처럼 순번을 계산하면:

ROW_NUMBER() OVER (
    ORDER BY amount DESC, sale_id
)

결과는 다음과 같습니다.

120 → 1
120 → 2
100 → 3
80  → 4

공동 1위가 사라졌습니다.

SQL이 틀린 것은 아닙니다.

우리가 순번을 구한 것인지 공동 순위를 구한 것인지가 달랐던 것입니다.

윈도우 함수에서는 문법보다 먼저 다음 세 가지를 정해야 합니다.

누구와 비교할 것인가?

어떤 순서로 계산할 것인가?

현재 행에서 어디까지 계산할 것인가?

이 세 질문이 각각 PARTITION BY, ORDER BY, 프레임과 연결됩니다.

2장. 윈도우 함수는 행을 없애지 않고 계산 결과를 붙인다#

일반 집계 함수는 여러 행을 하나로 줄입니다.

다음 SQL을 보겠습니다.

SELECT
    team,
    AVG(amount)
FROM sale
GROUP BY team;

결과는 팀별 한 행입니다.

A | 105
B | 100

개별 판매행은 사라졌습니다.

반면 윈도우 함수를 사용하면:

SELECT
    sale_id,
    team,
    amount,
    AVG(amount) OVER (
        PARTITION BY team
    ) AS team_avg
FROM sale;

원래 행을 그대로 유지하면서 평균을 옆에 붙일 수 있습니다.

A | 100 | 105
A | 120 | 105
A | 120 | 105
A |  80 | 105

B |  90 | 100
B | 110 | 100

즉:

GROUP BY
→ 행을 묶고 줄인다

윈도우 함수
→ 행을 유지하고 계산값을 붙인다

라고 이해하면 쉽습니다.

3장. 실습 데이터 만들기#

다음 테이블을 사용하겠습니다.

CREATE TABLE sale (
    sale_id integer PRIMARY KEY,
    team varchar(10) NOT NULL,
    sale_day date NOT NULL,
    amount integer NOT NULL
);

데이터를 입력합니다.

INSERT INTO sale VALUES
    (1, 'A', DATE '2026-01-01', 100),
    (2, 'A', DATE '2026-02-01', 120),
    (3, 'A', DATE '2026-03-01', 120),
    (4, 'A', DATE '2026-04-01', 80),
    (5, 'B', DATE '2026-01-01', 90),
    (6, 'B', DATE '2026-02-01', 110);

데이터는 다음과 같습니다.

판매ID 팀 판매일 금액
1 A 2026-01-01 100
2 A 2026-02-01 120
3 A 2026-03-01 120
4 A 2026-04-01 80
5 B 2026-01-01 90
6 B 2026-02-01 110

4장. OVER가 윈도우 함수의 시작이다#

윈도우 함수의 기본 형태는 다음과 같습니다.

함수(...) OVER (...)

예를 들어:

AVG(amount) OVER (
    PARTITION BY team
)

은 각 행이 속한 팀의 평균을 계산합니다.

OVER 안에는 대표적으로 다음 요소가 들어갑니다.

PARTITION BY
ORDER BY
ROWS 또는 RANGE

역할을 나누면 다음과 같습니다.

요소 역할
PARTITION BY 계산 집단을 나눔
ORDER BY 집단 안 계산 순서를 정함
ROWS·RANGE 현재 행에서 계산할 범위를 정함

5장. PARTITION BY는 비교 집단을 나눈다#

팀별 평균을 계산해 보겠습니다.

SELECT
    sale_id,
    team,
    amount,
    AVG(amount) OVER (
        PARTITION BY team
    ) AS team_avg
FROM sale
ORDER BY sale_id;

팀 A의 합계는:

100 + 120 + 120 + 80
=
420

입니다.

행은 네 개이므로 평균은:

420 / 4
=
105

입니다.

팀 B는:

90 + 110
=
200

이므로 평균은 100입니다.

결과:

판매ID 팀 금액 팀 평균
1 A 100 105
2 A 120 105
3 A 120 105
4 A 80 105
5 B 90 100
6 B 110 100

6장. PARTITION BY가 없으면 전체가 하나의 집단이 된다#

다음처럼 작성하면:

AVG(amount) OVER ()

A와 B를 구분하지 않습니다.

전체 판매액 합계는:

420 + 200
=
620

행은 6개입니다.

전체 평균은 약:

103.33

입니다.

모든 행에 같은 전체 평균이 붙습니다.

따라서 PARTITION BY는 다음 질문에 답합니다.

이 계산에서 서로 비교할 사람이나 데이터의 범위는 어디까지인가?

7장. 윈도우 안의 ORDER BY는 계산 순서를 결정한다#

다음 두 질문은 서로 다릅니다.

팀에서 금액이 높은 순서는?

날짜가 흐르면서 누적 매출은?

첫 번째는 금액 순서가 필요합니다.

ORDER BY amount DESC

두 번째는 날짜 순서가 필요합니다.

ORDER BY sale_day

같은 데이터를 사용해도 계산 목적에 따라 윈도우 안의 ORDER BY가 달라집니다.

8장. 최종 ORDER BY와 윈도우 내부 ORDER BY는 서로 다르다#

다음 SQL을 보겠습니다.

SELECT
    sale_id,
    amount,
    RANK() OVER (
        ORDER BY amount DESC
    ) AS rank_no
FROM sale
ORDER BY sale_id;

윈도우 내부:

ORDER BY amount DESC

는 순위를 계산합니다.

질의 마지막:

ORDER BY sale_id

는 최종 화면 표시 순서를 정합니다.

따라서 다음 두 개는 서로 대체되지 않습니다.

계산 순서

출력 순서

9장. ROW_NUMBER는 모든 행에 서로 다른 번호를 준다#

팀 A의 금액 순서를 계산합니다.

SELECT
    sale_id,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY team
        ORDER BY amount DESC, sale_id
    ) AS row_no
FROM sale
WHERE team = 'A'
ORDER BY row_no;

결과:

판매ID 금액 ROW_NUMBER
2 120 1
3 120 2
1 100 3
4 80 4

두 행이 모두 120이지만 번호는 다릅니다.

ROW_NUMBER는 동점을 공동 순위로 처리하지 않습니다.

10장. 동점에서 안정적인 ROW_NUMBER를 만들려면 추가 기준이 필요하다#

다음처럼 금액만 정렬했다고 하겠습니다.

ROW_NUMBER() OVER (
    ORDER BY amount DESC
)

120인 두 행 가운데 어느 것이 먼저인지 업무 규칙이 없습니다.

DBMS가 항상 같은 순서를 보여준다고 기대해서는 안 됩니다.

따라서 순번이 반드시 안정적이어야 한다면 다음처럼 고유한 기준을 추가할 수 있습니다.

ORDER BY
    amount DESC,
    sale_id

하지만 이 추가 기준을 RANK에도 그대로 넣으면 동점의 의미가 바뀔 수 있습니다.

11장. RANK는 동점 뒤 순위를 건너뛴다#

SELECT
    sale_id,
    amount,
    RANK() OVER (
        PARTITION BY team
        ORDER BY amount DESC
    ) AS rank_no
FROM sale
WHERE team = 'A';

결과는 다음과 같습니다.

120 → 1
120 → 1
100 → 3
80  → 4

공동 1위가 두 명이므로 다음 순위는 3위입니다.

12장. DENSE_RANK는 동점 뒤 순위를 이어 간다#

같은 데이터에서:

DENSE_RANK() OVER (
    PARTITION BY team
    ORDER BY amount DESC
)

를 사용하면:

120 → 1
120 → 1
100 → 2
80  → 3

이 됩니다.

차이를 정리하면 다음과 같습니다.

금액 ROW_NUMBER RANK DENSE_RANK
120 1 1 1
120 2 1 1
100 3 3 2
80 4 4 3

13장. 순위 함수는 요구사항을 먼저 구분해야 한다#

다음 세 문장은 서로 다른 요구입니다.

정확히 네 명에게 1, 2, 3, 4 번호를 붙여라.

→ ROW_NUMBER

동점은 공동 순위로 하고 다음 순위는 건너뛰어라.

→ RANK

동점은 공동 순위로 하고 다음 순위는 이어서 붙여라.

→ DENSE_RANK

순위라고 모두 같은 함수가 아닙니다.

14장. RANK에 고유 ID를 추가하면 공동 순위가 사라질 수 있다#

다음처럼 작성했다고 하겠습니다.

RANK() OVER (
    ORDER BY amount DESC, sale_id
)

120 두 행은 amount는 같지만 sale_id가 다릅니다.

따라서 정렬값 전체 조합이 다릅니다.

결과적으로 공동 순위가 깨질 수 있습니다.

즉 다음 두 요구를 분리해야 합니다.

순위 계산 기준

최종 표시 순서

순위는 amount만으로 계산하고, 화면은 바깥 ORDER BY에서 sale_id를 추가할 수 있습니다.

15장. 누적합은 현재 행까지 얼마가 쌓였는지를 계산한다#

팀 A의 월별 누적 매출을 구해 보겠습니다.

SELECT
    sale_id,
    sale_day,
    amount,
    SUM(amount) OVER (
        PARTITION BY team
        ORDER BY sale_day, sale_id
        ROWS BETWEEN
            UNBOUNDED PRECEDING
            AND CURRENT ROW
    ) AS running_total
FROM sale
WHERE team = 'A'
ORDER BY sale_day, sale_id;

결과:

판매ID 금액 누적합
1 100 100
2 120 220
3 120 340
4 80 420

16장. ROWS는 실제 행 개수를 기준으로 범위를 잡는다#

다음 프레임을 보겠습니다.

ROWS BETWEEN
    1 PRECEDING
    AND CURRENT ROW

뜻은 다음과 같습니다.

현재 행과 바로 이전 한 행을 계산에 포함한다.

팀 A에서 평균을 계산하면:

SELECT
    sale_id,
    amount,
    AVG(amount) OVER (
        PARTITION BY team
        ORDER BY sale_day, sale_id
        ROWS BETWEEN
            1 PRECEDING
            AND CURRENT ROW
    ) AS moving_avg
FROM sale
WHERE team = 'A'
ORDER BY sale_day, sale_id;

결과는:

판매ID 금액 현재+이전 평균
1 100 100
2 120 110
3 120 120
4 80 100

입니다.

17장. 같은 날짜가 여러 건이면 날짜만으로 행 단위 누적을 정의하기 어렵다#

다음 판매가 있다고 하겠습니다.

9월 1일 | 100

9월 2일 | 50

9월 2일 | 30

9월 3일 | 20

날짜만 사용해:

ORDER BY sale_day

라고 하면 9월 2일의 두 행 가운데 어떤 행이 먼저인지 명확하지 않습니다.

행마다 잔액처럼 누적값을 표시해야 한다면 다음처럼 추가 기준이 필요합니다.

ORDER BY
    sale_day,
    sale_id

그러면 한 행씩 계산할 순서를 정할 수 있습니다.

18장. 거래별 누적과 일별 누적은 다른 질문이다#

앞의 데이터를 거래별로 누적하면:

100
150
180
200

이 될 수 있습니다.

하지만 일별 합계를 먼저 만들면:

9월 1일
100

9월 2일
80

9월 3일
20

이 됩니다.

그리고 날짜별 누적은:

100
180
200

입니다.

최종 합계는 같지만 결과 한 행의 의미가 다릅니다.

거래 한 건

하루 한 건

무엇을 원하는지 먼저 정해야 합니다.

19장. RANGE는 같은 정렬값을 가진 행을 함께 다룰 수 있다#

ROWS는 실제 행 위치를 기준으로 합니다.

반면 RANGE는 정렬값을 기준으로 동점 행을 함께 다룰 수 있습니다.

예를 들어 팀 A 금액을 오름차순으로 정렬합니다.

80
100
120
120

기본적인 RANGE 성격의 프레임에서는 120이라는 같은 정렬값을 가진 두 행이 peer로 함께 계산될 수 있습니다.

따라서 두 120 행의 누적합이 둘 다 420으로 나타날 수 있습니다.

20장. 기본 프레임에 기대지 않고 필요한 범위를 직접 쓰는 것이 안전하다#

다음처럼 쓰면:

SUM(amount) OVER (
    ORDER BY amount
)

DBMS의 기본 프레임 규칙이 적용됩니다.

동점 데이터가 있을 때 예상과 다른 결과가 나올 수 있습니다.

한 행씩 확실하게 누적하려면 다음처럼 명시하는 편이 이해하기 쉽습니다.

SUM(amount) OVER (
    ORDER BY amount, sale_id
    ROWS BETWEEN
        UNBOUNDED PRECEDING
        AND CURRENT ROW
)

21장. LAG는 이전 행의 값을 가져온다#

전월 대비 매출 변화를 계산하고 싶다고 하겠습니다.

SELECT
    sale_id,
    sale_day,
    amount,
    LAG(amount) OVER (
        PARTITION BY team
        ORDER BY sale_day, sale_id
    ) AS previous_amount
FROM sale
WHERE team = 'A'
ORDER BY sale_day, sale_id;

결과:

판매ID 현재 금액 이전 금액
1 100 NULL
2 120 100
3 120 120
4 80 120

첫 번째 행은 이전 행이 없으므로 NULL입니다.

22장. 전월 대비 증감액도 바로 계산할 수 있다#

SELECT
    sale_day,
    amount,
    amount
      - LAG(amount) OVER (
            PARTITION BY team
            ORDER BY sale_day, sale_id
        ) AS difference
FROM sale
WHERE team = 'A'
ORDER BY sale_day, sale_id;

결과:

1월 | 100 | NULL
2월 | 120 | +20
3월 | 120 | 0
4월 |  80 | -40

첫 번째 행의 비교 대상이 없다는 사실을 NULL이 표현합니다.

23장. 첫 행의 NULL을 0으로 바꾸면 의미가 바뀔 수 있다#

다음처럼 작성할 수 있습니다.

COALESCE(
    LAG(amount) OVER (...),
    0
)

하지만 이것은:

이전 판매가 존재하지 않음

을:

이전 판매액이 0

으로 바꿉니다.

표시 목적에는 쓸 수 있지만 두 의미가 같은지는 확인해야 합니다.

24장. LEAD는 다음 행의 값을 가져온다#

SELECT
    sale_id,
    amount,
    LEAD(amount) OVER (
        PARTITION BY team
        ORDER BY sale_day, sale_id
    ) AS next_amount
FROM sale
WHERE team = 'A'
ORDER BY sale_day, sale_id;

결과:

100 → 다음 120
120 → 다음 120
120 → 다음 80
80  → 다음 없음

마지막 행에는 다음 행이 없으므로 NULL입니다.

25장. LAST_VALUE는 이름만 보고 쓰면 틀리기 쉽다#

팀의 마지막 판매액을 모든 행에 붙이고 싶다고 하겠습니다.

다음처럼 작성할 수 있습니다.

LAST_VALUE(amount) OVER (
    PARTITION BY team
    ORDER BY sale_day
)

그런데 예상과 달리 각 행에서 현재 프레임의 마지막 값이 나올 수 있습니다.

즉 모든 행에서 팀의 최종값 80이 나오지 않을 수 있습니다.

26장. 전체 파티션의 마지막 값을 원하면 프레임 끝을 명시한다#

SELECT
    sale_id,
    amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY team
        ORDER BY sale_day, sale_id
        ROWS BETWEEN
            UNBOUNDED PRECEDING
            AND UNBOUNDED FOLLOWING
    ) AS last_team_amount
FROM sale
WHERE team = 'A'
ORDER BY sale_id;

모든 행의 결과는:

80

입니다.

전체 팀 범위 끝까지 프레임에 포함했기 때문입니다.

27장. FIRST_VALUE도 프레임과 정렬 기준을 함께 봐야 한다#

같은 프레임에서:

FIRST_VALUE(amount)

을 사용하면 팀 A의 첫 판매액 100을 모든 행에 붙일 수 있습니다.

중요한 것은 FIRST_VALUE, LAST_VALUE라는 함수 이름만 보는 것이 아닙니다.

어떤 순서에서 첫 값인가?

어떤 프레임 안에서 마지막 값인가?

를 확인해야 합니다.

28장. CTE는 긴 SQL을 의미 있는 단계로 나눈다#

CTE는 Common Table Expression의 약자입니다.

기본 형태는 다음과 같습니다.

WITH 이름 AS (
    SELECT ...
)
SELECT ...
FROM 이름;

예를 들어 팀별 합계를 먼저 계산합니다.

WITH team_total AS (
    SELECT
        team,
        SUM(amount) AS total
    FROM sale
    GROUP BY team
)
SELECT *
FROM team_total;

결과:

A | 420
B | 200

29장. CTE는 결과 단위를 명확하게 만드는 데 특히 유용하다#

팀별 비중을 계산한다고 하겠습니다.

먼저 팀별 합계를 만듭니다.

WITH team_total AS (
    SELECT
        team,
        SUM(amount) AS total
    FROM sale
    GROUP BY team
)

이 시점에서 결과 한 행은:

팀 하나

입니다.

그다음 전체 합계를 계산합니다.

grand_total AS (
    SELECT SUM(total) AS total
    FROM team_total
)

마지막으로 비율을 계산합니다.

WITH team_total AS (
    SELECT
        team,
        SUM(amount) AS total
    FROM sale
    GROUP BY team
),
grand_total AS (
    SELECT SUM(total) AS total
    FROM team_total
)
SELECT
    t.team,
    t.total,
    ROUND(
        t.total * 100.0 / g.total,
        1
    ) AS pct
FROM team_total AS t
CROSS JOIN grand_total AS g
ORDER BY t.team;

결과:

A | 420 | 67.7
B | 200 | 32.3

30장. CTE는 무조건 임시 테이블로 저장되는 것이 아니다#

다음처럼 생각하면 안 됩니다.

CTE
=
반드시 임시 결과를 디스크나 메모리에 저장

현대 DBMS는 CTE를 상위 질의와 합쳐 최적화하거나 별도로 계산할 수 있습니다.

제품과 질의 구조에 따라 달라집니다.

따라서 CTE는 우선 논리적인 질의 단계에 이름을 붙이는 표현으로 이해하는 것이 좋습니다.

성능은 실행 계획으로 확인합니다.

31장. 월별 매출 순위는 먼저 월별 합계를 만들어야 한다#

원천 데이터가 거래 단위라고 하겠습니다.

한 부서가 한 달 동안 다음처럼 거래했습니다.

20
30
50

원천 행에서 바로 순위를 매기면 20, 30, 50 각각이 순위 대상이 됩니다.

하지만 요구가:

월별 부서 매출 순위

라면 먼저 합계를 만들어야 합니다.

20 + 30 + 50
=
100

그 뒤 이 100이라는 월 합계에 순위를 매겨야 합니다.

32장. CTE로 집계와 순위 단계를 분리할 수 있다#

WITH monthly_sales AS (
    SELECT
        team,
        DATE_TRUNC('month', sale_day) AS month,
        SUM(amount) AS monthly_amount
    FROM sale
    GROUP BY
        team,
        DATE_TRUNC('month', sale_day)
)
SELECT
    team,
    month,
    monthly_amount,
    RANK() OVER (
        PARTITION BY month
        ORDER BY monthly_amount DESC
    ) AS rank_no
FROM monthly_sales;

중요한 것은 순위 계산 전에 한 행의 단위를 월별 팀 합계로 바꾸었다는 점입니다.

33장. 상위 두 등급과 최대 두 행은 다른 요구다#

A, B, C 팀의 월매출이 다음과 같다고 하겠습니다.

A = 100
B = 100
C = 80

RANK 결과는:

A → 1
B → 1
C → 3

DENSE_RANK는:

A → 1
B → 1
C → 2

따라서:

상위 두 매출 등급

과:

최대 두 팀

은 다릅니다.

동점 처리 방식을 먼저 정해야 합니다.

34장. 재귀 CTE는 자기 자신을 반복 참조하는 계층 조회에 사용한다#

조직도를 생각해 보겠습니다.

대표
├─ 개발팀장
│  └─ 개발자
└─ 운영팀장

테이블을 만듭니다.

CREATE TABLE org_employee (
    emp_id integer PRIMARY KEY,
    emp_name varchar(30) NOT NULL,
    manager_id integer
        REFERENCES org_employee(emp_id)
);

데이터:

INSERT INTO org_employee VALUES
    (1, '대표', NULL),
    (2, '개발팀장', 1),
    (3, '개발자', 2),
    (4, '운영팀장', 1);

35장. 조직 관계를 ERD로 보면 자기 참조 관계다#

erDiagram
    EMPLOYEE o|--o{ EMPLOYEE : manages

    EMPLOYEE {
        int emp_id PK
        string emp_name
        int manager_id FK
    }

한 직원의 manager_id가 같은 테이블의 다른 직원 emp_id를 가리킵니다.

재귀 CTE는 이런 계층을 반복적으로 따라가는 데 사용할 수 있습니다.

36장. 재귀 CTE는 앵커와 재귀 항으로 구성된다#

구조를 단순화하면 다음과 같습니다.

WITH RECURSIVE org AS (

    -- 앵커
    SELECT ...

    UNION ALL

    -- 재귀 항
    SELECT ...
    FROM ...
    JOIN org ...
)
SELECT ...
FROM org;

앵커는 시작점을 찾습니다.

재귀 항은 현재 결과를 이용해 다음 단계로 내려갑니다.

37장. 루트부터 조직도를 내려가 보자#

WITH RECURSIVE org (
    emp_id,
    emp_name,
    manager_id,
    depth,
    path
) AS (
    SELECT
        emp_id,
        emp_name,
        manager_id,
        1,
        emp_name::text
    FROM org_employee
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.emp_id,
        e.emp_name,
        e.manager_id,
        o.depth + 1,
        o.path || ' → ' || e.emp_name
    FROM org_employee AS e
    JOIN org AS o
        ON e.manager_id = o.emp_id
)
SELECT
    emp_id,
    depth,
    path
FROM org
ORDER BY path;

결과는 개념적으로 다음과 같습니다.

대표
대표 → 개발팀장
대표 → 개발팀장 → 개발자
대표 → 운영팀장

38장. depth는 조직도 깊이를 계산한다#

앵커에서:

depth = 1

로 시작합니다.

재귀 항에서:

o.depth + 1

을 사용합니다.

따라서 결과는:

직원 깊이
대표 1
개발팀장 2
운영팀장 2
개발자 3

처럼 계산됩니다.

39장. path를 함께 만들면 계층 경로를 확인할 수 있다#

다음 부분이 경로를 만듭니다.

o.path || ' → ' || e.emp_name

개발자는:

대표
↓
개발팀장
↓
개발자

이므로:

대표 → 개발팀장 → 개발자

라는 경로를 얻습니다.

재귀 쿼리 디버깅에서도 경로 정보는 매우 유용합니다.

40장. 조직 데이터에 순환이 생기면 재귀가 끝나지 않을 수 있다#

잘못된 데이터가 다음처럼 들어갔다고 하겠습니다.

직원 2의 관리자 → 3

직원 3의 관리자 → 2

그러면:

2 → 3 → 2 → 3 → ...

처럼 반복될 수 있습니다.

따라서 실제 계층 데이터에서는 순환 방지 전략이 필요합니다.

41장. 방문한 ID를 경로에 기록해 순환을 막을 수 있다#

개념적으로는 다음처럼 생각할 수 있습니다.

이미 방문한 직원ID 목록

다음 직원이 이미 목록에 있다면 재귀를 중단합니다.

PostgreSQL에서는 배열을 이용하거나 SQL 표준의 재귀 관련 기능을 활용할 수 있습니다.

중요한 것은 재귀 쿼리를 작성할 때 다음 질문입니다.

데이터가 항상 완벽한 트리라고 확신할 수 있는가?

확신할 수 없다면 순환을 고려해야 합니다.

42장. Oracle의 계층형 질의와 재귀 CTE는 개념적으로 연결된다#

Oracle에서는 오래전부터 CONNECT BY 계열의 계층 질의를 사용해 왔습니다.

예를 들어 개념적으로:

CONNECT BY PRIOR emp_id = manager_id

와 같은 부모·자식 관계를 표현할 수 있습니다.

재귀 CTE에서는:

e.manager_id = o.emp_id

처럼 부모와 자식을 직접 조인합니다.

문법은 다르지만 모두 계층 관계를 반복적으로 따라간다는 목적은 같습니다.

43장. PIVOT은 행을 열로 펼치는 문제다#

현재 판매 데이터는 다음처럼 세로 형태입니다.

A | 1월 | 100
A | 2월 | 120
B | 1월 | 90
B | 2월 | 110

보고서에서 다음처럼 보고 싶을 수 있습니다.

팀 1월 2월
A 100 120
B 90 110

이것이 피벗 형태입니다.

44장. PostgreSQL에서는 조건부 집계로 고정 월 피벗을 만들 수 있다#

SELECT
    team,
    SUM(amount)
        FILTER (
            WHERE sale_day = DATE '2026-01-01'
        ) AS jan,
    SUM(amount)
        FILTER (
            WHERE sale_day = DATE '2026-02-01'
        ) AS feb
FROM sale
GROUP BY team
ORDER BY team;

결과:

팀 1월 2월
A 100 120
B 90 110

45장. 피벗은 정규화와 같은 개념이 아니다#

행을 열로 바꾸었다고 데이터 모델 자체가 정규화되거나 비정규화된 것은 아닙니다.

피벗은 주로 표현 형태를 바꾸는 작업입니다.

행 중심 결과
↔
열 중심 보고서

데이터베이스 테이블의 정규형과는 별개의 문제입니다.

46장. UNPIVOT은 열을 다시 행으로 펼친다#

다음 결과가 있다고 하겠습니다.

A | 100 | 120

이를:

A | 1월 | 100
A | 2월 | 120

처럼 되돌리는 것이 UNPIVOT 계열의 작업입니다.

DBMS마다 지원 구문이 다릅니다.

PostgreSQL에서는 상황에 따라 VALUES, UNION ALL, LATERAL 등을 사용할 수 있습니다.

47장. VIEW는 쿼리에 이름을 붙여 재사용한다#

팀별 합계를 자주 조회한다고 하겠습니다.

CREATE VIEW team_sales AS
SELECT
    team,
    SUM(amount) AS total
FROM sale
GROUP BY team;

이제:

SELECT *
FROM team_sales;

로 사용할 수 있습니다.

현재 원본 데이터 기준 결과는:

A | 420
B | 200

입니다.

일반 뷰는 기본적으로 쿼리 정의를 이용해 원본 데이터를 조회하는 논리적 객체로 이해할 수 있습니다.

48장. 원본이 바뀌면 일반 뷰 결과도 바뀐다#

A팀에 매출 50을 추가한다고 하겠습니다.

INSERT INTO sale
VALUES (
    7,
    'A',
    DATE '2026-05-01',
    50
);

이제 일반 뷰를 조회하면:

A | 470
B | 200

이 될 수 있습니다.

원본 데이터가 변경됐기 때문입니다.

49장. 물질화 뷰는 결과 자체를 저장한다#

PostgreSQL에서는 다음처럼 물질화 뷰를 만들 수 있습니다.

CREATE MATERIALIZED VIEW team_sales_cached AS
SELECT
    team,
    SUM(amount) AS total
FROM sale
GROUP BY team;

이 객체는 계산 결과를 저장합니다.

따라서 원본에 새로운 매출이 추가되어도 자동으로 항상 최신이 되는 것은 아닙니다.

50장. 물질화 뷰는 새로 고침 전까지 과거 결과를 보여줄 수 있다#

물질화 뷰 생성 시 A팀이 420이었다고 하겠습니다.

그 뒤 원본에 50을 추가했습니다.

일반 뷰:

A = 470

물질화 뷰:

A = 420

일 수 있습니다.

새로 고침을 실행합니다.

REFRESH MATERIALIZED VIEW team_sales_cached;

그러면:

A = 470

으로 갱신됩니다.

51장. 물질화 뷰에서는 최신성이 설계 조건이 된다#

물질화 뷰는 읽기 성능을 높이는 대신 데이터 최신성 관리가 필요합니다.

다음 질문을 정해야 합니다.

매 요청마다 최신이어야 하는가?

5분 지연을 허용하는가?

하루 한 번 갱신해도 되는가?

갱신 중 조회는 어떻게 처리할 것인가?

따라서 단순히:

물질화 뷰
→ 빠르다

로 끝나는 개념이 아닙니다.

52장. 한 화면에 순위와 누적합을 같이 표시할 수도 있다#

판매 목록에서 다음 두 값을 함께 보여주고 싶다고 하겠습니다.

팀 내 판매액 순위

날짜순 누적 매출

두 계산은 서로 다른 ORDER BY를 사용합니다.

SELECT
    sale_id,
    team,
    sale_day,
    amount,

    RANK() OVER (
        PARTITION BY team
        ORDER BY amount DESC
    ) AS amount_rank,

    SUM(amount) OVER (
        PARTITION BY team
        ORDER BY sale_day, sale_id
        ROWS BETWEEN
            UNBOUNDED PRECEDING
            AND CURRENT ROW
    ) AS running_total

FROM sale
ORDER BY
    team,
    sale_day,
    sale_id;

같은 행에 숫자 두 개가 붙어 있지만 계산 원리는 서로 다릅니다.

53장. 윈도우 함수 하나마다 세 가지를 따로 확인해야 한다#

예를 들어:

SUM(amount) OVER (
    PARTITION BY team
    ORDER BY sale_day
    ROWS BETWEEN
        UNBOUNDED PRECEDING
        AND CURRENT ROW
)

을 보면 다음처럼 읽을 수 있습니다.

누구와?
→ 같은 팀

어떤 순서로?
→ 날짜순

어디까지?
→ 첫 행부터 현재 행까지

이 세 질문으로 읽으면 긴 윈도우 구문도 쉽게 해석할 수 있습니다.

54장. 결과가 틀렸다면 PARTITION·ORDER·FRAME 중 어디가 다른지 찾는다#

순위가 전체 회사 기준으로 나와버렸다면:

PARTITION BY 누락

일 수 있습니다.

전월 대비 값이 엉뚱하다면:

ORDER BY 기준 오류

일 수 있습니다.

누적합이 동점 행에서 예상보다 한꺼번에 증가했다면:

프레임 또는 peer 처리

를 확인해야 합니다.

윈도우 함수 오류를 한 덩어리로 보지 않고 세 요소로 나누면 원인을 찾기 쉽습니다.

55장. CTE는 행의 단위가 바뀌는 지점을 보여주는 도구이기도 하다#

다음 흐름을 생각해 보겠습니다.

flowchart TD
    A["원천 판매<br/>한 행 = 거래"] --> B["월별 집계 CTE<br/>한 행 = 팀·월"]
    B --> C["순위 계산<br/>한 행 = 팀·월 + 순위"]
    C --> D["최종 보고서"]

CTE를 쓰는 목적은 SQL을 단순히 여러 줄로 나누는 것이 아닙니다.

각 단계에서:

한 행이 무엇을 의미하는가?

를 명확하게 만드는 데 도움이 됩니다.

56장. 윈도우 함수와 GROUP BY를 함께 사용할 수 있다#

먼저 GROUP BY로 월별 매출을 만들고 그 결과에 윈도우 함수를 적용할 수 있습니다.

WITH monthly AS (
    SELECT
        team,
        DATE_TRUNC('month', sale_day) AS month,
        SUM(amount) AS amount
    FROM sale
    GROUP BY
        team,
        DATE_TRUNC('month', sale_day)
)
SELECT
    team,
    month,
    amount,
    SUM(amount) OVER (
        PARTITION BY team
        ORDER BY month
        ROWS BETWEEN
            UNBOUNDED PRECEDING
            AND CURRENT ROW
    ) AS cumulative_amount
FROM monthly;

즉 GROUP BY와 윈도우 함수는 경쟁 관계가 아닙니다.

서로 다른 단계에서 함께 사용할 수 있습니다.

57장. 가장 흔한 윈도우 함수 실수 정리#

증상 먼저 확인할 것
동점인데 순위가 다름 ROW_NUMBER 사용 여부 또는 정렬 열
RANK 뒤 순위가 건너뜀 RANK 고유 동작
누적합이 동점에서 한꺼번에 증가 RANGE·기본 프레임
첫 행 전월값이 NULL LAG에 이전 행이 없음
LAST_VALUE가 최종값이 아님 프레임 끝
팀별 순위인데 전체 순위가 나옴 PARTITION BY
화면 순서가 매번 달라짐 최종 ORDER BY
월 순위가 거래 단위로 계산됨 집계 전 순위를 매겼는지

58장. 재귀 CTE에서 가장 흔한 오류#

계층형 조회에서는 다음을 확인해야 합니다.

시작점이 정확한가?

부모와 자식 조인 방향이 맞는가?

depth 증가가 맞는가?

순환 데이터가 가능한가?

같은 노드를 여러 번 방문할 수 있는가?

조인 방향을 반대로 쓰면 상위로 올라가거나 아무 결과도 나오지 않을 수 있습니다.

59장. 윈도우 함수·CTE·뷰의 역할을 구분하면#

기능 주된 역할
윈도우 함수 원래 행을 유지하며 순위·누적·비교값 계산
일반 CTE 복잡한 SQL의 단계를 이름으로 분리
재귀 CTE 계층·그래프 형태 관계 반복 탐색
VIEW 쿼리 정의를 재사용
MATERIALIZED VIEW 쿼리 결과 자체를 저장해 재사용

모두 SQL 결과를 다루지만 해결하는 문제가 다릅니다.

60장. 핵심 정리#

윈도우 함수의 핵심은 원래 행을 유지하면서 다른 행과의 관계를 계산한다는 데 있습니다.

다음 세 요소를 반드시 구분해야 합니다.

PARTITION BY
→ 누구와 함께 계산할 것인가

ORDER BY
→ 어떤 순서로 계산할 것인가

ROWS·RANGE
→ 현재 행에서 어디까지 계산할 것인가

순위 함수도 목적에 따라 달라집니다.

ROW_NUMBER
→ 모든 행에 서로 다른 순번

RANK
→ 공동 순위 후 번호 건너뜀

DENSE_RANK
→ 공동 순위 후 번호 이어짐

누적합에서는 같은 정렬값을 가진 행이 있을 때 프레임이 중요합니다.

한 행씩 확실하게 누적하려면 동점을 해결할 정렬 열과 ROWS 프레임을 명시하는 것이 이해하기 쉽습니다.

LAG와 LEAD는 이전·다음 행과 비교할 때 유용하고, FIRST_VALUE와 LAST_VALUE는 프레임 범위를 반드시 함께 확인해야 합니다.

CTE는 복잡한 쿼리를 단순히 예쁘게 나누는 문법이 아닙니다.

각 단계에서 결과 한 행의 의미가 어떻게 바뀌는지를 드러내는 도구로 사용할 수 있습니다.

재귀 CTE는 조직도처럼 부모와 자식 관계를 반복적으로 따라가며 깊이와 경로를 계산할 수 있습니다. 이때 실제 데이터에 순환이 존재할 가능성까지 고려해야 합니다.

일반 뷰와 물질화 뷰도 구분해야 합니다.

일반 뷰
→ 원본을 기준으로 조회

물질화 뷰
→ 계산 결과를 저장

물질화 뷰는 조회를 빠르게 만들 수 있지만 갱신 전까지 이전 값을 보여줄 수 있으므로 최신성 요구를 함께 설계해야 합니다.

윈도우 함수가 복잡해 보일 때는 하나의 문장으로 바꾸면 됩니다.

누구와 비교하고, 어떤 순서로, 어디까지 계산하는가?

이 세 가지를 설명할 수 있다면 동점 순위, 이동평균, 누적합, 전월 비교처럼 복잡해 보이는 분석 SQL도 훨씬 쉽게 검증할 수 있습니다.

이 페이지의 목차