MySQL ROLLUP修饰符

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

**ROLLUP修饰符只能与GROUP BY查询一起使用在 MySQL 中。 **

语法：

以下是使用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。

执行查询时，我们将获得以下输出：

NULL在 Year 列的输出中表示总计超级聚合值。然而，由于此查询能够生成每年的总销售额和总销售额的总计聚合，因此它存在两个问题：

1. 查询变得非常冗长。
2. 查询性能降低，因为数据库引擎在内部执行两个单独的查询，并将结果合并成单个输出。

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

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

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

当我们执行该命令时，我们将得到以下输出：

在这个输出中，我们可以看到 NULL 值位于 Year column 中，它标识了超级聚合行。它清楚地显示了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)
()

请看下面的查询以更清楚地解释：

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。
• 其次，为给定年份的每组行生成一个额外的超级汇总行，显示所有国家和产品的总数。它将国家和产品列设置为NULL。
• 最后，对于所有其他行，它生成一个额外的超级汇总行，显示所有列的总计。它将年份、国家和产品列设置为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。

我们可以在 select list、 HAVING 子句和 ORDER BY 子句中使用 GROUPING() 函数。

查看下面的查询：

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

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

类似地， GROUPING(Country) 在超级聚合行中，当Country列中出现NULL时返回1。否则，它将返回0。

而 GROUPING(Product) 在超级聚合行中，当Product列中出现NULL时返回1。否则，它将返回0。

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

下面的查询说明了如何将 IF() 函数与GROUPING()函数结合使用，以替换年份、国家和产品列中超级聚合空值的标签：

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()函数中有多个参数，它将返回代表每个表达式结果的位掩码的输出。在这里，最低位产生最右边参数的结果。以下示例将如下评估：

示例： GROUPING (年份，国家，产品)

  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;

它将给出以下输出：

本文链接：http://so.lmcjl.com/news/15987/