SQL バッチ処理 — LATERAL・JSONB・EXPLAINの応用

QUESTION 6

LATERAL JOIN — 各カテゴリの売上TOP3商品を一括取得する

LATERAL JOINLIMITTOP N取得ランキングAPI

前提知識 ▼

LATERAL は、JOIN の右側のサブクエリが左側の行の列を参照できるようにする構文です。「左側の各行に対して、その行の値を条件にしたサブクエリを実行する」という相関サブクエリのJOIN版です。

SELECT *
FROM   categories c
JOIN LATERAL (                       -- 右側のサブクエリが左側の c.category_id を参照できる
  SELECT * FROM products p
  WHERE  p.category_id = c.category_id  -- ← 左側の c を参照（LATERAL の核心）
  ORDER BY p.revenue DESC
  LIMIT  3                            -- カテゴリごとに上位3件に絞る
) sub ON TRUE;                        -- ON TRUE: LATERAL は JOIN 条件をサブクエリ内で処理済み

「各グループのTOP N」はLATERALが最も直感的：ROW_NUMBER()でも実現できますが、LATERALは「カテゴリごとに上位3件」という意図がコードに直接現れるため可読性が高いです。

問題 ▼

categories テーブルと products テーブルを使い、各カテゴリの売上（revenue）上位3商品をカテゴリ名付きで取得してください。3商品に満たないカテゴリも全件含めること（LEFT JOIN LATERAL）。結果はカテゴリID・売上降順で並べてください。

使用テーブル

▸ categories

category_id	name
1	飲料
2	食品

▸ products

product_id	category_id	name	revenue
P01	1	コーヒー	50000
P02	1	紅茶	30000
P03	1	緑茶	45000
P04	1	水	20000
P05	2	パン	25000
P06	2	おにぎり	18000

期待出力 ▼

category_id	category_name	product_id	product_name	revenue	rank
1	飲料	P01	コーヒー	50000	1
1	飲料	P03	緑茶	45000	2
1	飲料	P02	紅茶	30000	3
2	食品	P05	パン	25000	1
2	食品	P06	おにぎり	18000	2

模範解答コード ▼

SELECT
  c.category_id,
  c.name AS category_name,
  sub.product_id,
  sub.name AS product_name,
  sub.revenue,
  sub.rank                   -- サブクエリ内で付けた順位
FROM categories c

LEFT JOIN LATERAL (                       -- 商品が0件のカテゴリも残す
  SELECT
    p.product_id,
    p.name,
    p.revenue,
    ROW_NUMBER() OVER (ORDER BY p.revenue DESC) AS rank
  FROM products p
  WHERE p.category_id = c.category_id     -- 左側テーブル(c)の値を参照（LATERALの特徴）
  ORDER BY p.revenue DESC
  LIMIT 3                                 -- カテゴリごとの上位3件
) sub ON TRUE                             -- JOIN条件はWHEREで処理済みのためTRUE

ORDER BY c.category_id, sub.revenue DESC;

/*
  実行順序:
  1. FROM categories c        → 行を読み込む
  2. LEFT JOIN LATERAL (sub)  → 派生テーブルを評価して結合（左表を全行保持）
  3. SELECT                   → 列を評価
  4. ORDER BY                 → 並び替えて出力
*/

解説（テーブル変化・ポイント） ▼

SELECT
  c.category_id, c.name AS category_name,
  sub.product_id, sub.name AS product_name,
  sub.revenue, sub.rank
FROM   categories c
LEFT JOIN LATERAL (
  SELECT
    p.product_id, p.name, p.revenue,
    ROW_NUMBER() OVER (ORDER BY p.revenue DESC) AS rank
  FROM   products p
  WHERE  p.category_id = c.category_id
  ORDER BY p.revenue DESC
  LIMIT  3
) sub ON TRUE
ORDER BY c.category_id, sub.revenue DESC;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM categories c左テーブル categories の全2行を読み込みます。次のステップで各カテゴリ行に対してLATERALサブクエリが個別に実行されます。

1 / 3

category_id	name
1	飲料
2	食品

2行読込

学習ポイント

JOIN と LEFT JOIN の違い：INNER JOIN は両側にマッチする行がある場合のみ結果に出ます。LEFT JOIN は左側テーブルの全行を残し、右側にマッチしなければ NULL で埋めます。「左側テーブルのすべてを残したいとき」は LEFT JOIN を選びます。

LATERAL と ROW_NUMBER() の使い分け：どちらも「グループ内TOP N」を取得できますが、LATERAL は「カテゴリごとに独立した LIMIT」を書けるので直感的です。ROW_NUMBER() 版は CTEと組み合わせることが多く、複雑な順位条件がある場合に向いています。

