여러 단계로 나눈 쿼리와 행 사이의 계산: CTE와 윈도우 함수

CTE는 긴 쿼리를 이름 붙은 단계로 나누고, 윈도우 함수는 행을 줄이지 않은 채 순위·누적합·이전 행의 값을 계산합니다.

WITH: 이름이 있는 파생 테이블

지난 레슨에서 본 중첩된 파생 테이블을 공통 테이블 표현식(CTE)으로 바꾸면 다음과 같습니다.

1

WITH category_stats AS (
  SELECT cat.name AS category,
         COUNT(*) AS products,
         ROUND(AVG(p.price), 2) AS average
  FROM products p
  JOIN categories cat ON cat.id = p.category_id
  GROUP BY cat.id
)
SELECT *
FROM category_stats
WHERE products >= 10
ORDER BY average DESC;
Planche에서 실행

결과 — 26행 중 처음 5행

categoryproductsaverage
Wearables111672.29
Programming101585.67
Tablets171529.45
Audio111482.89
Kitchen121443.21

브라우저의 새 탭에서 Planche가 열리고, 샘플 데이터베이스가 로드된 채로 이 쿼리가 들어갑니다. 이 페이지의 다른 예제도 같은 탭을 다시 씁니다. F9를 누르기 전까지는 아무것도 실행되지 않습니다.

결과는 같지만 읽는 순서가 자연스러워집니다. 먼저 category_stats를 정의하고 다음에 사용합니다. 이 이름은 현재 쿼리 안에서만 유효하며, 별도의 테이블이 만들어지지는 않습니다.

단계를 여러 개 이어 붙이기

CTE는 여러 개를 이어 쓸 수 있고, 뒤의 CTE는 앞선 결과를 참조할 수 있습니다. 깊은 중첩 대신 단계가 드러나는 쿼리가 됩니다.

2

WITH paid_orders AS (
  SELECT id, customer_id, total
  FROM orders
  WHERE status IN ('paid', 'shipped', 'delivered')
),
per_customer AS (
  SELECT customer_id,
         COUNT(*) AS orders,
         ROUND(SUM(total), 2) AS revenue
  FROM paid_orders
  GROUP BY customer_id
)
SELECT c.first_name || ' ' || c.last_name AS customer,
       c.country,
       pc.orders,
       pc.revenue
FROM per_customer pc
JOIN customers c ON c.id = pc.customer_id
ORDER BY pc.revenue DESC
LIMIT 20;
Planche에서 실행

결과 — 20행 중 처음 5행

customercountryordersrevenue
Sofia PatelBR1070324.42
Zara NovakJP570149.53
Elena RossiKR1058867.23
Ines ChoiCA658455.76
Noah CostaES457721.52

이 쿼리는 주문을 고르고, 고객별로 요약하고, 고객 이름을 붙이는 세 단계로 나뉩니다. 중첩 서브쿼리와 달리 CTE는 처리 순서대로 읽을 수 있습니다.

윈도우 함수: 접지 않는 집계

GROUP BY는 여러 행을 한 행으로 요약합니다. 윈도우 함수는 여러 행을 기준으로 계산하지만 원래 행은 유지합니다.

3

SELECT p.name,
       p.price,
       RANK()       OVER (ORDER BY p.price DESC) AS price_rank,
       DENSE_RANK() OVER (ORDER BY p.price DESC) AS dense_price_rank,
       ROW_NUMBER() OVER (ORDER BY p.price DESC) AS row_no
FROM products p
ORDER BY p.price DESC
LIMIT 20;
Planche에서 실행

결과 — 20행 중 처음 5행

namepriceprice_rankdense_price_rankrow_no
Glide GPS Watch Pro2484.4111
Terra 3D Puzzle2481.77222
Aria Castle Kit2479.41333
Aria LED Strip Pro2463.41444
Nova Brief Answers2445.49555

OVER (ORDER BY p.price DESC)가 계산 대상과 순서를 지정합니다. 세 함수의 차이는 가격이 같은 행을 처리하는 방식에 있습니다.

  • ROW_NUMBER() — 1, 2, 3, 4. 항상 서로 다르고, 동점은 임의로 갈립니다.
  • RANK() — 1, 2, 2, 4. 동점은 순위를 나눠 갖고 다음 값이 건너뜁니다.
  • DENSE_RANK() — 1, 2, 2, 3. 동점은 순위를 나눠 갖되 건너뛰지 않습니다.

필요한 결과에 따라 함수를 고릅니다. 정확히 10개가 필요하면 ROW_NUMBER를, 동점자를 포함한 순위가 필요하면 순위 함수를 사용합니다.

그룹별 상위 N개

윈도우 함수가 없다면 같은 카테고리에서 자신보다 비싼 상품 수를 세는 상관 서브쿼리가 필요합니다. PARTITION BY는 카테고리마다 순번을 새로 매깁니다.

4

