第3回｜SQL を書ききる

JOIN・集約・サブクエリ・CTE・ウィンドウ関数を組み合わせれば、アプリ側でループしながら何度も投げていた SELECT のほとんどは1本のSQLに置き換えられる。

この回のねらい

第2回で「テーブルは集合であり、SQLは宣言的に書く」という頭の切り替えができた。この回はその上に、実務で手が止まらない程度のSQL表現力を積み上げる。JOINで複数テーブルをまたぎ、GROUP BY で集約し、サブクエリと CTE でクエリを分解し、ウィンドウ関数で「集約しつつ明細も残す」という GROUP BY だけでは書けない処理を実現する。ゴールは構文を覚えることではなく、「顧客ごとにループして1件ずつ SELECT を投げる」ようなアプリケーション側の処理を、1本のSQLに置き換える発想を身につけることにある。

到達目標

INNER JOIN と LEFT JOIN の結果の違いを説明し、要件に応じて使い分けられる。
自己結合（self join）で、同一テーブル内の行同士の関係（同じ顧客の別の注文、など）を表現できる。
GROUP BY と集約関数を使って、カテゴリ別・月別のような多次元の集計ができる。
相関サブクエリと EXISTS を使って、行ごとに条件が変わる絞り込みを書ける。
CTE（WITH 句）で複雑なクエリを、読める単位に分解できる。
ウィンドウ関数（ROW_NUMBER, RANK, SUM() OVER, LAG）で「集約しつつ明細を残す」処理を書ける。
LEFT JOIN の絞り込み条件を ON 句と WHERE 句のどちらに書くべきか、結果の違いから判断できる。

前提と準備

第1回で作成したPostgreSQL 16系の postshop データベースに、共通スキーマ（customers / categories / products / product_prices / orders / order_items / events）とデータが投入済みであることを前提にする。この回で主に扱うのは orders（約100万行）・order_items （約300万行）・customers（約5万行）・products（約5千行）・categories（約30行）である。

psql -d postshop

行数の多いテーブルを結合するクエリが増えるため、動作確認中は結果を LIMIT で絞りながら試すとよい。本編の出力イメージは次の設定を前提に整形している。

\x auto
\timing on

第2回の宿題3で、orders と order_items を素朴に INNER JOIN すると行数が orders 単体より増えることを確認したはずである。理由はこの回の最初の小節で説明する。

この回で新しく出てくる用語

上4つはこの回の主題で、本編で詳しく扱う。下4つは本編・ハンズオンで補助的に使う関数・句である。回をまたいで使う語は用語集にもまとめている。

用語	一行での説明
相関サブクエリ	外側のクエリの行を参照し、行ごとに評価されるサブクエリ
CTE（Common Table Expression、`WITH` 句）	サブクエリに名前を付けて先に定義し、あとから参照する書き方
ウィンドウ関数	行を1行にまとめず、各行から見た「行の集まり」を対象に値を計算する関数
`PARTITION BY`	ウィンドウ関数が計算対象とする行の範囲を区切る指定。行は潰さない
`date_trunc('month', 時刻)`	日時を指定した単位で切り捨て、その期間の先頭の時刻を返す関数
`coalesce(a, b)`	引数を左から見て、最初に `NULL` でない値を返す関数
`array_agg(列)`	グループ内の値を1つの配列にまとめる集約関数
`\x auto`	行が画面幅に収まらないときだけ縦向き表示に切り替える psql メタコマンド

本編

JOIN の使い分け — INNER / LEFT / 自己結合

第2回で見たとおり、FROM 句は論理的には「関係するテーブルの行の組み合わせをすべて作ってから、 ON／WHERE で絞り込む」処理である。orders の1行と order_items の1行を ON oi.order_id = o.id で結びつけると、1件の注文に商品明細が3件あれば3組の行ができる。これが第2回の宿題で INNER JOIN 後の行数が増えた理由である。JOINは「行を絞り込む」演算ではなく「行の組を作る」演算だと捉えると、結合後の行数が増えることも減ることも自然に理解できる。

