女性向けエンジニアスクール
3秒でわかる
複数の行をまとめてひとつの値を返すSQLの関数。件数や合計、平均を求めるときに使い、GROUP BYと組み合わせて集計します。
30秒図解
もう少し詳しく
どういうものか
集約関数は、複数行を入力として受け取り、結果を1つの値にまとめて返す関数です。標準的に使えるのは次の5つです。
| 関数 | 返るもの |
|---|---|
| COUNT | 行数 |
| SUM | 合計 |
| AVG | 平均 |
| MAX | 最大値 |
| MIN | 最小値 |
GROUP BY と組み合わせると、グループごとに1つずつ値が返ります。GROUP BY を書かない場合は、テーブル全体がひとつのグループとして扱われ、結果は1行になります。
なぜ必要か
「今月の売上はいくらか」「カテゴリごとに商品は何件あるか」という問いは、行を1件ずつ見ても答えが出ません。全行を取得してアプリ側で合計することもできますが、10万件を転送してから足すのは通信もメモリも無駄です。集約関数を使えば、データベースの中で計算を済ませ、答えの数行だけを受け取れます。
具体例
-- カテゴリごとの件数と平均価格を、商品が3件以上あるものに絞って出す
SELECT
category,
COUNT(*) AS items,
AVG(price) AS avg_price,
MAX(price) AS max_price
FROM products
GROUP BY category
HAVING COUNT(*) >= 3
ORDER BY avg_price DESC;絞り込みの位置に注目します。集計前に行を減らすなら WHERE、集計後の結果で絞るなら HAVING です。WHERE COUNT(*) >= 3 と書くとエラーになります。
つまずきやすいところ
NULL の扱いが直感とずれます。COUNT(*) は行数を数えるので NULL の行も含みますが、COUNT(price) は price が NULL の行を数えません。AVG も同様で、NULL は分母から外れます。「10行あるのに平均が8行分で計算されている」という食い違いはこれが原因です。NULL を0として扱いたいなら AVG(COALESCE(price, 0)) と書きます。
もうひとつは GROUP BY に入れていない列を SELECT に並べる誤りです。
-- カテゴリごとに1行なのに、商品名は複数ある。どれを返すか決まらない
SELECT category, name, COUNT(*) FROM products GROUP BY category;PostgreSQL はこれをエラーにし、MySQL は設定によって適当な1件を返します。エラーで止まる方が親切です。集計と一緒に出せるのは、グループを決めた列と集約関数の結果だけと覚えます。
COUNT(DISTINCT customer_id) のように重複を除いて数える書き方も頻出です。注文件数ではなく購入した人数を知りたいときに使います。
似た用語との違い
集約関数は複数行を1行にまとめますが、ROW_NUMBER や SUM() OVER (...) などのウィンドウ関数は行数を保ったまま、各行の横に集計値を添えます。合計だけ欲しいなら集約関数、明細と合計を同時に見たいならウィンドウ関数という使い分けになります。
