演習: 集計とグループ化
bookstore データベースで練習します。最初に USE bookstore; で切り替えてください。
4-a. 書籍の総数を調べる
Section titled “4-a. 書籍の総数を調べる”在庫管理の基本指標として、books テーブルのレコード数を調べてください。カラム名は total とします。
期待される結果:
| total |
|---|
| 58 |
ヒント
COUNT(*) でテーブルの行数を返します。
4-b. 書籍の平均価格を調べる
Section titled “4-b. 書籍の平均価格を調べる”価格帯の傾向を把握するため、books テーブルから、平均価格を整数で四捨五入して調べてください。カラム名は avg_price とします。
期待される結果:
| avg_price |
|---|
| 1858 |
ヒント
AVG(列) で平均値を返します。ROUND(値) は桁数を省略すると整数に丸めます。
4-c. 漫画カテゴリの在庫金額を合計する
Section titled “4-c. 漫画カテゴリの在庫金額を合計する”books テーブルから、category_id が 7（漫画）の書籍の在庫金額（price * stock）の合計を調べてください。カラム名は total_value とします。
期待される結果:
| total_value |
|---|
| 172950 |
ヒント
SUM(式) の引数には列名だけでなく、price * stock のような計算式も書けます。WHERE で絞り込んでから集計します。
4-d. 最高価格と最低価格を同時に調べる
Section titled “4-d. 最高価格と最低価格を同時に調べる”books テーブルから、最高価格と最低価格を同時に調べてください。カラム名はそれぞれ max_price, min_price とします。
期待される結果:
| max_price | min_price |
|---|---|
| 5280 | 420 |
ヒント
1 つの SELECT 文で複数の集計関数を並べて使えます。
4-e. 著者の国の種類数を調べる
Section titled “4-e. 著者の国の種類数を調べる”著者の多様性を見るため、authors テーブルに登録されている国の種類数（重複なし）を調べてください。カラム名は country_count とします。
期待される結果:
| country_count |
|---|
| 5 |
ヒント
COUNT(DISTINCT 列) は、指定した列の値の中で重複を除いた数を返します。
4-f. カテゴリ別の書籍数を集計する
Section titled “4-f. カテゴリ別の書籍数を集計する”books テーブルから、カテゴリごとの書籍数を、書籍数の多い順、同数の場合はカテゴリ ID の昇順に調べてください。カラム名は category_id, book_count とします。
期待される結果:
| category_id | book_count |
|---|---|
| 1 | 18 |
| 3 | 12 |
| 2 | 8 |
| 6 | 7 |
| 5 | 5 |
| 4 | 4 |
| 7 | 4 |
ヒント
GROUP BY 列 で同じ値の行をひとつのグループにまとめ、COUNT(*) でグループごとの行数が得られます。SELECT 句に書ける列は GROUP BY で指定した列と、集計関数の結果のみです。
4-g. カテゴリ別の平均価格を求める
Section titled “4-g. カテゴリ別の平均価格を求める”books テーブルから、カテゴリごとの平均価格を小数第 1 位で四捨五入した値を、平均価格の高い順に調べてください。カラム名は category_id, avg_price とします。
期待される結果:
| category_id | avg_price |
|---|---|
| 3 | 3867.5 |
| 2 | 2012.5 |
| 4 | 1925.0 |
| 5 | 1806.2 |
| 6 | 1499.1 |
| 1 | 898.3 |
| 7 | 462.5 |
ヒント
GROUP BY と集計関数を組み合わせます。ORDER BY には SELECT で定義したエイリアスを直接使えます。
4-h. 2024 年の月別販売数を集計する
Section titled “4-h. 2024 年の月別販売数を集計する”月次の売れ行きの推移を把握します。sales テーブルから、2024 年の月別の販売数量の合計を、月の昇順に調べてください。月は「YYYY-MM」形式で、カラム名は sold_month、販売数量のカラム名は total_quantity とします。
期待される結果:
| sold_month | total_quantity |
|---|---|
| 2024-01 | 25 |
| 2024-02 | 26 |
| 2024-03 | 17 |
| 2024-04 | 27 |
| 2024-05 | 15 |
| 2024-06 | 18 |
| 2024-07 | 17 |
| 2024-08 | 34 |
| 2024-09 | 17 |
| 2024-10 | 20 |
| 2024-11 | 24 |
| 2024-12 | 16 |
ヒント
DATE_FORMAT(sold_at, '%Y-%m') で年月の文字列に変換できます。範囲指定は BETWEEN より >= '2024-01-01' AND < '2025-01-01' が安全です（2024-12-31 12:00:00 のような時刻を含むデータを取りこぼしません）。
4-i. 顧客が 3 人以上いる都道府県を調べる
Section titled “4-i. 顧客が 3 人以上いる都道府県を調べる”配送網の検討のため、顧客が集中している都道府県を特定します。customers テーブルから、顧客が 3 人以上いる都道府県を、顧客数の多い順、同数の場合は都道府県名の昇順に調べてください。カラム名は prefecture, customer_count とします。
期待される結果:
| prefecture | customer_count |
|---|---|
| 東京都 | 15 |
| 神奈川県 | 6 |
| 大阪府 | 5 |
| 愛知県 | 3 |
ヒント
HAVING は GROUP BY でまとめた後のグループに対する絞り込みです。集計関数の結果に対する条件は HAVING に書きます。「元の行で絞り込む」ときは WHERE、「グループで絞り込む」ときは HAVING。
4-j. 複数冊を持つ著者の書籍数と平均価格を調べる
Section titled “4-j. 複数冊を持つ著者の書籍数と平均価格を調べる”著者ごとの作品量と価格帯を把握します。books テーブルから、書籍を 2 冊以上登録している著者を対象に、author_id、書籍数、平均価格（小数第 1 位で四捨五入）を、書籍数の多い順、同数の場合は author_id の昇順に調べてください。カラム名はそれぞれ book_count, avg_price とします。
期待される結果:
| author_id | book_count | avg_price |
|---|---|---|
| 2 | 5 | 891.0 |
| 3 | 3 | 1037.7 |
| 23 | 3 | 2273.3 |
| 27 | 3 | 2273.3 |
| 1 | 2 | 583.0 |
| 4 | 2 | 1045.0 |
| 6 | 2 | 781.0 |
| 7 | 2 | 1034.0 |
| 9 | 2 | 2035.0 |
| 10 | 2 | 1945.0 |
| 26 | 2 | 1760.0 |
| 28 | 2 | 1210.0 |
| 29 | 2 | 627.0 |
| 30 | 2 | 440.0 |
ヒント
著者ごとにグループ化し、HAVING でグループの件数を絞り込みます。ORDER BY にカンマ区切りで複数キーを指定できます。