SQLウィンドウ関数とCTE：実務の集計クエリで使う

Q: 累計が同じ日付の行で一気に跳ねるのはなぜですか？

ORDER BY だけでフレーム句がないと、既定のフレームは RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW で、ORDER BY の値が等しい行（ピア）をすべて含みます。そのため同じ日の行は全部その日の最終合計を表示します。一意な並び替えキーを足し、ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW を指定します。

Q: 再帰 CTE が終わらないのを防ぐには？

データに循環（自分の祖先になっているカテゴリ）があると UNION ALL の再帰は終わりません。例では 5 秒後もまだ動いていて kill しました。再帰側の WHERE に深さの上限を入れる、たどった経路を持って既出の id を飛ばす、試すときは LIMIT を付ける、のいずれかで防ぎます。

ROW_NUMBER・RANK・LAG・累計、グループごとの上位N件、再帰CTE、JSON関数を SQLite で実行。フレームと NULL の罠、相関サブクエリとの速度比較も 10 万行で実測。

t.laboratory 公開日 2026年11月12日

シリーズ：SQL 講座 6/6（上級）。 JOIN や GROUP BY は普通に書けるのに、順位付け・前月比・累計を出すときはデータを Excel に貼って計算している方向けです。読み終えると、それらを SQL 1 本で書け、再帰 CTE で木構造をたどり、JSON 列から値を取り出し、集計結果を「静かに間違える」3 つの罠を避けられるようになります。

集計クエリは SQL がいちばん役に立つ場所であり、同時に間違いがいちばん見えにくい場所でもあります。少しずれた累計や、「データなし」のはずが 0.0% と表示された前月比は、管理画面の上ではもっともらしく見えてしまいます。月次の売上報告、店舗別の日報、KPI のメール配信や分析用のエクスポートを作る方に向けた記事です。以下のクエリはすべて実行し、出力は表示されたまま貼っています。そのうち 1 つの罠は、出力がきれいすぎたおかげで気づいたものです。

実行環境： SQLite 3.51.0 の CLI（-header -column 表示）、計測には Python 3.9.6 の sqlite3 モジュール（同梱 SQLite も 3.51.0）、Apple Silicon Mac（M1 Pro）、macOS。

データと、1 分で分かるウィンドウ関数

3 店舗の 10 月の日別売上です。売上がない日は行そのものがなく、これが後で効いてきます。shop.sql として保存してください。

CREATE TABLE sales (
  id      INTEGER PRIMARY KEY,
  shop    TEXT    NOT NULL,
  day     TEXT    NOT NULL,      -- 'YYYY-MM-DD'
  amount  INTEGER NOT NULL
);
INSERT INTO sales (shop, day, amount) VALUES
 ('Shibuya','2026-10-01',120),('Shibuya','2026-10-02', 80),('Shibuya','2026-10-04',150),
 ('Shibuya','2026-10-05',150),('Shibuya','2026-10-06', 90),
 ('Umeda',  '2026-10-01',200),('Umeda',  '2026-10-02',200),('Umeda',  '2026-10-03', 60),
 ('Umeda',  '2026-10-05',110),
 ('Tenjin', '2026-10-02', 70),('Tenjin', '2026-10-03', 95);

ウィンドウ関数は、現在の行に関係する行の集まり（ウィンドウ）から値を計算しますが、GROUP BY と違って行を 1 つにまとめず全行を残します。PARTITION BY でグループに分け、ORDER BY でグループ内を並べます。3 つの順位関数は同点の扱いだけが違います。

SELECT shop, day, amount,
       ROW_NUMBER() OVER w AS row_number,
       RANK()       OVER w AS rank,
       DENSE_RANK() OVER w AS dense_rank
FROM sales
WINDOW w AS (PARTITION BY shop ORDER BY amount DESC)
ORDER BY shop, row_number;

