分组聚合 group by · 今日 | 有悟

分组聚合 group by ，会 select * 的人都会。如果不谈点高级的 group by 用法，有悟也没必要写此文了。

说在前面 #

在谈 group by 之前，先了解一个名词。


    Aggregate Function

翻译成 聚合函数 ，从多行输入计算单个结果行。返回的结果是基于行组而不是单行（与single row funtion 单行函数的区别）。它可以出现在 选择列表 、 order by子语 、 having 子语 中。通常我们见到的


    select 分组字段, sum() from 表名 group by 分组字段 [having ...]

形式的分组汇总SQL，其中的

SUM

函数就是聚合函数，常用的聚合函数还有


    COUNT

、

AVG

、

MAX

、

MIN

等。聚合函数有时也被称为汇总函数、分组函数。

聚合函数是数据库提供的两大类函数之一（另一大类函数为单行函数）。各知名数据库所支持的聚合函数，见官方文档。

为减少篇幅，本文后面出现的 union all group by 分组字段列表 代表

union all
select 分组字段列表, aggregate_function()
from   表
group  by 分组字段列表

多分组 #

what? rollup ? 🤔

当前，并没有一个名词来形容或者称呼这种用法，不是每个数据库产品均支持该功能。根据该功能的特点（合并多个不同分组的结果集），请允许有悟把它命名为 多分组聚合 。

也许以前在学习SQL时见过相关的用法，只是暂时忘记，有悟帮你唤醒。也许根本就没见过，永远也没有机会知道（在

group by 分组字段 -> group by rollup(分组字段)

通过前文，大家已经了解 ROLLUP 用法。它的作用就是将 rollup 函数中的字段列表，从右往左，逐一减少分组字段，形成更高级别的分组。 ROLLUP 多分组结果集大小，与分组字段列表中的字段数成正比。数据库把这些不同维度的结果行合并返回。

group by rollup(今, 日, 有, 悟)

与下面这段代码语义上等价