LATERAL はテーブル関数にも使える：LATERAL unnest(tags_array) のようにテーブル関数（配列展開等）と組み合わせると、配列型の列を行に展開しながら JOIN できます。JSONBの配列展開でも活用されます。

アンチパターン

通常の LIMIT で全体に制限をかけてしまう：LATERAL なしで JOIN (SELECT ... ORDER BY revenue DESC LIMIT 3) sub ON ... と書くと、全カテゴリの中から上位3件になってしまい、カテゴリごとの TOP3 にはなりません。

LATERAL のサポート確認を怠る：LATERAL は PostgreSQL 9.3 以降でサポート。MySQL 8.0.14 以降も使用可。古いDBバージョンや一部のSQLiteでは非サポートのため、環境確認が必要です。

実務コラム：ランキングAPIの設計

「カテゴリページのベストセラー商品TOP3」「ユーザーごとの直近注文TOP5」など、グループ内TOP N取得はAPIエンドポイントの定番要件です。アプリ側で「全商品を取得してからPython/JSでソートして先頭3件だけ使う」実装は大量データで致命的に非効率です。LATERALを使えばDB側で完結し、転送データ量を最小に抑えられます。

QUESTION 7

JSONB操作 — PostgreSQLのJSONBカラムを検索・集計・更新する

JSONB->> / @>JSON検索設定API

前提知識 ▼

PostgreSQL の JSONB 型は JSON データをバイナリ形式で格納し、インデックスを使った高速検索が可能です。フレキシブルなデータ構造（ユーザー設定・タグ・メタデータ等）をRDB内で扱うときに使います。

-- JSON値の取り出し（テキスト型 → キャスト可能）
col ->>  'key'            -- テキストとして取り出す（よく使う）
col ->   'key'            -- JSONB型のまま取り出す（ネスト参照に続けて使う）
col ->>  0               -- JSON配列の index 0 をテキストで取り出す

-- 包含チェック（GINインデックスと組み合わせると高速）
col @>  '{"key": "value"}'::jsonb   -- col が指定のキー/値を含むか

-- JSONB列の部分更新
jsonb_set(col, '{key}', '"new_value"'::jsonb)  -- 指定パスの値を新しい値に置き換える

text vs JSONB：JSON文字列をそのままTEXT型で保存するとSQLで中身を検索できません。JSONBを使うとキー・値を条件に絞り込めます。GINインデックスを貼ると @> 演算子が高速になります。

問題 ▼

user_settings テーブルの settings 列（JSONB型）に対して、①通知が有効（notifications = true）なユーザーを抽出するクエリと、②全ユーザーの theme 設定の集計（何人がdark/lightを使っているか）を取得してください。さらに③user_id=1 の language を 'en' → 'ja' に更新してください。

使用テーブル

▸ user_settings

user_id	settings (JSONB)
1	{"theme":"dark","language":"en","notifications":true}
2	{"theme":"light","language":"ja","notifications":false}
3	{"theme":"dark","language":"ja","notifications":true}
4	{"theme":"light","language":"en","notifications":true}

期待出力 ▼

期待する出力①：

user_id	notifications
1	true
3	true
4	true

期待する出力②：

theme	user_count
dark	2
light	2

期待する出力③：

user_id	settings（更新後）
1	{"theme":"dark","language":"ja","notifications":true}

模範解答コード ▼

-- ① notifications が true のユーザーを抽出（@> 包含演算子）
SELECT
  user_id,
  settings ->> 'notifications' AS notifications  -- テキストとして値を取り出す
FROM user_settings
WHERE settings @> '{"notifications": true}'::jsonb; -- 包含するかを判定

-- ② theme 設定ごとのユーザー数を集計
SELECT
  settings ->> 'theme' AS theme,  -- テキストに変換してGROUP BYキーに
  COUNT(*) AS user_count
FROM user_settings
GROUP BY settings ->> 'theme'
ORDER BY theme;

-- ③ user_id=1 の language を 'en' → 'ja' に更新
UPDATE user_settings
SET settings = jsonb_set(
  settings,             -- 更新対象のJSONB列
  '{language}',         -- 更新するキーのパス
  '"ja"'::jsonb         -- 新しい値（文字列は "" で囲み JSONBにキャスト）
)
WHERE user_id = 1;      -- 更新対象を限定（必須）

/*
  実行順序:
  [クエリ①]
  1. FROM        → 行を読み込む
  2. WHERE       → 行を絞り込む
  3. SELECT      → 列を評価
  [クエリ③]
  1. FROM/WHERE  → 更新対象の行を特定
  2. jsonb_set   → 値を整形（JSONB を生成）
  3. SET         → 列を更新
*/

