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 の処理」を参照してください。