カテゴリ:

クエリ構文

GROUP BY ROLLUP

GROUP BY ROLLUP (グループ化された行に加えて)小計行を生成する GROUP BY 句の拡張です。小計行は、グループ化された行を生成するために使用されたのと同じ集計関数を計算することによって値が導出される、さらに集計される行です。

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

  • 各店舗。

  • 各都市(大都市には複数の店舗がある場合があります)。

  • 各州。

  • すべて(すべての州のすべての店舗)。

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

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

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

GROUP BY GROUPING SETS

構文

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

条件:

groupRollup ::= { <column_alias> | <position> | <expr> }
列エイリアス

クエリブロックの SELECT リストに表示される列エイリアス。

ポジション

SELECT リスト内の式の位置。

現在のスコープ内のテーブルの式。

使用上の注意

  • クエリはより高いレベルで集約されるため、各行のより多くの列に NULL 値が表示されます。これは適切です。例えば、次の例では、州レベルの集計の都市列は NULL です。これは、利益列の値が1つの都市に対応していないためです。同様に、すべての州とすべての都市のデータを集計する最終合計では、収益は特定の州または特定の都市からのものではないため、その行の州と都市の列は両方とも NULL です。

  • クエリは、ROLLUPの後の括弧内に最初に「最上位レベル」をリストする必要があります。例えば、州には都市が含まれているため、州と都市間でデータをロールアップする場合、句は ...ROLLUP (State, City) である必要があります。列名の順序を逆にすると、おそらく期待したものとは異なる結果が得られます。以下の例では、少なくともカリフォルニアとプエルトリコの両方にサンノゼ(「SJ」)という名前の都市があるため、結果は少なくとも部分的には不正確になります。すべての収益の最終合計を除き、サンノゼという2つの都市の収益を結合するのは賢明ではありません。(同じ名前の異なる都市のデータを結合する問題は、都市ごとに一意の ID を作成し、クエリで名前ではなく ID を使用することで回避できました。)

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

-- 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つの「レベル」を持つクエリを示しています。

  • 各都市。

  • 各州。

  • すべての収益を合わせたもの。

    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)
     ;
    

    出力:

    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)
     ;
    +-------+---------+--------+
    | STATE | CITY    | PROFIT |
    |-------+---------+--------|
    | CA    | SF      |     13 |
    | CA    | SJ      |     26 |
    | FL    | Miami   |     48 |
    | FL    | Orlando |     96 |
    | PR    | SJ      |    192 |
    | CA    | NULL    |     39 |
    | FL    | NULL    |    144 |
    | PR    | NULL    |    192 |
    | NULL  | NULL    |    375 |
    +-------+---------+--------+