分组聚合 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等。聚合函数有时也被称为汇总函数、分组函数。

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

本文不会涉及分析函数的内容。

如何求分组最大、最小、总和、平均数、计算、标准差、均差等问题，各位都玩得贼溜，有悟不再啰嗦。

上图是《SQL技巧：行列转换》中的示例。根据左边产品区域销量，分别汇总区域销量、全部销量，可使用如下SQL来计算。

-- 区域销量
select 区域, sum(销量)
from   产品销量
group  by 区域;

-- 全部销量
select sum(销量)
from   产品销量;

是否存在一种用法，计算区域销量后，连同汇总全部销量呢？答案是肯定的。

-- 直接将两个不同维度的结果集合并
select 区域, sum(销量)
from   产品销量
group  by 区域

union  all
select '总和', sum(销量)
from   产品销量;

-- 若产品销量表记录数比较多
with 区域销量 as
(
  select 区域, sum(销量) as 销量
  from   产品销量
  group  by 区域
)
select '总和', sum(销量)
from   区域销量

union  all
select 区域, 销量
from   区域销量;

也许你好奇心十足，还想知道有没有其它更好的方法？答案依然是肯定的。

select 区域, sum(销量)
from   产品销量
group  by rollup(区域);

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

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

多分组 #

what? rollup? 🤔

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

也许以前在学习SQL时见过相关的用法，只是暂时忘记，有悟帮你唤醒。也许根本就没见过，永远也没有机会知道（在《什么是SQL》中，有悟建议大家没事多去翻翻数据库sql手册）。

group by rollup() 是sql分组聚合的扩展用法。与 ROLLUP 类似的还有 CUBE、GROUPING SETS，配合使用的有GROUPING、GROUPING_ID、GROUP_ID。

函数	postgres	oracle	hivesql㊟	mysql㊟	sqlite
ROLLUP	✔	✔	✔	✔	✘
CUBE	✔	✔	✔	✘	✘
GROUPING SETS	✔	✔	✘	✘	✘
GROUPING	✔	✔	✔	✔	✘
GROUPING_ID	✔	✔	✔	✔	✘
GROUP_ID	✔	✔	✔	✘	✘

㊟：

Hive 0.10.0 以后才支持，来自官方文档的说明 Enhanced Aggregation, Cube, Grouping and Rollup
虽然mysql是使用最广泛的开源数据库，但说它功能非常强大丰富就不合适了。绝大多数使用mysql的目的是存储、管理数据（OLTP），并没有强调它计算能力（OLAP）。在mysql中，rollup 被称为 GROUP BY Modifiers，GROUP BY 修饰符。使用rollup功能时，语法上有些差异，mysql中 GROUPING、GROUPING_ID 为同名函数，且mysql8之后才支持。

有悟几乎没接触过 mssql server，对它的了解甚少。

-- mysql 中的 rollup 语法
select 区域, sum(销量)
from   产品销量
group  by 区域 with rollup;

rollup #

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 语句 ，它的执行结果有这样的特征，结果集按基于分组字段的行，即分组字段成组的键可以用来唯一确定这个结果行（在《什么是SQL》中，特别强调的重点，数据行的业务标识（唯一性）是其中之一）。那么就可以使用 group by 语句去重复数据，效果等同于 select distinct。

字符串拼接 #

“字符串拼接”是绝对能表现SQL操作数据功能的技巧。我们日常操作的数据，除了数字型之外，还有重要的字符类型。

题外话：由于JSON的大规模使用，有些数据库产品也在添加了原生类型与函数支持。再去翻查下手册，会发现数据库内置了非常多的XML函数，说明有一段时间，非常流行数据库处理XML数据。（在有悟的工作生涯里，没有碰到过）

字符类型除了被用于做标识、代码编码之外，有时需要将某个分组的多个字符合并连接体现到结果行中。这在编程语言非常常见，但在基于数值计算的数据库，这个功能很晚才被原生支持。字符串不是数字，不能使用数学方法进行数值计算，但字符串分组拼接的过程，却与分组汇总过程非常相似。这涉及到一个重要的思维方式升级，把字符与数字都看成的数据。现在主流数据库都提供了对应的聚合函数，如 oracle 数据库的 LISTAGG、postgres数据库的string_agg、mysql与sqlite的 group_concat，在这些原生函数出现之前，手写 “字符串拼接” SQL 是被视为体现娴熟掌握SQL的一个技巧。

⚠️ 开卷：

你能区分 分组聚合 与 分析函数（窗口函数）的区别吗？