売れた数を1つの数にする
SQLの GROUP BY は、同じ値を持つ行を1つのグループにまとめ、グループごとに数や合計を出す句です。1行ずつ読む代わりに、全体の傾向を1つの数で答えられます。
カフェの店長は、今週いくつ売れたかや、どの種類が人気かを知りたがります。1件ずつ数えるのは大変ですが、SQLなら一度で答えが出ます。
このレッスンのゴール
メモ
データを数えて、まとめて、答えを出せる。
キーワード
| ことば | いみ | れい |
|---|---|---|
| 集計関数 | 複数の行から、1つの値を作る関数 | |
| COUNT | 行の数を数える | COUNT(*) |
| SUM と AVG | 合計と平均を出す | SUM(qty) |
| GROUP BY | 同じ値の行を、グループにまとめる句 | |
| HAVING | まとめたあとのグループを、条件で絞る句 | |
| 別名 | 結果の列に付ける読みやすい題 | AS total |
学生が入力するセルは、このプレビューでは空白です。
集計関数は1つの値を返す
COUNT(*) は行の数、SUM(列) は合計、AVG(列) は平均、MAX と MIN は一番大きい値と小さい値を返します。
行がたくさんあっても、答えは1行です。結果の列には、AS で読みやすい題を付けます。
試す: 売れた個数を集計する
-- 売れた個数の合計と、1回の販売の平均を出します。
SELECT COUNT(*) AS lines, SUM(qty) AS total, AVG(qty) AS average
FROM sold;
-- MAX(qty) と MIN(qty) も、同じ SELECT に足してみましょう。
例: 集計関数の答え
| 式 | 3, 5, 4 の列に使うと |
|---|---|
| COUNT(*) | 3 |
| SUM(qty) | 12 |
| AVG(qty) | 4 |
| MAX(qty) | 5 |
| MIN(qty) | 3 |
学生が入力するセルは、このプレビューでは空白です。
始める前に
メモ
上のバーの Start the lab を押し、マシンが動き出すまで待ってください。ターミナルを開き直したときは、最初の2つの Run をもう一度押します。
証明書を受け入れる設定
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
ショップ用の短い名前
alias sql='mariadb -h db -u student -pstudent shop -t -e'ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
商品の数を数える
sql "SELECT COUNT(*) AS products FROM products"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
GROUP BY でグループごとに数える
GROUP BY に列を書くと、その列の値が同じ行が、1つのグループになります。集計関数は、グループごとに1つの値を返します。
SELECT には、グループにした列と、集計関数だけを書きます。次の表は、カフェの販売を曜日でまとめたものです。
試す: 曜日ごとに合計する
-- 曜日ごとの、売れた個数の合計を出します。
SELECT day, SUM(qty) AS total
FROM sold
GROUP BY day
ORDER BY total DESC;
-- 曜日の代わりに menu_id でまとめる形に変えてみましょう。
国ごとの顧客の数
sql "SELECT country, COUNT(*) AS customers FROM customers GROUP BY country ORDER BY customers DESC, country"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
HAVING はまとめたあとに絞る
WHERE は、まとめる前に、行を絞ります。HAVING は、まとめたあとに、グループを絞ります。
「合計が10個以上の曜日だけ」のように、集計の答えで絞りたいときは、HAVING を使います。WHERE には、集計関数を書けません。
例: 句が働く順番
| 順 | 句 | 何をするか |
|---|---|---|
| 1 | FROM と JOIN | 読む表を決めて、つなぐ |
| 2 | WHERE | 行を絞る |
| 3 | GROUP BY | グループにまとめる |
| 4 | HAVING | グループを絞る |
| 5 | ORDER BY と LIMIT | 並べて、切る |
学生が入力するセルは、このプレビューでは空白です。
顧客が5人以上の国
sql "SELECT country, COUNT(*) AS customers FROM customers GROUP BY country HAVING COUNT(*) >= 5 ORDER BY customers DESC, country"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
まちがった書き方を試す
sql "SELECT country FROM customers WHERE COUNT(*) >= 5 GROUP BY country"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
よくある間違い
注意
集計の答えで絞りたいのに、WHERE に書いてしまうことがよくあります。WHERE は、まとめる前の行を見るので、集計関数を使うとエラーになります。集計の答えで絞るときは、HAVING です。
さらに挑戦: 平均の落とし穴
ショップの商品には、category_id が NULL のものがあります。GROUP BY category_id でまとめると、NULL の行は、1つのグループになります。
カテゴリごとの商品の数を出し、NULL のグループがあるかを確かめてください。次に、COUNT(category_id) と COUNT(*) の結果を並べ、どう違うかを説明してください。