解説（テーブル変化・ポイント） ▼

SELECT
  user_id,
  settings ->> 'notifications' AS notifications
FROM   user_settings
WHERE  settings @> '{"notifications": true}'::jsonb;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM user_settingsuser_settings テーブルの全4行を読み込みます。settings列はJSONB型で各ユーザーの設定が格納されています。

1 / 3

user_id	settings (JSONB)
1	{"theme":"dark","language":"en","notifications":true}
2	{"theme":"light","language":"ja","notifications":false}
3	{"theme":"dark","language":"ja","notifications":true}
4	{"theme":"light","language":"en","notifications":true}

全 4行読込

SELECT
  settings ->> 'theme' AS theme,
  COUNT(*) AS user_count
FROM   user_settings
GROUP BY settings ->> 'theme'
ORDER BY theme;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM user_settingsuser_settings テーブルの全4行を読み込みます。

1 / 3

user_id	settings (JSONB)
1	{"theme":"dark",...}
2	{"theme":"light",...}
3	{"theme":"dark",...}
4	{"theme":"light",...}

全 4行読込

UPDATE user_settings
SET    settings = jsonb_set(
  settings,
  '{language}',
  '"ja"'::jsonb
)
WHERE  user_id = 1;

LEGEND ▼

データ取得・読込対象

除外・非表示データ

① WHERE 対象特定 ▼

UPDATE user_settings WHERE user_id = 1UPDATE対象の行を特定します。user_id=1 の現在の settings を確認します。language は現在 "en" です。

1 / 3

user_id	settings (更新前)
1	{"theme":"dark","language":"en","notifications":true}
2	{"theme":"light","language":"ja",...}
3	{"theme":"dark","language":"ja",...}
4	{"theme":"light","language":"en",...}

対象: 1行

学習ポイント

JSON vs JSONB の選び方：PostgreSQL には JSON 型と JSONB 型の2種類があります。JSON はテキストを保存しキーの順序・空白を保持しますが、インデックスが貼れません。JSONB はバイナリ変換して格納し、GINインデックスが使えて検索が高速です。実務では基本的に JSONB を選ぶのが推奨です。

->> と -> のどちらを使うか：->> はテキスト型を返すので WHERE や GROUP BY での比較に便利。-> は JSONB 型を返すのでネストされた JSON（settings -> 'address' ->> 'city'）をたどるときに使います。

jsonb_set vs || 演算子：settings || '{"language":"ja"}'::jsonb でも部分更新できます。|| はキーを上書き（なければ追加）しますが、ネストされたキーの更新には jsonb_set が必要です。シンプルなトップレベルのキー変更なら || が簡潔です。

jsonb_each / jsonb_array_elements：SELECT * FROM user_settings, jsonb_each(settings) で JSONB のキー・値ペアを行に展開できます。動的なキーのレポートや JSONB 内の配列を行に展開する jsonb_array_elements と組み合わせると、複雑な JSON 集計も SQL 内で完結できます。

アンチパターン

UPDATE で WHERE を忘れる：UPDATE user_settings SET settings = jsonb_set(settings, '{language}', '"ja"'::jsonb) と WHERE なしで実行すると全ユーザーの language が書き換わります。UPDATE 文には必ず WHERE を書くこと。

検索可能な属性を JSONB だけに頼る：頻繁に検索・集計するカラム（user_idや状態フラグ）を JSONB 内だけに格納すると、インデックス設計が複雑になりパフォーマンスが劣化します。よく使うカラムは通常の列として持ち、補助的な可変属性だけ JSONB に入れる設計が鉄則です。

実務コラム：ユーザー設定APIとJSONBの使い所

ユーザー設定（テーマ・通知設定・UI言語など）は追加項目が多く、RDBの列追加コストが高い領域です。JSONB は「スキーマが変わりやすい補助属性」に向いており、追加項目ごとにALTER TABLEが不要というメリットがあります。ただし「ユーザーIDや登録日時」など必ず検索するカラムは通常の列に残し、JSONB は補完的なデータに使うというバランスが重要です。

QUESTION 8

差分更新バッチ — サブクエリで変更分類して INSERT / UPDATE / DELETE を一括実行

DML連鎖NOT EXISTS差分更新データ同期バッチ

前提知識 ▼

外部システムとのデータ同期バッチでは、「全件DELETEしてINSERT」よりも差分のみを更新する方が安全・高速です。サブクエリ（NOT EXISTS等）を使って「追加すべき行・更新すべき行・削除すべき行」を分類し、1トランザクションで実行するパターンを学びます。

-- 新データにあって現データにない → INSERT対象
WHERE NOT EXISTS (
  SELECT 1 FROM 現データ c WHERE c.id = n.id
)

