備忘録的プログラミングリファレンス

GROUP BY 句　グループ化

　GROUP BY は SELECT クエリによって抽出したデータをグループ化するための句です。

GROUP BY
> SELECT goods_id FROM sales_slip GROUP BY goods_id;
+----------+
| goods_id |
+----------+
|        1 |
|        2 |
|        3 |
+----------+

　GROUP BY 句で指定するカラム名は複数指定することもできます。また、式を指定することもできます。

　ただし、注意点として取得するカラムにグループ化できないものを含むことができません。 例えば、併せて id を取得するしようとすると対象にできないカラムが含まれているというエラーが返されます。

GROUP BY のエラー（ MySQL ）
> SELECT id, goods_id FROM sales_slip GROUP BY goods_id;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.sales_slip.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

　id は行によって違うためです。

　グループ化によって以下のような集約関数をカラムに含めることができます。
　集計関数を使用することで商品ごとの数量を調べるや部署や店ごとの人数を調べるといった、グループごとの合計値やレコード数を調べることができます。

COUNT()
グループごとの総数
SUM()
指定されたカラムデータの合計値
AVG()
指定されたカラムデータの平均値
MAX()
最大値
MIN()
最小値

　集計関数とは、レコード数の取得や合計値、平均値を求めたりといった集計に関する関数です。集約関数はデータ分析などに便利で都度計算する手間が省けます。

　集約関数の引数には四則計算といった式を指定することもできます。

　GROUP BY には、WHERE 句や HAVING 句といった検索条件を使用することができます。ただし、 WHERE 句や HAVING 句には使い方に違いがあります。以下のHAVING 句による検索を参照してください。

GROUP BY について

　このページでは、以下のようなテーブルページのデータの入力と閲覧で作成した売上記録テーブルとデータを例のデータとして使用してます。

売上記録テーブル
> SELECT * FROM sales_slip;

+----+---------------------+----------+------------+--------+
| id | date_time           | goods_id | unit_price | amount |
+----+---------------------+----------+------------+--------+
|  1 | 2023-09-26 22:29:57 |        1 |          3 |    100 |
|  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |
|  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |
|  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |
|  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |
+----+---------------------+----------+------------+--------+

　上記の売上データを goods_id（商品id）ごとにグループ化すると以下のようになります。

GROUP BY
> SELECT goods_id FROM sales_slip GROUP BY goods_id;
+----------+
| goods_id |
+----------+
|        1 |
|        2 |
|        3 |
+----------+

　GROUP BY には上記でも述べましたが、GROUP BY で指定されたカラムデータによってグループ化できないカラムは参照できません。
　例えば、併せて id を取得するしようとするとグループ化によって対象にできないカラムがあるというエラーが返されます。

GROUP BY のエラー（ MySQL ）
> SELECT id, goods_id FROM sales_slip GROUP BY goods_id;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.sales_slip.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

NULL 値の扱い

　GROUP BY で指定されたカラムのデータに NULL 値が含まれている場合は NULL のグループができます。
　NULL 値を省くためには WHERE 句で予め省くか、HAVING 句によって省くことができます。
　取得したいデータによって NULL も含む、予め省く、HAVING 句によって後で省くといったことが考えられます。取得するべきデータによって扱いを変えるべきでしょう。

HAVING 句による検索

　GROUP BY 句によってグループごとに分けられた行の検索には HAVING 句を使用します。

　GROUP BY 句と WHERE 句の関係には注意が必要です。
　GROUP BY 句は WHERE 句の後に評価されるので GROUP BY 句に関係する検索条件は WHERE 句で利用できません。
　WHERE 句によって抽出された行を GROUP BY 句によってグループ化するという順で評価されるためです。

GROUP BY と WHERE
> SELECT goods_id, count(*) FROM sales_slip
 	WHERE count(*) > 1
 	GROUP BY goods_id;
ERROR 1111 (HY000): Invalid use of group function

　この場合は HAVING 句を使用します。

GROUP BY と HAVING
> SELECT goods_id, count(*) FROM sales_slip
 	GROUP BY goods_id
	HAVING count(*) > 1;
+----------+----------+
| goods_id | count(*) |
+----------+----------+
|        1 |        2 |
|        2 |        2 |
+----------+----------+

　SELECT と WHERE 句によって抽出された行データをグループ化することは可能です。さらにその結果に検索条件を加えるには HAVING 句を使用します。

複数のグループ指定

　GROUP BY 句では複数のグループを指定することもできます。

　以下の例では GROUP BY に goods_id と unit_price を指定しています。複数を指定するとそのすべての組み合わせになるようです。

GROUP BY の複数指定
> SELECT goods_id, unit_price, COUNT(*) FROM sales_slip GROUP BY goods_id,
unit_price;
+----------+------------+----------+
| goods_id | unit_price | COUNT(*) |
+----------+------------+----------+
|        1 |          3 |        1 |
|        2 |          8 |        1 |
|        2 |          5 |        1 |
|        1 |          5 |        1 |
|        3 |         10 |        1 |
+----------+------------+----------+

