MySQL ROLLUP聚合

MySQL 中的 ROLLUP 是一个修饰符，用于生成汇总输出，包括表示超聚合（更高级别）汇总操作的额外行。它使我们能够使用单个查询总结多个分析级别的输出。它主要用于为OLAP（在线分析处理）操作提供支持。ROLLUP 修饰符只能与MySQL中的 GROUP BY 查询一起使用。

一、MySQL ROLLUP聚合语法

以下是使用 ROLLUP 修饰符的语法：

SELECT   
    column1, column2, column3, ...  
FROM   
    table_name  
GROUP BY  
    column1, column2,... WITH ROLLUP;

在这种语法中，我们需要在 SELECT 子句中指定查询结果中显示的列名。接下来，我们将提到表名。之后，我们指定了GROUP BY子句，包括我们想要聚合数据的列名。最后，我们指定WITH ROLLUP修饰符以在附加行中获取超聚合输出。

我们已经了解到 GROUP BY 查询与MAX等聚合函数一起应用,MIN,SUM,COUNT,AVG等，按单列或多列对输出行进行分组。ROLLUP 修饰符是使用 GROUP BY 查询的一个选项，该查询包括用于表示小计的额外字段。这些额外的行称为超聚合行，它是总计行的组合。因此，ROLLUP 修饰符允许我们根据MySQL 中 GROUP BY 子句中指定的列在单个查询中创建多个集合行分组。

二、MySQL ROLLUP聚合示例

如果我们想了解 ROLLUP 修饰符，我们必须要知道什么是分组集。分组集是我们想要分组以获得结果输出的一组列。例如，假设我们有一个表“sales”，其中包含以下数据：

如果我们想总结每年的结果，我们将使用简单的 GROUP BY 子句，如下所示：

SELECT Year, SUM(Sale) AS Total_Sales   
FROM sales   
GROUP BY Year;

它将给出以下输出，显示每年的总（总）销售额：

在上述查询中，分组集由列名 Year 表示。如果我们需要在单个查询中同时生成多个分组集，我们可以使用 UNION ALL 运算符，如下所示：

SELECT Year, SUM(Sale) AS Total_Sales   
FROM sales   
GROUP BY Year  
UNION ALL  
SELECT NULL, SUM(Sale) AS Total_Sales   
FROM sales;

在这个查询中，我们可以看到 NULL 列。这是因为Union All子句要求所有查询具有相同数量的列。所以为了满足这个要求，我们在第二个查询的选择列表中添加了 NULL。当我们执行查询时，我们得到以下输出：

Year列输出中的 NULL表示总计超聚合值。由于此查询能够生成每年的总销售额以及总计销售额，但是它有两个问题：

它使SQL相当冗长。
它降低了查询的性能，因为数据库引擎在内部执行两个单独的查询并将结果集组合成一个输出。

为了解决这些问题，MySQL 允许我们使用 ROLLUP 子句，它在一个查询中提供两种级别的分析。ROLLUP 子句是 GROUP BY 子句的扩展，它生成另一行并显示总计（超聚合）值。

让我们看看在 GROUP BY 子句中添加 WITH ROLLUP 修饰符后的结果，该子句显示所有年份值的总计：

SELECT Year, SUM(Sale) AS Total_Sales   
FROM sales   
GROUP BY Year WITH ROLLUP;

当我们执行命令时，我们会得到如下输出：

在此输出中，我们可以在标识超级聚合行的Year 列中看到NULL值。它清楚地表明，ROLLUP 子句不仅生成小计，还给出全年总销售额的总计。

如果 GROUP BY 子句有多个列，则 ROLLUP 修饰符具有更复杂的效果。在这种情况下，ROLLUP 修饰符假定在 GROUP BY 子句中指定的列之间存在层次结构。每次列值发生变化时，查询都会在结果末尾生成一个额外的超聚合汇总行。

例如，假设我们在 GROUP BY 子句中指定了三列，如下所示：

GROUP BY c1, c2, c3 WITH ROLLUP

ROLLUP 修饰符假定层次结构如下：

c1 > c2 > c3

并生成以下分组集：

(c1, c2, c3)  
(c1, c2)  
(c1)  
()

