MySQL :: MySQL 8.0 リファレンスマニュアル

MySQL 8.0 リファレンスマニュアル / ... / GROUP BY 修飾子

12.20.2 GROUP BY 修飾子

GROUP BY 句を使用すると、サマリー出力に上位レベル (つまり、上位集計) のサマリー操作を表す追加の行を含めることができる WITH ROLLUP 修飾子が許可されます。したがって、ROLLUP で単一のクエリーを使用すれば、複数レベルの分析で質問に回答できます。たとえば、ROLLUP を使用して OLAP (オンライン分析処理) 操作をサポートできます。

sales テーブルに、売上収益性を記録するための year, country, product カラムと profit カラムがあるとします:

CREATE TABLE sales
(
    year    INT,
    country VARCHAR(20),
    product VARCHAR(32),
    profit  INT
);

年ごとにテーブルの内容を要約するには、次のような単純な GROUP BY を使用します:

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

出力には、各年の利益の合計 (集計) が表示されます。すべての年の合計利益も決定するには、個々の値を自分で加算するか、追加のクエリーを実行する必要があります。または、単一のクエリーで両方のレベルの分析を提供する ROLLUP も使用できます。 GROUP BY 句に WITH ROLLUP 修飾子を追加すると、クエリーによって、すべての年の値の総計を示す別の (スーパー集計) 行が生成されます:

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
| NULL |   7535 |
+------+--------+

year カラムの NULL 値は、超集計行の総計を識別します。

複数の GROUP BY カラムがある場合は、ROLLUP の効果がより複雑になります。この場合、最後のグループ化カラム以外の値が変更されるたびに、クエリーは追加の超集計サマリー行を生成します。

たとえば、ROLLUP がない場合、year、country および product に基づく sales テーブルのサマリーは次のようになります。出力には year/country/product レベルの分析でのみサマリー値が示されます:

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | Finland | Phone      |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
+------+---------+------------+--------+

ROLLUP が追加されると、クエリーによっていくつかの追加の行が生成されます:

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | Finland | NULL       |   1600 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | India   | NULL       |   1350 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2000 | USA     | NULL       |   1575 |
| 2000 | NULL    | NULL       |   4525 |
| 2001 | Finland | Phone      |     10 |
| 2001 | Finland | NULL       |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
| 2001 | USA     | NULL       |   3000 |
| 2001 | NULL    | NULL       |   3010 |
| NULL | NULL    | NULL       |   7535 |
+------+---------+------------+--------+

出力には、1 つだけでなく、4 つの分析レベルのサマリー情報が含まれるようになりました:

特定の年および国の各製品行の後に、すべての製品の合計を示す追加の上位集計サマリー行が表示されます。これらの行には、NULL に設定された product カラムが含まれています。
特定の年の各行の後に、追加の上位集計サマリー行が表示され、すべての国および製品の合計が示されます。これらの行には、NULL に設定された country および products カラムが含まれています。
最後に、他のすべての行の後に、追加の上位集計サマリー行が表示され、すべての年、国および製品の総計が示されます。この行には、NULL に設定された year、country、および products カラムが含まれています。

各超集約行の NULL 指示子は、行がクライアントに送信されるときに生成されます。サーバーでは、変更された値を持つ左端のカラムに続いて、GROUP BY 句で名前が指定されたカラムが調査されます。これらの名前のいずれかと一致する名前を持つ結果セット内のカラムの場合、その値は NULL に設定されます。 (カラムの位置でカラムをグループ化するように指定した場合、サーバーは位置で NULL に設定するカラムを識別します。)

上位集計行の NULL 値は、クエリー処理のこのような遅延ステージで結果セットに配置されるため、SELECT 構文のリストまたは HAVING 句でのみ NULL 値としてテストできます。結合条件または WHERE 句で NULL 値としてテストして、選択する行を決定することはできません。たとえば、WHERE product IS NULL をクエリーに追加して、スーパー集計行以外のすべての行を出力から除外することはできません。

