彙總延伸項目 - Amazon Redshift
GROUPING SETSROLLUPCUBEGROUPING/GROUPING_ID 函數部份 ROLLUP 與 CUBE串連分組巢狀分組使用須知

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

彙總延伸項目

Amazon Redshift 支援彙總延伸項目，可在單一陳述式中執行多個 GROUP BY 操作的工作。

彙總延伸項目的範例會使用 orders 資料表，該資料表會保留電子公司的銷售資料。您可以執行下列操作來建立 orders：

CREATE TABLE ORDERS (
    ID INT,
    PRODUCT CHAR(20),
    CATEGORY CHAR(20),
    PRE_OWNED CHAR(1),
    COST DECIMAL
);

INSERT INTO ORDERS VALUES
    (0, 'laptop',       'computers',    'T', 1000),
    (1, 'smartphone',   'cellphones',   'T', 800),
    (2, 'smartphone',   'cellphones',   'T', 810),
    (3, 'laptop',       'computers',    'F', 1050),
    (4, 'mouse',        'computers',    'F', 50);

GROUPING SETS

在單一陳述式中計算一或多個群組集。群組集是單一 GROUP BY 子句的集合，也就是一組 0 個或多個資料行，您可以以此將查詢的結果集分組。GROUP BY GROUPING SETS 等同於在由不同資料欄分組的一個結果集上執行 UNION ALL 查詢。例如，GROUP BY GROUPING SETS((a), (b)) 等同於 GROUP BY a UNION ALL GROUP BY b。

下列範例會傳回訂單資料表的產品根據產品類別和銷售產品種類進行分組的成本。


SELECT category, product, sum(cost) as total
FROM orders
GROUP BY GROUPING SETS(category, product);

       category       |       product        | total
----------------------+----------------------+-------
 computers            |                      |  2100
 cellphones           |                      |  1610
                      | laptop               |  2050
                      | smartphone           |  1610
                      | mouse                |    50

(5 rows)

ROLLUP

假設有一個階層，其中之前的資料欄被視為後續資料欄的父項。ROLLUP 會依提供的資料欄將資料分組，並傳回額外的小計資料列 (代表所有分組資料欄層級的總計)，以及已分組的資料列。例如，您可以使用 GROUP BY ROLLUP((a), (b)) 傳回一個先按 a 分組，然後按 b 分組的結果集 (假設 b 是 a 的子區段)。ROLLUP 也會傳回包含整個結果集的資料列，而不會將資料欄分組。

GROUP BY ROLLUP((a), (b)) 等於 GROUP BY GROUPING SETS((a,b), (a), ())。

下列範例會傳回訂單資料表產品先依類別分組，然後依產品分組的成本，其中以產品做為類別的細項。

SELECT category, product, sum(cost) as total
FROM orders
GROUP BY ROLLUP(category, product) ORDER BY 1,2;

       category       |       product        | total
----------------------+----------------------+-------
 cellphones           | smartphone           |  1610
 cellphones           |                      |  1610
 computers            | laptop               |  2050
 computers            | mouse                |    50
 computers            |                      |  2100
                      |                      |  3710
(6 rows)

CUBE

依提供的資料欄將資料分組，並傳回額外的小計資料列 (代表所有分組資料欄層級的總計)，以及已分組的資料列。CUBE 會傳回與 ROLLUP 相同的資料列，同時針對 ROLUP 未涵蓋的每個分組資料欄組合新增額外的小計資料列。例如，您可以使用 GROUP BY CUBE ((a), (b)) 傳回一個先按 a 分組，然後按 b 分組的結果集 (假設 b 是 a 的子區段)，然後再獨自按 b 分組。CUBE 也會傳回包含整個結果集的資料列，而不會將資料欄分組。

GROUP BY CUBE((a), (b)) 等於 GROUP BY GROUPING SETS((a, b), (a), (b), ())。

下列範例會傳回訂單資料表產品先依類別分組，然後依產品分組的成本，其中以產品做為類別的細項。與前面的 ROLLUP 範例不同，陳述式會傳回每個分組資料欄組合的結果。

SELECT category, product, sum(cost) as total
FROM orders
GROUP BY CUBE(category, product) ORDER BY 1,2;

       category       |       product        | total
----------------------+----------------------+-------
 cellphones           | smartphone           |  1610
 cellphones           |                      |  1610
 computers            | laptop               |  2050
 computers            | mouse                |    50
 computers            |                      |  2100
                      | laptop               |  2050
                      | mouse                |    50
                      | smartphone           |  1610
                      |                      |  3710
