SQLのJOINと集計 — 行が消える・増える理由とGROUP BYの基本

SQLのJOINと集計。結合する2つのテーブルと、COUNT・SUM・GROUP BYのカード。

テーブルを分けた瞬間から、SQL は「1つの表を読む」道具ではなくなります。

前の回で、テーブルを作って SELECT しました。ただ、実際のデータは1つの表に収まりません。タスクと利用者、注文と商品。分けて持つのが正しい設計で、そのぶん取り出すときに繋ぎ直す必要があります。それが JOIN です。

この記事では、2つの表を実際に作って繋ぎ、繋ぎ方を間違えると行が消えること、増えることを、件数を数えて確かめます。あわせて GROUP BY で集計し、WHERE と HAVING の使い分けまで扱います。

対象レベル: 初級〜中級 / 区分: 必修

目次

この記事でわかること

  • JOIN が行を掛け合わせてから絞り込んでいること
  • 内部結合で行が消えるのはどんなときか。LEFT JOIN との違い
  • 気づかないうちに行が増える(1対多の結合)仕組みと、件数での見つけ方
  • GROUP BY を書いたときに選べる列と選べない列がある理由
  • WHERE と HAVING の順番の違い

この記事でやること

  1. 練習用の表を3つ作る(本線で作った表には触りません)
  2. JOIN で繋ぎ、件数が合わないことを先に体験する
  3. LEFT JOIN に変えて、消えていた行を出す
  4. 1対多で行が水増しされるところを、件数で確かめる
  5. GROUP BY で集計し、HAVING で絞る

先に知る言葉

この記事で初めて出てくる言葉です。 詳しくは用語集にもまとめてあります。

言葉意味
結合(JOIN)2 つの表を繋いで 1 つの結果にすること
内部結合 / LEFT JOIN両側に相手がいる行だけ返すか、左側を全部残すか
1 対多片方の 1 件に、もう片方が複数ぶら下がる関係。繋ぐと行が増える
集計関数SUM や COUNT のように、複数の行を 1 つの値に畳むもの
NULL値が無いという状態。0 でも空文字でもない

前提と作業場所

VM の中(Ubuntu Server)で打ちます。 前の回で MySQL を入れて、appdb と appuser を作ってあることが前提です。

本線で作った tasks / users には一切触りません。 この記事で作る表は すべて j_ で始めます(join のつもりです)。最後に全部消します。

管理者で入ります。表を作る権限は appuser に渡していないためです。

sudo mysql
USE appdb;

練習用の表を作る

DROP TABLE IF EXISTS j_task;
DROP TABLE IF EXISTS j_member;
DROP TABLE IF EXISTS j_team;

CREATE TABLE j_team (
  team_id   INT PRIMARY KEY,
  team_name VARCHAR(30) NOT NULL
);

CREATE TABLE j_member (
  member_id INT PRIMARY KEY,
  name      VARCHAR(30) NOT NULL,
  team_id   INT
);

CREATE TABLE j_task (
  task_id   INT PRIMARY KEY,
  title     VARCHAR(40) NOT NULL,
  member_id INT,
  hours     INT NOT NULL
);

データを入れます。わざと「所属が無い人」と「誰にも割り当てていないタスク」を混ぜます。

INSERT INTO j_team VALUES (1,'開発'),(2,'運用'),(3,'品質');

INSERT INTO j_member VALUES
  (10,'さとう',1),
  (11,'たなか',1),
  (12,'すずき',2),
  (13,'やまだ',NULL);

INSERT INTO j_task VALUES
  (100,'画面を作る',   10, 5),
  (101,'APIを作る',    10, 8),
  (102,'手順書を書く', 11, 2),
  (103,'監視を足す',   12, 3),
  (104,'棚卸し',       NULL, 1);

件数を確かめます。これから何度も数えるので、出発点を覚えておいてください。

SELECT COUNT(*) FROM j_member;
SELECT COUNT(*) FROM j_task;
4
5

JOIN は「掛け合わせてから絞る」

先に、仕組みを1回見ておきます。条件を書かずに繋ぐと、こうなります。

SELECT COUNT(*) FROM j_member, j_team;
12

4 × 3 = 12。 すべての組み合わせが作られました。これが JOIN の出発点です。 ON は、この 12 通りから「正しい組み合わせ」だけを残す条件です。