-- 現データにあって新データにない → DELETE対象
WHERE NOT EXISTS (
  SELECT 1 FROM 新データ n WHERE n.id = c.id
)

NOT IN vs NOT EXISTS：NOT IN はサブクエリの結果に NULL が1つでも含まれると全体が FALSE（または UNKNOWN）になり、意図しない結果を生む罠があります。実務では NOT EXISTS を使うのが安全です。

問題 ▼

products_current（現在のDB内データ）と products_new（外部システムから取得した最新データ）を比較して、①新規追加すべき商品をINSERTし、②不要になった商品をDELETEし、③変更のあった商品をUPDATEする差分更新バッチを書いてください。

使用テーブル

▸ products_current（現在DB）

product_id	name	price
P01	コーヒー	300
P02	紅茶	250
P03	緑茶	200

▸ products_new（外部取得）

product_id	name	price
P01	コーヒー	350
P02	紅茶	250
P04	ほうじ茶	220

期待出力 ▼

product_id	name	price
P01	コーヒー	350
P02	紅茶	250
P04	ほうじ茶	220

模範解答コード ▼

BEGIN;  -- トランザクション開始

-- ① 新規追加: products_new にあって products_current にない行を INSERT
INSERT INTO products_current (product_id, name, price)
SELECT n.product_id, n.name, n.price
FROM products_new n
WHERE NOT EXISTS (
  SELECT 1 FROM products_current c WHERE c.product_id = n.product_id
);

-- ② 削除: products_current にあって products_new にない行を DELETE
DELETE FROM products_current c
WHERE NOT EXISTS (
  SELECT 1 FROM products_new n WHERE n.product_id = c.product_id
);

-- ③ 更新: 両方に存在し、値が異なる行を UPDATE
UPDATE products_current c
SET
  name  = n.name,
  price = n.price
FROM products_new n
WHERE c.product_id = n.product_id
  AND (c.name <> n.name OR c.price <> n.price); -- 変更がある行のみ

COMMIT;  -- 3つのDMLを一括確定

SELECT product_id, name, price
FROM products_current
ORDER BY product_id;  -- 同期後の状態を検証

/*
  実行順序:
  1. BEGIN   → トランザクションを開始
  2. INSERT  → 新規行を追加
  3. DELETE  → 不要な行を削除
  4. UPDATE  → 変更行を更新
  5. COMMIT  → 変更を確定
  6. SELECT  → 同期後の3行を検証
*/

解説（テーブル変化・ポイント） ▼

BEGIN;

-- ① 新規追加 (products_newにあってcurrentにない行)
INSERT INTO products_current (product_id, name, price)
SELECT n.product_id, n.name, n.price
FROM products_new n
WHERE NOT EXISTS (
  SELECT 1 FROM products_current c WHERE c.product_id = n.product_id
);

-- ② 削除 (currentにあってnewにない行)
DELETE FROM products_current c
WHERE NOT EXISTS (
  SELECT 1 FROM products_new n WHERE n.product_id = c.product_id
);

-- ③ 更新 (両方に存在し、値が異なる行)
UPDATE products_current c
SET name = n.name, price = n.price
FROM products_new n
WHERE c.product_id = n.product_id
  AND (c.name <> n.name OR c.price <> n.price);

COMMIT;
SELECT product_id, name, price FROM products_current ORDER BY product_id;

LEGEND ▼

データ取得・読込対象

① 開始・初期状態 ▼

BEGIN; 実行前の状態トランザクションを開始します。現在のDBデータ(左)と最新の外部データ(右)を比較して、過不足と変更を特定します。

1 / 5

▸ products_current（現在）

product_id	name	price
P01	コーヒー	300
P02	紅茶	250
P03	緑茶	200

⋈

▸ products_new（最新）

product_id	name	price
P01	コーヒー	350
P02	紅茶	250
P04	ほうじ茶	220

左: 3行 / 右: 3行

学習ポイント

NOT IN vs NOT EXISTS の違い：NOT IN はサブクエリの結果に NULL が1つでも含まれると全行がマッチしなくなる（NULL の三値論理の罠）。サブクエリにNULLが入りうる場合は NOT EXISTS (...) か LEFT JOIN ... WHERE ... IS NULL パターンが安全です。

EXCEPT / INTERSECT での差分検出：EXCEPT を使うと「全列が一致する行の差集合」が取れます。主キーだけでなく全カラムの差分を見たい場合に便利です。大量データでは NOT EXISTS より EXCEPT のほうが可読性が高い場合があります。

PostgreSQL の MERGE 文（v15+）：PostgreSQL 15 から MERGE が使えます。

