PostgreSQL 基礎講座 #3 結合と集計 — 読み取りクエリの実務感覚

インフラ PostgreSQL データベース
読了 4分

テーブルを作ったので(第 2 回)、今度は読む番です。実務の読み取りクエリの 8 割は結局、結合と集計の組み合わせで、この組み合わせで起きる事故もパターン化されています。今回は文法の羅列の代わりにその定番の事故を中心に、users と orders の 2 テーブルで実務感覚を固めます。

結合: INNER が基本、LEFT は「なくても出てほしいとき」 #

INNERとLEFT JOIN 
-- 注文があるユーザーだけ: INNER JOIN
SELECT u.name, o.amount_jpy
FROM users u
JOIN orders o ON o.user_id = u.id;

-- 注文がないユーザーも: LEFT JOIN (ない側は NULL)
SELECT u.name, o.amount_jpy
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

選択基準は質問の形です。「注文したユーザーたちの……」なら INNER、「すべてのユーザーの……(注文がなければなしとして)」なら LEFT です。そしてここで一番よくある事故が出ます。LEFT JOIN をしておいて右側テーブルの条件を WHERE に書くと、NULL の行が濾されて INNER JOIN と同じになります。

右側の条件はONへ 
-- 意図: 全ユーザー + 12 月の注文。実際: 12 月の注文があるユーザーだけ (LEFT が無力化)
... LEFT JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2026-12-01';

-- 正しい形: 右側テーブルの条件は ON 句へ
... LEFT JOIN orders o
       ON o.user_id = u.id
      AND o.created_at >= '2026-12-01';

LEFT JOIN では右側テーブルのフィルタは ON に、左側テーブルのフィルタは WHERE にというルール 1 つで、この系統の事故が消えます。

集計: GROUP BY と HAVING の役割 #

WHEREとHAVING 
SELECT o.user_id, count(*) AS order_count, sum(o.amount_jpy) AS total_jpy
FROM orders o
WHERE o.status = 'paid'          -- 行単位のフィルタ: 集計の前に
GROUP BY o.user_id
HAVING sum(o.amount_jpy) >= 100000;  -- グループ単位のフィルタ: 集計の後に

WHERE は集計の前に行を濾し、HAVING は集計の後にグループを濾します。「決済完了の注文だけ数えるが、合計 10 万円以上のユーザーだけ」という文章が、そのまま 2 つのフィルタに分かれます。ちなみに count(*) は行数、count(o.column) は NULL を除いた数なので、LEFT 結合の結果を数えるときはこの差が「注文 0 件が 0 と出るか」を分けます。

結合 + 集計の罠: 重複集計 #

あるユーザーに注文 3 件とレビュー 2 件があるとき、users に orders と reviews を同時に結合すると行が 3 × 2 = 6 個に膨らみ、その上で sum(o.amount_jpy) をすると金額が 2 倍に集計されます。 多対多の結合の後の集計には、いつもこの危険があります。定石は、各集計をサブクエリ(または次回の CTE)で先に済ませて 1 対 1 で結合することです。

サブクエリで集計を分離 
SELECT u.name, coalesce(os.total_jpy, 0) AS total_jpy, coalesce(rs.review_count, 0) AS review_count
FROM users u
LEFT JOIN (SELECT user_id, sum(amount_jpy) AS total_jpy FROM orders GROUP BY user_id) os
       ON os.user_id = u.id
LEFT JOIN (SELECT user_id, count(*) AS review_count FROM reviews GROUP BY user_id) rs
       ON rs.user_id = u.id;

「合計が妙に大きい」というバグ報告のかなりの部分が、このパターン 1 つで説明できます。

グループごとの最新 1 件: DISTINCT ON #

「ユーザーごとの一番最近の注文」は実務の定番の要求ですが、標準 SQL では意外に面倒です。PostgreSQL には専用の道具があります。

グループごとの最新1件 
SELECT DISTINCT ON (user_id) user_id, id, amount_jpy, created_at
FROM orders
ORDER BY user_id, created_at DESC;

DISTINCT ON (user_id) は user_id ごとに最初の行だけを残し、ORDER BY がその「最初の行」を定義します(ユーザーごとに created_at 降順の 1 番目、つまり最新)。同じことをする標準の道具であるウィンドウ関数 ROW_NUMBER() は第 8 回で扱い、上位 N 件(最新 3 件など)が必要ならそちらが答えです。

サブクエリと結合のどちらを使うかは、たいてい読みやすさの問題で、PostgreSQL のプランナが同等の形に勝手に変換してくれる場合も多いです。「どちらが速いか」は推測せず実行計画で確かめるのが正解で、その道具である EXPLAIN が第 5 回の主題です。

まとめ #

  • INNER は「両側にあるものだけ」、LEFT は「左側は全部」です。質問の文章の形がそのまま選択基準です。
  • LEFT JOIN の右側テーブルの条件は ON に書きます。WHERE に書くと INNER に無力化されるのが、この回最大の罠です。
  • WHERE は集計前の行フィルタ、HAVING は集計後のグループフィルタです。count(*) と count(カラム) の NULL の差も覚えておいてください。
  • 複数の子テーブルを結合した後の集計は重複集計の地雷原です。集計をサブクエリで先に済ませて 1 対 1 で結合します。
  • グループごとの最新 1 件は DISTINCT ON が PostgreSQL らしい答えです。上位 N 件は第 8 回のウィンドウ関数に続きます。
X