応用SQLと索引｜ウィンドウ関数・再帰問合せ・実行効率を読む

部門別の売上合計を一行にまとめる処理と、各売上行に累計を付ける処理は違います。階層をたどる問合せも、直属の子だけを結合する問合せでは足りません。応用SQLでは「何行を残し、どの範囲を計算し、どう終了するか」を先に決めます。索引はその結果を変えず、アクセスの費用を減らすために検討します。

この記事で理解すること

• GROUP BYと窓関数を区別し、区分・順序・フレームから結果を求める。

• 順位の同値処理と上位行の抽出、再帰問合せの開始点と終了条件を説明する。

• 複合索引の列順、検索対象の割合、更新の負担を踏まえて実行効率を判断する。

以下のSQLはSQLiteで実行でき、PostgreSQLでも共通する基本構文を使います。日付は比較可能なYYYY-MM-DD形式の文字列、金額は整数で表します。主キー・外部キーの内部IDは整数です。実際のDBMSで日時型や採番方法を選ぶ場合は、その仕様を確認します。

一つの売上表で、残す行を決める

架空の販売部門の表salesです。A部門には同日2件の売上があり、同じ日付だけで並べても順番は一意になりません。これは累計の意味と、同額時の順位の意味を分けて考えるための小さなデータです。

CREATE TABLE sales (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  dept TEXT NOT NULL,
  sold_on TEXT NOT NULL,
  amount INTEGER NOT NULL CHECK(amount >= 0)
);

GROUP BY deptでSUM(amount)を求めるなら結果はAの350、Bの80という2行です。SUM(amount) OVER (PARTITION BY dept)なら元の4行を残し、Aの各行に350、Bの行に80を付けます。行の粒度を変えるか、行へ計算値を添えるかが違います。

PARTITION・ORDER BY・フレームを分ける

PARTITION BYは計算を分ける区分、窓内のORDER BYは計算上の順序、フレームは各行から見た計算対象です。全体の表示順を決める最後のORDER BYとは別です。窓内を並べただけで、最終出力の順序が保証されるわけではありません。

