手順に分けるクエリと行をまたぐ計算：CTEとウィンドウ関数

CTE は長いクエリを名前付きの手順に分けます。ウィンドウ関数は行をまとめずに、順位や累計、前後の行の値を計算します。

WITH：名前の付いた導出テーブル

前のレッスンは FROM の中の導出テーブルで終わりました。同じクエリを共通テーブル式で書き直したものがこれです。

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 を定義し、それを使う。名前はこの1文の中だけで有効で、何も作られませんし、後片付けも要りません。

手順をいくつも並べる

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

名前の付いた手順が3つ。注文を絞り、顧客ごとにまとめ、顧客名を付ける。入れ子のサブクエリで書けば内側から読むことになりますが、こう書けば起きる順に並んだ手順の一覧です。クエリが追いにくくなってきたら、まず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) がウィンドウです。この計算がどの行を、どんな順序で見るかを決めます。同じ価格が並んだときの答えが、関数によって3通りに分かれます。

  • ROW_NUMBER() — 1, 2, 3, 4。必ず重複せず、同点は適当に決着します。
  • RANK() — 1, 2, 2, 4。同点は同じ順位で、次の数字が飛びます。
  • DENSE_RANK() — 1, 2, 2, 3。同点は同じ順位で、数字は飛びません。

どれを使うかは質問しだいです。「ちょうど10行ほしい」なら ROW_NUMBER、「2位は誰か」なら順位系のどちらかです。

グループごとの上位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が月ごとに1行を作り、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) はウィンドウの中で1行前に手を伸ばし、LEAD は1行先に伸ばします。本来ならテーブルをずらして自分自身と結合しないと書けない前月比が、1行で済みます。最初の行の previous_month は NULL です。前がないのだから当然で、増減も NULL になります。誤解を招くゼロより、こちらが正直な答えです。

対応状況

ウィンドウ関数は標準機能で、出会う可能性のあるエンジンにはほぼ揃っています。PostgreSQL、SQL Server、Oracle は何年も前から、SQLite は 3.25（2018年）から、MySQL は 8.0（2018年）からです。MariaDB は 10.2 から使えます。まず出会うもので使えないのは MySQL 5.7 くらいです。

ここまでで書けるもの

読み取りは、必要な形にほぼ何でも整えられるようになりました。残る2つのレッスンは仕事のもう半分、つまりデータを安全に変えることと、遅い理由を突き止めることです。

自分で書いてみる

  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 は同点を適当に決めるので、同点なら2人とも返したい場合は 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 ...)。