类别：

查询语法

GROUP BY ROLLUP

GROUP BY ROLLUP 是 GROUP BY 子句的扩展，除了分组的行之外，还可以用于生成小计行。小计行是进一步聚合的行，其值是通过计算用于生成分组行的相同聚合函数得出的。

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

  • 每家商店。

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

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

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

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

如果您熟悉分组集 (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；这是因为利润列中的值并非对应于一个城市。同样，在汇总来自所有省/自治区/直辖市和所有城市的数据的最终总计中，收入并非来自一个特定省/自治区/直辖市或一个特定城市，因此该行中的省/自治区/直辖市和城市列都是 NULL。

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

    ...ROLLUP (state, city)

    如果颠倒列名的顺序，则得到的结果可能不是您想要的结果。在下面的示例中，如果您颠倒了 ROLLUP 子句中城市和省/自治区/直辖市的顺序，则结果将不正确，至少部分原因是加利福尼亚和波多黎各都有一个名为圣何塞（“SJ”）的城市，您可能不想合并来自两个不同圣何塞的收入，除非是所有收入的最终总额。（避免将来自不同城市的数据合并为同一名称的另一种方法是为每个城市创建一个唯一 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

运行汇总查询，按城市、省/自治区/直辖市以及所有省/自治区/直辖市的总和显示利润。

下面的示例显示了一个具有三个“级别”的查询：

  • 每个城市。

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

  • 所有收入的总和。

此示例使用 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 值，因为数据针对所有城市和省/自治区/直辖市，而不是针对特定的城市和省/自治区/直辖市。

语言: 中文