SELECT id, dept, sold_on, amount,
       SUM(amount) OVER (
         PARTITION BY dept
         ORDER BY sold_on, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales
ORDER BY dept, sold_on, id;

ROWSの「先頭から現在行まで」を使い、sold_onとidの組合せで一意に並べます。これにより、Aの同日2行にも100、300と一行ずつ累計が付きます。Bは別の区分なので累計が80から始まります。

ここでORDER BY sold_onだけを書き、フレームを省くと、SQLiteやPostgreSQLの既定では現在行と同じ並び値の行も含む範囲になります。4月1日のAの2行はどちらも300になります。同じ日をまとめた累計か、一行ずつの累計かを要件から選びます。

移動窓：直前の行か、暦日の範囲か

ROWS BETWEEN 1 PRECEDING AND CURRENT ROWは、同じ区分の現在行と直前の一行を計算対象にします。Aのid1は先行行がないので100、id2は100＋200で300、id3は200＋50で250です。区分の先頭で架空の0行を追加する必要はありません。

SELECT id, SUM(amount) OVER (
  PARTITION BY dept ORDER BY sold_on, id
  ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS last_two_rows
FROM sales ORDER BY id;

直前の一行は「前日」とは限りません。一日に複数行がある、売上のない日が欠けるなどの場合、行数の窓と暦日の窓は違います。日別に集約し欠日を補うか、DBMSの日付範囲のフレーム等を使うか、要件に合わせて設計します。

順位と上位抽出：同額を何位にするか

ROW_NUMBERは一行ずつ連番、RANKは同額を同順位にして後の順位に欠番を作り、DENSE_RANKは同順位の後に欠番を作りません。金額が200、100、100、50ならRANKは1、2、2、4、DENSE_RANKは1、2、2、3です。

SELECT id, amount,
       RANK() OVER (ORDER BY amount DESC) AS r,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS dr,
       ROW_NUMBER() OVER (ORDER BY amount DESC, id) AS rn
FROM sales
ORDER BY amount DESC, id;

順位の窓にidまで入れると、金額が同じでも並び値の組合せが違うため、RANKで同順位になりません。金額による順位はamountだけで決め、同額を一行に絞る連番ではidを補助キーにします。意味に合うORDER BYを関数ごとに設定します。

WITH ranked AS (
  SELECT id, dept, amount,
         ROW_NUMBER() OVER (
           PARTITION BY dept ORDER BY amount DESC, id
         ) AS rn
  FROM sales
)
SELECT id, dept, amount FROM ranked
WHERE rn = 1
ORDER BY dept;

部門ごとに金額の大きい一行を選ぶ例です。Aはid2の200、Bはid4の80です。窓関数の値を同じSELECTのWHEREで直接絞らず、CTEや副問合せの外側で絞ります。同額の首位を全員残す要件ならRANK等を用い、返る件数が部門ごとに一行とは限らないことを確認します。

再帰問合せ：開始点から次の階層を広げる

departmentはid1「本部」を親なしとし、id2「営業」とid3「開発」の親が1、id4「第一営業」の親が2という木です。parent_idは同じ表のidを参照します。直属の子を求める結合を、見つかった行から繰り返すのが再帰問合せです。

WITH RECURSIVE tree(id, parent_id, name, depth) AS (
  SELECT id, parent_id, name, 0
  FROM department WHERE id = 1
  UNION ALL
  SELECT d.id, d.parent_id, d.name, t.depth + 1
  FROM department d JOIN tree t ON d.parent_id = t.id
)
SELECT id, name, depth FROM tree
ORDER BY depth, id;

非再帰部分でid1を選び、再帰部分で前回見つかった行を親とする子を加えます。結果は(1,本部,0)、(2,営業,1)、(3,開発,1)、(4,第一営業,2)です。起点が存在しなければ0行です。実行内部の探索順と、最終表示の順序を混同しません。

外部キーは親の存在を守れても、階層の循環まで自動で禁止しません。このSQLは循環のない木を前提にします。一般のグラフでは訪問済みIDの記録、DBMSのCYCLE機能、深さ制限等を検討します。depthを増やす場合、UNIONへ変えるだけでは行全体が異なるので循環を止められないことがあります。

複合索引：列順と条件の形を見る

B-tree系の複合索引(dept, sold_on)は、まず部門、その中を日付の順に整理した索引です。部門の等価条件と日付範囲を組み合わせる検索では、候補を連続した範囲へ絞りやすくなります。WHEREの文字の記載順ではなく、索引の列順と条件の意味が関係します。

CREATE INDEX idx_sales_dept_day ON sales(dept, sold_on);
SELECT id, amount FROM sales
WHERE dept = 'A'
  AND sold_on >= '2026-04-01'
  AND sold_on < '2026-05-01';

先頭列に条件がない場合を「絶対に索引が使えない」と断定しません。DBMSによってスキップスキャンなどの選択肢があります。索引があれば常に高速ともいえません。対象行の割合、表の幅、並び替え、統計情報、キャッシュ等を合わせて評価します。

実行計画と更新負担を合わせて判断する

EXPLAINやSQLiteのEXPLAIN QUERY PLANで、どの表を走査し、どの索引を使うかを調べます。計画の推定費用はミリ秒の実測値ではありません。PostgreSQLのEXPLAIN ANALYZEは実際にSQLを実行するため、変更するSQLなら実データも変更し得ます。

LIKEの前方一致は、照合順序やDBMS等の条件が合えば通常のB-tree索引で範囲を絞れる場合があります。先頭に%を付ける部分一致は、同じようには先頭の範囲を決められません。全文・トライグラム等の索引は別方式なので、検索の意味と対応する索引を確認します。

索引の追加には容量とINSERT・UPDATE・DELETEでの保守が必要です。検証は小さな4行だけで速度を断定せず、想定件数・条件・更新比率で行います。件数を減らす条件、必要列の限定、結合や並び替え、適切な索引の順に目的と結果を確認し、返る行が同じことを守ります。

演習1：部門合計を各行へ

条件：Aに3行、Bに1行ある売上表にSUM(amount) OVER (PARTITION BY dept)を付ける。

問い：結果は何行で、Aの合計列は何になるか。

解答例：4行を残し、Aの各行に350を付ける。

根拠と誤答の確認：GROUP BYの2行へまとめる結果と区別します。

演習2：同日の累計

条件：Aの4月1日に100と200があり、ORDER BY sold_onだけで既定のフレームを使う。

問い：同日の2行の累計は何になるか。

解答例：どちらも300になる。

根拠と誤答の確認：同じ並び値の行を含みます。一行ずつにするなら一意な順序とROWSを明示します。

演習3：同額の順位

条件：金額の降順は200、100、100、50。順位は金額だけで決める。

問い：最後の行のRANKとDENSE_RANKを答える。

解答例：RANKは4、DENSE_RANKは3。

根拠と誤答の確認：同順位の後の欠番の有無が違います。

演習4：再帰の起点

条件：木のSQLの開始条件をid=99に変え、id99は存在しない。

問い：結果と理由を答える。

解答例：0行。非再帰部分が空なので広げる起点がない。

根拠と誤答の確認：再帰部分だけで無関係な部署が自動的に選ばれるわけではありません。

演習5：循環とUNION

条件：親子関係に循環があり、再帰結果はidと増加するdepthを含む。

問い：UNION ALLをUNIONに変えるだけで終了を保証できるか。

解答例：できない。depthが異なる行が増えるので、訪問済み判定等が必要。

根拠と誤答の確認：UNIONが除くのは選択した列全体の重複です。

演習6：索引の評価

条件：全売上の95%を返す検索に索引を追加した。

問い：必ず高速になると言えるか。

解答例：言えない。対象行の割合と表へのアクセス、更新負担を含めて計画と実測で判断する。

根拠と誤答の確認：索引の存在と、採用されるアクセス方法・実行時間は別です。

参照資料とこの記事の範囲

事例・数値・図・演習は独自に作成した教材です。用語の範囲はIPAシラバス、仕組みは以下の一次資料で確認しました。特定年度の問題を読んでいなくても学べます。製品固有の動作と一般的な原理は本文で区別します。

IPA APシラバス

PostgreSQL 18：窓関数

SQLite：窓関数とフレーム

PostgreSQL 18：WITH・再帰・循環

PostgreSQL 18：複合索引

PostgreSQL 18：EXPLAIN

関連テーマを続けて学ぶ

SQLの結合と集計｜JOIN・NULL・副問合せを結果表から理解する

E-R図とキー｜業務ルールからエンティティ・関連・多対多を設計する

正規化とデータモデル変更｜関数従属・サブタイプ・制約・DDLをつなぐ

DBトランザクション｜ACID・分離レベル・ロック・障害回復を追う