(9 rows)

GROUPING/GROUPING_ID 函數

ROLLUP 和 CUBE 會將 NULL 值新增至結果集，以指出小計資料列。例如，GROUP BY ROLLUP((a), (b)) 會傳回 b 分組資料欄中值為 NULL 的一或多個資料列，以指出這些資料列是分組資料欄中欄位的小計。這些 NULL 值僅用於滿足傳回元組的格式。

當您在儲存 NULL 值本身的關係上使用 ROLLUP 和 CUBE 執行 GROUP BY 操作時，這可能會產生資料列看起來具有相同分組資料欄的結果集。返回上一個範例，如果 b 分組資料欄包含一個儲存的 NULL 值，GROUP BY ROLLUP((a), (b)) 會在 b 分組資料欄中 (不是小計) 傳回值為 NULL 的資料列。

若要區分 ROLLUP 和 CUBE 建立的 NULL 值，以及儲存在資料表本身的 NULL 值，您可以使用 GROUPING 函數或其別名 GROUPING_ID。GROBING 採用單個分組集作為其引數，並且對於結果集中的每一個資料列傳回對應於該位置中分組資料欄的 0 或 1 位元值，然後將該值轉換為整數。如果該位置中的值是由彙總延伸項目建立的 NULL 值，則 GROUPING 會傳回 1。對於所有其他值，包括儲存的 NULL 值，則傳回 0。

例如，GROUPING(category, product) 可以針對指定的資料列傳回下列值，視該資料列的分組資料欄值而定。就此範例而言，資料表中的所有 NULL 值都是彙總延伸項目所建立的 NULL 值。

類別資料欄 產品資料欄 GROUPING 函數位元值 十進制值
非 NULL 非 NULL 00 0
非 NULL NULL 01 1
NULL 非 NULL 10 2
NULL NULL 11 3

GROUPING 函數會以下列格式顯示在查詢的 SELECT 清單部分中。

SELECT ... [GROUPING( expr )...] ...
  GROUP BY ... {CUBE | ROLLUP| GROUPING SETS} ( expr ) ...

下列範例與前面的 CUBE 範例相同，但為其分組集增加了 GROUPING 函數。

SELECT category, product,
       GROUPING(category) as grouping0,
       GROUPING(product) as grouping1,
       GROUPING(category, product) as grouping2,
       sum(cost) as total
FROM orders
GROUP BY CUBE(category, product) ORDER BY 3,1,2;

       category       |       product        | grouping0 | grouping1 | grouping2 | total
----------------------+----------------------+-----------+-----------+-----------+-------
 cellphones           | smartphone           |         0 |         0 |         0 |  1610
 cellphones           |                      |         0 |         1 |         1 |  1610
 computers            | laptop               |         0 |         0 |         0 |  2050
 computers            | mouse                |         0 |         0 |         0 |    50
 computers            |                      |         0 |         1 |         1 |  2100
                      | laptop               |         1 |         0 |         2 |  2050
                      | mouse                |         1 |         0 |         2 |    50
                      | smartphone           |         1 |         0 |         2 |  1610
                      |                      |         1 |         1 |         3 |  3710
(9 rows)

部份 ROLLUP 與 CUBE

您只能使用小計的一部分來執行 ROLLUP 和 CUBE 操作。

部分 ROLLUP 和 CUBE 操作的語法如下。

GROUP BY expr1, { ROLLUP | CUBE }(expr2, [, ...])

在這裡，GROUP BY 子句僅在表達式 expr2 和以後的層級上建立小計資料列。

下列範例會顯示訂單資料表上的部份 ROLLUP 和 CUBE 操作，並先依產品是否為二手產品分組，然後在類別與產品資料欄上執行 ROLLUP 和 CUBE。

SELECT pre_owned, category, product,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY pre_owned, ROLLUP(category, product) ORDER BY 4,1,2,3;

 pre_owned |       category       |       product        | group_id | total
-----------+----------------------+----------------------+----------+-------
 F         | computers            | laptop               |        0 |  1050
 F         | computers            | mouse                |        0 |    50
 T         | cellphones           | smartphone           |        0 |  1610
 T         | computers            | laptop               |        0 |  1000
 F         | computers            |                      |        2 |  1100
 T         | cellphones           |                      |        2 |  1610
 T         | computers            |                      |        2 |  1000
 F         |                      |                      |        6 |  1100
 T         |                      |                      |        6 |  2610
(9 rows)

SELECT pre_owned, category, product,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY pre_owned, CUBE(category, product) ORDER BY 4,1,2,3;

 pre_owned |       category       |       product        | group_id | total