请参阅以下SQL以更清楚地解释它：

SELECT Year, Country, Product, SUM(Sale) AS Total_Sales  
FROM sales  
GROUP BY Year, Country, Product;

如果没有 ROLLUP，基于 GROUP BY 子句中指定的多个列的销售表摘要类似于以下输出。在这里，我们将仅在年份/国家/产品级别的分析中获得汇总值。

添加 ROLLUP 后，查询会产生几个额外的行：

SELECT Year, Country, Product, SUM(Sale) AS Total_Sales  
FROM sales  
GROUP BY Year, Country, Product WITH ROLLUP;

查看下面输出：

上面的输出生成了四个分析级别的信息，解释如下：

首先，给定年份和国家/地区的每组产品行都会生成一个额外的超级汇总汇总行，显示所有产品的总数。它会将产品列设置为 NULL。
接下来，给定年份的每组行都会生成一个额外的超级汇总行，显示所有国家和产品的总数。它会将 Country 和 Products 列设置为 NULL。
最后，对于所有其他行，它会生成一个额外的超级汇总汇总行，显示所有列的总计。它将 Year、Country 和 Products 列设置为 NULL。

如果我们更改 GROUP BY 列中指定的列的顺序，我们将得到不同的结果：

SELECT Year, Country, Product, SUM(Sale) AS Total_Sales  
FROM sales  
GROUP BY Country, Year, Product WITH ROLLUP;

输出结果如下：

三、GROUPING() 函数

GROUPING() 函数用于检查结果集中的 NULL 是否代表常规分组值、超聚合值或总计。当 NULL 出现在超聚合行中时，它返回 1。否则，它返回 0。

我们可以在选择列表、 HAVING子句和ORDER BY子句中使用 GROUPING() 函数。

请参阅以下SQL：

SELECT Year, Country, Product, SUM(Sale) AS Total_Sales,  
         GROUPING(Year),  
         GROUPING(Country),  
         GROUPING(Product)  
       FROM sales  
       GROUP BY Year, Country, Product WITH ROLLUP;

我们将得到以下输出，其中当Year列中的NULL出现在超级聚合行中时， GROUPING(Year)返回一个。否则，它将返回零。

同样，当 Country 列中的 NULL 出现在超聚合行中时， GROUPING(Country)返回 1。否则，它将返回零。

此外，当 Product 列中的 NULL 出现在超聚合行中时， GROUPING(Product)返回一个。否则，它将返回零。

我们还可以使用 GROUPING() 函数将有意义的标签替换为超聚合 NULL 值，而不是直接显示它。

以下查询说明了如何将IF()函数与 GROUPING() 函数结合起来，以用标签替换 Year、Country 和 Product 列中的超聚合 NULL 值：

SELECT  
         IF(GROUPING(Year), 'All years', year) AS year,  
         IF(GROUPING(Country), 'All countries', country) AS country,  
         IF(GROUPING(Product), 'All products', product) AS product,  
         SUM(Sale) AS Total_Sales  
       FROM sales  
       GROUP BY Year, Country, Product WITH ROLLUP;

输出结果如下：

如果我们在 GROUPING() 函数中有多个参数，它将返回代表一个bitmask的输出，该bitmask结合了每个表达式的结果。在这里，最低位产生最右边参数的结果。请看下面的示例：

 result for GROUPING(Product)  
+ result for GROUPING(Country) << 1  
+ result for GROUPING(Year) << 2

如果任何参数具有超聚合 NULL 值，则此类 GROUPING() 的结果非零。在这种情况下，它将仅返回超聚合行并使用以下查询过滤常规分组行：

mysql> SELECT Year, Country, Product, SUM(Sale) AS Total_Sale  
       FROM sales  
       GROUP BY Year, Country, Product WITH ROLLUP  
       HAVING GROUPING(Year, Country, Product) <> 0;

输出结果如下：

MySQL 函数 MySQL INT类型

MySQL ROLLUP聚合

一、MySQL ROLLUP聚合 语法

二、MySQL ROLLUP聚合 示例

三、GROUPING() 函数

微信关注

一、MySQL ROLLUP聚合语法

二、MySQL ROLLUP聚合示例