MERGE INTO products_current USING products_new ON (条件) WHEN MATCHED AND 変化あり THEN UPDATE ... WHEN NOT MATCHED BY TARGET THEN INSERT ... WHEN NOT MATCHED BY SOURCE THEN DELETE

と1文で差分更新が書けます。バージョンを確認して積極的に使いましょう。

updated_at タイムスタンプで変更追跡：UPDATE 後に updated_at = NOW() をセットすることで「いつ同期されたか」を追跡できます。障害発生時に「最後の同期時刻」を確認して、再実行すべき範囲を特定するのに役立ちます。

アンチパターン

全件 TRUNCATE + INSERT：同期バッチ実行中に他のAPI・クエリがテーブルを参照すると空テーブルを見てしまう。また全行書き換えはWALログ・インデックス再構築コストが差分更新より大幅に高い。

トランザクションを張らずに分割実行：INSERT/DELETE/UPDATE を別々にコミットすると、途中でエラーが起きたとき「INSERTだけ完了してDELETEは未実行」という中途半端な状態になる。3操作を必ず同一トランザクションでまとめること。

実務コラム：外部システム同期バッチのエラー設計

差分更新バッチは BEGIN → INSERT/DELETE/UPDATE → COMMIT の1トランザクションにすることで冪等性（何度実行しても同じ結果）が保てます。さらに「どの行をINSERT/DELETE/UPDATEしたか」をログテーブルに書き込んでおくと障害発生時の調査が容易になります。CTE を使って WITH ins AS (...INSERT ... RETURNING *) INSERT INTO sync_log SELECT ... とすることで、メイン処理とログ書き込みを同一トランザクション内で行う設計も実務でよく使われます。

QUESTION 9

EXPLAIN ANALYZE — スロークエリを読み解きインデックスで改善する

EXPLAIN ANALYZEINDEXパフォーマンスチューニング

前提知識 ▼

EXPLAIN ANALYZE はクエリの実行計画と実際の実行時間・行数を出力するコマンドです。「なぜ遅いのか」の原因（インデックス未使用・大量行スキャン等）を特定するための基本ツールです。

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 'U01';

-- 代表的な出力:
-- Seq Scan on orders (cost=0.00..12.50 rows=1 width=50)
--              ↑全件スキャン（インデックス未使用）
-- Index Scan using idx_orders_user_id on orders
--              ↑インデックス使用（高速）

出力キーワード	意味	対処
Seq Scan	全行スキャン（インデックス未使用）	検索条件の列にインデックスを作成
Index Scan	インデックスを使って行を特定	良い状態
Hash Join	ハッシュを使った JOIN（大テーブル向き）	JOIN キーにインデックスを貼ると Nested Loop に変わることも
rows= 推定 vs 実際	統計情報の精度	大きくズレる場合は ANALYZE でテーブル統計を更新

EXPLAIN ANALYZE は実際にクエリを実行します。UPDATE/DELETEを EXPLAIN ANALYZE するときはトランザクション内で実行して ROLLBACK すること。

問題 ▼

下記の遅いクエリがあります。EXPLAIN で問題を特定し、適切なインデックスを作成してください。さらに複合インデックスが有効なケースも示してください。

遅いクエリ（大量データ想定）

▸ 問題のあるクエリ

クエリ	問題
SELECT * FROM orders WHERE user_id = 'U01'	user_id に INDEX なし → Seq Scan
SELECT * FROM orders WHERE user_id='U01' AND status='paid' ORDER BY ordered_at DESC	複合条件 → 複合インデックス未使用
SELECT * FROM products WHERE LOWER(name) = 'coffee'	関数適用 → インデックス未使用

期待出力 ▼

クエリ	対処前	対処後
WHERE user_id = 'U01'	Seq Scan（全件）	Index Scan（高速）
WHERE user_id AND status ORDER BY ordered_at	Seq Scan または非効率なIndex Scan	Index Scan（複合インデックス完全活用）
WHERE LOWER(name) = 'coffee'	Seq Scan（関数でインデックス無効化）	Index Scan（関数インデックス）

模範解答コード ▼

-- ============================================================
-- Step 1: 実行計画の確認（ANALYZEで実測値を取得）
-- ============================================================
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 'U01';

-- ============================================================
-- Step 2: 単一列インデックス（Seq Scan を Index Scan に改善）
-- ============================================================
CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders (user_id);                   -- user_id列の検索を高速化

-- ============================================================
-- Step 3: 複合インデックス（複数条件とソートの最適化）
-- ============================================================
CREATE INDEX CONCURRENTLY idx_orders_user_status_date
ON orders (user_id, status, ordered_at DESC);          -- 等値条件(user_id, status)を先、ソート条件(ordered_at)を最後に指定
-- ============================================================
-- Step 4: 関数インデックス（関数適用後の結果をインデックス化）
-- ============================================================
CREATE INDEX CONCURRENTLY idx_products_name_lower
ON products (LOWER(name));                             -- LOWER(name)の検索で利用可能になる