　PostgreSQL では以下のようにすべての組み合わせではなく、GROUP BY 句の先頭になるカラムを主体としてグループ化されるようです。

PostgreSQL における GROUP BY の複数指定
> SELECT goods_id, unit_price, COUNT(*) FROM sales_slip GROUP BY goods_id,
unit_price;
 goods_id | unit_price | count
----------+------------+-------
        1 |          3 |     2
        2 |          5 |     2
        3 |          8 |     1

式によるグループ指定

　GROUP BY 句には式を指定することもできます。

　例えば月ごとの売上数を取得するには以下のようにします。

式によるグループ指定
> SELECT
 	EXTRACT( YEAR FROM date_time ) AS year,
 	EXTRACT( MONTH FROM date_time ) AS month,
 	SUM(amount)
 	FROM sales_slip
 	GROUP BY EXTRACT( YEAR FROM date_time ), EXTRACT( MONTH FROM date_time );
+------+-------+-------------+
| year | month | SUM(amount) |
+------+-------+-------------+
| 2023 |    10 |         100 |
| 2023 |     9 |         170 |
+------+-------+-------------+

　EXTRACT()は日時要素を取得するための関数です。EXTRACT( 日時要素 FROM 日時データ )といった使い方をします。
　年度は YEAR、月度は MONTH、日にちは DAY です。時間は HOUR、分は MINUTE、秒は SECOND です。

COUNT() 関数

　COUNT() 関数は行数を返す集約関数のひとつです。以下は全レコード数を取得しています。

COUNT() 関数
> SELECT COUNT(*) FROM sales_slip;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+

　GROUP BY 句と組み合わせることでグループごとの行数を返します。グループ化された行が幾つあるかが分かります。

COUNT() 関数
> SELECT goods_id, COUNT(*) FROM sales_slip
 	GROUP BY goods_id;
+----------+----------+
| goods_id | COUNT(*) |
+----------+----------+
|        1 |        2 |
|        2 |        2 |
|        3 |        1 |
+----------+----------+

　COUNT() の引数*は行を指定することになります。COUNT(*)は抽出された行や指定されたグループの行数をカウントします。
　引数にカラム名を指定することもできますが、結果は行数をカウントすることと変わりません。

　COUNT() の引数にカラムを指定するとその値が NULL の場合はカウントされません。

COUNT() と DISTINCT

　もしも特定のカラムデータごとの総数のみを知りたい場合は、カラム名にDISTINCTを付加します。

> SELECT COUNT( DISTINCT good_id ) FROM sales_slip;
+----------------------------+
| COUNT( DISTINCT goods_id ) |
+----------------------------+
|                          3 |
+----------------------------+

SUM() 関数

　SUM() 関数は総数を返す集約関数です。

　例えば sales_slip テーブルの売上数の合計を取得するような場合に使用します。

SUM() 関数
> SELECT SUM( amount ) FROM sales_slip;
+---------------+
| SUM( amount ) |
+---------------+
|           270 |
+---------------+

　SUM 関数を GROUP BY 句と組み合わせることでグループごとの合計を返します。

SUM() 関数と GROUP BY
> SELECT goods_id, SUM( amount ) FROM sales_slip
 	GROUP BY goods_id;
+----------+---------------+
| goods_id | SUM( amount ) |
+----------+---------------+
|        1 |           180 |
|        2 |            80 |
|        3 |            10 |
+----------+---------------+

AVG() 関数

　AVG() 関数は平均値を返す集約関数です。指定されたカラムの値の合計値を行数で割った数値です。

AVG() の値
AVG() の値 = カラムの値の合計 / 行数

　sales_slip テーブルの売上数の平均値を取得するような場合に使用します。
　以下では、分かりやすくするために合計値と行数が表示しています。

AVG() 関数
> SELECT SUM(amount), COUNT(*), AVG( amount ) FROM sales_slip;
+-------------+----------+---------------+
| SUM(amount) | COUNT(*) | AVG( amount ) |
+-------------+----------+---------------+
|         270 |        5 |       54.0000 |
+-------------+----------+---------------+

　AVG 関数を GROUP BY 句と組み合わせることでグループごとの合計を返します。

AVG() 関数と GROUP BY
> SELECT goods_id, SUM(amount), COUNT(*), AVG( amount ) FROM sales_slip
 	GROUP BY goods_id;
+----------+-------------+----------+---------------+
| goods_id | SUM(amount) | COUNT(*) | AVG( amount ) |
+----------+-------------+----------+---------------+
|        1 |         180 |        2 |       90.0000 |
|        2 |          80 |        2 |       40.0000 |
|        3 |          10 |        1 |       10.0000 |
+----------+-------------+----------+---------------+

MAX(), MIN() 関数