SELECT COUNT(*)
FROM j_member m
JOIN j_team t ON m.team_id = t.team_id;
3

4 人いたのに 3 行です。 ここが最初の落とし穴で、次で正体を見ます。

内部結合で、行が消える

誰が消えたのかを出します。

SELECT m.name, t.team_name
FROM j_member m
JOIN j_team t ON m.team_id = t.team_id;
+--------+-----------+
| name   | team_name |
+--------+-----------+
| さとう | 開発      |
| たなか | 開発      |
| すずき | 運用      |
+--------+-----------+

「やまだ」がいません。 team_id が NULL なので、m.team_id = t.team_id が どのチームとも成立しなかったためです。

JOIN(内部結合)は、両側に相手がいる行しか返しません。 「人の一覧が欲しい」つもりで書くと、所属していない人が黙って消えます。

これはエラーになりません。 件数が減っているだけなので、気づかずに集計まで進むと、 そのぶん少ない数字が出ます。「人数が合わない」の典型的な原因です。

LEFT JOIN で、消さずに出す

SELECT m.name, t.team_name
FROM j_member m
LEFT JOIN j_team t ON m.team_id = t.team_id;
+--------+-----------+
| name   | team_name |
+--------+-----------+
| さとう | 開発      |
| たなか | 開発      |
| すずき | 運用      |
| やまだ | NULL      |
+--------+-----------+

4 行に戻りました。 LEFT JOIN は左側(FROM に書いたほう)を必ず全部残し、 相手がいなければ右側の列を NULL で埋めます。

書き方返る行使う場面
JOIN(内部結合)両側に相手がいる行だけ関係が必ずある前提のとき
LEFT JOIN左側は全部。右は無ければ NULL一覧が欲しいとき

迷ったら「左の表の行数と、結果の行数が同じであるべきか」を考えてください。 同じであるべきなら LEFT JOIN です。

LEFT JOIN を台無しにする書き方

よくある間違いを、わざとやります。チーム名で絞り込みたいときです。

SELECT COUNT(*)
FROM j_member m
LEFT JOIN j_team t ON m.team_id = t.team_id
WHERE t.team_name = '開発';
2

これは意図どおりです。では、「開発チームの人と、所属なしの人」を出したいときは?

SELECT m.name, t.team_name
FROM j_member m
LEFT JOIN j_team t ON m.team_id = t.team_id AND t.team_name = '開発';
+--------+-----------+
| さとう | 開発      |
| たなか | 開発      |
| すずき | NULL      |
| やまだ | NULL      |
+--------+-----------+

条件を ON に書くか WHERE に書くかで、結果がまったく変わります。

どこに書くか何が起きるか
ON に書く繋ぐときの条件。 合わなくても左側の行は残る
WHERE に書く繋いだ後の絞り込み。 NULL の行は落ちる

LEFT JOIN したのに WHERE で右側の列を条件にすると、内部結合と同じになります。 「LEFT JOIN にしたのに行が減る」の原因は、ほぼこれです。

気づかないうちに、行が増える

今度は逆です。1人が複数のタスクを持っているので、繋ぐと増えます。

SELECT COUNT(*) FROM j_member;
SELECT COUNT(*)
FROM j_member m
LEFT JOIN j_task k ON m.member_id = k.member_id;
4
5

4 人が 5 行になりました。 「さとう」がタスクを2つ持っているためです。中身を見ます。

SELECT m.name, k.title
FROM j_member m
LEFT JOIN j_task k ON m.member_id = k.member_id;
+--------+------------+
| さとう | 画面を作る |
| さとう | APIを作る  |
| たなか | 手順書を書く |
| すずき | 監視を足す |
| やまだ | NULL       |
+--------+------------+

これ自体は正しい結果です。 危ないのは、この状態で人の側の数字を集計したときです。

1対多を繋いだ結果は、「人の一覧」ではなく「人とタスクの組の一覧」です。 そのまま人数を数えると、多いほうの件数になります。

SELECT COUNT(*), COUNT(DISTINCT m.member_id)
FROM j_member m
LEFT JOIN j_task k ON m.member_id = k.member_id;
5    4

COUNT(*) は 5、実際の人数は 4。 DISTINCT を付けると正しく数えられます。 結合したあとの COUNT(*) は、たいてい意味を持ちません。

