3 min to read
浅析 SQL 窗口函数与聚合函数
浅析 SQL 窗口函数与聚合函数
1. 序言
大 K 和 小 Q 是一家企业内数据分析部门的同事。大 K 是部门中的一名小组经理，此前是一名资深数据分析师，拥有较丰富的数据分析经验。而小 Q 是一名应届毕业生，今年通过秋招新晋加入公司，是组内的一名新人数据分析师。一天到了下班时间，大 K 像往常一样高效高质地完成了手头的工作，收拾了一下行装以后从座位起身，迈出稳健的步伐，向电梯间方向走去，准备坐电梯离开公司。当他路过小 Q 的座位时，发现小 Q 正对着眼前的电脑屏幕若有所思，面孔上露出一丝难色。颇有洞察力的大 K 面对眼前这番景象，认为小 Q 可能在工作中遇到了一定困难，就想了解一下情况。他走到小 Q 身旁，试探性地问了问他：“小 Q，之前分配给你完成的分析报表做得怎样啦？” 小 Q 很尊敬 大 K，而且觉得大 K 为人也很亲和，容易相处，就经常向他请教一些工作上的问题，这次也不例外。他长吐了一口气，对大 K 说：“确实碰到了一个挺棘手的问题呢。现在我手上有个需求，要求我统计公司于电商渠道销售的各产品购买客户于去年全年的平均复购天数，并按月份分组，且按月将该月发生复购的客户按复购天数升序排列。我之前尝试用 GROUP BY + 聚合函数来求出上述结果，但我发现我写出来的语句中有不少子查询和表连接，让查询语句显得冗长难懂。另外我的查询运行起来也很慢，我想部分原因来自数据量大，而另一部分原因我也不太能讲清楚，大 K 你之前遇到过类似的需求吗？对我现在遇到的问题有什么看法吗？” 大 K 看着小 Q 一脸困惑无奈的神情，对小 Q 说：“建议你去了解一下 SQL 窗口函数，想想该怎么用它来解决你手头的问题吧。” 那么大 K 为什么会这么和小 Q 说呢？窗口函数究竟有什么特别的地方呢？
2. 窗口函数与聚合函数的定义
听了上面大 K 和 小 Q 两人间的故事，你是不是也想起自己曾经遇到过类似问题呢？如果你熟悉 SQL ，相信你十有八九知道窗口函数和聚合函数，至少听说过。我们先回顾一下这两类函数的定义，重温一下相关知识。
先说聚合函数，通常聚合函数会被用来对一张数据库表中的某一列或几列的全量数据进行汇总计算，得到一个汇总值。这类函数的例子有 COUNT(), SUM(), MAX(), MIN(), AVG()等等。比如说我们现在有一张数据库表，里面有国家、地区、城市、产品 SKU 等维度字段和产品销售额、毛利额等度量字段。我们需要求得某国家某一地区各款产品的总毛利额。这个时候我们就可以用到聚合函数来帮我们实现这一需求，参考下面的 SQL 语句：
WITH gross_prof (country,regn,prod,sum_grossp) AS (
SELECT country, region, prod, SUM(grossp)
FROM prod_sales
)
SELECT prod, sum_grossp
FROM gross_prof
GROUP BY country, region, prod;
聚合函数一般会和GROUP BY一起使用。 GROUP BY在 SQL 执行过程中于 WHERE 后执行，它会聚合由 WHERE 子句过滤的数据集，并且输出分组。通过 GROUP BY 聚合得到的组一定是唯一的。值得一提的是，由于 GROUP BY 子句先于 SELECT 子句被执行，因此在SELECT子句被执行前数据将被按照 GROUP BY 子句中的条件分组。而如果SELECT子句中存在非聚合字段，那么这些字段就必须出现在 GROUP BY 子句中 （SELECT 列表中的非聚合字段需要是 GROUP BY 中字段的子集）。通常情况下，人们习惯在 GROUP BY 子句中只使用一个聚合字段，前提是该字段能够唯一地确定其余非聚合字段的值。由于本篇文章旨在对比聚合函数与窗口函数，在此不对GROUP BY 做过多展开，如感兴趣可以参考的 《EFFECTIVE SQL》 一书中的相关章节（Viescas 等，2018）。
再说说窗口函数。窗口函数和聚合函数类似，也可以对数据进行分组计算。而与聚合函数的不同在于其在对指定的分组执行聚合操作后会返回多个而非一个值。“窗口”代表要聚合的分组。在被执行时，窗口函数不会对整个结果集进行分组，而在结果集的子集中执行计算。这种特性令窗口函数可以用于在同一行结果集中执行多级聚合。举例来说，当我们希望知道某公司中各部门员工的信息以及全公司的员工总数时，我们可以用窗口函数来实现这一查询：
SELECT dept, e_no, e_name, COUNT(*) OVER() emp_count, SUM(salary) total_sal OVER(PARTITION BY dept ORDER BY salary)
FROM emp;
而如果我们只使用 GROUP BY 与聚合函数的话，我们将不得不使用标量子查询：
SELECT dept, e_no, e_name, (
SELECT COUNT(*)
FROM emp
) emp_count, SUM(salary) total_sal
FROM emp
GROUP BY dept, e_no, emp_count
ORDER BY salary
显然，上述查询在数据集中的记录数很多时会较低效。因此，窗口函数是一个显而易见的选择。
窗口函数亦被称为 OLAP 函数或分析函数。常用的窗口函数包括ROW_NUMBER(), RANK(), SUM_OVER()等。窗口函数在 WHERE 子句后，ORDER BY 子句前被执行，因此 WHERE 中的筛选条件会影响窗口函数返回的结果。
与聚合函数只由一部分构成不同的是，窗口函数由两大元素组成：窗口函数以及窗口定义子句。其中窗口函数与聚合函数类似，而窗口定义子句则允许用户定义好窗口的范围和窗口中记录的排序方式，以及窗口框架 (window frame) 或者框架子句 (framing clause)，该子句指定了要在计算中指定的数据子窗口范围。如果说我们可以把窗口函数比作一个书架的话，那么窗口函数就可以被认为是对书架上的书进行分类汇总统计的方法，而窗口定义子句则对应了书架各栏位的大小和栏位中书本的排序方式，而框架子句则类似书架栏位中将书本分隔开的一个个书立。如果聪明伶俐的读者有更恰当的比喻，欢迎在评论区下方留言讨论👇
3. 窗口函数与聚合函数的共性
下面谈谈窗口函数和聚合函数的一些共性。首先，窗口函数和聚合函数都可以对数据进行分组聚合，并且返回聚合后的结果。两者都对 NULL 值敏感，在进行分组时都会将该值单独编入一组中。比如我们有家公司新近成立，在该公司内部有一些部门目前暂时处于已挂名但尚无伙伴加入的状态。那么此时如果我们想知道这个公司中所有部门的在职伙伴总数应该怎么办呢？我们可以考虑用分组聚合和窗口函数来解决这个问题。假定我们用分组聚合：
SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id,d.department_name;
此时如果在departments表里存在department_name为空值的行，那么分组聚合的结果中就会出现 department_name 为 NULL 的行，而 employee_count的结果则视 COUNT() 函数中的参数而定。在本例中，COUNT(e.employee_id) 中被传入的参数为e.employee_id，即为具体的一列。由于聚合函数只计算非 NULL 值的个数，这一行对应的 employee_count 值即为 0。换言之，由于这个名称为 NULL 的部门没有任何伙伴在职，员工数量自然为 0。而如果我们用 COUNT(*)，那么 department_name 为 NULL 的行对应的 employee_count 的值即为1，原因是 COUNT(*) 计算了组内的所有行数，而其中包括 department_name 为 NULL 的那一行。
假如我们用窗口函数来实现这一查询，那么可以将上述查询改写成如下形式：
SELECT d.department_name, COUNT(e.employee_id) OVER(PARTITION BY d.department_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id;
4. 窗口函数与聚合函数的区别
上述对窗口函数和聚合函数的介绍和举例说明其实已经或多或少提及了两者的区别，想必聪明伶俐的读者一定已经有了自己的想法。那么在这里我们把两者的区别再单独拎出来强调一下。
从处理方式来说，聚合函数在执行计算前必然涉及到对整个数据集的分组，而窗口函数则会对数据集的子集分组，具体的子集（或窗口）取决于 PARTITION BY 子句中定义的列，当传入 OVER() 的参数为空时，即窗口定义子句为空，那么此时函数的计算结果则基于数据集中的所有行。
从参数和用法来说，聚合函数通常只需要一列或多列作为参数，然后返回一个汇总值。而窗口函数则需要两个参数，首先是列或表达式，接下来还包括窗口定义子句，该子句定义了窗口的范围和各窗口中结果集的排序方式，此外用户还可以定义在窗口函数执行计算时需要将当前记录前后的哪些记录考虑在内，比如位于该记录前，且位于当前分区内的所有值；或者当前记录的前后几条记录。需要注意的是，窗口函数家族中既有可以聚合查询结果的，又有可以返回单个数值的，需要根据具体情况选择使用合适的函数。
从返回的结果来说，聚合函数只为每一组返回单个值。而窗口函数则为数据集中的每行返回一个值。
从性能来说，在某些情况下窗口函数的性能会优于聚合函数，尤其是在涉及多级聚合的场景下，使用窗口函数不但效率更高，而且查询的可读性更佳。
从使用场景来说，窗口函数和聚合函数也有各自合适的领域。例如在需要返回数据集的汇总信息，比如某企业某年的总销售额时，我们可以选择使用聚合函数。而另一方面，如果我们需要对数据集的子集进行汇总计算时，我们就会自然地想到使用窗口函数。这样的一个例子是统计某公司销售部门负责销售的各品类产品的销售额，并按销售额排行前五的销售人员汇总。
5. 对比窗口函数与聚合函数
这里用一张表格来对窗口函数和聚合函数做一个整体的对比。
| 窗口函数 | 聚合函数 | |
|---|---|---|
| 使用场景 | 需要同时返回聚合数据和明细数据，且数据量较大时 | 需要返回基于整个数据集的单个统计值时 |
| 参数和用法 | 两大部分：窗口函数+窗口定义子句，其中窗口定义子句中又包含三部分：分区子句、排序子句和框架子句 | 聚合函数 + GROUP BY |
| 执行顺序 | 窗口函数在WHERE、GROUP BY 子句后，ORDER BY 子句前被执行 | 聚合函数在 FROM 后，SELECT 子句被执行时被执行，GROUP BY 在 WHERE 子句后，SELECT 子句前被执行，注意 GROUP BY 子句中需要包含 SELECT 子句中所有未被聚合的字段 （若某一非聚合字段与其它非聚合字段存在一一对应关系除外） |
| 结果 | 针对数据集中的每一行返回一个值 | 针对每个分组返回一个值 |
| 处理方式 | 每次被执行时对数据集中的单行进行计算 | 每次被执行时对数据集中的所有行进行计算 |
| 性能 | 很好，尤其在查询需要同时返回明细和聚合数据时 | 较好，在数据量较小且需要返回整个数据集的统计值时性能与窗口函数无明显差异 |
6. 总结
聚合函数和窗口函数都是 SQL 中常用的两类函数，其中窗口函数在聚合函数的基础上进行了拓展，能够实现对数据集子集的分组计算，并方便快捷地实现多级聚合。两类函数存在差异和共同点，也有着各自不同的适用场景。我们可以根据具体的使用场景来选择合适的函数帮我们拿到结果。
7. 小提示
MySQL 8.0 及以后版本中包含 Window 子句特性。各位与数据打交道的伙伴可以利用该特性来定义窗口并且为其赋予一个名称。比如像这样：
SELECT row_number() OVER(ws)
FROM tbl
WHERE 1=1
window ws AS (ORDER BY col)
个人认为这是一个不错的特性，大家觉得呢？
参考来源
- Viescas 等，2018。《Effective SQL》，机械工业出版社。
- 百度文心一言
- MySQL 官方文档
Author
Xiaoming
A young fellow who is passionate about tech, on the way of becoming a better developer
Comments