　MAX(), MIN() 関数は抽出されたカラムデータの中から最大値、最小値を返す関数です。
　MAX() が最大値で、MIN() が最小値です。

　sales_slip テーブルの売上数の平均値を取得するような場合に使用します。
　以下では、分かりやすくするために合計値と行数が表示しています。

AVG() 関数
> SELECT SUM(amount), COUNT(*), AVG( amount ) FROM sales_slip;
+-------------+----------+---------------+
| SUM(amount) | COUNT(*) | AVG( amount ) |
+-------------+----------+---------------+
|         270 |        5 |       54.0000 |
+-------------+----------+---------------+

　AVG 関数を GROUP BY 句と組み合わせることでグループごとの合計を返します。

AVG() 関数と GROUP BY
> SELECT goods_id, SUM(amount), COUNT(*), AVG( amount ) FROM sales_slip
 	GROUP BY goods_id;
+----------+-------------+----------+---------------+
| goods_id | SUM(amount) | COUNT(*) | AVG( amount ) |
+----------+-------------+----------+---------------+
|        1 |         180 |        2 |       90.0000 |
|        2 |          80 |        2 |       40.0000 |
|        3 |          10 |        1 |       10.0000 |
+----------+-------------+----------+---------------+

グループごとの小計値

　GROUP BY 句によるグループごとの小計値をWITH ROLLUPを使用することで取得することができます。

WITH ROLLUP
> SELECT goods_id, SUM(amount) FROM sales_slip
 	GROUP BY goods_id WITH ROLLUP;
+----------+-------------+
| goods_id | SUM(amount) |
+----------+-------------+
|        1 |         180 |
|        2 |          80 |
|        3 |          10 |
|     NULL |         270 |
+----------+-------------+

　上記の例における NULL の行が小計値です。複数のカラムによるグループ化においてはより効果的です。

複数のグループ化による WITH ROLLUP
> SELECT goods_id, unit_price, SUM(amount) FROM sales_slip
 	GROUP BY goods_id, unit_price WITH ROLLUP;
+----------+------------+-------------+
| goods_id | unit_price | SUM(amount) |
+----------+------------+-------------+
|        1 |          3 |         100 |
|        1 |          5 |          80 |
|        1 |       NULL |         180 |
|        2 |          5 |          30 |
|        2 |          8 |          50 |
|        2 |       NULL |          80 |
|        3 |         10 |          10 |
|        3 |       NULL |          10 |
|     NULL |       NULL |         270 |
+----------+------------+-------------+

　GROUP BY 句の先頭になっているカラムを主体に小計値と合計値が返されます。

PostgreSQL と ROLLUP

　PostgreSQL では GROUP BY ROLLUP() という記述方法をします。

WITH ROLLUP
> SELECT goods_id, SUM(amount) FROM sales_slip
 	GROUP BY ROLLUP( goods_id );
 goods_id | sum
----------+-----
          | 270
        1 | 180
        3 |  10
        2 |  80

　複数のカラムをグループ化した場合には以下のようになります。

PostgreSQL ROLLUP()
> SELECT goods_id, unit_price, SUM(amount) FROM sales_slip
        GROUP BY ROLLUP( goods_id, unit_price );
 goods_id | unit_price | sum
----------+------------+-----
          |            | 270
        1 |          3 | 180
        2 |          5 |  80
        3 |          8 |  10
        1 |            | 180
        3 |            |  10
        2 |            |  80

　PostgreSQL では小計値や合計値の表示方法に違いがあります。

CUBE

　WITH CUBE または CUBE() といった関数もあります。ただし、 MySQL では WITH CUBE は使用できません。

　以下は PostgreSQL で CUBE() を使用した例です。

PostgreSQL CUBE()
> SELECT goods_id, unit_price, SUM(amount) FROM sales_slip
        GROUP BY CUBE( goods_id, unit_price );
 goods_id | unit_price | sum
----------+------------+-----
          |            | 270
        1 |          3 | 180
        2 |          5 |  80
        3 |          8 |  10
        1 |            | 180
        3 |            |  10
        2 |            |  80
          |          3 | 180
          |          5 |  80
          |          8 |  10

ORDER BY 句

　GROUP BY 句によってグループ化されたレコードは ORDER BY 句によって並べ替えることができます。

　以下は売上数を商品ごとに集計した上で、ORDER BY 句によって売上の多い順に並べ替えています。

ORDER BY 句による並べ替え
> SELECT goods_id,sum(amount) FROM sales_slip
	GROUP BY goods_id
	ORDER BY sum(amount) DESC;
+----------+-------------+
| goods_id | sum(amount) |
+----------+-------------+
|        1 |         180 |
|        2 |          80 |
|        3 |          10 |
+----------+-------------+

　ORDER BY 句はデフォルトで昇順で並び替えが行われます。DESC を指定することで降順に指定することができます。 デフォルトの昇順は ASK です。