shop     day         amount  row_number  rank  dense_rank
-------  ----------  ------  ----------  ----  ----------
Shibuya  2026-10-04  150     1           1     1
Shibuya  2026-10-05  150     2           1     1
Shibuya  2026-10-01  120     3           3     2
Shibuya  2026-10-06  90      4           4     3
Shibuya  2026-10-02  80      5           5     4
Tenjin   2026-10-03  95      1           1     1
Tenjin   2026-10-02  70      2           2     2
Umeda    2026-10-01  200     1           1     1
Umeda    2026-10-02  200     2           1     1
Umeda    2026-10-05  110     3           3     2
Umeda    2026-10-03  60      4           4     3

ROW_NUMBER は必ず 1, 2, 3。RANK は同点に同じ番号を付けて次を飛ばし（1, 1, 3）、DENSE_RANK は飛ばしません（1, 1, 2）。なお、2 つある 150 のどちらが 1 番になるかはこのクエリでは決まっていません。結果を固定したいときは , day のような並び替えキーを足します。

グループごとの上位 N 件

「店舗ごとの売上ベスト 2 日」は定番の問いです。素直に書くと失敗します。

sqlite> SELECT shop, day, amount FROM sales WHERE ROW_NUMBER() OVER (PARTITION BY shop ORDER BY amount DESC) <= 2;
Parse error near line 13: misuse of window function ROW_NUMBER()
  SELECT shop, day, amount FROM sales WHERE ROW_NUMBER() OVER (PARTITION BY shop
                              error here ---^

ウィンドウ関数は WHERE の後で計算されるので、WHERE の中では使えません。CTE（共通テーブル式。WITH で名前を付けたサブクエリ）の中で計算し、外側で絞り込みます。

WITH ranked AS (
  SELECT shop, day, amount,
         ROW_NUMBER() OVER (PARTITION BY shop ORDER BY amount DESC, day) AS rn,
         RANK()       OVER (PARTITION BY shop ORDER BY amount DESC)      AS rnk
  FROM sales
)
SELECT shop, day, amount, rn, rnk FROM ranked WHERE rn <= 2 ORDER BY shop, rn;

shop     day         amount  rn  rnk
-------  ----------  ------  --  ---
Shibuya  2026-10-04  150     1   1
Shibuya  2026-10-05  150     2   1
Tenjin   2026-10-03  95      1   1
Tenjin   2026-10-02  70      2   2
Umeda    2026-10-01  200     1   1
Umeda    2026-10-02  200     2   1

「ちょうど N 件」なら rn で、「N 件と、最後と同点の行も全部」なら rnk で絞ります。

よくある間違い：同じ日で跳ねる累計

間違い。 全社の日別累計を素直に SUM(amount) OVER (ORDER BY day) と書いた。

実際に起きること。 同じ日の行がすべて同じ数字になり、下の行の分まで含んでしまいます。

SELECT day, shop, amount,
       SUM(amount) OVER (ORDER BY day)                     AS default_frame,
       SUM(amount) OVER (ORDER BY day, id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM sales ORDER BY day, id;

day         shop     amount  default_frame  rows_frame
----------  -------  ------  -------------  ----------
2026-10-01  Shibuya  120     320            120
2026-10-01  Umeda    200     320            320
2026-10-02  Shibuya  80      670            400
2026-10-02  Umeda    200     670            600
2026-10-02  Tenjin   70      670            670
2026-10-03  Umeda    60      825            730
2026-10-03  Tenjin   95      825            825
2026-10-04  Shibuya  150     975            975
2026-10-05  Shibuya  150     1235           1125
2026-10-05  Umeda    110     1235           1235
2026-10-06  Shibuya  90      1325           1325

フレームとは、集計関数がウィンドウのうちどの範囲を見るかです。ORDER BY があってフレーム句がないとき、既定は RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW で、RANGE はピア（ORDER BY の値が等しい行）を全部含みます。直し方： 一意な並び替えキーと ROWS を付けます（rows_frame 列）。両方の列とも最後は 1,325 なので、最終合計のチェックではこの誤りに気づけません。間違っているのは途中の行だけです。

LAG と抜けている日、再帰 CTE でカレンダーを作る

LAG(x) はウィンドウ内の 1 つ前の行の x を返します（LEAD は 1 つ後）。渋谷店の前日比です。

shop     day         amount  prev_amount  prev_day    diff
-------  ----------  ------  -----------  ----------  ----
Shibuya  2026-10-01  120
Shibuya  2026-10-02  80      120          2026-10-01  -40
Shibuya  2026-10-04  150     80           2026-10-02  70
Shibuya  2026-10-05  150     150          2026-10-04  0
Shibuya  2026-10-06  90      150          2026-10-05  -60

10-04 の行は 10-02 と比べています。「1 つ前の行」は「前日」ではありません。直すには、再帰 CTE（自分自身を参照する CTE）で全日付を作ります。開始行、UNION ALL、そして前回の結果に対して WHERE が止めるまで繰り返されるステップから成ります。

WITH RECURSIVE cal(day) AS (
  SELECT '2026-10-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM cal WHERE day < '2026-10-06'
),
daily AS (
  SELECT cal.day, COALESCE(s.amount, 0) AS amount
  FROM cal LEFT JOIN sales s ON s.day = cal.day AND s.shop = 'Shibuya'
)
SELECT day, amount,
       LAG(amount) OVER (ORDER BY day) AS prev_day_amount,
       ROUND(AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_3d
FROM daily;

day         amount  prev_day_amount  avg_3d
----------  ------  ---------------  ------
2026-10-01  120                      120.0
2026-10-02  80      120              100.0
2026-10-03  0       80               66.7
2026-10-04  150     0                76.7
2026-10-05  150     150              100.0
2026-10-06  90      150              130.0

10-03 が 0 として現れ、3 日移動平均が本当の 3 日平均になりました（最初の 2 行は少ない日数の平均です。報告に出すかどうかは決めておきましょう）。

木構造の再帰 CTE と、終わらないクエリ

parent_id で持つカテゴリの木も、日常的な使いどころです。

CREATE TABLE categories (id INTEGER PRIMARY KEY, parent_id INTEGER, name TEXT NOT NULL);
INSERT INTO categories VALUES
 (1, NULL, 'Electronics'), (2, 1, 'Computers'), (3, 2, 'Laptops'), (4, 2, 'Desktops'),
 (5, 1, 'Audio'), (6, 5, 'Headphones'), (7, NULL, 'Books'), (8, 7, 'Programming');
WITH RECURSIVE tree(id, name, depth, path) AS (
  SELECT id, name, 0, name FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.depth + 1, t.path || ' > ' || c.name
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT substr('        ', 1, depth * 2) || name AS indented, depth, path FROM tree ORDER BY path;

indented        depth  path
--------------  -----  ----------------------------------
Books           0      Books
  Programming   1      Books > Programming
Electronics     0      Electronics
  Audio         1      Electronics > Audio
    Headphones  2      Electronics > Audio > Headphones
  Computers     1      Electronics > Computers
    Desktops    2      Electronics > Computers > Desktops
    Laptops     2      Electronics > Computers > Laptops

そこへ誰かが管理画面で「Electronics」の親を誤って「Laptops」にした（UPDATE categories SET parent_id = 3 WHERE id = 1）とします。1 → 2 → 3 → 1 の循環ができ、Computers から始める同じ再帰は終わりません。5 秒動かしてから kill しました。

  PID STAT      TIME    RSS COMMAND
37366 RN     0:04.95   2896 sqlite3 :memory:
exit=143

CPU 1 コアを使い切る一方でメモリはほとんど使わないので、「応答が返ってこない」以外に何の警告もありません。終了コード 143 は 128 + 15（SIGTERM）です。3 つの防ぎ方を実行しました。

-- 1. LIMIT outside (for testing): returned 100000 rows, max depth 50000, in 0.040 s
-- 2. depth limit in the recursive step: UNION + WHERE t.depth < 20  -> 40 rows, max depth 20
-- 3. remember the path, skip ids already on it:
WITH RECURSIVE tree(id, path) AS (
  SELECT 2, '/2/'
  UNION ALL
  SELECT c.id, t.path || c.id || '/' FROM categories c JOIN tree t ON c.parent_id = t.id
  WHERE instr(t.path, '/' || c.id || '/') = 0
)
SELECT id, path FROM tree;
id  path
--  -----------
2   /2/
3   /2/3/
4   /2/4/
1   /2/3/1/
5   /2/3/1/5/
6   /2/3/1/5/6/

本番に残すなら 3 番です。各ノードを 1 回ずつ返し、循環が経路に見えるようになります。

集計で使う JSON 列

API から受け取った注文を JSON のまま保存していることはよくあります。SQLite はそのまま読めます。-> は JSON を、->> は SQL の値を返し、json_each は配列を行に展開します。

CREATE TABLE orders (id INTEGER PRIMARY KEY, shop TEXT, payload TEXT);
INSERT INTO orders VALUES
 (1, 'Shibuya', '{"customer":"c-01","items":[{"sku":"A1","qty":2,"price":300},{"sku":"B7","qty":1,"price":1200}],"coupon":null}'),
 (2, 'Umeda',   '{"customer":"c-02","items":[{"sku":"A1","qty":1,"price":300}],"coupon":"OCT10"}'),
 (3, 'Shibuya', '{"customer":"c-01","items":[{"sku":"C3","qty":3,"price":450}]}');
SELECT id, payload ->> '$.customer' AS customer, payload -> '$.coupon' AS coupon_json,
       payload ->> '$.coupon' AS coupon_sql, json_array_length(payload, '$.items') AS n_items
FROM orders;
SELECT i.value ->> 'sku' AS sku, SUM((i.value ->> 'qty') * (i.value ->> 'price')) AS revenue,
       json_group_array(DISTINCT o.shop) AS shops
FROM orders o, json_each(o.payload, '$.items') AS i GROUP BY sku ORDER BY revenue DESC;

id  customer  coupon_json  coupon_sql  n_items
--  --------  -----------  ----------  -------
1   c-01      null                     2
2   c-02      "OCT10"      OCT10       1
3   c-01                               1
sku  revenue  shops
---  -------  -------------------
C3   1350     ["Shibuya"]
B7   1200     ["Shibuya"]
A1   900      ["Shibuya","Umeda"]

1 行目と 3 行目の違いは細かいけれど重要です。JSON の null は -> では文字列 null、->> では SQL の NULL になり、キーが存在しない場合はどちらも SQL の NULL です。

見ることになるエラーメッセージ：

sqlite> SELECT json_extract('{"a": 1,', '$.a');
Runtime error near line 2: malformed JSON
sqlite> SELECT json_extract('{"a": 1}', 'a');
Runtime error near line 3: bad JSON path: 'a'

そしてエラーにならないもの：{"customer": "c-03", items: []}（キーに引用符がない）は正しい JSON ではありませんが、json_extract(payload, '$.customer') は c-03 を返しました。SQLite は JSON5 も読めるからです。その行の json_valid(payload) は 0 でした。API 仕様が「正しい JSON」を求めるなら、取り出せたかどうかではなく json_valid で確かめます。

100 万行の月次レポートと、0.0% に化けた NULL

第 4 回の 100 万行の orders テーブルを使い、月別の支払済み売上・前月比・年初からの累計・年間シェアを 1 本のクエリで出します。ウィンドウ関数は GROUP BY の後に計算されるので、集計結果に対して使えます。

WITH monthly AS (
  SELECT substr(created_at, 1, 7) AS month, count(*) AS orders, sum(amount) AS revenue
  FROM orders WHERE status = 'paid' GROUP BY month
)
SELECT month, orders, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month)                          AS diff,
       printf('%.1f%%', 100.0 * revenue / LAG(revenue) OVER (ORDER BY month) - 100) AS mom,
       sum(revenue) OVER (ORDER BY month)                                    AS ytd,
       printf('%.1f%%', 100.0 * revenue / sum(revenue) OVER ())              AS share
FROM monthly ORDER BY month;

month    orders  revenue    diff       mom     ytd         share
-------  ------  ---------  ---------  ------  ----------  -----
2025-01  73160   737992278             0.0%    737992278   8.6%
2025-02  65317   659562716  -78429562  -10.6%  1397554994  7.7%
2025-03  72047   728849528  69286812   10.5%   2126404522  8.5%
…
2025-12  72379   730238892  30203404   4.3%    8584287069  8.5%
Run Time: real 0.422 user 0.346108 sys 0.058657

1 月の mom を見てください。0.0% です。前月がないので diff は正しく空なのに、printf が NULL を 0 にしています。読む人は「1 月は横ばい」と受け取るでしょう。printf('%.1f%%', NULL) は 0.0% を返し、round(NULL, 1) || '%' は NULL のままです。数値の整形はアプリ側で行うか、CASE WHEN … IS NULL で NULL を見える形に残します。

実測：ウィンドウ関数と相関サブクエリ

ウィンドウ関数（SQLite 3.25）以前は、グループごとの上位 N 件を相関サブクエリで書いていました。行ごとに「同じグループで自分より上の行が何件あるか」を数える方法です。第 4 回のテーブルの最初の顧客 5,000 人分（100,212 行）で両方を比べ、結果集合が一致することも確認しました。5 回の中央値で、インデックスなしの相関サブクエリだけは 1 回のみです。

クエリ	インデックスなし	インデックスあり
顧客ごとのトップ 3、`ROW_NUMBER`	79.9 ms	48.5 ms
顧客ごとのトップ 3、相関 `count(*)`	350,554 ms（5.8 分）	125.6 ms
顧客ごとの累計、`SUM() OVER`	127.5 ms	87.6 ms
顧客ごとの累計、相関 `SUM`	実行していない	253.8 ms

インデックスはトップ N 用に (customer_id, amount DESC, id)、累計用に (customer_id, created_at, id, amount) です。

インデックスありのクエリ時間の棒グラフ。ROW_NUMBER による顧客ごとのトップ 3：48.5ms。相関 count サブクエリによるトップ 3：125.6ms。SUM OVER による顧客ごとの累計：87.6ms。相関 SUM サブクエリによる累計：253.8ms。 — 同じ結果でもコストは違う：100,212 行（顧客 5,000 人）でのウィンドウ関数と相関サブクエリ。どちらもインデックスあり、5 回の中央値。インデックスなしの相関サブクエリ版トップ 3 は 350,554ms。SQLite 3.51.0、Apple M1 Pro。

ウィンドウ関数はデータを 1 回読んでソートするだけですが、相関サブクエリは行ごとに内側のクエリを 1 回実行します。インデックスがあれば内側は短い SEARCH なので差は 2.6 倍と 2.9 倍。インデックスがないと内側が毎回 100,212 行の全件スキャンになり、約 100 億行の読み取りで差は 4,000 倍を超えました。ウィンドウ関数版はインデックスすら必要としませんでした。

どの問いにどの道具を使うか

判断フロー。グループごとに 1 行でよければ GROUP BY。全行を残すなら、行が階層構造かを確認し、そうなら循環対策付きの再帰 CTE。そうでなければ、同点を同順位にするかを確認し、するなら RANK か DENSE_RANK、グループごとにちょうど N 行なら CTE 内の ROW_NUMBER を外側で絞り込む。累計は ROWS フレームと一意な並び替えキーを付けた SUM OVER を使う。 — 集計の問いに合う道具の選び方。表計算ソフトに頼りがちな問いの多くは、ウィンドウ関数か再帰 CTE で終わる。

問い	道具
グループごとに 1 行（店舗別合計）	`GROUP BY`
全行と、グループの値（店舗合計に対する割合）	`SUM(x) OVER (PARTITION BY g)`
同点は同順位の順位付け	`RANK` / `DENSE_RANK`
グループごとにちょうど N 件	CTE 内の `ROW_NUMBER`、外側で絞り込み
前の行との差	`LAG` / `LEAD`
抜けた日がある場合の前日比	再帰 CTE のカレンダー + `LEFT JOIN`、その後 `LAG`
累計	`SUM() OVER (ORDER BY … 一意なキー ROWS …)`
条件付き件数を 1 回で	`count(*) FILTER (WHERE …)`
階層（カテゴリ、組織図、コメント）	経路か深さで循環対策をした再帰 CTE
JSON の中の値	`->>`、`json_each`、`json_group_array`

FILTER も実行しておきました。count(*) FILTER (WHERE amount >= 100) は渋谷・天神・梅田でそれぞれ 3・0・3 日を返しました。

練習問題

店舗ごとに、記録のある前日と比べて最も売上が伸びた日を求めてください。
再帰 CTE で 2026 年の毎月 1 日を生成してください。「月末」を作るつもりで '2026-01-31' から '+1 month' を足していってはいけないのはなぜですか？

解答

LAG で差を出し、ROW_NUMBER で店舗ごとの 1 位を取ります。

WITH d AS (
  SELECT shop, day, amount - LAG(amount) OVER (PARTITION BY shop ORDER BY day) AS diff
  FROM sales
), r AS (
  SELECT shop, day, diff, ROW_NUMBER() OVER (PARTITION BY shop ORDER BY diff DESC) AS rn
  FROM d WHERE diff IS NOT NULL
)
SELECT shop, day, diff FROM r WHERE rn = 1 ORDER BY shop;

shop     day         diff
-------  ----------  ----
Shibuya  2026-10-04  70
Tenjin   2026-10-03  25
Umeda    2026-10-05  50

diff IS NOT NULL は大事です。SQLite では NULL は昇順で先頭、降順で末尾に並ぶので、ここでは 1 位になりませんが、昇順で「最も落ちた日」を探すクエリでは NULL が 1 位になってしまいます。

WITH RECURSIVE m(d) AS (SELECT '2026-01-01' UNION ALL SELECT date(d, '+1 month') FROM m WHERE d < '2026-12-01') SELECT count(*), min(d), max(d) FROM m; は 12|2026-01-01|2026-12-01 を返しました。31 日から始めると壊れます。date('2026-01-31', '+1 month') は 2026-03-03 を返しました。2 月 31 日があふれるためです。月末は date(d, 'start of month', '+1 month', '-1 day') で作ります。

正直な限界とバージョンの注意

実行したのは SQLite 3.51.0 だけです。ウィンドウ関数は SQLite 3.25.0、GROUPS・EXCLUDE などのフレーム指定は 3.28.0、通常の集計関数の FILTER は 3.30.0、再帰 CTE は 3.8.3、->・->> 演算子は 3.38.0、JSON5 の読み込みは 3.42.0 からです。古い Android や古いサーバーの SQLite では、この記事の一部が動きません。
インデックスなしの相関サブクエリは 1 回だけ（5.8 分）実行しました。5 回の中央値のためにさらに 30 分かける価値はないと判断しました。表の「実行していない」は一度も実行していないという意味です。
PostgreSQL と MySQL 8 も同じウィンドウ関数と再帰 CTE を持ちますが、MySQL は cte_max_recursion_depth（既定 1000）で再帰の深さを制限し、PostgreSQL は意味の異なる独自の JSON 演算子を使います（ここでは実行していません）。
売上テーブルはわざと小さくしています。計測表は金額が繰り返される合成データなので、データの形が違えば比率も変わります。

SQL 講座はこれで最後です。次はプログラミング学習ロードマップで SQL と並行して学ぶものを確認するか、PDO + SQLite と SQL インジェクションでこうしたクエリを Web ページの裏で安全に使う方法を見てください。

前回： 第 5 回：トランザクション・分離レベル・ロック · 最初に戻る： 第 1 回：SELECT・WHERE・ORDER BY

よくある質問

グループごとの上位 N 件を SQL で取るには？

CTE の中で ROW_NUMBER() OVER (PARTITION BY グループ ORDER BY 値 DESC) で番号を振り、外側のクエリで rn <= N に絞ります。WHERE で直接ウィンドウ関数を使うと SQLite は「misuse of window function ROW_NUMBER()」を返します。100,212 行ではウィンドウ関数版が 48.5ms、相関サブクエリはインデックスありで 125.6ms、なしで 350.6 秒でした。

累計が同じ日付の行で一気に跳ねるのはなぜですか？