HAVINGでグループ条件
GROUP BY でグループごとの件数や平均までは出せるようになりました。ところが「そのうち平均が 4.0 以上の商品だけ見たい」と思って WHERE に条件を足すと、たいていエラーで止まります。
WHERE に集計関数を書くと怒られる
SQL クエリ
-- reviews を商品ごとにまとめ、評価の高い商品だけ見たい
SELECT product_id, AVG(rating) AS avg_rating
FROM reviews
WHERE AVG(rating) >= 4.0 -- ここでエラーになる
GROUP BY product_id;理由は「その時点で平均がまだ存在しない」からです。WHERE はテーブルから読み込んだ行を 1 行ずつ見て、残すか捨てるかを決めます。この段階のレビューはまだばらばらの 1 件 1 件で、平均という値はどこにも作られていません。無い値を条件にしようとしたので怒られたわけです。
集計し終わってから絞るのが HAVING
グループにまとめて平均を計算したあとで絞り込む句が HAVING です。書く場所は GROUP BY のうしろになります。
SQL クエリ
SELECT product_id, AVG(rating) AS avg_rating
FROM reviews
GROUP BY product_id
HAVING AVG(rating) >= 4.0;これで「全レビューの平均が 4.0 以上だった商品」だけが残ります。集計する前に絞るのが WHERE、集計した後に絞るのが HAVING。違いはこの一点だけです。下の図も、集計を終えて 4 つになったグループを HAVING が 2 つに絞るところを表しています。
同じ 4.0 でも、書く場所で意味が変わる
条件を WHERE に移すと、まったく別の集計になります。
SQL クエリ
-- 4 点以上のレビューだけを集めて、その平均を出す
SELECT product_id, AVG(rating) AS avg_rating
FROM reviews
WHERE rating >= 4.0
GROUP BY product_id;こちらは低い評価を最初に捨ててから平均を取るので、どの商品も平均は必ず 4.0 以上になります。「評価の高い商品を選ぶ」のではなく「高評価だけを集めて平均する」クエリです。両方を同時に書くこともできて、その場合は WHERE が先に行を減らし、残った行だけが集計されます。読み込む行が減るぶん速くなります。
迷ったら、その条件に集計関数が入っているかどうかで決めます。入っていれば
HAVING、入っていなければWHEREです。
テーブル構造
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT NOT NULL,
product VARCHAR(50) NOT NULL,
amount INT NOT NULL
);
INSERT INTO orders (id, user_id, product, amount) VALUES
(1, 101, 'ノートPC', 128000),
(2, 101, 'キーボード', 6800),
(3, 101, '小説A', 1800),
(4, 102, 'マウス', 2980),
(5, 102, 'コーヒー豆', 1500),
(6, 102, '紅茶葉', 1200),
(7, 103, 'マウス', 2980),
(8, 104, '小説B', 2200);期待される出力
| user_id | order_count |
|---|---|
| 101 | 3 |
| 102 | 3 |