-- ============================================================
-- Step 5: 統計情報の更新（プランナの推定精度向上）
-- ============================================================
ANALYZE orders;                        -- テーブルの統計情報（行数・分布等）を最新化

/*
  インデックス選択のフロー:
  1. EXPLAIN ANALYZE でボトルネック（Seq Scan等）を特定。
  2. WHERE の等値条件 → BTree 単一列インデックス。
  3. 複数条件 + ORDER BY → 複合インデックス（等値条件を先に配置）。
  4. LOWER()などの関数検索 → 関数インデックス。
  5. JSONBの検索 → GINインデックス。
*/

解説（テーブル変化・ポイント） ▼

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE  user_id = 'U01';

CREATE INDEX CONCURRENTLY
  idx_orders_user_id
ON orders (user_id);

LEGEND ▼

データ取得・読込対象

① Seq Scan（インデックスなし） ▼

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 'U01'インデックスがない状態では Seq Scan（全件スキャン）が選ばれます。5行すべてを読み込んでから WHERE 条件を評価するため、大量データで深刻に遅くなります。

1 / 4

order_id	user_id	status	amount
O1	U01	paid	5000
O2	U02	pending	3000
O3	U01	paid	8000
O4	U03	shipped	2000
O5	U02	paid	6000

全 5行読込（Seq Scan）

CREATE INDEX CONCURRENTLY
  idx_orders_user_status_date
ON orders (user_id, status, ordered_at DESC);

SELECT * FROM orders
WHERE user_id = 'U01' AND status = 'paid'
ORDER BY ordered_at DESC;

LEGEND ▼

データ取得・読込対象

① WHERE 複数条件 + ソート ▼

WHERE user_id='U01' AND status='paid' ORDER BY ordered_at DESCuser_id の単一インデックスでは、取得後に status の判定や ordered_at によるソート（メモリやディスク上での並び替え）が必要となり、件数が多いと遅延の原因になります。

1 / 3

order_id	user_id	status	ordered_at
O1	U01	paid	2024-01-15
O2	U02	pending	2024-01-10
O3	U01	paid	2024-02-01
O4	U03	shipped	2024-01-05
O5	U02	paid	2024-01-20

全行または単一インデックスで取得後に追加処理

CREATE INDEX CONCURRENTLY
  idx_products_name_lower
ON products (LOWER(name));

SELECT * FROM products
WHERE LOWER(name) = 'coffee';

LEGEND ▼

データ取得・読込対象

除外・非表示データ

① Seq Scan（関数適用） ▼

WHERE LOWER(name) = 'coffee'通常のname列にインデックスがあっても、LOWER() 関数で囲むとインデックスは使えません（値が変化するため）。そのため全行を読み込み、実行時に小文字変換しながら比較する Seq Scan になります。

1 / 3

product_id	name (元データ)	LOWER(name) の評価
P1	Coffee	'coffee' = 'coffee'
P2	Tea	'tea' = 'coffee'
P3	COFFEE	'coffee' = 'coffee'
P4	Water	'water' = 'coffee'

全 4行読込 → 関数評価

学習ポイント

インデックスとは何か：インデックスは「本の巻末索引」のようなものです。索引なし（Seq Scan）は本の全ページをめくって探す状態、索引あり（Index Scan）はページ番号が記載された索引を引く状態です。WHERE で頻繁に検索される列に作成します。

CONCURRENTLY の重要性：本番DBで CREATE INDEX（CONCURRENTLY なし）はテーブル全体をロックして、インデックス作成が終わるまでINSERT/UPDATE/SELECTが待機状態になります。CONCURRENTLY を付けるとロックなしで作成でき、本番トラフィックへの影響を最小化できます。

インデックスの副作用：インデックスはSELECTを高速にしますが、INSERT/UPDATE/DELETE 時にインデックスも更新されるためその分だけ書き込みが遅くなります。また、ストレージ容量を消費します。使われていないインデックスは pg_stat_user_indexes で確認して削除しましょう。

pg_stat_statements でスロークエリを特定：SELECT query, total_exec_time, calls FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; で累計実行時間の多いクエリ（= 改善効果が大きいクエリ）を特定できます。EXPLAIN は特定後に使うツールです。

アンチパターン

WHERE で関数を使って既存インデックスを無効化：WHERE LOWER(name) = 'coffee' は name 列のインデックスを使えません（BTreeインデックスはLOWER後の値を知らないため）。関数インデックスを作るか、最初からデータをlowercaseで保存するか、生成列（Generated Column）を使うこと。

