MySQL 聚合函数（二）Group By的修饰符——ROLLUP
原文为MySQL 5.7 官方手册：12.20.2 GROUP BY Modifiers
一、ROLLUP 修饰符的意义
GROUP BY子句允许添加WITH ROLLUP修饰符，该修饰符可以对分组后各组的某个列的结果值进行汇总，并在结果中输出，即提供更高一级的聚合操作。
因此，ROLLUP使您能够使用单个查询在多个分析级别回答问题。例如，ROLLUP可用于为OLAP（在线分析处理）操作提供支持。
现在有一个示例销售表如下：
CREATE TABLE sales ( year INT, country VARCHAR(20), product VARCHAR(32), profit INT );
先通过以下的分组查询了解下该销售表的数据：
SELECT year, country, product, SUM(profit) AS profit FROM sales GROUP BY year, country, product;
结果：
按年份分组后，展示利润：
SELECT year, SUM(profit) AS profit FROM sales GROUP BY year; /*结果*/ /* +------+--------+ | year | profit | +------+--------+ | 2000 | 4525 | | 2001 | 3010 | +------+--------+ */
现在要想获得所有年份的利润加起来的总利润，只能在此基础上再运行一个查询。而ROLLUP修饰符就是为此而生的，在单个查询上对数据进行不同级别的聚合操作。
在GROUP BY子句添加WITH ROLLUP后，查询结果会添加一个高级别的聚合行，显示所有奶奶分的利润总和：
SELECT year, SUM(profit) AS profit FROM sales GROUP BY year WITH ROLLUP; /*结果*/ /* +------+--------+ | year | profit| +------+--------+ | 2000 | 4525 | | 2001 | 3010 | | NULL | 7535 | +------+--------+ */
year字段下的null代表高级别的聚合行。
二、多字段分组时ROLLUP的用法
当对表中多个字段进行分组时，ROLLUP会有更复杂的高级别聚合操作。
这种情况下，当GROUP BY后字段中，除最后一个字段外的任一字段的值将要发生改变时，MySQL就会产生一个高级别的聚合（汇总）行。
例如，对上面的第一个分组查询添加“WITH ROLLUP”：
SELECT year, country, product, SUM(profit) AS profit FROM sales GROUP BY year, country, product WITH ROLLUP;
结果如下：
按照之前所提到的规则，应该是：每当year和country列的值有变动时，就会产生一个高级别的汇总行。
上面的结果有四种级别的销售数据：
- 某一年在某个国家单个产品的销售利润（没有ROLLUP的数据）
- 某一年在某个国家所有产品的总销售利润（例如2000年在芬兰的总利润为1600）
- 某一年在所有国家的总销售利润（2000年，全球的利润为4525）
- 所有年份的利润总和（2000，2001两年的总利润为7535）
三、高级聚合行中的NULL值
MySQL服务器把高级聚合行发送到MySQL客户端时，将会生成每个高级聚合行中的NULL指示符。
因为高级聚合行中的NULL值是在查询处理的后期阶段被放入结果集中，所以开发者只能在select或者having子句中将它们作为null值进行测试。你无法您无法在连接条件或WHERE子句中将它们作为NULL值进行测试，开确定要选择的行。例如，开发者不能在查询中添加“where product is null”来消除掉高级聚合行之外的行。
那么该如何区分一个列中的NULL值是高级聚合行中的null呢？即怎么筛选出高级聚合行，返回高级聚合信息。MySQL 8.0中提供了grouping(field)函数来区分它们。当某一行的null值属于MySQL为高级聚合行生成的NULL值时，就返回1。我们就可以据此对这些NULL值进行重命名。详情参考MySQL 8.0官方手册——GROUPING()。
四、在使用ROLLUP时的其它事项
4.1 当使用ROLLUP时，不能同时使用ORDER BY对结果集进行排序。
即在MySQL中，ROLLUP和ORDER BY是互斥的，当然可以通过变通的方式来同时实现他们，即将分组结果集生成为派生表，应用order by。例如：
SELECT * FROM(SELECT year, SUM(profit) AS profit FROM sales GROUP BY year WITH ROLLUP) AS dt ORDER BY year DESC;
五、LIMIT与ROLLUP
LIMIT可以限定返回给MySQL客户端的行的数量。
若在ROLLUP后面应用LIMIT，因而此时的数量限制也同样适用于通过ROLLUP添加的额外的行。（即高级聚合行没有特权）
例如：
SELECT year, country, product, SUM(profit) AS profit FROM sales GROUP BY year, country, product WITH ROLLUP LIMIT 5; /* +------+---------+------------+--------+ | year | country | product | profit | +------+---------+------------+--------+ | 2000 | Finland | Computer | 1500 | | 2000 | Finland | Phone | 100 | | 2000 | Finland | NULL | 1600 | | 2000 | India | Calculator | 150 | | 2000 | India | Computer | 1200 | +------+---------+------------+--------+ */
这种情况容易让人迷惑，无法有效识别出高级聚合行。
六、MySQL的ONLY_FULL_GROUP_BY模式
该模式由系统变量sql_model控制。
当sql_model='ONLY_FULL_GROUP_BY'时，在select中出现未在Group By后进行分组的字段时，服务器会拒绝这种查询。
显然默认情况下，并未开启这种模式，即我们可以在select中直接指定未被进行分组的字段。这种状况下，在这种情况下，服务器可以自由选择摘要行中此非聚合列的任何值，这包括WITH ROLLUP添加的额外行。
如下所示，country是未被执行分组操作的字段，而从该字段返回的值是不确定的。（但是在这里展示country除了困惑人又有什么意义呢？）
SELECT year, country, SUM(profit) AS profit FROM sales GROUP BY year WITH ROLLUP; /* +------+---------+--------+ | year | country | profit | +------+---------+--------+ | 2000 | India | 4525 | | 2001 | USA | 3010 | | NULL | USA | 7535 | +------+---------+--------+ */
更让人困惑的是，在“ONLY_FULL_GROUP_BY”模式下， MySQL提供了ANY_VALUE()函数来执行上述操作（返回未被聚合列的任意一个值）。
下一节会对这种情况有专门的阐述。
