カテゴリ：: クエリ構文

GROUP BY ROLLUP¶

GROUP BY ROLLUPは GROUP BY 句の拡張機能であり、（詳細なグループ化された行に加えて）階層の複数のレベルで集計された行を生成します。たとえば、都市と州ごとにグループ化した場合、ROLLUPは各都市と州の組み合わせ、各州の合計、およびすべての州の総合計を生成します。これらの集計は、SELECT句で指定されているのと同じ集計関数を使用して計算されます。

ROLLUPは他のGROUP BY式と組み合わせることができます。たとえば、 GROUP BY x, ROLLUP(y, z) と記述し、列 x でグループ化すると共に y および z に対するロールアップ集約を組み合わせることができます。

ロールアップは、複数の結果セットを生成するものと考えることができます。各結果セット（最初の結果セット）は、前の結果セットの集合です。例えば、小売店のチェーンを所有している場合、次の利益を確認できます。

各店舗。
各都市（大都市には複数の店舗がある場合があります）。
各州。
すべて（すべての州のすべての店舗）。

別のレポートを作成してその情報を取得することもできますが、データを1回スキャンする方が効率的です。

グループ化セットの概念に精通している場合、ROLLUPグループ化は一連のグループ化セットと同等であると考えることができますが、本質的に仕様が短くなります。N 仕様の ROLLUP 要素は N+1 GROUPING SETS に対応しています。

こちらもご参照ください¶

GROUPING （各行を生成したグループ化レベルを識別するユーティリティ関数）
GROUP BY GROUPING SETS
GROUP BY CUBE

構文¶

SELECT ...
FROM ...
[ ... ]
GROUP BY [ groupItem [ , groupItem [ , ... ] ] , ] ROLLUP ( groupItem [ , groupItem [ , ... ] ] )
[ ... ]

条件:

groupItem ::= { <column_alias> | <position> | <expr> }

パラメーター¶

column_alias: クエリブロックの SELECT リストに表示される列エイリアス。
position: SELECT リスト内の式の位置。
expr: 現在のスコープ内のテーブルの式。

使用上の注意¶

クエリはより高いレベルで集約されるため、各行のより多くの列に NULL 値が表示されます。これは適切です。次の例では、州レベルの集計の場合 city 列はNULLです。これは、 profit 列の値が1つの都市に対応していないからです。同様に、すべての州とすべての都市のデータを集計する最終合計では、収益は特定の州または特定の都市からのものではないため、その行の state と city の列は両方ともNULLです。
クエリは、 ROLLUP の後の括弧内で最初に「最上位レベル」をリストする必要があります。たとえば、州には都市が含まれているため、州と都市間でデータをロールアップする場合、句は GROUP BY ROLLUP (state, city) である必要があります。

列名の順序を逆にすると、期待したものとは異なる結果になる可能性が高くなります。以下の例で、ROLLUP句にある city と state の順序を逆にした場合、カリフォルニアとプエルトリコの両方にサンノゼ（ SJ ）という名前の都市があるため、結果は少なくとも部分的には不正確になります。収益すべての最終合計での場合を除き、サンノゼという2つの都市の収益を結合するのは望ましくありません。（同じ名前の異なる都市のデータを組み合わせないようにする別の方法は、都市ごとに一意の ID を作成し、クエリで名前ではなく ID を使用することです。）
GROUPING ユーティリティ関数は、ロールアップ集計から得られるNULL値とデータの実際のNULL値の区別に役立ちます。GROUPINGは、指定された列のグループ化された行について 0 を返し、集約によって列がNULLになっている行について 1 を返します。

例¶

さまざまな都市や州/準州に支店を持つチェーンストアでの販売に関する情報を含むテーブルを作成して読み込むことから始めます。

-- Create some tables and insert some rows.
CREATE TABLE products (product_ID INTEGER, wholesale_price REAL);
INSERT INTO products (product_ID, wholesale_price) VALUES 
    (1, 1.00),
    (2, 2.00);

CREATE TABLE sales (product_ID INTEGER, retail_price REAL, 
    quantity INTEGER, city VARCHAR, state VARCHAR);
INSERT INTO sales (product_id, retail_price, quantity, city, state) VALUES 
    (1, 2.00,  1, 'SF', 'CA'),
    (1, 2.00,  2, 'SJ', 'CA'),
    (2, 5.00,  4, 'SF', 'CA'),
    (2, 5.00,  8, 'SJ', 'CA'),
    (2, 5.00, 16, 'Miami', 'FL'),
    (2, 5.00, 32, 'Orlando', 'FL'),
    (2, 5.00, 64, 'SJ', 'PR');

都市、州、およびすべての州の合計別に利益を示すロールアップクエリを実行します。クエリは、3つの「レベル」の集約を生成します。

各都市。
各州。
すべての州を集約したすべての収益。

このクエリでは、 ORDER BY state, city NULLS LAST を使用して、各州のロールアップがその州内の都市すべてで直後にくるようにし、最終的なロールアップが出力の最後に表示されるようにします。

SELECT state, city, SUM((s.retail_price - p.wholesale_price) * s.quantity) AS profit 
 FROM products AS p, sales AS s
 WHERE s.product_ID = p.product_ID
 GROUP BY ROLLUP (state, city)
 ORDER BY state, city NULLS LAST
 ;
+-------+---------+--------+
| STATE | CITY    | PROFIT |
|-------+---------+--------|
| CA    | SF      |     13 |
| CA    | SJ      |     26 |
| CA    | NULL    |     39 |
| FL    | Miami   |     48 |
| FL    | Orlando |     96 |
| FL    | NULL    |    144 |
| PR    | SJ      |    192 |
| PR    | NULL    |    192 |
| NULL  | NULL    |    375 |
+-------+---------+--------+

一部のロールアップ行には NULL 値が含まれています。たとえば、テーブルの最後の行には、都市の NULL 値と州の NULL 値が含まれています。これは、データが特定の都市と州ではなく、すべての都市と州に関するものだからです。