NULL 値は、クライアント側では NULL として表示され、任意の MySQL クライアントプログラミングインタフェースを使用してテストできます。ただし、この時点では、NULL が通常のグループ化された値を表すか、スーパー集計値を表すかは区別できません。区別をテストするには、後述する GROUPING() 関数を使用します。

以前は、MySQL では、WITH ROLLUP オプションを持つクエリーで DISTINCT または ORDER BY を使用できませんでした。この制限は、MySQL 8.0.12 以降ではなくなりました。 (Bug #87450、Bug #86311、Bug #26640100、Bug #26073513)

GROUP BY ... WITH ROLLUP クエリーでは、結果の NULL 値が超集計値を表すかどうかをテストするために、GROUPING() 関数を選択リスト、HAVING 句および (MySQL 8.0.12 の時点) ORDER BY 句で使用できます。たとえば、GROUPING(year) は、year カラムの NULL がスーパー集計行にある場合は 1 を返し、それ以外の場合は 0 を返します。同様に、country カラムと product カラムのスーパー集計 NULL 値に対して、GROUPING(country) と GROUPING(product) はそれぞれ 1 を返します:

mysql> SELECT
         year, country, product, SUM(profit) AS profit,
         GROUPING(year) AS grp_year,
         GROUPING(country) AS grp_country,
         GROUPING(product) AS grp_product
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+----------+-------------+-------------+
| year | country | product    | profit | grp_year | grp_country | grp_product |
+------+---------+------------+--------+----------+-------------+-------------+
| 2000 | Finland | Computer   |   1500 |        0 |           0 |           0 |
| 2000 | Finland | Phone      |    100 |        0 |           0 |           0 |
| 2000 | Finland | NULL       |   1600 |        0 |           0 |           1 |
| 2000 | India   | Calculator |    150 |        0 |           0 |           0 |
| 2000 | India   | Computer   |   1200 |        0 |           0 |           0 |
| 2000 | India   | NULL       |   1350 |        0 |           0 |           1 |
| 2000 | USA     | Calculator |     75 |        0 |           0 |           0 |
| 2000 | USA     | Computer   |   1500 |        0 |           0 |           0 |
| 2000 | USA     | NULL       |   1575 |        0 |           0 |           1 |
| 2000 | NULL    | NULL       |   4525 |        0 |           1 |           1 |
| 2001 | Finland | Phone      |     10 |        0 |           0 |           0 |
| 2001 | Finland | NULL       |     10 |        0 |           0 |           1 |
| 2001 | USA     | Calculator |     50 |        0 |           0 |           0 |
| 2001 | USA     | Computer   |   2700 |        0 |           0 |           0 |
| 2001 | USA     | TV         |    250 |        0 |           0 |           0 |
| 2001 | USA     | NULL       |   3000 |        0 |           0 |           1 |
| 2001 | NULL    | NULL       |   3010 |        0 |           1 |           1 |
| NULL | NULL    | NULL       |   7535 |        1 |           1 |           1 |
+------+---------+------------+--------+----------+-------------+-------------+

GROUPING() の結果を直接表示するかわりに、GROUPING() を使用して、スーパー集計 NULL 値のラベルを置換できます:

mysql> SELECT
         IF(GROUPING(year), 'All years', year) AS year,
         IF(GROUPING(country), 'All countries', country) AS country,
         IF(GROUPING(product), 'All products', product) AS product,
         SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+-----------+---------------+--------------+--------+
| year      | country       | product      | profit |
+-----------+---------------+--------------+--------+
| 2000      | Finland       | Computer     |   1500 |
| 2000      | Finland       | Phone        |    100 |
| 2000      | Finland       | All products |   1600 |
| 2000      | India         | Calculator   |    150 |
| 2000      | India         | Computer     |   1200 |
| 2000      | India         | All products |   1350 |
| 2000      | USA           | Calculator   |     75 |
| 2000      | USA           | Computer     |   1500 |
| 2000      | USA           | All products |   1575 |
| 2000      | All countries | All products |   4525 |
| 2001      | Finland       | Phone        |     10 |
| 2001      | Finland       | All products |     10 |
| 2001      | USA           | Calculator   |     50 |
| 2001      | USA           | Computer     |   2700 |
| 2001      | USA           | TV           |    250 |
| 2001      | USA           | All products |   3000 |
| 2001      | All countries | All products |   3010 |
| All years | All countries | All products |   7535 |
+-----------+---------------+--------------+--------+

複数の式引数を使用すると、GROUPING() は、各式の結果を結合するビットマスクを表す結果を、右端の式の結果に対応する最下位ビットと返します。たとえば、GROUPING(year, country, product) は次のように評価されます:

  result for GROUPING(product)
+ result for GROUPING(country) << 1
+ result for GROUPING(year) << 2

このような GROUPING() の結果は、いずれかの式がスーパー集計 NULL を表す場合はゼロ以外であるため、スーパー集計行のみを返し、次のように通常のグループ化された行をフィルタで除外できます:

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP
       HAVING GROUPING(year, country, product) <> 0;
+------+---------+---------+--------+
| year | country | product | profit |
+------+---------+---------+--------+
| 2000 | Finland | NULL    |   1600 |
| 2000 | India   | NULL    |   1350 |
| 2000 | USA     | NULL    |   1575 |
| 2000 | NULL    | NULL    |   4525 |
| 2001 | Finland | NULL    |     10 |
| 2001 | USA     | NULL    |   3000 |
| 2001 | NULL    | NULL    |   3010 |
| NULL | NULL    | NULL    |   7535 |
+------+---------+---------+--------+

sales テーブルには NULL 値が含まれていないため、ROLLUP 結果のすべての NULL 値は超集計値をテーブルします。データセットに NULL 値が含まれている場合、ROLLUP サマリーには、スーパー集計行だけでなく、通常のグループ化された行にも NULL 値が含まれることがあります。 GROUPING() では、これらを区別できます。テーブル t1 に、数量値のセットに対する 2 つのグループ化係数を持つ単純なデータセットが含まれているとします。ここで、NULL は「other」や「不明」などを示します:

mysql> SELECT * FROM t1;
+------+-------+----------+
| name | size  | quantity |
+------+-------+----------+
| ball | small |       10 |
| ball | large |       20 |
| ball | NULL  |        5 |
| hoop | small |       15 |
| hoop | large |        5 |
| hoop | NULL  |        3 |
+------+-------+----------+

単純な ROLLUP 操作では次の結果が生成されるため、通常のグループ化された行の NULL 値とスーパー集計行の NULL 値を区別するのはそれほど簡単ではありません:

mysql> SELECT name, size, SUM(quantity) AS quantity
       FROM t1
       GROUP BY name, size WITH ROLLUP;
+------+-------+----------+
| name | size  | quantity |
+------+-------+----------+
| ball | NULL  |        5 |
| ball | large |       20 |
| ball | small |       10 |
| ball | NULL  |       35 |
| hoop | NULL  |        3 |
| hoop | large |        5 |
| hoop | small |       15 |
| hoop | NULL  |       23 |
| NULL | NULL  |       58 |
+------+-------+----------+

GROUPING() を使用してスーパー集計 NULL 値のラベルを置換すると、結果の解釈が容易になります:

mysql> SELECT
         IF(GROUPING(name) = 1, 'All items', name) AS name,
         IF(GROUPING(size) = 1, 'All sizes', size) AS size,
         SUM(quantity) AS quantity
       FROM t1
       GROUP BY name, size WITH ROLLUP;
+-----------+-----------+----------+
| name      | size      | quantity |
+-----------+-----------+----------+
| ball      | NULL      |        5 |
| ball      | large     |       20 |
| ball      | small     |       10 |
| ball      | All sizes |       35 |
| hoop      | NULL      |        3 |
| hoop      | large     |        5 |
| hoop      | small     |       15 |
| hoop      | All sizes |       23 |
| All items | All sizes |       58 |
+-----------+-----------+----------+

ROLLUP 使用時のその他の考慮事項

次の説明では、ROLLUP の MySQL 実装に固有の動作の一部を示します。

MySQL 8.0.12 より前では、ROLLUP を使用する場合、ORDER BY 句を使用して結果をソートすることもできません。つまり、ROLLUP と ORDER BY は MySQL で相互に排他的でした。ただし、ソート順序を一部制御することはできます。 ORDER BY で ROLLUP を使用せず、グループ化された結果の特定のソート順序を達成するという制限を回避するには、グループ化された結果セットを導出テーブルとして生成し、それに ORDER BY を適用します。例:

mysql> SELECT * FROM
         (SELECT year, SUM(profit) AS profit
         FROM sales GROUP BY year WITH ROLLUP) AS dt
       ORDER BY year DESC;
+------+--------+
| year | profit |
+------+--------+
| 2001 |   3010 |
| 2000 |   4525 |
| NULL |   7535 |
+------+--------+

MySQL 8.0.12 では、ORDER BY と ROLLUP を一緒に使用できるため、ORDER BY と GROUPING() を使用して、グループ化された結果の特定のソート順序を実現できます。例:

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP
       ORDER BY GROUPING(year) DESC;
+------+--------+
| year | profit |
+------+--------+
| NULL |   7535 |
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

どちらの場合も、集計上のサマリー行は計算元の行でソートされ、その配置はソート順 (昇順ソートの場合は末尾、降順ソートの場合は先頭) に依存します。

LIMIT を使用すると、クライアントに返される行の数を制限できます。 LIMIT は ROLLUP のあとに適用されるため、ROLLUP で追加された追加の行に対して制限が適用されます。例:

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP
       LIMIT 5;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | Finland | NULL       |   1600 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
+------+---------+------------+--------+

LIMIT を ROLLUP とともに使用すると、スーパー集計行を理解するためのコンテキストが少なくなるため、解釈が困難な結果が生成される場合があります。

MySQL 拡張機能では、GROUP BY リストに表示されないカラムを選択リストに指定できます。 (非集計カラムおよび GROUP BY の詳細は、セクション12.20.3「MySQL での GROUP BY の処理」を参照してください。) この場合、サーバーはサマリー行内のこのような非集約カラムから任意の値を自由に選択できます。これには、WITH ROLLUP で追加された追加の行も含まれます。たとえば、次のクエリーでは、country は GROUP BY リストに表示されない非集計カラムであり、このカラムに選択された値は非決定的です:

mysql> SELECT year, country, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+---------+--------+
| year | country | profit |
+------+---------+--------+
| 2000 | India   |   4525 |
| 2001 | USA     |   3010 |
| NULL | USA     |   7535 |
+------+---------+--------+

この動作は、ONLY_FULL_GROUP_BY SQL モードが有効になっていない場合に許可されます。このモードが有効になっている場合は、country が GROUP BY 句に一覧表示されないため、サーバーはそのクエリーを不正として拒否します。 ONLY_FULL_GROUP_BY が有効になっている場合でも、非決定的値カラムに対して ANY_VALUE() 関数を使用してクエリーを実行できます:

mysql> SELECT year, ANY_VALUE(country) AS country, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+---------+--------+
| year | country | profit |
+------+---------+--------+
| 2000 | India   |   4525 |
| 2001 | USA     |   3010 |
| NULL | USA     |   7535 |
+------+---------+--------+

関連キーワード: 　関数, year, country, profit, ROLLUP, 集計, GROUPING, カラム, All, sales

PREV HOME UP NEXT