MySQL-分组函数ROLLUP的基本用法

一、ROLLUP简介

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

MySQL中的ROLLUP是用于 产生汇总输出的修饰符，包括表示超级汇总(较高级别)汇总操作的额外行。它使我们能够使用单个查询在多个分析级别上汇总输出。它主要用于为OLAP(在线分析处理)操作提供支持。

二、基本语法

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

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

我们指定了 GROUP BY 子句，其中包括我们要基于其聚合数据的列名。最后，我们指定 WITH ROLLUP 修饰符以在另一行中获得超级聚合输出。

我们已经了解到GROUP BY查询已与诸如 MAX ， MIN ， SUM ， COUNT ， AVG 等将输出行按单列或多列进行分组。 ROLLUP修饰符是使用GROUP BY查询的选项，该查询包含用于表示小计的额外字段。这些额外的行称为超级汇总行，是总计行的总和。因此，ROLLUP修饰符使我们可以基于 GROUP BY子句中指定的列，在单个查询中创建一组集合行。

三、ROLLUP的使用

例如，假设我们有一个包含以下数据的表 " sales" :

CREATE TABLE IF NOT EXISTS sales
(
id      BIGINT   COMMENT  'ID',
years   INT  COMMENT '年份',
country VARCHAR(20)  COMMENT '国家',
product VARCHAR(32)  COMMENT  '产品',
sales  INT  COMMENT '销售额',
PRIMARY KEY(id)
);

insert into  sales VALUES
(1,2000,'Finland','Computer',1500),
(2,2000,'Finland','Phone',100),
(3,2000,'India','Calculator',150),
(4,2000,'India','Computer',1200),
(5,2000,'USA','Calculator',75),
(6,2000,'USA','Computer',1500),
(7,2001,'Finland','Phone',100),
(8,2001,'USA','Calculator',50),
(9,2001,'USA','Computer',2700),
(10,2001,'USA','TV',250);

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

SELECT
years,
SUM(sales) AS total_sales  
FROM  sales  
GROUP BY years;

它将给出以下输出，显示每年的总销售额(总计):

在上面的查询中，分组集由列名称Year表示。如果我们需要在单个查询中一起生成多个分组集，则可以使用UNION ALL运算符，如下所示:

SELECT
years,
SUM(sales) AS total_sales
FROM  sales  
GROUP BY years
UNION ALL
SELECT
null as years,
SUM(sales) AS  total_sales
FROM  sales ;  

在此查询中，我们可以看到NULL列。这是因为 UNION ALL子句要求所有查询具有相同的列数。因此，为了满足此要求，我们在第二个查询的选择列表中添加了NULL。

在执行查询时，将获得以下输出:

年份列的输出中的NULL表示超级总计的总计值。由于此查询能够产生每年的总销售额以及总销售额的总和，因此，它有两个问题:

这使查询很长。由于数据库引擎在内部执行两个单独的查询并将结果集组合为一个输出，因此降低了查询的性能。

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

让我们在向WITH BY ROLLUP修饰符添加后看到结果子句显示所有年份的总计:

SELECT
years,
SUM(sales) AS total_sales
FROM sales
GROUP BY years 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)

()

请参阅以下查询以更清楚地说明它:

SELECT
years,
country,
product,
SUM(sales) AS total_sales
FROM sales
GROUP BY years, country, product ;

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

添加ROLLUP后，查询会产生一些额外的行:

SELECT
years,
country,
product,
SUM(sales) AS total_sales
FROM sales
GROUP BY years, country, product WITH ROLLUP;

请参见以下输出:

以上输出在以下四个级别生成信息: 分析，说明如下:

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

四、GROUPING()函数

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

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

请参阅以下查询:

SELECT
years,
country,
product,
SUM(sales) AS total_sales,
GROUPING(years),
GROUPING(country),
GROUPING(product)
FROM sales
GROUP BY years, country, product WITH ROLLUP;

当超级集合行中 Years 列中的NULL出现时， GROUPING(Years)返回1，我们将得到以下输出。否则，它将返回零。

类似地，当"超级汇总"行中"国家/地区"列中的NULL为空时， GROUPING(Country)将返回1、否则，它将返回零。

此外，当超级汇总行中"产品"列中的NULL出现时， GROUPING(produce)返回1、否则，它将返回零。

我们还可以使用GROUPING()函数来替代超级汇总NULL值的有意义的标签，而不是直接显示。

以下查询说明了如何将 IF()函数与GROUPING()函数组合以替换标签"年份"，"国家/地区"和"产品"列中的超级汇总NULL值:

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

我们将得到如下输出:

如果我们在其中有多个参数GROUPING()函数，它将返回代表位掩码的输出，该掩码合并了每个表达式的结果。在这里，最低位产生最右边参数的结果。下面的示例将按以下方式进行评估:

示例: 分组(年份，国家/地区，产品)

  result for GROUPING(product)

+ result for GROUPING(country) << 1

+ result for GROUPING(years) << 2

使用多个参数GROUPING()函数示例：

SELECT
years,
country,
product,
SUM(sales) AS Total_Sales,
GROUPING(years,country,product) AS Grouping_Result
FROM sales
GROUP BY years, country, product WITH ROLLUP;

它将给出以下输出:

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

SELECT
years,
country,
product,
SUM(sales) AS Total_Sales
FROM sales
GROUP BY years, country, product WITH ROLLUP
HAVING GROUPING(years, country, product) <> 0;

它将给出以下输出: