WITH ROLLUP - MｙSQL @Wiki 実用リファレンス

HOME > 関数 > GROUP BY節で使用する関数 > WITH ROLLUP

WITH ROLLUP

MySQL 4.1.1 以降では、GROUP BY 節に WITH ROLLUP 修飾子を使用することによって、合計出力に追加のレコードを挿入できます。これらの追加レコードは上位レベル（上位集約）の集計演算を表します。そのため、ROLLUP では、複数の分析レベルでの疑問に 1 回のクエリで答えることができます。たとえば、この修飾子は、OLAP（オンライン分析処理）をサポートする目的で使用できます。
例として、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 を使用すれば、2 つのレベルの分析を 1 回のクエリで実行することができます。 GROUP BY 節に WITH ROLLUP 修飾子を追加すると、クエリですべての年度値を足した総計を示すレコードが別に生成されます。

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

総計の上位集約行は、year カラムの値 NULL によって示されます。
ROLLUP は、GROUP BY カラムが複数ある場合、より複雑に作用します。この場合、最後のグループ化カラム以外の場所で ``ブレーク''（値の変化）があると、そのつどクエリで追加の上位集約の集計レコードが生成されます。
たとえば、ROLLUP を指定していない場合に、sales テーブルの、year、country、product に基づく集計が次のように出力されるとします。

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 |
+------+---------+------------+-------------+

この出力は、年/国/製品レベルでの分析における集計値のみを示しています。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 の出力は次のように解釈されます。

• 特定の年度と国に対応する各製品レコードセットの後に、製品ごとのすべて値の合計を示す追加の集計レコードが 1 つずつ生成される。これらのレコードでは、product カラムの値が NULL に設定される。
• 特定の年度に対応する各レコードセットの後に、国ごと、製品ごとのすべての値の合計を示す追加の集計レコードが 1 つずつ生成される。これらのレコードでは、country カラムと products カラムの値が NULL に設定される。
• 最後に、他のすべてのレコードの後に、年度ごと、国ごと、製品ごとのすべての値の総計を示す追加の集計レコードが 1 つ生成される。このレコードでは、year、country、products の各カラムの値が NULL に設定される。

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 値が表示されるため、MySQL クライアントプログラミングインタフェースを使用して NULL 値としてテストすることができます。