部署ごとの人件費を一度で
経理の課長が聞きました。部署ごとに何人が働いていて、給与の合計はいくらですか。
人の情報は1つの表にあり、部署の名前は別の表にあります。答えるには両方が要ります。
結合と集計は、こういう質問にデータベースが答える方法です。
このレッスンのゴール
メモ
表を結合して、集計できる。
重要な言葉
| ことば | いみ | れい |
|---|---|---|
| 結合 | キーが合う行を、2つの表から横に並べること | |
| 別名 | クエリの中で、列や表に付ける短い名前。エイリアスとも言う | AS payroll |
| 集計関数 | 多くの行を1つの値にまとめる関数 | SUM(pay) |
| GROUP BY | 値の種類ごとに、結果の行を1つ作る | |
| HAVING | テストに通ったグループだけを残す | |
| 左外部結合 | 最初の表の行を全部残す結合。LEFT JOIN と書く |
学生が入力するセルは、このプレビューでは空白です。
結合は2つの表を並べる
データは、同じことを2度書かないように、表に分けてあります。結合は、それをまた1つにします。
ON は、どの列が合うかを決めます。普通は、片方の外部キーと、もう片方の主キーです。
2つの表に同じ名前の列があるときは、表の名前を前に付けます。suppliers.city のように書きます。
準備の行を Run しましょう。前回と同じです。
証明書を受け入れる設定
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
ショップ用の短い名前
alias sql='mariadb -h db -u student -pstudent shop -t -e'ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
商品と、その仕入先の町
sql "SELECT products.name, suppliers.city FROM products JOIN suppliers ON suppliers.id = products.supplier_id WHERE suppliers.country = 'Japan' ORDER BY products.name"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
例: キーで合わせる
| staff.name | staff.dept_id | departments.id | departments.name |
|---|---|---|---|
| 佐藤 | 1 | 1 | 営業 |
| 高橋 | 2 | 2 | 保守 |
| 渡辺 | 3 | 3 | 法務 |
| dept_id が 9 の人 | 9 | 行がない | 普通の JOIN では消える |
学生が入力するセルは、このプレビューでは空白です。
GROUP BY は値ごとに1行
集計関数は、多くの行を1つの値にします。COUNT(*) は行を数えます。SUM、AVG、MIN、MAX は列に使います。
集計関数だけなら、表全体のまとめが1つ出ます。GROUP BY を付けると、列の値の種類ごとにまとめが出ます。
答えには AS で名前を付けます。
役職ごとの人数と給与
sql "SELECT job_title, COUNT(*) AS staff, SUM(salary) AS payroll FROM employees GROUP BY job_title ORDER BY job_title"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
先に結合して、次にまとめる
sql "SELECT suppliers.country, COUNT(*) AS items, SUM(products.stock) AS units FROM products JOIN suppliers ON suppliers.id = products.supplier_id GROUP BY suppliers.country ORDER BY suppliers.country"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
試す: 部署ごとにまとめる
-- 部署ごとの人数と、給与の合計を出します。
SELECT dept_id, COUNT(*) AS people, SUM(pay) AS payroll
FROM staff
GROUP BY dept_id
ORDER BY dept_id;
-- ORDER BY の前に HAVING COUNT(*) > 1 を足してみましょう。
HAVING はグループを調べる
WHERE は、グループにする前の1行ずつを調べます。その時点では、合計はまだありません。
HAVING は、グループができた後で、グループを調べます。集計関数と一緒に使います。HAVING COUNT(*) >= 25 のように書きます。
句の順番は、WHERE、GROUP BY、HAVING、ORDER BY です。
有効な注文を25件以上取った人
sql "SELECT employee_id, COUNT(*) AS orders FROM orders WHERE status <> 'cancelled' GROUP BY employee_id HAVING COUNT(*) >= 25 ORDER BY orders DESC"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
よくある間違い
注意
WHERE COUNT(*) >= 25 で、グループを絞れると思いがちです。WHERE の時点ではグループがまだないので、エラーになります。行には WHERE、グループには HAVING を使います。
その間違いを実際に試す
sql "SELECT employee_id, COUNT(*) AS orders FROM orders WHERE COUNT(*) >= 25 GROUP BY employee_id"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
左外部結合は合わない行も残す
普通の結合は、合う相手のない行を落とします。でも、合う相手がないことこそ、知りたいときがあります。
LEFT JOIN は、最初の表の行を全部残します。合う相手がなければ、相手側の列は NULL になります。
WHERE ... IS NULL を足すと、相手のない行だけが残ります。
カテゴリのない商品
sql "SELECT products.name, categories.name AS category FROM products LEFT JOIN categories ON categories.id = products.category_id WHERE categories.id IS NULL ORDER BY products.name"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
試す: 人と部署を並べる
-- 人の名前を、部署の名前の隣に並べます。
SELECT staff.name, departments.name AS dept
FROM staff
JOIN departments ON departments.id = staff.dept_id
ORDER BY staff.id;
-- ORDER BY の前に WHERE departments.name = '保守' を足してみましょう。
さらに挑戦
employees 表の manager_id 列は、同じ表の別の行を指しています。表をそれ自身と結合することを、自己結合と呼びます。
全員の名(first_name)を、その上司の名と並べて出してください。表に e と m のような2つの別名を付けます。社長には上司がいません。社長も残る結合を選んでください。