类别：

查询语法

GROUP BY ROLLUP

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

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

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

  • 每家商店。

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

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

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

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

If you are familiar with the concept of grouping sets, you can think of a ROLLUP grouping as equivalent to a series of grouping sets, but essentially a shorter specification. The N elements of a ROLLUP specification correspond to N+1 GROUPING SETS.

See also

语法

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

其中：

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

参数

column_alias

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

position

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

expr

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

使用说明

  • As the query is aggregated at higher and higher levels, it shows NULL values in more columns of each row. This is appropriate. In the following example, for the aggregate at the state level, the city column is NULL; that's because the value in the profit column does not correspond to one city. Similarly, in the final total, which aggregates data from all the states and all the cities, the revenue is not from one specific state or one specific city, so both the state and city columns in that row are NULL.

  • The query should list the "most significant level" first in the parentheses after the ROLLUP. For example, states contain cities, so if you are rolling up data across states and cities, the clause should be GROUP BY ROLLUP (state, city)

    If you reverse the order of the column names, you get a result that is probably not what you want. In the following example, if you reversed the order of city and state in the ROLLUP clause, the result would be incorrect, at least in part because both California and Puerto Rico have a city named San Jose (SJ), and you probably would not want to combine the revenue from the two different San Jose cities, except in the final total of all revenue. (An alternative way to avoid combining data from different cities with the same name is to create a unique ID for each city and use the ID rather than the name in the query.)

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

示例

Start by creating and loading a table with information about sales at a chain store that has branches in different cities and states/territories.

-- 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

Run a rollup query that shows profit by city, state, and total across all states. The query produces three "levels" of aggregation:

  • 每个城市。

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

  • All revenue combined across all states.

The query uses ORDER BY state, city NULLS LAST to ensure that each state's rollup comes immediately after all of the cities in that state, and that the final rollup appears at the end of the output.

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

语言: 中文