MENU

SQL入門 STEP10｜GROUP BYでグループごとに集計しよう

karin@taishi@felmia

商品をグループに分けて集計したい

STEP9では、集計関数を使って商品の件数や平均価格などを調べました。

例えば、商品の件数を調べるなら、

SELECT COUNT(*)
FROM products;

平均価格なら、

SELECT AVG(price)
FROM products;

と書きました。

これでテーブル全体の集計はできます。

では、商品に「PC」「周辺機器」「アクセサリ」といったカテゴリーがあったらどうでしょう。

単純に商品の平均価格を出すだけでなく、

「カテゴリーごとの商品数を知りたい」

「カテゴリーごとの平均価格を比べたい」

ということもありそうです。

こうしたグループごとの集計で使うのがGROUP BYです。

GROUP BYとは？

同じ値を持つデータをグループに分けるために使います。

集計関数と組み合わせることで、

  • カテゴリーごとの商品数
  • カテゴリーごとの平均価格
  • カテゴリーごとの最高価格

といった集計ができます。

ルミィ
ルミィ

全部まとめて計算するんじゃなくて、グループごとに分けてから計算するんだね♪

productsにカテゴリーを追加しよう

今使っているproductsテーブルには、

product_id
product_name
price

の3つのカラムがあります。

GROUP BYを試すために、今回は商品カテゴリーを表すcategoryというカラムを追加します。

すでに作ったテーブルへ新しいカラムを追加するときは、ALTER TABLEを使います。

Query Toolで次のSQLを実行してください。

ALTER TABLE products
ADD COLUMN category VARCHAR(50);

実行すると、productsテーブルにcategoryが追加されます。

ALTER TABLEとは？

すでに存在するテーブルの構造を変更するときに使います。

今回は、

ADD COLUMN category VARCHAR(50)

として、categoryという新しいカラムを追加しています。

ALTER TABLEにはほかにも使い方がありますが、今回は「後からカラムを追加できる」と分かれば十分です。

商品にカテゴリーを設定しよう

カラムを追加しただけでは、categoryにはまだ値が入っていません。

そこで、これまで使ってきたUPDATEを使ってカテゴリーを設定します。

UPDATE products
SET category = 'PC'
WHERE product_id = 1;

UPDATE products
SET category = '周辺機器'
WHERE product_id IN (2, 3, 4, 6, 7);

UPDATE products
SET category = 'アクセサリ'
WHERE product_id IN (5, 8);

ここで初めて出てきたのが、

WHERE product_id IN (2, 3, 4, 6, 7)

という書き方です。

INを使うと、複数の値をまとめて条件に指定できます。

これは、

WHERE product_id = 2
   OR product_id = 3
   OR product_id = 4
   OR product_id = 6
   OR product_id = 7

と同じような条件です。

今回の主役はGROUP BYなので、INを細かく覚える必要はありません。

「複数の商品IDをまとめて指定している」と分かればOKです。

カテゴリーが入ったか確認しよう

UPDATEが終わったら、SELECTで確認します。

SELECT *
FROM products
ORDER BY product_id ASC;

記事どおりに進めていれば、おおよそ次のような状態になります。

product_id | product_name    | price  | category
-----------+-----------------+--------+-----------
1          | ノートPC         | 120000 | PC
2          | マウス           | 3000   | 周辺機器
3          | キーボード        | 8000   | 周辺機器
4          | モニター          | 30000  | 周辺機器
5          | USBメモリ        | 2000   | アクセサリ
6          | Webカメラ        | 7000   | 周辺機器
7          | ヘッドセット      | 7000   | 周辺機器
8          | HDMIケーブル 2m   | 1800   | アクセサリ

これでGROUP BYを試す準備ができました。

カテゴリーごとの商品数を調べてみよう

それではGROUP BYを使います。

今回は、

「カテゴリーごとに商品が何件あるのか」

を調べてみましょう。

SELECT category, COUNT(*)
FROM products
GROUP BY category;

実行すると、カテゴリーごとに商品の件数が表示されます。

category   | count
-----------+------
PC         | 1
アクセサリ  | 2
周辺機器    | 5

STEP9の、

SELECT COUNT(*)
FROM products;

