SQL ROLLUP

摘要：本教程将介绍如何使用 SQL ROLLUP 生成多个分组集。

SQL ROLLUP 简介

ROLLUP 是 GROUP BY 子句的一个扩展。ROLLUP 选项允许你包括额外的行来表示子总计，这些行通常称为超级聚合行，以及总行。使用 ROLLUP 选项，你可以使用一个查询生成多个 分组集。

请注意，分组集是你分组时所用的列的集合。例如，返回库存按仓库分组的查询中，分组集为 (仓库)。

SELECT
    warehouse, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    warehouse;Code language: SQL (Structured Query Language) (sql)

有关 GROUPING SETS 的详细信息，请参阅 分组集教程。

以下是 SQL ROLLUP 的基本语法

SELECT 
    c1, c2, aggregate_function(c3)
FROM
    table
GROUP BY ROLLUP (c1, c2);
Code language: SQL (Structured Query Language) (sql)

ROLLUP 假设输入列之间存在层次结构。例如，如果输入列为 (c1,c2)，则层次结构为 c1 > c2。ROLLUP 将生成所有分组集，这些分组集在考虑此层次结构时是有意义的。这就是我们经常使用 ROLLUP 来生成用于报表目的的子总计和总计的原因。

在以上语法中，ROLLUP(c1,c2) 生成了以下三个分组集

(c1,c2)
(c1)
()Code language: SQL (Structured Query Language) (sql)

此语法受 Oracle、Microsoft SQL Server 和 PostgreSQL 的支持。但是，MySQL 的语法略有不同，如下所示

SELECT 
    c1, c2, aggregate_function(c3)
FROM
    table_name
GROUP BY c1, c2 WITH ROLLUP;
Code language: SQL (Structured Query Language) (sql)

SQL ROLLUP 示例

对于演示，我们将使用 GROUPING SETS 教程中设置的 inventory 表。

单列 SQL ROLLUP 示例

以下语句使用 GROUP BY 子句和 SUM() 函数来查找按仓库分类的总库存

SELECT 
    warehouse, SUM(quantity)
FROM
    inventory
GROUP BY warehouse;
Code language: SQL (Structured Query Language) (sql)

要检索所有仓库中的总产品，请将 ROLLUP 添加到 GROUP BY 子句，如下所示

SELECT 
    warehouse, SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP (warehouse);
Code language: SQL (Structured Query Language) (sql)

如你在结果中所见，warehouse 列中的 NULL 值指定了超级聚合总计行。在本示例中，ROLLUP 选项导致查询生成另一行，其中显示了所有仓库中的总产品。

要使输出更易于阅读，你可以使用 COALESCE() 函数用 All warehouses 替换 NULL 值，如下所示

SELECT 
    COALESCE(warehouse, 'All warehouses') AS warehouse,
    SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP (warehouse);
Code language: SQL (Structured Query Language) (sql)

多列 SQL ROLLUP 示例

以下语句按仓库和产品计算了库存

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY warehouse, product;
Code language: SQL (Structured Query Language) (sql)

让我们将 ROLLUP 添加到 GROUP BY 子句

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP (warehouse , product);
Code language: SQL (Structured Query Language) (sql)

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

部分 rollup 的 SQL ROLLUP 示例

你可以使用 ROLLUP 执行部分 rollup，以减少计算的子总计数量，如下面的示例所示

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY warehouse, ROLLUP (product);
Code language: SQL (Structured Query Language) (sql)

在本示例中，ROLLUP 仅对 product 列生成超级聚合摘要，而不是 warehouse 列。

在本教程中，你学会了如何使用 SQL ROLLUP 函数使用单一查询执行多级别分析。