WHERE で型が合わずインデックスが効かない：WHERE user_id = 1（整数）と書いたが user_id 列が VARCHAR 型の場合、型キャストが発生してインデックスが効かないことがあります。WHERE user_id = '1'（文字列）と合わせること。

実務コラム：インデックス設計の現場判断

インデックスは「全てに貼れば速くなる」わけではありません。「検索が多く更新が少ない列」「カーディナリティ（値の種類）が高い列」が効果的です。例えばステータス列（active/inactive の2値）にインデックスを貼っても、全行の半数が取得対象になるためプランナが Seq Scan を選ぶことが多いです。本番で困ったときは EXPLAIN ANALYZE → ボトルネック特定 → 最小限のインデックス追加 → 再度 EXPLAIN で効果確認、というサイクルを繰り返すことが実務の基本です。

QUESTION 10

ROLLUP / CUBE — 多次元集計レポートを1クエリで生成する

ROLLUP / CUBEGROUPING SETS多次元集計レポートバッチ

前提知識 ▼

ROLLUP は階層的な小計・合計を自動で付加するGROUP BYの拡張です。CUBE は全組み合わせの集計を生成します。GROUPING SETS はそれらを柔軟に指定できる一般化版です。バッチ集計レポートで複数の GROUP BY クエリを1本化できます。

GROUP BY ROLLUP(col1, col2)
-- col1+col2の集計、col1だけの小計、全体合計の3段階を自動生成

GROUP BY CUBE(col1, col2)
-- (col1,col2), (col1), (col2), () の全4パターンの集計を生成

GROUP BY GROUPING SETS((col1, col2), (col1), ())
-- 任意の集計パターンを列挙する（ROLLUP/CUBEの一般化版）

GROUPING() 関数：GROUPING(col1) は小計・合計行で列が集計されていると 1 を返します。これを使って小計行の NULL を '合計' 等の文字列に置き換えることができます。

問題 ▼

sales テーブルから、年・月・カテゴリ別の売上合計を ROLLUP を使って取得してください。年合計・全体合計（小計行）も含め、GROUPING() 関数で NULL を分かりやすいラベルに置き換えること。さらに同じテーブルで CUBE を使った全組み合わせ集計も示してください。

使用テーブル

▸ sales

sale_id	year	month	category	amount
1	2024	1	food	3000
2	2024	1	drink	2000
3	2024	2	food	4000
4	2024	2	drink	1500
5	2023	12	food	5000
6	2023	12	drink	3000

期待出力 ▼

期待する出力①：

year_label	month_label	category_label	total_amount
2023	12	drink	3000
2023	12	food	5000
2023	12月計	—	8000
2023年計	—	—	8000
2024	1	drink	2000
2024	1	food	3000
2024	1月計	—	5000
2024	2	drink	1500
2024	2	food	4000
2024	2月計	—	5500
2024年計	—	—	10500
総計	—	—	18500

期待する出力②：

year	category	total_amount	g_year	g_category
2023	drink	3000	0	0
2023	food	5000	0	0
2023	NULL	8000	0	1
2024	drink	3500	0	0
2024	food	7000	0	0
2024	NULL	10500	0	1
NULL	drink	6500	1	0
NULL	food	12000	1	0
NULL	NULL	18500	1	1

模範解答コード ▼

-- ① ROLLUP: 年→月→カテゴリの階層的な小計・合計を生成
SELECT
  CASE
    WHEN GROUPING(year) = 1 THEN '総計'          -- yearが集計対象なら総計
    WHEN GROUPING(month) = 1 THEN year::TEXT || '年計'
    ELSE year::TEXT
  END AS year_label,
  CASE
    WHEN GROUPING(month) = 1 THEN '—'
    WHEN GROUPING(category) = 1 THEN month::TEXT || '月計'
    ELSE month::TEXT
  END AS month_label,
  CASE
    WHEN GROUPING(category) = 1 THEN '—'
    ELSE category
  END AS category_label,
  SUM(amount) AS total_amount
FROM sales
GROUP BY ROLLUP(year, month, category)      -- 階層順（年 > 月 > カテゴリ）で集計
ORDER BY
  GROUPING(year), year NULLS LAST,          -- 明細を先、合計行(NULL)を後に
  GROUPING(month), month NULLS LAST,
  GROUPING(category), category NULLS LAST;

-- ② CUBE: 指定列の全組み合わせパターンの集計を生成
SELECT
  year,
  category,
  SUM(amount) AS total_amount,
  GROUPING(year) AS g_year,          -- 1: yearが集計対象
  GROUPING(category) AS g_category   -- 1: categoryが集計対象