GROUP BY で集計する

人ごとの合計時間を出します。

SELECT m.name, SUM(k.hours) AS total
FROM j_member m
LEFT JOIN j_task k ON m.member_id = k.member_id
GROUP BY m.name;
+--------+-------+
| name   | total |
+--------+-------+
| さとう |    13 |
| たなか |     2 |
| すずき |     3 |
| やまだ |  NULL |
+--------+-------+

GROUP BY は「同じ値の行を1行にまとめる」命令です。まとめた結果、 1行に複数の値が入ってしまう列は、そのままでは選べません。

試します。

SELECT m.name, k.title, SUM(k.hours)
FROM j_member m
LEFT JOIN j_task k ON m.member_id = k.member_id
GROUP BY m.name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'appdb.k.title' which is not functionally
dependent on columns in GROUP BY clause

「さとう」は 1 行にまとまったのに、title は2つある。 どちらを出せばいいか 決められないので、拒否されました。これは親切なエラーです。

選べるのは2種類だけです。

選べる列理由
GROUP BY に書いた列まとめる基準なので、1グループに1つしかない
SUM() / COUNT() などでまとめた結果複数を1つの値に畳んでいる

古い MySQL では、この状態でもエラーにならず「どれか1つ」を勝手に返していました。 値が日によって変わる不具合の原因になるため、いまは既定で拒否されます。

WHERE と HAVING

「合計3時間より多い人」だけを出します。WHERE では書けません。

SELECT m.name, SUM(k.hours) AS total
FROM j_member m
LEFT JOIN j_task k ON m.member_id = k.member_id
WHERE SUM(k.hours) > 3
GROUP BY m.name;
ERROR 1111 (HY000): Invalid use of group function

WHERE はまとめる前に働くので、合計はまだ存在しません。 まとめた後に絞るのが HAVING です。

SELECT m.name, SUM(k.hours) AS total
FROM j_member m
LEFT JOIN j_task k ON m.member_id = k.member_id
GROUP BY m.name
HAVING total > 3;
+--------+-------+
| さとう |    13 |
+--------+-------+

順番は「繋ぐ → WHERE で絞る → まとめる → HAVING で絞る」。 この順番が、そのまま書ける場所を決めています。

どちらでも書ける条件は WHERE に書いてください。 まとめる前に減らしたほうが、 処理する行が少なくて済みます。

つまずいたところ

人数を数えたら、実際より多く出ました。 タスクと結合したあとの COUNT(*) を そのまま使っていたためです。結合したら、数える前に「何の一覧になっているか」を言葉にする ようにしてから、この間違いは無くなりました。

LEFT JOIN にしたのに行が減りました。 右側の列を WHERE で絞っていたためです。 LEFT JOIN の相手側に条件を付けるときは ON に書くと覚え直しました。

GROUP BY のエラーを、設定を変えて黙らせようとしました。 通ることは通りますが、 返る値が実行のたびに変わりうると知って戻しました。エラーのほうが安全です。

後片づけ

この記事で作った表を消します。j_ で始まる3つだけです。

DROP TABLE IF EXISTS j_task;
DROP TABLE IF EXISTS j_member;
DROP TABLE IF EXISTS j_team;
SHOW TABLES;

本線で作った tasks / users が残っていることを確認してください。 消す順番は、参照している側から先です(ここでは外部キーを張っていないので順不同です)。

EXIT;

まとめ

  • JOIN は全部の組み合わせを作ってから ON で絞る。だから増えることも減ることもある
  • 内部結合は、相手がいない行を黙って落とす。 一覧が欲しいなら LEFT JOIN
  • LEFT JOIN の相手側の条件は ON に書く。 WHERE に書くと内部結合と同じになる
  • 1対多を繋いだあとの COUNT(*) は組の数。人数なら COUNT(DISTINCT ...)
  • まとめる前は WHERE、まとめた後は HAVING。どちらでも書けるなら WHERE

参考リンク

次に読む

この記事は 【第5段階】バックエンドとデータベース の 4 / 29 本目です。

記事
← 前の記事プロセスを止めても消えない場所 — MySQLとSQLの基本
→ 次の記事MySQLに別のサーバーから接続する — bind-addressとユーザーの接続元

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次