SQL Rollup

作者：--    发布时间：2019-11-20

在本教程中，您将学习如何使用sql rollup生成多个分组集。

1. sql rollup简介

rollup是group by子句的扩展。 rollup选项允许包含表示小计的额外行，通常称为超级聚合行，以及总计行。 通过使用rollup选项，可以使用单个查询生成多个分组集。

注意，分组集是一组用于分组的列。 例如，一个由仓库返回库存的查询，分组集是(warehouse)。

select
    warehouse, 
    sum(quantity) qty
from
    inventory
group by
    warehouse;

有关grouping sets的更多信息，请查看分组集教程。

以下是sql rollup的基本语法：

select 
    c1, c2, aggregate_function(c3)
from
    table
group by rollup (c1, c2);

rollup假定输入列之间存在层次结构。 例如，如果输入列是(c1，c2)，则层次结构c1> c2。 rollup生成考虑此层次结构有意义的所有分组集。 这就是为什么我们经常使用rollup来生成小计和总计以用于报告目的。

在上面的语法中，rollup(c1，c2)生成以下三个分组集：

(c1,c2)
(c1)
()

oracle，microsoft sql server和postgresql支持此语法。 但是，mysql的语法略有不同，如下所示：

select 
    c1, c2, aggregate_function(c3)
from
    table_name
group by c1, c2 with rollup;

2. sql rollup示例

我们将使用在grouping sets教程中设置的inventory表进行演示。

mysql> select * from inventory;
+---------------+---------+----------+----------+
| warehouse     | product | model    | quantity |
+---------------+---------+----------+----------+
| san jose      | iphone  | 6s       |      100 |
| san fransisco | iphone  | 6s       |       50 |
| san jose      | iphone  | 7        |       50 |
| san fransisco | iphone  | 7        |       10 |
| san jose      | iphone  | x        |      150 |
| san fransisco | iphone  | x        |      200 |
| san jose      | samsung | galaxy s |      200 |
| san fransisco | samsung | galaxy s |      200 |
| san fransisco | samsung | note 8   |      100 |
| san jose      | samsung | note 8   |      150 |
+---------------+---------+----------+----------+
10 rows in set

2.1. sql rollup有一个列示例

以下语句使用group by子句和sum()函数按仓库查找总库存：

select 
    warehouse, sum(quantity)
from
    inventory
group by warehouse;
+---------------+---------------+
| warehouse     | sum(quantity) |
+---------------+---------------+
| san fransisco | 560           |
| san jose      | 650           |
+---------------+---------------+
2 rows in set

要检索所有仓库中的总产品数，请将rollup添加到group by子句，如下所示：

select 
    warehouse, sum(quantity)
from
    inventory
group by rollup(warehouse);

执行上面示例代码，得到以下结果：

正如在结果中看到的那样，warehouse列中的null值指定了总计超级聚合行。 在此示例中，rollup选项使查询生成另一行，显示所有仓库中的总产品数量。
要使输出更具可读性，可以使用coalesce()函数将null值替换all值，如下所示：

select 
    coalesce(warehouse, 'all warehouses') as warehouse,
    sum(quantity)
from
    inventory
group by rollup (warehouse);

执行上面示例代码，得到以下结果：

2.2. sql rollup有多列示例

以下语句按warehouse和product计算库存：

select 
    warehouse, product, sum(quantity)
from
    inventory
group by warehouse, product;

执行上面示例代码，得到以下结果：

将rollup添加到group by子句：

select 
    warehouse, product, sum(quantity)
from
    inventory
group by rollup (warehouse , product);

执行上面示例代码，得到以下结果：

请注意，输出包含两个分析级别的摘要信息，而不仅仅是一个：

• 在指定仓库的每组产品行之后，将显示一个额外的摘要行，显示总库存。 在这些行中，product列中的值设置为null。
• 在所有行之后，将显示一个额外的摘要行，显示所有仓库和产品的总库存。 在这些行中，warehouse 和 product 列中的值设置为null。

2.3. sql rollup带有部分汇总的示例

可以使用rollup执行部分汇总，以减少计算的小计数，如以下示例所示：

select 
    warehouse, product, sum(quantity)
from
    inventory
group by warehouse, rollup (product);

执行上面示例代码，得到以下结果：

在此示例中，rollup仅为product列而不是warehouse列生成超级聚合摘要。

在本教程中，您学习了如何使用sql rollup通过单个查询执行多级分析。