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

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 について

 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

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