类别：

查询语法

GROUP BY ROLLUP

GROUP BY ROLLUP 是 GROUP BY 子句的扩展，该子句在层次结构的多个级别上生成聚合行（除了详细的分组行之外）。例如，如果按城市和省/自治区/直辖市分组，ROLLUP 会生成每个城市/省/自治区/直辖市组合的聚合、每个省/自治区/直辖市的总计，以及所有省/自治区/直辖市的总计。这些聚合是使用 SELECT 子句中指定的相同聚合函数计算得出的。

ROLLUP 可以与其他 GROUP BY 表达式组合。例如，您可以编写 GROUP BY x, ROLLUP(y, z) 以按照列 x 进行分组，并对 y 和 z 进行汇总聚合。

您可以将汇总视为生成多个结果集，其中每个结果集（在第一个结果集之后）都是前一个结果集的聚合。因此，举例来说，如果您拥有一家零售连锁店，您可能希望查看以下方面的利润：

  • 每家商店。

  • 每个城市（大城市可能有多家商店）。

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

  • 所有商店（所有省/自治区/直辖市的所有商店）。

您可以创建单独的报告来获取该信息，但扫描一次数据会更有效。

如果您熟悉 分组集 的概念，则可以将 ROLLUP 分组视为等同于一系列分组集，但本质上是一个较短的规范。ROLLUP 规范的 N 元素对应于 N+1 GROUPING SETS。

另请参阅

语法

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

其中：

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

参数

column_alias

出现在查询块的 SELECT 列表中的列别名。

position

表达式在 SELECT 列表中的位置。

expr

当前作用域中表的任意表达式。

使用说明

  • 当查询在越来越高的级别聚合时，它会在每行的更多列中显示 NULL 值。这是恰当的。在以下示例中，对于省/自治区/直辖市级别的聚合，city 列为 NULL；这是因为 profit 列中的值并非对应于一个城市。同样，在汇总来自所有省/自治区/直辖市和所有城市的数据的最终总计中，收入并非来自一个特定省/自治区/直辖市或一个特定城市，因此该行中的 state 和 city 列都是 NULL。

  • 查询应在 ROLLUP 之后的括号中首先列出“最重要的级别”。例如，省/自治区/直辖市包含城市，因此，如果要跨省/自治区/直辖市和城市汇总数据，则子句应为 GROUP BY ROLLUP (state, city)

    如果颠倒列名的顺序，则得到的结果可能不是您想要的结果。在以下示例中，如果您颠倒了 ROLLUP 子句中 city 和 state 的顺序，则结果将不正确，至少部分原因是加利福尼亚和波多黎各都有一个名为圣何塞 (SJ) 的城市，您可能不想合并来自两个不同圣何塞城市的收入，除非是所有收入的最终总额。（避免将来自不同城市的数据合并为同一名称的另一种方法是为每个城市创建一个唯一 ID，并在查询中使用 ID，而不是名称。）

  • GROUPING 实用函数可帮助区分汇总聚合产生的 NULL 值与数据中的实际 NULL 值。GROUPING 对于按指定列分组的行返回 0，而对于因聚合而显示 NULL 的列所在的行返回 1。

示例

首先创建一个表，然后向其中加载一家连锁店的销售信息，该连锁店在不同的城市以及省/自治区/直辖市或地区设有分店。

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

运行汇总查询，按城市、省/自治区/直辖市以及所有省/自治区/直辖市的总和显示利润。该查询生成了三个“级别”的聚合：

  • 每个城市。

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

  • 所有省/自治区/直辖市的所有收入总和。

此查询使用 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 |
+-------+---------+--------+

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