JOIN の種類	結果に残る行	一致しない側の列	典型的な用途
`INNER JOIN`	両テーブルで条件に一致した行だけ	—	「両方に存在するものだけ」でよい集計・明細展開
`LEFT JOIN`	左テーブルの全行＋一致した右テーブルの行	`NULL` で埋まる	「左側は全部残したい」（未購入顧客も0件で出す、など）
自己結合（self join）	同じテーブルを別名で2回 `FROM` に置いて結合した行	通常のJOINと同じ	同一テーブル内の行同士の関係（同じ顧客の別注文、同一カテゴリの別商品など）

INNER JOIN の例。ある注文の明細を、商品名・カテゴリ名つきで展開する。

SELECT
  o.id AS order_id,
  p.name AS product_name,
  cat.name AS category_name,
  oi.qty,
  oi.unit_price
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.id = 12345
ORDER BY p.name;

 order_id | product_name | category_name | qty | unit_price
----------+--------------+----------------+-----+------------
    12345 | 商品A123     | 家電           |   1 |    2980.00
    12345 | 商品B456     | 生活雑貨       |   2 |    1580.00
(2 rows)

LEFT JOIN の例。全顧客と注文件数を出す。注文が1件もない顧客も0件として残したいので LEFT JOIN を使う。

SELECT
  c.id,
  c.email,
  count(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.email
ORDER BY order_count ASC
LIMIT 5;

  id  |         email          | order_count
------+-------------------------+-------------
   17 | user00017@example.com  |           0
   42 | user00042@example.com  |           0
  103 | user00103@example.com  |           0
  ...
(5 rows)

ここで count(o.id) を使っている点に注意する。一致する orders 行がない顧客は o.id が NULL になり、count(列名) は NULL を数えないため、正しく 0 になる。count(*) を使うと、一致しない場合でも LEFT JOIN は結合結果として1行返す（全列が NULL の行）ため 1 と数えてしまい、「注文が1件ある」かのような誤った結果になる。第2回で扱った COUNT(*) と COUNT(列名) の違いが、ここで実務上の意味を持つ。

自己結合の例。同じ顧客が7日以内に再注文した組を検出する。orders を o1・o2 という別名で2回 FROM に置き、「同じ customer_id で、o2 の注文日が o1 より後、かつ7日以内」という条件で結合する。

SELECT
  o1.customer_id,
  o1.id AS first_order_id,
  o1.ordered_at AS first_ordered_at,
  o2.id AS second_order_id,
  o2.ordered_at AS second_ordered_at
FROM orders o1
JOIN orders o2
  ON o2.customer_id = o1.customer_id
 AND o2.ordered_at > o1.ordered_at
 AND o2.ordered_at <= o1.ordered_at + interval '7 days'
ORDER BY o1.customer_id, o1.ordered_at
LIMIT 5;

 customer_id | first_order_id |    first_ordered_at    | second_order_id |   second_ordered_at
-------------+-----------------+--------------------------+-------------------+-------------------------
         101 |           88213 | 2025-01-05 10:12:00+09  |             88790 | 2025-01-09 21:03:00+09
         204 |           91567 | 2025-02-14 09:03:00+09  |             92011 | 2025-02-18 15:40:00+09
  ...
(5 rows)

o1.id <> o2.id を書いていないが、o2.ordered_at > o1.ordered_at という不等号の条件がすでに「同じ行同士の一致」を排除している。自己結合では、片方の別名をもう片方より「後の行」に限定する条件（> や <）を入れることが、無意味な組み合わせや重複を防ぐ定石になる。

集約と GROUP BY

GROUP BY に指定した列の値が同じ行をひとつにまとめ、SELECT リストではその列か集約関数（sum, count, avg, max, min など）しか書けない。集約関数に包まれていない非グループ化列を書くと、次のようにエラーになる。

ERROR:  column "c.email" must appear in the GROUP BY clause or be used in an aggregate function

これは「1つの region の値に対して、複数ある email のどれを表示すればよいかSQLには決められない」という理由によるエラーである。

以降、この回では便宜上「成立した売上」を status NOT IN ('pending', 'cancelled') と定義する。決済前の pending と取消済みの cancelled を除く。refunded（返金済み）を売上に含めるかどうかは本来業務要件次第だが、本教材では単純化のため含める。

月別の総売上を出す。売上金額は order_items.qty * order_items.unit_price の合計であり、月は date_trunc('month', o.ordered_at) で丸める。

SELECT
  date_trunc('month', o.ordered_at) AS month,
  sum(oi.qty * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status NOT IN ('pending', 'cancelled')
GROUP BY date_trunc('month', o.ordered_at)
ORDER BY month;

          month           |   revenue
---------------------------+-------------
 2025-01-01 00:00:00+09    | 48213500.00
 2025-02-01 00:00:00+09    | 45980200.00
  ...
(例: データ期間が18か月なら 18 rows)

GROUP BY の後に、集約結果自体を条件に絞り込みたいときは WHERE ではなく HAVING を使う。 WHERE は集約前の行を絞る段階、HAVING は集約後のグループを絞る段階であり、両者は評価される段階が違う（第2回で見た評価順序のとおり WHERE → GROUP BY → HAVING の順）。明細行数が100件以上あるカテゴリだけを抽出する。

SELECT
  cat.id,
  cat.name,
  count(*) AS item_count
FROM order_items oi
JOIN products p    ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
GROUP BY cat.id, cat.name
HAVING count(*) >= 100
ORDER BY item_count DESC;

 id |  name  | item_count
----+--------+------------
  4 | 家電   |      48213
  9 | 食品   |      39120
  ...
(例: 22 rows)

サブクエリと相関サブクエリ

サブクエリ（副問い合わせ）は、クエリの中に別の SELECT を埋め込む書き方である。外側のクエリの行を参照しない非相関サブクエリと、外側のクエリの行を1行ごとに参照する相関サブクエリを区別すると理解しやすい。

非相関サブクエリの例。全商品の平均価格より高い商品を一覧する。かっこ内のサブクエリは、外側の products の行に依存せず一度だけ評価される。

SELECT id, name, price
FROM products
WHERE price > (SELECT avg(price) FROM products)
ORDER BY price DESC
LIMIT 5;

  id  |    name    |  price
------+------------+----------
 3821 | 商品C789   | 89800.00
 1904 | 商品D012   | 62000.00
  ...
(5 rows)

相関サブクエリの例。各顧客の最終注文日を、顧客1行ごとにサブクエリで求める。サブクエリの中の o.customer_id = c.id が外側の customers の行 c を参照しているため、概念的には外側の行1つにつきサブクエリが1回評価される。

SELECT
  c.id,
  c.email,
  (SELECT max(o.ordered_at) FROM orders o WHERE o.customer_id = c.id) AS last_ordered_at
FROM customers c
ORDER BY c.id
LIMIT 3;

 id |         email          |     last_ordered_at
----+-------------------------+---------------------------
  1 | user00001@example.com  | 2025-06-11 14:22:00+09
  2 | user00002@example.com  | 2025-06-09 08:10:00+09
  3 | user00003@example.com  |
(3 rows)

顧客5万人に対してこれを実行すると、素朴には5万回サブクエリが評価される計算になる。実際にはプランナが結合に書き換えて最適化することもあるが、常にそうなるとは限らない。相関サブクエリが遅くなっていないかは第8回の EXPLAIN で確認する。この例のような「顧客ごとの最終注文日」は、後述するウィンドウ関数や LEFT JOIN + GROUP BY で書き直すほうが多くの場合効率的である。

EXISTS は「サブクエリが1行でも返るか」だけを見る、真偽値を返す相関サブクエリの一種である。一度もキャンセルをしたことがない顧客（未購入の顧客も含む）を抽出する。

SELECT c.id, c.email
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'cancelled'
)
ORDER BY c.id
LIMIT 3;

 id |         email
----+-------------------------
  2 | user00002@example.com
  4 | user00004@example.com
  5 | user00005@example.com
(3 rows)

EXISTS は行の中身ではなく「存在するかどうか」だけを見るため、サブクエリの SELECT リストは SELECT 1 のような形式的な式でよい。

CTE（WITH句）で複雑なクエリを分解する

CTE（Common Table Expression、共通テーブル式）は WITH 名前 AS (...) の形で、サブクエリに名前をつけて先出しする書き方である。ネストした無名のサブクエリを何段も重ねるより、処理のステップを上から順に読める形に分解できる。

WITH monthly_category_sales AS (
  SELECT
    cat.id AS category_id,
    cat.name AS category_name,
    date_trunc('month', o.ordered_at) AS month,
    sum(oi.qty * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN products p     ON p.id = oi.product_id
  JOIN categories cat ON cat.id = p.category_id
  JOIN orders o        ON o.id = oi.order_id
  WHERE o.status NOT IN ('pending', 'cancelled')
  GROUP BY cat.id, cat.name, date_trunc('month', o.ordered_at)
)
SELECT category_name, month, revenue
FROM monthly_category_sales
WHERE revenue > 1000000
ORDER BY month, revenue DESC;

 category_name |           month           |  revenue
----------------+----------------------------+------------
 家電           | 2025-01-01 00:00:00+09    | 12345678.00
 食品           | 2025-01-01 00:00:00+09    |  9876543.00
  ...
(例: 84 rows)

CTEは必要な数だけ , で連ねて複数定義できる。ある CTE が別の CTE を参照することもでき、「注文単位に集約 → 顧客単位に集約 → ランキングを付与」のような多段の処理を、途中経過に名前をつけながら書き下せる。PostgreSQL 16系では、単純に参照されるだけのCTEはデフォルトで呼び出し元にインライン展開される（MATERIALIZED を明示しない限り最適化の壁にならない）。この挙動は PostgreSQL 12で変わったバージョン依存の挙動であり、意図的に一度だけ評価させたい場合は WITH x AS MATERIALIZED (...) と明示する。

ウィンドウ関数はいつ評価されるか

第2回で確認したSELECTの論理的評価順序（FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY）に、ウィンドウ関数の評価タイミングを1つ足す。ウィンドウ関数は GROUP BY/HAVING の後、SELECT リストの最終的な計算や ORDER BY の前に評価される。

FROM / JOIN → WHERE → GROUP BY → HAVING → 【ウィンドウ関数】 → SELECT リスト → ORDER BY → LIMIT

ここが GROUP BY とウィンドウ関数を混同しやすい理由である。GROUP BY を書いた時点で行はすでに集約後の1グループ1行に潰れており、ウィンドウ関数はその潰れた後の行に対してしか動けない。「集約しつつ明細も残したい」なら、GROUP BY を使わずウィンドウ関数だけで書く必要がある。次の小節で扱う。

ウィンドウ関数 — 集約しつつ明細を残す

ウィンドウ関数とは、行を1行にまとめずに、各行から見た「行の集まり」を対象に値を計算する関数である。この「行の集まり」をウィンドウと呼び、OVER (...) の中でその範囲を指定する。集約関数が N 行を1行に潰すのに対し、ウィンドウ関数は N 行を N 行のまま残し、各行に集計値を1列として付け加える。書き方は 関数名(...) OVER (PARTITION BY ... ORDER BY ... フレーム句) である。

PARTITION BY: GROUP BY に近いが、行を1行に潰さず「集計の範囲」を区切るだけ。
ORDER BY: パーティション内で行に順序をつける。ROW_NUMBER/RANK/LAG はこの順序が必須。
フレーム句（ROWS/RANGE BETWEEN ... AND ...）: 現在行から見てどこまでを計算対象にするか。 ORDER BY を書いた集約ウィンドウ関数の既定フレームは RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（先頭から現在行まで）で、これが「累計」の正体である。ORDER BY を書かなければ既定フレームはパーティション全体になる。

ROW_NUMBER() は同一パーティション内で重複なく1から連番を振る。RANK() は似ているが、同順位（タイ）があると同じ順位を割り当て、その分だけ次の順位を飛ばす。同じカテゴリ内で価格順位を両方の関数で付けて違いを見る。

SELECT
  category_id,
  id,
  name,
  price,
  row_number() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn,
  rank()       OVER (PARTITION BY category_id ORDER BY price DESC) AS rk
FROM products
WHERE category_id = 4
ORDER BY price DESC
LIMIT 5;

 category_id |  id  |   name   |  price   | rn | rk
--------------+-------+----------+----------+----+----
            4 |   812 | 商品A123 | 24800.00 |  1 |  1
            4 |   340 | 商品B456 | 19800.00 |  2 |  2
            4 |   551 | 商品C789 | 19800.00 |  3 |  2
            4 |   129 | 商品D012 | 15000.00 |  4 |  4
            4 |   980 | 商品E345 | 12000.00 |  5 |  5
(5 rows)

id=340 と id=551 は同価格19800円でタイになっている。rn は 2, 3 と重複なく振られるのに対し、 rk はどちらも2位のままで、次の商品は4位から始まる（3位が欠番になる）。「同率n位」を表現したいなら RANK()、「重複なくちょうどn件」を取りたいなら ROW_NUMBER() を選ぶ。

SUM() OVER による移動累計の例。ある顧客の注文を古い順に並べ、各注文までの累計購入額を出す。

SELECT
  customer_id,
  order_id,
  ordered_at,
  amount,
  sum(amount) OVER (
    PARTITION BY customer_id
    ORDER BY ordered_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM (
  SELECT o.id AS order_id, o.customer_id, o.ordered_at,
         sum(oi.qty * oi.unit_price) AS amount
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.customer_id = 101
    AND o.status NOT IN ('pending', 'cancelled')
  GROUP BY o.id, o.customer_id, o.ordered_at
) order_amounts
ORDER BY ordered_at;

 customer_id | order_id |       ordered_at        | amount  | running_total
-------------+----------+---------------------------+---------+---------------
         101 |    88213 | 2025-01-05 10:12:00+09    | 3200.00 |       3200.00
         101 |    91567 | 2025-02-14 09:03:00+09    | 1500.00 |       4700.00
         101 |    95820 | 2025-03-02 21:40:00+09    | 4800.00 |       9500.00
(3 rows)

フレーム句を明示的に ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW と書いているのは、既定の RANGE フレームだと ORDER BY の値（ここでは ordered_at）が完全に一致する行同士を1つの塊として扱い、同じ累計値を返してしまうためである。ordered_at はマイクロ秒まで持つ timestamptz なので実際にタイになることは稀だが、「行単位で確実に積み上げたい」ときは ROWS を明示する習慣をつけておくと事故がない。

LAG() は同一パーティション内で、現在行より1つ前（既定）の行の値を参照する。前回注文からの経過日数を出す。

SELECT
  customer_id,
  id AS order_id,
  ordered_at,
  ordered_at - lag(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS days_since_prev
FROM orders
WHERE customer_id = 101
ORDER BY ordered_at;

 customer_id | order_id |       ordered_at        | days_since_prev
-------------+----------+---------------------------+------------------
         101 |    88213 | 2025-01-05 10:12:00+09    |
         101 |    91567 | 2025-02-14 09:03:00+09    | 40 days 22:51:00
         101 |    95820 | 2025-03-02 21:40:00+09    | 16 days 12:37:00
(3 rows)

最初の行の days_since_prev が NULL になるのは、lag() が「1つ前の行」を参照できないためである。 LAG は「アプリ側で1つ前のループ結果を変数に保持しておく」処理をSQLだけで完結させる典型例であり、 LEAD()（1つ後の行を参照）と対で覚えておくとよい。

ハンズオン

以降の課題は、すべて postshop に接続した状態で実行する。

課題1: カテゴリ別・月別の売上を集計する

各カテゴリについて、月ごとの売上合計（「成立した売上」の定義は本編を参照）を求める。

想定解答

SELECT
  cat.id AS category_id,
  cat.name AS category_name,
  date_trunc('month', o.ordered_at) AS month,
  sum(oi.qty * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p     ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
JOIN orders o        ON o.id = oi.order_id
WHERE o.status NOT IN ('pending', 'cancelled')
GROUP BY cat.id, cat.name, date_trunc('month', o.ordered_at)
ORDER BY cat.name, month;

 category_id | category_name |           month           |  revenue
--------------+----------------+----------------------------+------------
           4 | 家電           | 2025-01-01 00:00:00+09    | 12345678.00
           4 | 家電           | 2025-02-01 00:00:00+09    |  9876543.00
  ...
(例: 30カテゴリ × 18か月分 = 540 rows 程度)

なぜこれで解けるか: order_items はカテゴリを直接持たないため、products を経由して categories に到達する必要がある。同様に月は orders.ordered_at にしかないため orders も結合する。GROUP BY に列挙した3列（カテゴリID・カテゴリ名・月）の組み合わせがそのまま出力の1行になり、 sum() がその組み合わせに属する order_items 行の売上を集計する。カテゴリ名は categories.name が UNIQUE なので GROUP BY に cat.id だけでも一意に決まるが、SELECT に出す列は明示的に GROUP BY にも含めるほうが読みやすい。

課題2: 顧客ごとの累計購入額（移動累計）を出す

各顧客の注文を古い順に並べ、注文ごとの金額とその時点までの累計購入額（移動累計）を1本のクエリで出す。

想定解答

WITH order_amounts AS (
  SELECT
    o.id AS order_id,
    o.customer_id,
    o.ordered_at,
    sum(oi.qty * oi.unit_price) AS amount
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status NOT IN ('pending', 'cancelled')
  GROUP BY o.id, o.customer_id, o.ordered_at
)
SELECT
  customer_id,
  order_id,
  ordered_at,
  amount,
  sum(amount) OVER (
    PARTITION BY customer_id
    ORDER BY ordered_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM order_amounts
ORDER BY customer_id, ordered_at;

 customer_id | order_id |       ordered_at        | amount  | running_total
-------------+----------+---------------------------+---------+---------------
           1 |    50122 | 2025-01-03 11:20:00+09    | 5400.00 |       5400.00
           1 |    61980 | 2025-03-19 08:44:00+09    | 2100.00 |       7500.00
           1 |    77213 | 2025-05-02 19:10:00+09    | 3300.00 |      10800.00
           2 |    50890 | 2025-01-08 13:02:00+09    | 8000.00 |       8000.00
  ...
(約100万 rows。特定顧客だけ見たい場合は WHERE customer_id = ... を足して確認する)

なぜこれで解けるか: order_items だけでは「注文1件あたりの金額」が存在しないため、まずCTE order_amounts で注文単位に集約する。外側のクエリでは GROUP BY を使わず、sum() OVER だけを使っている。GROUP BY を使ってしまうと注文明細ではなく顧客単位の1行に潰れてしまい、「注文ごとの行を残したまま累計を付与する」という要件を満たせない。これが本編で説明した「ウィンドウ関数と GROUP BY の違い」がそのまま効いてくる例である。

課題3: 各顧客の「直近の注文」だけを1行ずつ取り出す

各顧客について、最も新しい注文（ordered_at が最大のもの）だけを1行取り出す。

想定解答

WITH ranked_orders AS (
  SELECT
    o.id AS order_id,
    o.customer_id,
    o.status,
    o.ordered_at,
    row_number() OVER (
      PARTITION BY o.customer_id
      ORDER BY o.ordered_at DESC, o.id DESC
    ) AS rn
  FROM orders o
)
SELECT customer_id, order_id, status, ordered_at
FROM ranked_orders
WHERE rn = 1
ORDER BY customer_id;

 customer_id | order_id |  status   |       ordered_at
-------------+----------+-----------+---------------------------
           1 |   482913 | shipped   | 2025-06-11 14:22:00+09
           2 |   481022 | paid      | 2025-06-09 08:10:00+09
           3 |   479887 | delivered | 2025-06-02 19:45:00+09
  ...
(50000 rows。customers の行数と一致する)

なぜこれで解けるか: row_number() は PARTITION BY customer_id で顧客ごとに区切り、 ORDER BY ordered_at DESC で新しい注文ほど小さい番号（1）を振る。同一顧客が同時刻に複数注文することは通常ないが、タイが起きても結果が1行に定まるよう o.id DESC を第2キーに加えている。 WHERE rn = 1 はウィンドウ関数の結果に対する絞り込みだが、WHERE はウィンドウ関数より論理的に前の段階で評価されるため（本編「ウィンドウ関数はいつ評価されるか」を参照）、rn を直接 WHERE に書くことはできない。一度CTE（またはサブクエリ）に包んで rn を通常の列として確定させてから、外側で WHERE に書く必要がある。同じことはPostgreSQL独自の DISTINCT ON (customer_id) ... ORDER BY customer_id, ordered_at DESC でも書ける。DISTINCT ON (列) は、指定した列の値ごとに ORDER BY の先頭1行だけを残す構文である。本教材では標準SQLに近い ROW_NUMBER() の形を基本形として採用する。

課題4: RFM（最終購入日・購入回数・購入額）を1クエリで算出する

各顧客について、Recency（最終購入日）・Frequency（購入回数）・Monetary（累計購入額）を1本のクエリで求める。1件も購入していない顧客も、Frequency=0・Monetary=0 として結果に含める。

想定解答

WITH order_amounts AS (
  SELECT
    o.customer_id,
    o.id AS order_id,
    o.ordered_at,
    sum(oi.qty * oi.unit_price) AS amount
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status NOT IN ('pending', 'cancelled')
  GROUP BY o.customer_id, o.id, o.ordered_at
)
SELECT
  c.id AS customer_id,
  c.email,
  max(oa.ordered_at) AS last_ordered_at,
  count(oa.order_id) AS frequency,
  coalesce(sum(oa.amount), 0) AS monetary
FROM customers c
LEFT JOIN order_amounts oa ON oa.customer_id = c.id
GROUP BY c.id, c.email
ORDER BY monetary DESC;

 customer_id |         email          |      last_ordered_at      | frequency |  monetary
--------------+-------------------------+----------------------------+-----------+------------
        4821 | user04821@example.com  | 2025-06-20 11:03:00+09    |        18 | 452300.00
        1190 | user01190@example.com  | 2025-06-18 09:44:00+09    |        15 | 398100.00
  ...
       29981 | user29981@example.com  |                            |         0 |       0.00
(50000 rows)

なぜこれで解けるか: RFMは「顧客ごとにループして最終注文日・注文回数・購入額を集計する」処理を 1本のSQLに寄せた典型例である。1件も購入していない顧客も結果に残したいので customers を起点に LEFT JOIN order_amounts とする。ここで count(oa.order_id) を使っている点が要になる。 LEFT JOIN で一致しない顧客も結合結果としては1行返るため、count(*) を使うと未購入の顧客まで frequency = 1 と誤って数えてしまう。count(列名) は NULL を数えないため、一致しなかった顧客では正しく 0 になる。同様に sum(oa.amount) は未購入の顧客では NULL になるため、 coalesce(..., 0) で 0 に変換している。max(oa.ordered_at) は未購入の顧客では NULL のままでよい（「最終購入日がない」をそのまま表す）。

よくあるつまずきと対処

症状	原因	対処
`ERROR: column "c.email" must appear in the GROUP BY clause or be used in an aggregate function`	`SELECT` に集約されていない列を、`GROUP BY` にも集約関数にも入れずに書いた	列を `GROUP BY` に足すか、`max()`/`array_agg()` などで集約する
`GROUP BY` した結果、明細行がウィンドウ関数の計算前に潰れてしまう	ウィンドウ関数は `GROUP BY`/`HAVING` の後に評価されるため、`GROUP BY` を書いた時点ですでに1グループ1行になっている	「集約しつつ明細を残す」なら `GROUP BY` を使わず、ウィンドウ関数だけで書く（課題2を参照）
`WHERE row_number() = 1` のように `SELECT` の別名をそのまま `WHERE` に書いてエラーになる	`WHERE` は論理的に `SELECT`（ウィンドウ関数を含む）より前に評価される	CTEかサブクエリでウィンドウ関数の結果を確定させ、外側の `WHERE` で絞り込む
`LEFT JOIN` したのに未一致の左側の行が消える	右テーブルへの絞り込み条件を `WHERE` に書き、`NULL` との比較が unknown になって行ごと除外された	絞り込み条件を `ON` 句に移す

最後の「LEFT JOIN のつもりが WHERE で内部結合化する」罠は、実害が大きいわりに気づきにくいのでコードで対比する。全顧客と、支払い済み以降（paid/shipped/delivered）の注文件数を数えたいとする。

-- 誤り: 注文が1件もない顧客まで結果から消える
SELECT c.id, c.email, count(o.id) AS paid_order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status IN ('paid', 'shipped', 'delivered')
GROUP BY c.id, c.email;

未一致の顧客は o.status が NULL になる。NULL IN ('paid', 'shipped', 'delivered') は unknown と評価され、WHERE はunknownの行を除外するため、注文が1件もない顧客（本来 paid_order_count = 0 であってほしい顧客）が結果から丸ごと消える。LEFT JOIN を書いた意味が失われ、実質的に INNER JOIN と同じ結果になる。

-- 正しい: 絞り込み条件を ON 句に移す
SELECT c.id, c.email, count(o.id) AS paid_order_count
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status IN ('paid', 'shipped', 'delivered')
GROUP BY c.id, c.email;

ON 句に条件を書くと、それは「結合する行の条件」として扱われる。条件に合わない orders 行は結合されないが、customers 側の行自体は LEFT JOIN の性質どおり必ず1行以上残り、対応する orders 側の列は NULL で埋まる。WHERE は結合が終わったあとの最終フィルタなので、そこに右側の条件を書くと未一致行（NULL 埋めの行）ごと弾かれてしまう。「右テーブルが存在するかどうかに関わる条件は ON に、それ以外の絞り込みは WHERE に」と役割を分けて考えると事故が減る。

到達度チェックリスト

INNER JOIN と LEFT JOIN の結果件数がなぜ違うのかを、NULL 埋めの観点から説明できる。
自己結合を使って、同じテーブル内の行同士の関係を1本のクエリで表現できる。
GROUP BY と集約関数を使って、カテゴリ×月のような多次元の集計ができる。
WHERE と HAVING を、集約の前後どちらを絞り込むかで正しく使い分けられる。
相関サブクエリと非相関サブクエリの違いを説明し、EXISTS を使った絞り込みが書ける。
CTE（WITH句）で、多段の集計を読める単位に分解できる。
ROW_NUMBER() を使って、パーティションごとの直近1件・上位n件を取り出せる。
SUM() OVER と LAG() を使って、明細行を保持したまま累計・前回比較を計算できる。
LEFT JOIN の絞り込み条件を ON 句に書くべきか WHERE 句に書くべきかを、結果の違いから判断できる。

宿題（次回までの自習）

各カテゴリについて、これまでの累計売上（キャンセルを除く）でランキング上位3商品を抽出するクエリを書く。RANK() または ROW_NUMBER() と PARTITION BY category_id を組み合わせ、課題1・課題3で書いたパターンを流用する。
「過去に一度でも購入したことがある顧客」のうち「直近30日間に events の記録がない顧客」を、 LEFT JOIN + WHERE の罠に注意しながら抽出する。events 側の条件を ON に書く場合と WHERE に書く場合で結果件数が変わることを実際に確認する。
課題2の移動累計クエリを、注文単位ではなく月単位の累計に拡張する。まず月ごとの購入額を GROUP BY で集約し、そのうえで顧客ごとに月次の累計を SUM() OVER で計算する2段構えのクエリになる。

次回への接続

この回でSQLの表現力は一通り揃った。しかし課題1〜4のクエリを書きながら「なぜ order_items にカテゴリが直接入っていないのか」「なぜ毎回 products を経由しないとカテゴリにたどり着けないのか」と感じた読者もいるはずである。それはテーブル設計、すなわち正規化の結果である。第4回では、この回で「JOINして辿る」ことでしか得られなかったデータ構造が、なぜそう設計されているのかを正規化の理論から説明する。

参考

PostgreSQL 公式ドキュメント: "Tutorial" > "Window Functions"（ウィンドウ関数の考え方の入門）
PostgreSQL 公式ドキュメント: "The SQL Language" > "Queries" > "Table Expressions"（JOIN の構文と意味）
PostgreSQL 公式ドキュメント: "The SQL Language" > "Queries" > "WITH Queries (Common Table Expressions)"
PostgreSQL 公式ドキュメント: "Functions and Operators" > "Aggregate Functions"
PostgreSQL 公式ドキュメント: "Functions and Operators" > "Window Functions"（OVER 句・フレーム句の正確な構文）
PostgreSQL 公式ドキュメント: "Functions and Operators" > "Subquery Expressions"（EXISTS・相関サブクエリ）