では、products全体をまとめて数えていました。

今回は、

GROUP BY category

が追加されています。

これによって、同じcategoryを持つ商品をひとまとまりにして、それぞれの件数を数えています。

ルミィ
ルミィ

8件まとめて数えるんじゃなくて、カテゴリーごとに分けて数えてくれたんだね♪

GROUP BYの書き方を確認しよう

基本的な形は次のようになります。

SELECT グループにするカラム, 集計関数
FROM テーブル名
GROUP BY グループにするカラム;

今回なら、

SELECT category, COUNT(*)
FROM products
GROUP BY category;

です。

categoryが、

PC
周辺機器
アクセサリ

というグループに分かれ、それぞれにCOUNTが実行されるイメージです。

GROUP BYの考え方

GROUP BY category

とすると、同じカテゴリーの商品が同じグループになります。

そのグループに対して、

COUNT(*)

を使えば件数、

AVG(price)

を使えば平均価格を求められます。

カテゴリーごとの平均価格を調べてみよう

COUNTだけでなく、STEP9で学んだほかの集計関数も使えます。

今度はカテゴリーごとの平均価格を求めてみます。

SELECT category, AVG(price)
FROM products
GROUP BY category;

これで、

PCの平均価格

周辺機器の平均価格

アクセサリの平均価格

がそれぞれ計算されます。

STEP9ではproducts全体の平均を1つだけ求めましたが、GROUP BYを使うことでグループごとの平均を出せるようになりました。

ASを使って結果を分かりやすくしよう

STEP9で使ったASも組み合わせてみます。

SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS average_price
FROM products
GROUP BY category;

これなら、

  • カテゴリー
  • 商品数
  • 平均価格

を一度に確認できます。

結果の名前も、

category | product_count | average_price

となるので、何を集計したのか分かりやすくなります。

SQLは動けばそれで終わりではなく、後から見たときに何をしているか分かる書き方も大切です。

合計・最大・最小もグループごとに求められる

SUM、MAX、MINも同じように使えます。

例えば、カテゴリーごとの最高価格を調べるなら、

SELECT
    category,
    MAX(price) AS max_price
FROM products
GROUP BY category;

と書けます。

カテゴリーごとの最低価格なら、

SELECT
    category,
    MIN(price) AS min_price
FROM products
GROUP BY category;

です。

STEP9で覚えた集計関数が、そのままGROUP BYでも使えていますね。

ここで新しい集計関数を覚えているわけではありません。

「今までテーブル全体に使っていた集計関数を、グループごとに使えるようになった」

と考えると分かりやすいです。

WHEREとGROUP BYを組み合わせてみよう

WHEREとGROUP BYを一緒に使うこともできます。

例えば、

「5000円以上の商品だけを対象にして、カテゴリーごとの商品数を調べたい」

とします。

SELECT
    category,
    COUNT(*) AS product_count
FROM products
WHERE price >= 5000
GROUP BY category;

ここでは、まず、

WHERE price >= 5000

で対象の商品を絞っています。

その後、

GROUP BY category

でカテゴリーごとに分けてCOUNTしています。

書く順番にも注目してください。

SELECT
FROM
WHERE
GROUP BY

WHEREを使う場合は、GROUP BYより前に書きます。

ORDER BYも一緒に使ってみよう

さらに、集計した結果を並び替えることもできます。

例えば、平均価格が高いカテゴリーから表示してみます。

SELECT
    category,
    AVG(price) AS average_price
FROM products
GROUP BY category
ORDER BY average_price DESC;

ここでは、

AVG(price) AS average_price

と名前を付けているので、そのaverage_priceをORDER BYで使っています。

書く順番は、

SELECT
FROM
WHERE
GROUP BY
ORDER BY

です。

SQLが少し長くなってきましたが、一つひとつはこれまで学んできた内容です。

ルミィ
ルミィ

WHEREで絞って、GROUP BYで分けて、ORDER BYで並べる！
今まで覚えたSQLがつながってきたね♪

SELECTに書くカラムには注意しよう

GROUP BYを使い始めたときに、つまずきやすいところがあります。

例えば、次のSQLです。

SELECT category, product_name, COUNT(*)
FROM products
GROUP BY category;

