カテゴリ：

クエリ構文

GROUP BY CUBE

GROUP BY CUBEは、 GROUP BY に似た GROUP BY ROLLUP 句の拡張機能です。ROLLUPのように、CUBEは複数のレベルで集計された行を生成します。ただしROLLUPは、自然な階層に従う集計を作成します（たとえば、都市は州まで集計し、州は国まで集計します）。CUBEは、指定された列のすべての可能な組み合わせの集計を作成します。これらには、ROLLUPが生成する階層的集計および個々のディメンション全体で個別に集計する追加の「クロス集計」行の両方が含まれます。

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

CUBE グループ化は一連のグループ化セットと同等であり、本質的にはより短い仕様です。CUBE 仕様の N 要素は 2^N GROUPING SETS に対応しています。

こちらもご参照ください

構文

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

条件:

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

パラメーター

column_alias

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

position

SELECT リスト内の式の位置。

expr

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

使用上の注意

  • Snowflakeでは、各キューブで最大7個の要素（128グループ化セットに相当）を使用できます。

例

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

-- 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 CUBE (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  | Miami   |     48 |
| NULL  | Orlando |     96 |
| NULL  | SF      |     13 |
| NULL  | SJ      |    218 |
| NULL  | NULL    |    375 |
+-------+---------+--------+

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

GROUPING ユーティリティ関数は、キューブ集計から得られるNULL値とデータの実際のNULL値の区別に役立ちます。

GROUP BY CUBEとGROUP BY ROLLUPはどちらも、都市と州のペアごとに1つの行を生成し、両方のGROUP BY句は、個々の州とすべての州を組み合わせた場合のロールアップ行も生成します。2つのGROUP BY句の違いは、GROUP BY CUBEが各都市名（Miami、``SJ``など）の出力行も生成することです。

階層データにGROUP BY CUBEを使用する場合は注意してください。この例では、同じ都市名であるという理由だけのために、``SJ``の行に、``CA``州にある``SJ``という名前の都市と、``PR``準州にある``SJ``という名前の都市の合計両方が含まれています。一般に、GROUP BY ROLLUPを使用して階層データを分析し、GROUP BY CUBEを使用して独立した軸全体のデータを分析します。