MySQL 用得上的新特性(一) -- GROUP BY Modifier

0x01 白话解释

顾名思义，GROUP BY 的修饰符，对 GROUP BY 的结果，提供二次处理的能力。

---

0x02 举例说明

平均工资问题，已知部门薪资表如下：

CREATE TABLE `employee` (
 `name` varchar(32) DEFAULT NULL,
 `department_id` int DEFAULT NULL,
 `salary` int DEFAULT NULL
)

数据如下：

name	department_id	salary
张三	3	27000
张四	2	4000
张五	1	18000
张六	1	17000
赵三	3	26000
赵四	2	18000

求每个部门的平均工资，和整体平均工资。

---

0x03 举例题解

题解 1： 使用两个语句分别求出各个部分和总体的 avg(salary)，然后再 union 两个语句

select department_id, avg(salary) salary from employee group by department_id
union 
select 0, avg(salary) salary from employee 

题解 2： 使用 GROUP BY Modifier

select department_id, avg(salary) salary from employee group by department_id 
with rollup 

两者结果都是:

department_id	salary
3	26500.0000
2	11000.0000
1	17500.0000
null	18333.3333

---

0x04 解释者再

题解 2 尾部新增的 with rollup，这就是 GROUP BY Modifier。
回到开头的释义：

对 GROUP BY 的结果，提供二次处理的能力。 这里的二次处理，就是对 GROUP BY 的结果再次 GROUP BY，然后 union 两次数据。 题解 2 中使用with rollup，展示了和union相同的效果。因此，也可用 SQL 语句来解释： select a$_1$, a$_2$, a$_3$, sum(b) from tb group by a$_1$, a$_2$, a$_3$ with rollup 等价于 select a$_1$, a$_2$, a$_3$, sum(b) from tb group by a$_1$, a$_2$, a$_3$
union
select a$_1$, a$_2$, null, sum(b) from tb group by a$_1$, a$_2$
union
select a$_1$, null, null, sum(b) from tb group by a$_1$
union
select null, null, null, sum(b) from tb

自忖无法信达雅地将 MySQL 文档的英文诠释为中文，这里用自己的语言表述了一番。如果还是不太理解，建议直接看 MySQL 文档的一手定义。。

---

0x05 Tips

有一些函数和使用技巧，可以搭配使用

1. ifnull 函数

   select ifnull(department_id, 'all')
   

   搭配 MySQL 自带的ifnull函数, 可以将null转为需要的字符串。

   上述题解 2稍作修改

   select ifnull(department_id, 'all') department_id, avg(salary) salary 
   from employee group by department_id 
   with rollup 
   

   该结果会将 null 值替换为 all，具体效果还请自己实践。

2. Grouping 函数
   如果 GROUP BY 分组字段本身含有null值，例如department_id为空。 则执行结果中，会有两个null值，可以通过grouping(field)，来判断是super-aggregate，还是regular grouped field。
   这里只提供思路，不做具体展开，有兴趣的可以查看 Grouping 文档

0x06 题外话

都说PostgreSQL比MySQL要强，PostgreSQL在 2016 年就提供了该功能，关键字除了with rollup关键字之外，还有with cube和grouping sets，简而言之：

With cube: (a, b) => (a, b), (a, null), (null, b), (null, null)
Grouping sets: ((a, b), (a), (b), ()) `手动选取需要 group by 的具体字段`
With rollup: (a, b) => (a, b), (a, null), (null, null)

详细可以查看 PostgreSQL 文档。

Fin.