このSQLはPostgreSQLではエラーになります。

categoryでグループに分けているのに、product_nameをそのままSELECTしているためです。

例えば「周辺機器」には、

マウス
キーボード
モニター
Webカメラ
ヘッドセット

と複数の商品があります。

この状態で、

「周辺機器の商品名を1つ表示して」

と言われても、どの商品名を表示すればいいのか決められません。

一方、

SELECT category, COUNT(*)
FROM products
GROUP BY category;

なら問題ありません。

categoryはグループ分けに使い、COUNT(*)はそのグループ全体を数えているからです。

GROUP BYを使うときのSELECT

基本的には、

  • GROUP BYに指定しているカラム
  • COUNTやAVGなどの集計関数

をSELECTに指定します。

最初のうちは、この形を基本にすると分かりやすいです。

WHEREとGROUP BYの役割を混同しないようにしよう

WHEREとGROUP BYは、一緒に登場することがありますが役割は違います。

WHERE price >= 5000

は、どのデータを対象にするかを決めています。

一方、

GROUP BY category

は、対象になったデータをどの単位でグループに分けるかを決めています。

例えば、

SELECT category, COUNT(*)
FROM products
WHERE price >= 5000
GROUP BY category;

なら、

  1. 5000円以上の商品を対象にする
  2. その商品をカテゴリーごとに分ける
  3. 各カテゴリーの商品数を数える

という流れです。

SQLが長くなって分からなくなったときは、上から順番に「ここでは何をしている？」と確認してみると読みやすくなります。

よくある間違い

GROUP BYに指定していないカラムをそのままSELECTする

SELECT category, product_name, COUNT(*)
FROM products
GROUP BY category;

このように、グループ化していないproduct_nameをそのまま取得しようとするとエラーになります。

まずは、

SELECT category, COUNT(*)
FROM products
GROUP BY category;

の形を基本にしましょう。

WHEREとGROUP BYの順番を逆にする

正しい順番は、

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
ORDER BY ...;

です。

これまで学んだWHEREやORDER BYの間にGROUP BYが入る形になります。

GROUP BYだけで件数が数えられると思ってしまう

GROUP BYは、データをグループに分けるためのものです。

件数を数えたい場合は、

COUNT(*)

平均を出したいなら、

AVG(price)

のように集計関数と組み合わせます。

STEP10のまとめ

STEP10のポイント

  • GROUP BYを使うと同じ値を持つデータをグループに分けられる
  • COUNTやAVGなどの集計関数と組み合わせて使う
  • カテゴリーごとの件数や平均などを求められる
  • WHEREで対象を絞ってからGROUP BYすることもできる
  • ORDER BYを使って集計結果を並び替えられる
  • GROUP BYを使うときはSELECTするカラムに注意する
  • SQLを書く順番はWHERE → GROUP BY → ORDER BY

GROUP BYが入ると、SQLが少し長くなります。

ただ、今回使ったCOUNTやAVG、WHERE、ORDER BYはすべてこれまでに出てきたものです。

新しく覚えるGROUP BYをそこへ一つ足しただけ、と考えてみてください。

最初から長いSQLを一気に書こうとせず、

「まず集計できるか」

「次にカテゴリーで分けられるか」

「最後に並び替えられるか」

と少しずつ組み立てると、間違いにも気づきやすくなります。

ルミィ
ルミィ

STEP10クリア！
カテゴリーごとに分けて、商品の数や平均価格を調べられるようになったね♪

次のSTEPへ

ここまでは、1つのproductsテーブルを使ってSQLを学んできました。

実際のデータベースでは、すべての情報を1つのテーブルに詰め込むのではなく、複数のテーブルに分けて管理することがあります。

そこで重要になるのが、主キーと外部キーです。

STEP2で少し触れたPRIMARY KEYも、ここでもう一度しっかり確認します。

次のSTEPでは新しいテーブルも作りながら、テーブル同士がどのようにつながるのかを見ていきましょう。

→ STEP11「主キーと外部キーを学ぼう」

学習内容を確認したいときは、SQL入門の一覧から各STEPを確認できます。

→ SQL入門｜初心者が基礎から学べるSQL学習ガイド

記事URLをコピーしました