グループ単位での集計(GROUP BY)| プログラミングの砂場

プログラミング学習やタイピング練習に役立つツールを公開しているサイトです。

グループ単位での集計(GROUP BY)

「GROUP BY」を使うと、指定した列の値に基づいたデータの集計(グループ単位での集計)ができます。

「~ごとに集計」とか「~別で集計」とか「~単位で集計」という言葉がでてきたら「GROUP BY」を使います。

ここでは、「GROUP BY」の使い方について見ていきましょう。

目次

1. はじめに

まずは、Shouhinの内容を確認してください。

SELECT * FROM Shouhin;
shouhin_id name name_kana category_id price
1 アスパラガス あすぱらがす 1 256
2 かぼちゃ かぼちゃ 1 300
3 キャベツ きゃべつ 1 128


Shouhinの全データ(全行)が取得されました。

「GROUP BY」の例で使用するのは、「category_id」です。

「category_id」は、1が野菜、2が果物、空(NULL)が未定の商品ということを意味しています。

このデータに対して、「GROUP BY」を使用してグループ単位での集計を施していきます。

2. GROUP BYの使い方

「GROUP BY」は、指定された列の内、値のセットが同じ行を1つにまとめる(グループ化、集約する)働きがあります。

「GROUP BY」は、ほとんどの場合、集約関数とセットで使用します。

構文

SELECT
  グループ化対象列名,
  集約関数
FROM
  テーブル名
GROUP BY
  グループ化対象列名

「グループ化対象列名」は、「,(カンマ)」区切りで複数指定できます。

集約関数COUNTを使って商品カテゴリ別(未定、野菜、果物)の件数を取得してみます。

SELECT
    category_id,
    COUNT(*)
FROM
    Shouhin
GROUP BY
    category_id
category_id COUNT(*)
2
1 10
2 10

結果が3行しかないのは、「GROUP BY」で指定した列「category_id」の「値」が同じもので1つにまとめられて(グループ化、集約されて)いるからです。

集約関数の「COUNT(*)」は、「category_id」の値ごとにグループ化された行数をカウントしています。

結果、商品カテゴリが、「未定(空)の商品は2件」、「野菜は10件」、「果物は10件」登録されていることがわかります。

次に、集約関数AVGを使って商品カテゴリ別の平均価格を取得してみます。

SELECT
    category_id,
    AVG( price )
FROM
    Shouhin
GROUP BY
    category_id
category_id AVG( price )
3500.0000
1 186.8000
2 524.6000

こちらも、「GROUP BY」で指定した列「category_id」の「値」が同じもので1つにまとめられて(グループ化、集約されて)いるので3行になっています。

集約関数の「AVG( price )」は、「category_id」の値ごとにグループ化されたprice(値段)の平均値を計算しています。

結果、商品カテゴリごとの平均価格が取得できました。

次に、WHERE句を織り交ぜて野菜の値段ごとの件数を取得してみます。

野菜は、「category_id=1」の商品です。

SELECT
    price,
    COUNT( * )
FROM
    Shouhin
WHERE
    category_id = 1
GROUP BY
    price
price COUNT( * )
100 1
128 2
130 1


こちらは、まずWHERE句によって「GROUP BY」の対象を野菜「category_id=1」の行に絞っています。

その後、「GROUP BY」で指定した列「price」の「値」が同じもので1つにまとめられて(グループ化、集約されて)いるので9行になっています。

集約関数の「COUNT( * )」は、「price」の値ごとにグループ化された行数をカウントしています。

結果、野菜の値段ごとの件数を取得できました。128円の野菜が2件あることがわかります。

最後に、グループ化対象列を2つ指定して「GROUP BY」を使ってみます。

集約関数COUNTと使って、「商品カテゴリ別、値段別」の件数を取得してみます。

SELECT
    category_id,
    price,
    COUNT( * )
FROM
    Shouhin
GROUP BY
    category_id,
    price
category_id price COUNT( * )
3000 1
4000 1
1 100 1
1 128 2


こちらは、「GROUP BY」で指定した列「category_id」と「price」の「値のセット」が同じもので1つにまとめられて(グループ化、集約されて)いるので21行になっています。

「category_id」と「price」の「値のセット」が同じもので集約されていることに注意してください。

ここでいう「値のセット」とは、「category_id」と「price」の値の組み合わせということです。

集約関数の「COUNT( * )」は、「category_id」と「price」の「値のセット」ごとにグループ化された行数をカウントしています。

結果、「商品カテゴリ別、値段別」の件数を取得できました。Shouhinテーブル全体で、同じ値段の商品は、野菜に2つあるということがわかります。

3. 「GROUP BY」使用時の注意点

「GROUP BY」の構文をもう一度確認してみましょう。

構文

SELECT
  グループ化対象列名,
  集約関数
FROM
  テーブル名
GROUP BY
  グループ化対象列名

「GROUP BY」を使う上で注意しなければいけないのは、 SELECT句の「グループ化対象列名」は、「GROUP BY」で指定した「グループ化対象列名」しか指定できないという点です。

集約関数に渡す列については、「GROUP BY」で指定した「グループ化対象列名」以外の列名を渡すことができます。

また、集約関数COUNTに限っては、「*(アスタリスク)」が使えます。

例えば下のようなSQLはエラーになります。
SELECT
  A,  ← GROUP BY に指定されていない列を使っている!
  count(*)  ← これはOK
FROM
  Hoge
GROUP BY
  B

「GROUP BY」で「B」列を指定しているのに、SELECT句で「A」列を指定しています。

SELECT
  A,
  B,
  C,  ← GROUP BY に指定されていない列を使っている!
  SUM( D )  ← これはOK
FROM
  Hoge
GROUP BY
  A,
  B

「GROUP BY」で「A,B」列を指定しているのに、SELECT句で「C」列を指定しています。

このように、「GROUP BY」に無い列をSELECT句で指定すると大抵のテータベースではエラーになります。

ただ厄介なことに、最近のMySQLやMariaDBなどはエラーにはなりません。(このサイトの砂場SQLはMariaDBです。)

エラーにならなくとも、意図しないデータが取得されたりしますので、このような使い方は避けた方が無難です。

4. まとめ

「GROUP BY」を使って、グルーブ単位での集計を見てきました。

「GROUP BY」を使うと、「~ごとに集計」するといったことができます。

「GROUP BY」は、普通、「集約関数」とセットで使用します。

「GROUP BY」には、「グループ化対象列名」を指定します。

SELECT句には、「GROUP BY」で指定した「グループ化対象列名」が使えます。
中には「GROUP BY」で指定した列以外の列を使用できるデータベースがあるので注意)


次は、「グループ単位で集計した結果の条件指定(HAVING)」についてです。
TOP