HAVINGと高度な集約

HAVINGで集約後にフィルタリングし、集約関数の中でCASE WHENを使ってデータをピボットしましょう。

8 分 · 3 問

GROUP BYはすでに知っていますね。このモジュールでは、正しいクエリとプロフェッショナルなクエリを分けるもの、つまり集約後のフィルタリング、条件付き集約、そして評価順序のしっかりした理解を扱います。練習するには、sqliteonline.com（SQLiteエンジン）を開くか、ターミナルでsqlite3を起動してください。各レッスンには必要なCREATE TABLE文とINSERT文が用意されています。例を実行する前に、このデータセットをコピーしましょう。

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  category TEXT NOT NULL,
  amount REAL NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO orders (customer, category, amount, status)
VALUES
  ('alice', 'books', 42.0, 'paid'),
  ('alice', 'games', 55.0, 'paid'),
  ('alice', 'books', 18.5, 'refunded'),
  ('bruno', 'games', 70.0, 'paid'),
  ('bruno', 'books', 12.0, 'paid'),
  ('chloe', 'games', 95.0, 'paid'),
  ('chloe', 'books', 30.0, 'paid'),
  ('david', 'books', 8.0, 'refunded');

鍵となるのは評価の論理的な順序です。FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。WHEREはグループ化の前に行をフィルタリングし、HAVINGは集約が計算されたあとにグループをフィルタリングします。だからこそWHERE SUM(amount) > 90は不正なのです。WHEREの段階では、まだ合計は存在しません。ここでは、支払済みの合計が90を超える顧客だけを残します（期待される結果：aliceとchloe）。

SELECT customer,
       COUNT(*) AS nb_orders,
       SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) > 90;

二つ目の必修パターンは条件付き集約です。SUMやCOUNTの中にCASE WHENをしのばせることで、初心者なら三つの別々のクエリを実行するところを、テーブルを一度なぞるだけで複数の指標を計算できます。これは行を列へピボットするための定番のテクニックです。

SELECT customer,
  SUM(CASE WHEN category = 'books'
      THEN amount ELSE 0 END) AS books_total,
  SUM(CASE WHEN category = 'games'
      THEN amount ELSE 0 END) AS games_total
FROM orders
WHERE status = 'paid'
GROUP BY customer;

理解度チェック

このレッスンの要点をしっかり覚えているか確認しましょう。

1. エンジンはこれらの句をどの論理的な順序で評価しますか？
   • WHERE → GROUP BY → HAVING
   • HAVING → WHERE → GROUP BY
   • GROUP BY → HAVING → WHERE
2. HAVINGではなくWHEREに置くべき条件はどれですか？
   • SUM(amount) > 100
   • status = 'paid'
   • COUNT(*) >= 2
3. SUM(CASE WHEN category = 'books' THEN amount ELSE 0 END)は何を計算しますか？
   • すべての注文の合計
   • 'books'カテゴリの行だけの合計
   • 'books'カテゴリの行の数
   • 'books'以外の行の合計