group by 今, 日, 有, 悟
union
group by 今, 日, 有
union
group by 今, 日
union
group by 今
union
group by () --全表汇总
下文会告诉大家更通用化的多分组用法
当然，ROLLUP函数的参数，除了字段外，还可以是字段列表。如 “今”、“日”理应“今日”，不管是按“今”，还是按“日”分组汇总，其结果是相同的，如果使用 rollup(今, 日)其结果是有重复值的，这里可以用 rollup((今日)) 来减少结果集行数。故 group by rollup(今, 日, 有, 悟) 可以简化为group by rollup((今, 日), (有, 悟))，语义等价于
group by 今, 日, 有, 悟
union
group by 今, 日
union
group by () --全表汇总
cube #
ROLLUP 函数，扩展了 group by语句，结果集中的分组是受字段列表中的字段顺序影响的。在多维数据库中，存储数据的表被称为cube（立方体）。立方体的记录数，与维度字段顺序无关，反而与字段数存在指数关系。
一些关系型数据库产品，提供了与 ROLLUP 函数类似的多分组汇总函数CUBE，它的功能类似于立方体，穷举分组字段的所有组合情况，逐一计算各个分组并合并结果集返回。
group by cube(今, 日, 有悟)
语义等价于:
group by 今, 日, 有悟
union all
group by 今, 日
union all
group by 今,     有悟
union all
group by     日, 有悟
union all
group by 今
union all
group by     日
union all
group by         有悟
union all
group by () -- 全表汇总
本例只使用了三个字段做分组，结果就有8种分组情况。（如果你接触过多维数据库，对于稀疏维度、数据倾斜就不会陌生）并不是每一种分组都是我们需要的结果，CUBE 函数也支持字段列表做为参数单元。
group by cube((今, 日), 有悟)
语义等价于:
group by 今, 日, 有悟
union all
group by 今, 日
union all
group by         有悟
union all
group by () -- 全表汇总
使用 CUBE 函数时，应极为小心，它增加非常多的分组聚合计算，极快的膨胀聚合结果，降低查询返回结果的速度。虽然可以配合grouping_id来做结果集过滤，但过滤是在计算完成才发生，无法减少不必要的资源浪费。分组设计、计算资源使用、结果存储是重要的权衡指标。如果只是要分组字段列表中的某种组合，可以使用GROUPING SETS。
grouping sets #
ROLLUP 函数产生的分组太生硬、CUBE 函数产生的分组数太多太浪费，那么GROUPING SETS一定能满足你的要求。它与前两者一样，同样支持多分组，只是分组列表由你来指定。
例，若只分别统计分组“(今, 日)”、“(有, 悟)”后再全部聚合，使用 rollup 函数得不了纯最右边分组的聚合，使用cube 函数都会产生多余的结果，而用 GROUPING SETS 可以写为:
group by grouping sets (
    (今, 日)
  , (有, 悟)
  , ()
可以看出，GROUPING SETS 正是前文等价示例中group by ... union all group by ...的简化形式，把有需要的分组列表使用 GROUPING SETS 函数包裹起来。
grouping、grouping_id #
ROLLUP、CUBE、GROUPING SETS 等函数把多个不同分组的结果合并，程序无法无将各不同的分组结果集对应起来，这时grouping函数来帮忙。
select 今日
     , 有悟
     , grouping(今日) as g_今日
     , grouping(有悟) as g_有悟
     , grouping_id(今日, 有悟) as gid
     , ...
from   ...
group  by rollup(今日, 有悟);
今日 | 有悟 | g_今日 | g_有悟 | gid
----|-----|-------|-------|------
🍗  | 🍺  |  0    |  0    | 0
🍗  |NULL |  0    |  1    | 1	 
NULL|NULL |  1    |  1    | 3
grouping(分组字段)
返回 0 或 1 。它唯一的作用，标识当前行数据是否是该分组字段的聚合行。
可以根据 grouping 值（总是0或1）来判断是否是某个字段的聚合，从而根据需要进行值选择。grouping函数可以在 select选择列表、having过滤 语句中使用。
grouping_id(分组字段, 分组字段 , ...)
返回各grouping(分组字段)值所组成的位向量（“01”串）对应的十进制数字。它的值与分组字段中的顺序有关。它与grouping函数用法类似，通常被用来判断是否某个分组的聚合行结果，可以在select选择列表、having过滤 语句中使用。mysql 中提供的函数为grouping(分组字段 [, 分组字段 , ...])。
分组聚合特殊用途 #
在本文中，有悟尽量避免使用“分组聚合”，而不使用“分组汇总”。group by 语句 除了被用在数字型的数据统计、汇总场景外，还有其他用途，而使用“分组汇总”把用途限制了。
去重复数据 #
通过观察 group by 语句 ，它的执行结果有这样的特征，结果集按基于分组字段的行，即分组字段成组的键可以用来唯一确定这个结果行（在
中，特别强调的重点，数据行的业务标识（唯一性）是其中之一）。那么就可以使用 group by 语句去重复数据，效果等同于 select distinct。
字符串拼接 #
“字符串拼接”是绝对能表现SQL操作数据功能的技巧。我们日常操作的数据，除了数字型之外，还有重要的字符类型。
题外话：由于JSON的大规模使用，有些数据库产品也在添加了原生类型与函数支持。再去翻查下手册，会发现数据库内置了非常多的XML函数，说明有一段时间，非常流行数据库处理XML数据。（在有悟的工作生涯里，没有碰到过）
字符类型除了被用于做标识、代码编码之外，有时需要将某个分组的多个字符合并连接体现到结果行中。这在编程语言非常常见，但在基于数值计算的数据库，这个功能很晚才被原生支持。字符串不是数字，不能使用数学方法进行数值计算，但字符串分组拼接的过程，却与分组汇总过程非常相似。这涉及到一个重要的思维方式升级，把字符与数字都看成的数据。现在主流数据库都提供了对应的聚合函数，如 oracle 数据库的 LISTAGG、postgres数据库的string_agg、mysql与sqlite的 group_concat，在这些原生函数出现之前，手写 “字符串拼接” SQL 是被视为体现娴熟掌握SQL的一个技巧。
⚠️ 开卷：
你能区分 分组聚合 与 分析函数（窗口函数）的区别吗？