12.19.2 GROUP BY 修飾子

GROUP BY 句では、追加の行がサマリー出力に追加される WITH ROLLUP 修飾子が許可されます。これらの行は、高レベル (または超集約) のサマリー演算を表します。したがって、ROLLUP で単一のクエリーを使用すれば、複数レベルの分析で質問に回答できます。たとえば、これを使用すれば、OLAP (Online Analytical Processing) 演算をサポートできます。

sales という名前が付けられたテーブルに、売り上げの収益性を記録するための year、country、product、および profit カラムが含まれていると仮定します。

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

次のような単純な GROUP BY を使用すれば、テーブルの内容を年ごとにまとめることができます。

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

この出力には、年ごとの合計収益が表示されますが、すべての年にわたって集計された合計収益を確認する場合は、各値を自分で合計するか、追加のクエリーを実行する必要があります。

または、単一のクエリーで両方のレベルの分析を提供する ROLLUP も使用できます。GROUP BY 句に WITH ROLLUP 修飾子を追加すると、クエリーによってすべての年にわたる総合計を示す別の行が生成されます。

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

総合計の超集約行は、year カラムの NULL 値で識別されます。

複数の GROUP BY カラムがある場合は、ROLLUP の効果がより複雑になります。この場合、最後のグループ化カラム以外で「ブレイク」 (値の変更) が発生するたびに、クエリーによって追加の超集約サマリー行が生成されます。

たとえば、ROLLUP を使用しない場合は、year、country、および product に基づいた sales テーブルのサマリーが次のように表示されます。

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product;
+------+---------+------------+-------------+
| year | country | product    | SUM(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 |
+------+---------+------------+-------------+

この出力には、year/country/product の分析レベルでのみサマリー値が示されます。ROLLUP が追加されると、クエリーによって複数の追加行が生成されます。

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product    | SUM(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 |
+------+---------+------------+-------------+

このクエリーでは、ROLLUP 句を追加すると、出力に 1 つだけでなく、4 つの分析レベルでのサマリー情報が含まれます。次に、ROLLUP 出力の解釈方法を示します。

• 指定された year と country に対応する product 行の各セットに続いて、すべての product の合計を示す追加のサマリー行が生成されます。これらの行には、NULL に設定された product カラムが含まれています。

• 指定された year に対応する行の各セットに続いて、すべての country と product の合計を示す追加のサマリー行が生成されます。これらの行には、NULL に設定された country および products カラムが含まれています。

• 最後に、その他のすべての行に続いて、すべての year、country、および product の総合計を示す追加のサマリー行が生成されます。この行には、NULL に設定された year、country、および products カラムが含まれています。

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

次の項目には、MySQL での ROLLUP の実装に固有の動作の一部を一覧表示します。

ROLLUP を使用する場合は、ORDER BY 句を同時に使用して結果をソートできません。つまり、ROLLUP と ORDER BY は相互に排他的です。ただし、ソート順序を一部制御することはできます。MySQL の GROUP BY によって結果がソートされます。GROUP BY で名前が指定されたカラムで明示的な ASC および DESC キーワードを使用すると、カラムごとにソート順序を指定できます。(ROLLUP で追加された高レベルのサマリー行は、ソート順序には関係なく、計算された行の後ろに表示されます。)

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

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

LIMIT と ROLLUP を同時に使用すると、超集約行を理解するためのコンテキストが少ないため、解釈がより難しい結果が生成される可能性があります。

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

超集約行の NULL 値は、クエリー処理のかなり後半の段階で結果セットに配置されるため、クエリー自体で NULL 値としてテストすることはできません。たとえば、クエリーに HAVING product IS NULL を追加しても、超集約行以外のすべてを出力から除去できません。

一方で、NULL 値はクライアント側で NULL として表示され、MySQL クライアントプログラミングインタフェースのいずれかを使用してテストできます。

MySQL では、GROUP BY リストに表示されないカラムを、選択リストで名前を指定することが許可されます。この場合、サーバーはサマリー行内のこのような非集約カラムから任意の値を自由に選択できます。これには、WITH ROLLUP で追加された追加の行も含まれます。たとえば、次のクエリーでは、country は GROUP BY リストに表示されない非集約カラムであり、このカラムで選択された値は不確定です。

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

この動作は、ONLY_FULL_GROUP_BY SQL モードが無効になっている場合に発生します。このモードが有効になっている場合は、country が GROUP BY 句に一覧表示されないため、サーバーはそのクエリーを不正として拒否します。非集約カラムおよび GROUP BY についての詳細は、セクション12.19.3「MySQL での GROUP BY の処理」を参照してください。