集計する

読了目安 約3分

COUNT などの集計関数と、GROUP BY、HAVING によるグループごとの集計を学びます。

集計関数

投稿は全部で何件あるか。 数えるのは COUNT です。

SELECT COUNT(*) FROM posts;

+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+

COUNT のように、行の集まりを 1 つの値にまとめる関数を集計関数と呼びます。 主なものは 5 つです。

• COUNT(*)：行数。
• SUM(列)：合計。
• AVG(列)：平均。
• MAX(列)：最大値。
• MIN(列)：最小値。

WHERE と組み合わせれば、数える対象を絞れます。

SELECT COUNT(*) FROM posts WHERE user_id = 1;

+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

GROUP BY でグループごとに集計する

「ユーザーごとの投稿数」を上のクエリで求めると、user_id を変えて人数ぶん打つことになります。 GROUP BY を使うと、行をグループに分けてからグループごとに集計関数を適用する処理が 1 回のクエリで書けます。

SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id;

+---------+------------+
| user_id | post_count |
+---------+------------+
|       1 |          3 |
|       2 |          2 |
|       3 |          1 |
+---------+------------+

GROUP BY user_id が「user_id の値が同じ行を 1 グループにまとめよ」、SELECT 側の COUNT(*) が「各グループの行数を出せ」です。 AS post_count は結果の列に付ける名前で、次章の Go から読むときに効きます。

競プロで map[int]int に出現回数を数え上げるパターンと同じで、GROUP BY のキーが map のキー、集計関数が値の更新に対応します。

前章の JOIN と組み合わせれば、番号ではなく名前で出せます。

SELECT users.name, COUNT(*) AS post_count
FROM posts
JOIN users ON posts.user_id = users.id
GROUP BY users.id;

+-------+------------+
| name  | post_count |
+-------+------------+
| alice |          3 |
| bob   |          2 |
| carol |          1 |
+-------+------------+

AS で付けた名前は ORDER BY でも使え、ORDER BY post_count DESC LIMIT 3 を足せば投稿数ランキングが 1 クエリで書けます。

COUNT には COUNT(列名) という形もあり、こちらはその列が NULL の行を数えません。

HAVING は集計後の絞り込み

「2 件以上投稿しているユーザーだけ」を WHERE COUNT(*) >= 2 と書くと、エラーになります。 WHERE は行をグループに分ける前に 1 行ずつ絞り込む句で、その時点では COUNT(*) の値がまだ存在しないからです。 集計した後の値で絞り込むには HAVING を使います。

SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id
HAVING COUNT(*) >= 2;

+---------+------------+
| user_id | post_count |
+---------+------------+
|       1 |          3 |
|       2 |          2 |
+---------+------------+

WHERE と HAVING は併用できます（期間で絞ってから件数で絞る、など）。

アプリ側で数えてはいけない場面

全 posts を取ってきて Go のループで map に数え上げても、結果は GROUP BY と同じです。 しかし投稿が 100 万件なら、数個の数字のために 100 万行がネットワークを渡ります。 数える作業はデータを手元に持つ DB に任せ、結果の数字だけ受け取ります。

「アプリのループを GROUP BY 1 発に置き換える」は ISUCON で頻出の改善パターンで、/isucon/03-nplus1/ でまとめて扱います。