店長の質問に数字で答える
カフェの店長が聞きます。「先週、一番売れた商品は何？ 売上はいくら？」
1行ずつ見て足していくと、売上の行が数千あれば何時間もかかります。SQL の集計を使うと、データベースが数秒で答えます。
このレッスンのゴール
メモ
店長の質問に、集計のクエリで答えることができる。
キーワード
| ことば | いみ | れい |
|---|---|---|
| 集計 | 多くの行を、合計や件数などの数にまとめること | |
| 集計関数 | 行をまとめて1つの値を出す関数 | SUM |
| GROUP BY | 同じ値の行を1つのグループにまとめる句 | |
| HAVING | まとめたあとのグループを、条件で絞る句 | |
| 結合（JOIN） | 2つの表を、共通の列でつなぐこと |
学生が入力するセルは、このプレビューでは空白です。
集計関数は多くの行を1つにする
COUNT は行の数、SUM は合計、AVG は平均、MAX は最大、MIN は最小を出します。
COUNT(*) は、条件に合う行の数を数えます。SUM(qty) は、qty の列の値を全部足します。答えは1行だけの結果になります。
試す: 売上を1つの数にまとめる
-- 全部の行を、1つの数にまとめます。
SELECT COUNT(*) AS lines, SUM(qty) AS cups, MAX(price) AS top_price
FROM sales;
-- SUM(qty) を AVG(qty) に変えて、もう一度実行しましょう。
GROUP BY でグループごとに集計する
店長が知りたいのは、全体ではなく商品ごとの数です。GROUP BY item と書くと、同じ item の行が1つのグループになります。
集計関数は、グループごとに1回働きます。結果は、グループ1つにつき1行です。
例: 3つの行が2つのグループになる
| もとの行 | グループ | 杯数の合計 |
|---|---|---|
| コーヒー 3杯、コーヒー 4杯 | コーヒー | 7 |
| ラテ 2杯 | ラテ | 2 |
学生が入力するセルは、このプレビューでは空白です。
試す: 商品ごとの杯数
-- 商品ごとに行をまとめて、売れた数を足します。
SELECT item, SUM(qty) AS cups
FROM sales
GROUP BY item
ORDER BY cups DESC;
-- GROUP BY item を GROUP BY sold_on に変えると、日ごとの数になります。
ラボの短い名前（準備）
alias sql='mariadb -h db -u student -pstudent shop -t -e'ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
国ごとの仕入先の数
sql "SELECT country, COUNT(*) AS suppliers FROM suppliers GROUP BY country ORDER BY country"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
HAVING はまとめたあとに絞る
WHERE は、まとめる前の行を絞ります。HAVING は、まとめたあとのグループを絞ります。
「仕入先が2社以上ある国」は、数えたあとでないと分かりません。だから HAVING COUNT(*) > 1 と書きます。
2社以上ある国だけ残す
sql "SELECT country, COUNT(*) AS suppliers FROM suppliers GROUP BY country HAVING COUNT(*) > 1"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
よくある間違い
注意
WHERE COUNT(*) > 1 と書けば、数が2以上のグループが残ると考えがちです。WHERE は数える前に働くので、エラーになります。集計した値で絞るときは、必ず HAVING を使います。
JOIN で2つの表をつなぐ
商品の表には、カテゴリの番号だけが入っています。名前はカテゴリの表にあります。
JOIN ... ON は、共通の列が等しい行どうしをつなぎます。つないだ表にも GROUP BY が使えるので、カテゴリの名前ごとの数が出せます。
カテゴリ名ごとの商品の数
sql "SELECT c.name, COUNT(*) AS items FROM products p JOIN categories c ON p.category_id = c.id GROUP BY c.name ORDER BY items DESC, c.name"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
さらに挑戦: 日ごとの売上
同じ表で、日ごと（sold_on）の売上合計を出す GROUP BY を、ページ内の SQL で書いてみましょう。
売上は SUM(qty * price) です。結果を、売上の多い日が上に来るように並べてください。