Skip to main content
  1. 经验技巧分享/
  2. thinking in SQL | 废话SQL,思维与实践/

分组聚合 group by

··572 字
sql 概念

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

说在前面 #

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

Aggregate Function
翻译成 聚合函数,从多行输入计算单个结果行。返回的结果是基于行组而不是单行(与single row funtion 单行函数的区别)。它可以出现在 选择列表order by子语having 子语中。通常我们见到的 select 分组字段, sum() from 表名 group by 分组字段 [having ...]形式的分组汇总SQL,其中的SUM函数就是聚合函数,常用的聚合函数还有COUNTAVGMAXMIN等。聚合函数有时也被称为汇总函数、分组函数。

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

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

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


数据汇总
数据汇总

上图是 《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 类似的还有 CUBEGROUPING SETS,配合使用的有GROUPINGGROUPING_IDGROUP_ID

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

㊟:

  1. Hive 0.10.0 以后才支持,来自官方文档的说明 Enhanced Aggregation, Cube, Grouping and Rollup
  2. 虽然mysql是使用最广泛的开源数据库,但说它功能非常强大丰富就不合适了。绝大多数使用mysql的目的是存储、管理数据(OLTP),并没有强调它计算能力(OLAP)。在mysql中,rollup 被称为 GROUP BY Modifiers,GROUP BY 修饰符。使用rollup功能时,语法上有些差异,mysql中 GROUPINGGROUPING_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 #

ROLLUPCUBEGROUPING 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的一个技巧。


⚠️ 开卷:

  1. 你能区分 分组聚合分析函数(窗口函数)的区别吗?