第4章 SQL入門② — 集計と結合

この章も第3章で作った company.db を使います。sqlite3 company.db で開き、.mode column と .headers on を実行してから進んでください。

4.1 集計関数 — 行の集まりを1つの値にする

これまでのSELECTは「行を選んで表示する」ものでした。集計関数は発想が違い、複数の行をまとめて1つの値を計算します。代表は次の5つです。

sqlite> SELECT COUNT(*) AS 社員数, SUM(salary) AS 給与総額, AVG(salary) AS 平均給与
   ...> FROM employees;
社員数  給与総額  平均給与
------  --------  ----------------
6       2030000   338333.333333333
sqlite> SELECT MAX(salary) AS 最高, MIN(salary) AS 最低 FROM employees;
最高    最低
------  ------
400000  280000

COUNT(*) と COUNT(列) の違い — またNULLの話

ここで第3章のNULLが再登場します。COUNT(*) は行そのものを数えますが、COUNT(列) はその列がNULLの行を数えません。

sqlite> SELECT COUNT(*) AS 全社員, COUNT(dept_id) AS 配属済み FROM employees;
全社員  配属済み
------  --------
6       5

渡辺さん(dept_id が NULL)は COUNT(dept_id) に数えられていません。この性質は「NULLでない行だけ数えたい」ときには便利ですが、知らずに使うと「社員数が合わない」という混乱のもとになります。AVG(列)も同様にNULLの行を無視して平均します。「NULLを0として平均したい」場合とは結果が変わるので、集計対象の列にNULLがありうるかは常に確認してください。

4.2 GROUP BYでグループ集計

「全社の平均給与」ではなく「部署別の平均給与」が欲しい——そこで GROUP BY です。指定した列の値が同じ行をグループにまとめ、グループごとに集計関数を適用します。

sqlite> SELECT dept_id, COUNT(*) AS 人数, AVG(salary) AS 平均給与
   ...> FROM employees
   ...> GROUP BY dept_id;
dept_id  人数  平均給与
-------  ----  --------
         1     300000.0
10       2     300000.0
20       2     365000.0
30       1     400000.0

6行の社員データが、部署ごとの4行に集約されました。1行目のdept_idが空欄なのは、NULL(配属未定)も1つのグループとしてまとめられているからです。渡辺さん1人のグループです。

4.3 HAVING — 集計結果をさらに絞る

「部署別の平均給与のうち、35万円以上の部署だけ見たい」。この条件は平均を計算し終わったあとでないと判定できないので、WHEREでは書けません。集計結果への条件は HAVING に書きます。

sqlite> SELECT dept_id, AVG(salary) AS 平均給与
   ...> FROM employees
   ...> GROUP BY dept_id
   ...> HAVING AVG(salary) >= 350000;
dept_id  平均給与
-------  --------
20       365000.0
30       400000.0

WHEREとHAVINGの違いは実行されるタイミングです。SQLは書いた順ではなく、おおむね次の順で処理されます。

「行の条件はWHERE、グループの条件はHAVING」。たとえば「2020年以降入社の社員だけを対象に、部署別平均給与が35万以上の部署」なら、入社日の条件はWHERE(行の絞り込み)、平均の条件はHAVING(グループの絞り込み)に書きます。WHEREで先に行を減らすほうが処理も速くなります。

4.4 JOIN — テーブルを結合する(このコース最大の山場)

ここからがこのコースの最大の山場です。逆に言えば、JOINが分かればリレーショナルデータベースの核心を理解したことになります。

いま社員一覧を表示すると、部署は「10」「20」という数字で出てきます。人間が読みたいのは「総務部」「営業部」という名前です。名前は departments テーブルにあります。2つの表を、dept_id と id の対応でつなげて1つの表にする——これが結合(JOIN)です。

SELECT e.name AS 社員, d.name AS 部署
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

新しい書き方を分解します。

• employees e / departments d — テーブルに短い別名を付けています。以後 e.name(社員テーブルのname)、d.name(部署テーブルのname)のように「どちらのテーブルの列か」を明示できます。両テーブルに同名のname列があるので、この区別は必須です。
• INNER JOIN ... ON ... — 「departmentsを、e.dept_id = d.id という条件が成り立つ行同士でつなげる」という宣言です。

DBMSの中で起きることを段階的に見てみましょう。社員の各行について、ON条件に合う部署の行を探して横に並べるイメージです。

実行結果です。

sqlite> SELECT e.name AS 社員, d.name AS 部署
   ...> FROM employees e
   ...> INNER JOIN departments d ON e.dept_id = d.id;