-----------+----------------------+----------------------+----------+-------
 F         | computers            | laptop               |        0 |  1050
 F         | computers            | mouse                |        0 |    50
 T         | cellphones           | smartphone           |        0 |  1610
 T         | computers            | laptop               |        0 |  1000
 F         | computers            |                      |        2 |  1100
 T         | cellphones           |                      |        2 |  1610
 T         | computers            |                      |        2 |  1000
 F         |                      | laptop               |        4 |  1050
 F         |                      | mouse                |        4 |    50
 T         |                      | laptop               |        4 |  1000
 T         |                      | smartphone           |        4 |  1610
 F         |                      |                      |        6 |  1100
 T         |                      |                      |        6 |  2610
(13 rows)

由於二手資料欄不包含在 ROLLUP 和 CUBE 作業中，因此沒有包含所有其他資料列的總計資料列。

串連分組

您可以串連多個 GROUPING SETS/ROLLUP/CUBE 子句，以計算不同層次的小計。串連分組會傳回所提供分組集的笛卡爾乘積。

串連 GROUPING SETS/ROLLUP/CUBE 子句的語法如下。

GROUP BY {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...]),
         {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...])[, ...]

請考慮下列範例，看看小型串連分組如何產生大型的最終結果集。

SELECT pre_owned, category, product,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY CUBE(category, product), GROUPING SETS(pre_owned, ())
ORDER BY 4,1,2,3;

 pre_owned |       category       |       product        | group_id | total
-----------+----------------------+----------------------+----------+-------
 F         | computers            | laptop               |        0 |  1050
 F         | computers            | mouse                |        0 |    50
 T         | cellphones           | smartphone           |        0 |  1610
 T         | computers            | laptop               |        0 |  1000
           | cellphones           | smartphone           |        1 |  1610
           | computers            | laptop               |        1 |  2050
           | computers            | mouse                |        1 |    50
 F         | computers            |                      |        2 |  1100
 T         | cellphones           |                      |        2 |  1610
 T         | computers            |                      |        2 |  1000
           | cellphones           |                      |        3 |  1610
           | computers            |                      |        3 |  2100
 F         |                      | laptop               |        4 |  1050
 F         |                      | mouse                |        4 |    50
 T         |                      | laptop               |        4 |  1000
 T         |                      | smartphone           |        4 |  1610
           |                      | laptop               |        5 |  2050
           |                      | mouse                |        5 |    50
           |                      | smartphone           |        5 |  1610
 F         |                      |                      |        6 |  1100
 T         |                      |                      |        6 |  2610
           |                      |                      |        7 |  3710
(22 rows)

巢狀分組

您可以使用 GROUPING SETS/ROLLUP/CUBE 操作作為您的 GROUPING SETS expr，形成一個巢狀分組。巢狀 GROUPING SETS 中的子分組會被扁平化。

巢狀分組的語法如下。

GROUP BY GROUPING SETS({ROLLUP|CUBE|GROUPING SETS}(expr[, ...])[, ...])

請考量下列範例。

SELECT category, product, pre_owned,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY GROUPING SETS(ROLLUP(category), CUBE(product, pre_owned))
ORDER BY 4,1,2,3;

       category       |       product        | pre_owned | group_id | total
----------------------+----------------------+-----------+----------+-------
 cellphones           |                      |           |        3 |  1610
 computers            |                      |           |        3 |  2100
                      | laptop               | F         |        4 |  1050
                      | laptop               | T         |        4 |  1000
                      | mouse                | F         |        4 |    50
                      | smartphone           | T         |        4 |  1610
                      | laptop               |           |        5 |  2050
                      | mouse                |           |        5 |    50
                      | smartphone           |           |        5 |  1610
                      |                      | F         |        6 |  1100
                      |                      | T         |        6 |  2610
                      |                      |           |        7 |  3710
                      |                      |           |        7 |  3710
(13 rows)

請注意，由於 ROLLUP(category) 和 CUBE(product, pre_owned) 都包含分組集 ()，因此代表總計的資料列將重複。

使用須知

  • GROUP BY 子句最多支援 64 個分組集。如果是 ROLLUP 和 CUBE，或是 GROUPING SETS、ROLLUP 和 CUBE 的某些組合，則此限制會套用至隱含的分組集數目。例如，GROUP BY CUBE((a), (b)) 計為 4 個分組集，而不是 2 個。

  • 使用彙總延伸項目時，您無法使用常數作為分組資料欄。

  • 您無法建立包含重複資料欄的分組集。