FROM sales
GROUP BY CUBE(year, category)        -- 2^2 = 4パターンの集計
ORDER BY year NULLS LAST, category NULLS LAST;

/*
  生成される集計グループの違い:
  [ROLLUP(year, month, category)]
  1. (year, month, category) : 明細
  2. (year, month)           : 月計
  3. (year)                  : 年計
  4. ()                      : 総計

  [CUBE(year, category)]
  1. (year, category) : 明細
  2. (year)           : 年計
  3. (category)       : カテゴリ計（ROLLUPには含まれない）
  4. ()               : 総計
*/

解説（テーブル変化・ポイント） ▼

SELECT
  CASE WHEN GROUPING(year)=1 THEN '総計'
       WHEN GROUPING(month)=1 THEN year||'年計'
       ELSE year::TEXT END AS year_label,
  CASE WHEN GROUPING(month)=1 THEN '—'
       WHEN GROUPING(category)=1 THEN month||'月計'
       ELSE month::TEXT END AS month_label,
  CASE WHEN GROUPING(category)=1 THEN '—'
       ELSE category END AS category_label,
  SUM(amount) AS total_amount
FROM   sales
GROUP BY ROLLUP(year, month, category)
ORDER BY GROUPING(year), year NULLS LAST,
GROUPING(month), month NULLS LAST,
GROUPING(category), category NULLS LAST;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM salessales テーブルの6行を読み込みます。year, month, category, amount の4列があります。ROLLUP でこれらの列の階層的な小計・合計行が自動生成されます。

1 / 3

sale_id	year	month	category	amount
1	2024	1	food	3000
2	2024	1	drink	2000
3	2024	2	food	4000
4	2024	2	drink	1500
5	2023	12	food	5000
6	2023	12	drink	3000

全 6行読込

SELECT
  year, category,
  SUM(amount) AS total_amount,
  GROUPING(year)     AS g_year,
  GROUPING(category) AS g_category
FROM   sales
GROUP BY CUBE(year, category)
ORDER BY year NULLS LAST, category NULLS LAST;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM salessales テーブルの6行を読み込みます。CUBE(year, category) は year と category の全組み合わせ（2^2=4パターン）の集計を生成します。

1 / 3

year	category	amount
2024	food	3000
2024	drink	2000
2024	food	4000
2024	drink	1500
2023	food	5000
2023	drink	3000

全 6行読込

学習ポイント

ROLLUP の NULL は「集計のNULL」：ROLLUP が生成する合計・小計行では、集計されたレベルの列が NULL になります。これはデータが空なのではなく「この列は集計対象（全てを合算）」を意味します。GROUPING() 関数でデータNULLと区別できます。

ROLLUP は「階層の順序が重要」：ROLLUP(year, month, category) は「year > month > category」の順に階層集計します。ROLLUP(category, year) と書くと「category > year」になり意味が変わります。必ず「大 → 小」の粒度順で書くこと。

GROUPING SETS で完全な制御：GROUP BY GROUPING SETS ((year, month), (year), ()) は ROLLUP(year, month) と同等です。「year×category と year のみ」のような非階層な組み合わせが必要なときは GROUPING SETS で直接列挙します。

UNION ALL との違い（パフォーマンス）：ROLLUP は複数の GROUP BY クエリを UNION ALL したのと等価ですが、内部的に1回のテーブルスキャンで全集計レベルを処理します（DB次第）。複数の GROUP BY + UNION ALL クエリを ROLLUP に置き換えることで、I/O と実行時間を削減できます。

アンチパターン

ROLLUP の NULL をデータ欠損と誤解する：ROLLUP の集計行に現れる NULL は「全体を集計している」印です。WHERE で year IS NOT NULL と書くと合計行が消えてしまいます。GROUPING() を使ってラベルを置き換えた後に表示上の処理をすること。

CUBE を使って不要なパターンまで生成する：CUBE(year, month, category) は 2^3=8パターンを生成します。大量データや列数が多い場合に不要なパターンまで計算してパフォーマンスに影響します。本当に必要な組み合わせだけを GROUPING SETS で指定することが最適です。

実務コラム：BIツール連携と多次元集計

Redash・Metabase・Tableau などのBIツールに渡す集計クエリでは、ROLLUP/CUBE を使うことで「ドリルダウン（全体→年→月→カテゴリ）に必要な全粒度のデータを1クエリで生成できます。アプリ側で「全体合計クエリ」「年別クエリ」「月別クエリ」を別々に実行する手間がなくなり、バッチ処理の設計が大幅にシンプルになります。結果をキャッシュして配信するレポートバッチの実装に特に向いています。