WITH ranked AS (
  SELECT cat.name AS category,
         p.name AS product,
         p.price,
         ROW_NUMBER() OVER (PARTITION BY cat.id ORDER BY p.price DESC) AS rank_in_category
  FROM products p
  JOIN categories cat ON cat.id = p.category_id
)
SELECT category, product, price
FROM ranked
WHERE rank_in_category <= 3
ORDER BY category, rank_in_category;
Planche에서 실행

결과 — 81행 중 처음 5행

categoryproductprice
AudioAtlas Earbuds2334.28
AudioKite Speaker2331.15
AudioQuill Speaker2177.84
BagsPulse Crossbody Mini2377
BagsZen Crossbody2295.35

누적합

윈도우에 ORDER BY를 지정하면 각 행은 현재 행까지의 앞선 행을 함께 계산합니다.

5

WITH monthly AS (
  SELECT substr(ordered_at, 1, 7) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE status IN ('paid', 'shipped', 'delivered')
    AND ordered_at >= '2025-01-01'
  GROUP BY month
)
SELECT month,
       revenue,
       ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total
FROM monthly
ORDER BY month;
Planche에서 실행

결과 — 12행 중 처음 5행

monthrevenuerunning_total
2025-01311707.73311707.73
2025-02350775.62662483.35
2025-03351184.961013668.31
2025-04374465.941388134.25
2025-05381541.091769675.34

CTE가 월마다 한 행을 만들고, SUM(revenue) OVER (ORDER BY month)가 그 목록을 따라 누적합니다. 누적 건수나 누적 비율도 같은 방식으로 구합니다. 어떤 행의 값이 그 앞 행들에 따라 정해지는 계산은 모두 이 형태입니다.

앞 행과 비교하기

6

WITH monthly AS (
  SELECT substr(ordered_at, 1, 7) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE status IN ('paid', 'shipped', 'delivered')
    AND ordered_at >= '2025-01-01'
  GROUP BY month
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS previous_month,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change
FROM monthly
ORDER BY month;
Planche에서 실행

결과 — 12행 중 처음 5행

monthrevenueprevious_monthchange
2025-01311707.73NULLNULL
2025-02350775.62311707.7339067.89
2025-03351184.96350775.62409.34
2025-04374465.94351184.9623280.98
2025-05381541.09374465.947075.15

LAG(revenue)는 이전 행의 값을, LEAD는 다음 행의 값을 가져옵니다. 이전에는 테이블을 자기 자신과 조인해야 했던 전월 대비 계산을 간단히 작성할 수 있습니다. 첫 행의 previous_month는 이전 행이 없으므로 NULL이며, 증감도 NULL이 됩니다.

지원 현황

윈도우 함수는 표준이고 만날 만한 곳에는 다 있습니다. PostgreSQL, SQL Server, Oracle은 오래전부터 있었고, SQLite는 3.25(2018년), MySQL은 8.0(2018년)부터입니다. MariaDB는 10.2부터입니다. 만날 가능성이 있는 버전 중 없는 건 MySQL 5.7 정도입니다.

여기까지로 할 수 있는 것

필요한 거의 모든 형태로 데이터를 읽을 수 있습니다. 남은 두 레슨에서는 데이터를 안전하게 바꾸는 법과, 쿼리가 느린 원인을 찾는 법을 다룹니다.

직접 해보기

  1. 국가마다 적립금이 가장 많은 고객 한 명은 누구인가요?

    정답 보기

    정답 보기

    WITH ranked AS (
  SELECT country,
         first_name || ' ' || last_name AS customer,
         loyalty_points,
         ROW_NUMBER() OVER (PARTITION BY country ORDER BY loyalty_points DESC) AS rn
  FROM customers
)
SELECT country, customer, loyalty_points
FROM ranked
WHERE rn = 1
ORDER BY loyalty_points DESC;
    Planche에서 실행

    결과 — 12행 중 처음 5행

    countrycustomerloyalty_points
    FRMinjun Meyer5000
    CAHugo Sato4994
    JPLina Smith4992
    KRLucas Ahmed4992
    AUSana Ahmed4988

    레슨에 나온 그룹별 상위 N과 같은 모양인데 <= 3 대신 rn = 1입니다. ROW_NUMBER는 동점을 임의로 가른다는 점에 유의하세요. 동점이면 둘 다 나와야 한다면 RANK를 쓰세요.

  2. 2025년 월별 주문 건수를, 누적 건수와 나란히 보여주세요.

    정답 보기

    정답 보기

    WITH monthly AS (
  SELECT substr(ordered_at, 1, 7) AS month, COUNT(*) AS orders
  FROM orders
  WHERE ordered_at >= '2025-01-01'
  GROUP BY month
)
SELECT month,
       orders,
       SUM(orders) OVER (ORDER BY month) AS running_orders
FROM monthly
ORDER BY month;
    Planche에서 실행

    결과 — 12행 중 처음 5행

    monthordersrunning_orders
    2025-017474
    2025-0272146
    2025-0384230
    2025-0470300
    2025-0581381

    월별 집계를 CTE로 만들고 그 위에 윈도우 함수를 씁니다. “누적” 컬럼은 전부 이 모양입니다. 먼저 집계하고, 그다음 SUM(...) OVER (ORDER BY ...).