类别：

查询语法

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

其中：

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

参数

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

运行多维数据集查询，按城市、省/自治区/直辖市以及所有省/自治区/直辖市的总和显示利润。下面的示例显示了一个具有三个“级别”的查询：

  • 每个城市。

  • 每个省/自治区/直辖市。

  • 所有收入的总和。

此示例使用 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 |
+-------+---------+--------+
Copy

某些汇总行包含 NULL 值。例如，表中的最后一行包含城市的 NULL 值和省/自治区/直辖市的 NULL 值，因为数据针对所有城市和省/自治区/直辖市，而不是针对特定的城市和省/自治区/直辖市。

GROUPING 实用函数可帮助区分多维数据集聚合产生的 NULL 值与数据中的实际 NULL 值。

GROUP BY CUBE 和 GROUP BY ROLLUP 都为每个城市/省/自治区/直辖市对生成一行，并且这两个 GROUP BY 子句还为每个单独的省/自治区/直辖市和所有省/自治区/直辖市的组合生成汇总行。这两个 GROUP BY 子句的区别在于，GROUP BY CUBE 还会为每个城市名称（Miami、SJ 等）生成一个输出行。

对分层数据使用 GROUP BY CUBE 时要小心。在此示例中，SJ 行包含 CA 州中名为 SJ 的城市和 PR 境内名为 SJ 的城市的总数，尽管这些城市之间的唯一关系是它们具有相同的名称。通常，GROUP BY ROLLUP 用于分析分层数据，GROUP BY CUBE 用于跨独立轴分析数据。