社員       部署
---------  --------------
佐藤 花子  総務部
鈴木 一郎  営業部
田中 実    総務部
高橋 美咲  情報システム部
伊藤 健    営業部

部署名が表示できました。しかし気づいたでしょうか——社員は6人いるのに、5行しかありません。INNER JOINは「ON条件の相手が両方に見つかった行だけ」を返すため、dept_id がNULLの渡辺さんは相手が見つからず、結果から静かに消えたのです。

4.5 LEFT JOIN — 相手がいなくても残す

「全社員の一覧に部署名を添えたい。配属未定の人も一覧から漏らしたくない」——このときは LEFT JOIN(LEFT OUTER JOIN)を使います。左側(FROMに書いたほう)のテーブルの行は、相手が見つからなくても全部残し、相手側の列にはNULLを入れます。

sqlite> SELECT e.name AS 社員, d.name AS 部署
   ...> FROM employees e
   ...> LEFT JOIN departments d ON e.dept_id = d.id;
社員       部署
---------  --------------
佐藤 花子  総務部
鈴木 一郎  営業部
田中 実    総務部
高橋 美咲  情報システム部
伊藤 健    営業部
渡辺 由紀  

6行になり、渡辺さんが戻ってきました。部署の列はNULL(表示上は空欄)です。

4.6 サブクエリ入門 — 問い合わせの入れ子

「平均給与より高い給与の社員」を出したいとします。WHERE句に平均額を書きたいのですが、その平均額自体がSELECTしないと分かりません。こんなときは、SQLの中にカッコで別のSQLを埋め込みます。これがサブクエリ(副問い合わせ)です。

sqlite> SELECT name, salary FROM employees
   ...> WHERE salary > (SELECT AVG(salary) FROM employees);
name       salary
---------  ------
鈴木 一郎  350000
高橋 美咲  400000
伊藤 健    380000

カッコの中が先に実行されて平均 338333.33… という1つの値になり、外側は WHERE salary > 338333.33… として評価されます。「まず内側、次に外側」と読むのがコツです。

第3章のINと組み合わせると、「値の一覧」を返すサブクエリも使えます。部署IDを覚えていなくても、部署名から社員を引けます。

sqlite> SELECT name FROM employees
   ...> WHERE dept_id IN (SELECT id FROM departments WHERE name = '営業部');
name
---------
鈴木 一郎
伊藤 健

この例はJOINでも書けます。サブクエリとJOINはしばしば交換可能で、どちらが読みやすいかで選んで構いません。まずは「カッコの中に小さなSELECTを入れられる」という感覚をつかめば十分です。

SELECT ip_address, COUNT(*) AS 失敗回数
FROM access_log
WHERE result = 'FAIL'
GROUP BY ip_address
ORDER BY 失敗回数 DESC
LIMIT 10;

まとめ

• 集計関数(COUNT・SUM・AVG・MAX・MIN)は複数の行を1つの値にまとめる。COUNT(列)とAVG(列)はNULLの行を数えない
• GROUP BYで「◯◯別の集計」ができる。SELECTに書けるのはGROUP BYした列と集計関数だけ、が鉄則(SQLiteはエラーにしないので特に注意)
• 行の条件はWHERE(グループ化の前)、集計結果への条件はHAVING(グループ化の後)。実行順序は FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
• INNER JOINはON条件の相手が見つかった行だけを返す。相手がいない行も残したいならLEFT JOIN(相手側の列はNULLになる)
• サブクエリはSQLの中に埋め込む小さなSELECT。「まず内側、次に外側」と読む
• GROUP BYはログ分析・セキュリティ監視の基本技。「IP別ログイン失敗TOP10」はWHERE+GROUP BY+ORDER BY+LIMITで書ける

練習問題

いずれも company.db に対して実際にSQLを実行し、結果を確かめてください。

SELECT d.name AS 部署, AVG(e.salary) AS 平均給与
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
GROUP BY d.name
HAVING AVG(e.salary) >= 350000;

部署            平均給与
--------------  --------
営業部          365000.0
情報システム部  400000.0

SELECT d.name AS 部署, COUNT(e.id) AS 人数
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.id, d.name;

部署            人数
--------------  ----
総務部          2
営業部          2
情報システム部  1
経理部          0

SELECT e.name AS 社員, e.salary AS 給与, d.name AS 部署
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE e.salary > (SELECT AVG(salary) FROM employees)
ORDER BY e.salary DESC;

社員       給与    部署
---------  ------  --------------
高橋 美咲  400000  情報システム部
伊藤 健    380000  営業部
鈴木 一郎  350000  営業部