---
note: JOIN・集約・サブクエリ・CTE・ウィンドウ関数で、アプリのループ処理を1クエリに寄せる
created: 2026-08-14T10:00:00+09:00
---

# 第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行）である。

```bash
psql -d postshop
```

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

```bash
\x auto
\timing on
```

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

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

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

| 用語 | 一行での説明 |
|---|---|
| 相関サブクエリ | 外側のクエリの行を参照し、行ごとに評価されるサブクエリ |
| 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` の例。ある注文の明細を、商品名・カテゴリ名つきで展開する。

```sql
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;
```

```text
 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`
を使う。

```sql
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;
```

```text
  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日以内」という条件で
結合する。

```sql
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;
```

```text
 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` など）しか書けない。集約関数に包まれていない非グループ化列を
書くと、次のようにエラーになる。

```text
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)` で丸める。

```sql
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;
```

```text
          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件以上
あるカテゴリだけを抽出する。

```sql
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;
```

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

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

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

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

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

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

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

```sql
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;
```

```text
 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行でも返るか」だけを見る、真偽値を返す相関サブクエリの一種である。
一度もキャンセルをしたことがない顧客（未購入の顧客も含む）を抽出する。

```sql
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;
```

```text
 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 (...)` の形で、サブクエリに
名前をつけて先出しする書き方である。ネストした無名のサブクエリを何段も重ねるより、処理のステップ
を上から順に読める形に分解できる。

```sql
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;
```

```text
 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` の**前**に評価される。

```text
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()` は似ているが、同順位
（タイ）があると同じ順位を割り当て、その分だけ次の順位を飛ばす。同じカテゴリ内で価格順位を両方の
関数で付けて違いを見る。

```sql
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;
```

```text
 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` による移動累計の例。ある顧客の注文を古い順に並べ、各注文までの累計購入額を出す。

```sql
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;
```

```text
 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つ前（既定）の行の値を参照する。前回注文からの経過
日数を出す。

```sql
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;
```

```text
 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: カテゴリ別・月別の売上を集計する

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

**想定解答**

```sql
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;
```

```text
 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本のクエリで
出す。

**想定解答**

```sql
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;
```

```text
 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行取り出す。

**想定解答**

```sql
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;
```

```text
 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 として結果に含める。

**想定解答**

```sql
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;
```

```text
 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`）の注文件数を数えたいと
する。

```sql
-- 誤り: 注文が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` と同じ結果になる。

```sql
-- 正しい: 絞り込み条件を 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` 句に書くべきかを、結果の違いから
      判断できる。

## 宿題（次回までの自習）

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

## 次回への接続

この回でSQLの表現力は一通り揃った。しかし課題1〜4のクエリを書きながら「なぜ `order_items` に
カテゴリが直接入っていないのか」「なぜ毎回 `products` を経由しないとカテゴリにたどり着けないのか」
と感じた読者もいるはずである。それはテーブル設計、すなわち正規化の結果である。
[第4回](04-normalization.md)では、この回で「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`・相関
  サブクエリ）