カテゴリ：

クエリ構文

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 [ , ... ] ] )
[ ... ]
Copy

条件:

groupRollup ::= { <column_alias> | <position> | <expr> }
Copy
column_alias

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

position

SELECT リスト内の式の位置。

expr

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

使用上の注意

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

  • クエリは、 ROLLUP の後の括弧内で最初に「最上位レベル」をリストする必要があります。たとえば、州には都市が含まれているため、州と都市間でデータをロールアップする場合、句は次である必要があります。

    ...ROLLUP (state, city)

    列名の順序を逆にすると、期待したものとは異なる結果になる可能性が高くなります。以下の例で、 ROLLUP 句にある都市と州の順序を逆にした場合、カリフォルニアとプエルトリコの両方にサンノゼ（「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');
Copy

都市、州、およびすべての州の合計別に利益を示すロールアップクエリを実行します。

次の例は、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 |
+-------+---------+--------+
Copy

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

言語: 日本語