「SQL 数据分析系列」16. 分析函数
写在前面：
大家好，我是强哥，一个热爱分享的技术狂。目前已有 12 年大数据与 AI 相关项目经验， 10 年推荐系统研究及实践经验。平时喜欢读书、暴走和写作。
业余时间专注于输出大数据、AI 等相关文章，目前已经输出了 40 万字的推荐系统系列精品文章，强哥的畅销书「构建企业级推荐系统：算法、工程实现与案例分析」已经出版，需要提升可以私信我呀。如果这些文章能够帮助你快速入门，实现职场升职加薪，我将不胜欢喜。
想要获得更多免费学习资料或内推信息，一定要看到文章最后喔。
内推信息
如果你正在看相关的招聘信息，请加我微信：liuq4360，我这里有很多内推资源等着你，欢迎投递简历。
免费学习资料
如果你想获得更多免费的学习资料，请关注同名公众号【数据与智能】，输入“资料”即可！
学习交流群
如果你想找到组织，和大家一起学习成长，交流经验，也可以加入我们的学习成长群。群里有老司机带你飞，另有小哥哥、小姐姐等你来勾搭！加小姐姐微信：epsila，她会带你入群。
2020 Learning SQL-中译
第十六章分析函数
数据量一直在以惊人的速度增长，组织很难存储所有数据，更不用说试图理解这些海量数据了。数据分析一般是在数据库服务器之外使用专门的工具或语言（如 Excel、R 和 Python）进行的，而 SQL 语言包含一组有利于分析处理的强大函数。如果要对公司销售人员进行排名选出前十名，或者要为客户生成财务报告并且需要计算三个月的滚动平均数，则可以使用 SQL 内置分析函数来执行这些类型的计算。
分析函数概念
在数据库服务器完成评估查询所需的所有步骤（包括连接、过滤、分组和排序）之后就可以将生成的结果集返回给调用者了。想象一下，如果此时可以中止查询，并在结果集仍保留在内存中的情况下遍历结果集，你会希望执行什么类型的分析？如果结果集包含销售数据，可能你会想为销售人员或地区生成排名，或者计算两个时间段之间的百分比差异。如果要生成财务报表，则可能需要计算每个报表部分的小计，以及最后生成总计。你可以使用分析函数做到包括但不限于以上情况的所有事情。在深入讨论细节之前，以下小节将介绍几种最常用的分析函数机制。
数据窗口
假设你编写了一个查询，用于生成给定时间段的每月销售总额。例如，以下查询汇总了 2005 年 5 月至 8 月期间电影租赁操作的每月总付款额：
mysql> SELECT quarter(payment_date) quarter,
-> monthname(payment_date) month_nm,
-> sum(amount) monthly_sales
-> FROM payment
-> WHERE year(payment_date) = 2005
-> GROUP BY quarter(payment_date), monthname(payment_date);
+---------+----------+---------------+
| quarter | month_nm | monthly_sales |
+---------+----------+---------------+
| 2 | May | 4824.43 |
| 2 | June | 9631.88 |
| 3 | July | 28373.89 |
| 3 | August | 24072.13 |
+---------+----------+---------------+
4 rows in set (0.13 sec)
从结果可以看到七月是所有四个月中月总付款额最高的月份，六月是第二季度月总付款额最高的月份。然而，为了让程序自动显示最高值，需要向每行添加额外的列，以显示每季度和整个时间段内的最大值。基于上一个查询的修改版本如下，新添加了两列用于计算最大值：
mysql> SELECT quarter(payment_date) quarter,
-> monthname(payment_date) month_nm,
-> sum(amount) monthly_sales,
-> max(sum(amount))
-> over () max_overall_sales,
-> max(sum(amount))
-> over (partition by quarter(payment_date)) max_qrtr_sales
-> FROM payment
-> WHERE year(payment_date) = 2005
-> GROUP BY quarter(payment_date), monthname(payment_date);
+---------+----------+---------------+-------------------+----------------+
| quarter | month_nm | monthly_sales | max_overall_sales | max_qrtr_sales |
+---------+----------+---------------+-------------------+----------------+
| 2 | May | 4824.43 | 28373.89 | 9631.88 |
| 2 | June | 9631.88 | 28373.89 | 9631.88 |
| 3 | July | 28373.89 | 28373.89 | 28373.89 |
| 3 | August | 24072.13 | 28373.89 | 28373.89 |
+---------+----------+---------------+-------------------+----------------+
4 rows in set (0.09 sec)
用于生成这些附加列的分析函数将行分为两个不同的集合：一个集合包含同一季度的所有行，另一个集合包含所有行。为了处理这种类型的分析，分析函数包括将行分组到窗口（window）中的功能，以便有效地划分数据以供分析函数在不更改整个结果集的前提下使用。窗口由 over 子句和可选的以子句划分的 partition 来定义。在前面的查询中，两个分析函数都包括 over 子句，但是第一个分析函数的 partition 为空，说明窗口应该包含整个结果集，第二个分析函数的 partition 非空，指定窗口应仅包含同一季度内的行。数据窗口可以包含从一行到结果集中所有行的任何情况 ,不同的分析函数可以定义不同的数据窗口。
局部排序
除了将结果集划分到分析函数的数据窗口外，还可以指定排序的顺序。例如，如果要为给每个月定义排名，值 1 指定销售额最高的月份，则需要指定用于排名的列：
mysql> SELECT quarter(payment_date) quarter,
-> monthname(payment_date) month_nm,
-> sum(amount) monthly_sales,
-> rank() over (order by sum(amount) desc) sales_rank
-> FROM payment
-> WHERE year(payment_date) = 2005
-> GROUP BY quarter(payment_date), monthname(payment_date)
-> ORDER BY 1, month(payment_date);
+---------+----------+---------------+------------+
| quarter | month_nm | monthly_sales | sales_rank |
+---------+----------+---------------+------------+
| 2 | May | 4824.43 | 4 |
| 2 | June | 9631.88 | 3 |
| 3 | July | 28373.89 | 1 |
| 3 | August | 24072.13 | 2 |
+---------+----------+---------------+------------+
4 rows in set (0.00 sec)
此查询包括对 rank 函数的调用（将在下一节中介绍），并指定 amount 列的总和用于生成排名，按值的多少降序排序。因此，销售额最高的月份（本例中为 7 月）的排名将为 1。
注意：前面的示例包含两个 order by 子句，一个位于查询尾部，用于确定结果集应如何排序，另一个位于 rank 函数中，用于确定应如何分配排名。要知道同一子句用于不同的目的，所以即使将分析函数与一个或多个 order by 子句一起使用，如果希望以特定方式对结果集排序，则仍需在查询尾部使用 order by 子句。
在某些情况下，会需要在同一个分析函数调用中同时使用 partition by 和 order by 子句。例如，可以修改上一个示例使得显示不同的每个季度的排名，而不是整个结果集的单个排名：
mysql> SELECT quarter(payment_date) quarter,
-> monthname(payment_date) month_nm,
-> sum(amount) monthly_sales,
-> rank() over (partition by quarter(payment_date)
-> order by sum(amount) desc) qtr_sales_rank
-> FROM payment -> WHERE year(payment_date) = 2005
-> GROUP BY quarter(payment_date), monthname(payment_date)
-> ORDER BY 1, month(payment_date);
+---------+----------+---------------+----------------+
| quarter | month_nm | monthly_sales | qtr_sales_rank |
+---------+----------+---------------+----------------+
| 2 | May | 4824.43 | 2 |
| 2 | June | 9631.88 | 1 |
| 3 | July | 28373.89 | 1 |
| 3 | August | 24072.13 | 2 |
+---------+----------+---------------+----------------+
4 rows in set (0.00 sec)
上述示例旨在说明 over 子句的用法，以下各节将详细介绍各种分析函数。
排名
人们喜欢给事物排序。如果要访问你最喜欢的新闻/体育/旅游网站，你会看到类似下面的标题：
• 度假的十大价值；
• 最好的共同基金回报；
• 大学足球季前赛排名；
• 有史以来的前 100 首歌曲。
公司会出于更实际的目的使用排名（比如了解哪些产品最畅销/最滞销，或者哪些地区的收入最少/最多），有助于做出战略决策。
排名分析函数
SQL 标准中有多个可用的排名函数，每个函数都采用不同的方法来处理关系：
• row_number
为每一行返回一个唯一的数字，排名相等则可以任意指定排名。
• rank
排名相等的情况下返回相同的排名，名次中会留下空位。
• dense_rank
排名相等的情况下返回相同的排名，名次中不会留下空位。
下面看一个例子来了解这些函数之间的差别。如果市场部想为前十名客户发放免费电影租赁券，可以使用下面的查询，确定每位客户的租赁数，降序生成结果：
mysql> SELECT customer_id, count(*) num_rentals
-> FROM rental
-> GROUP BY customer_id
-> ORDER BY 2 desc;
+-------------+-------------+
| customer_id | num_rentals |
+-------------+-------------+
| 148 | 46 |
| 526 | 45 |
| 236 | 42 |
| 144 | 42 |
| 75 | 41 |
| 469 | 40 |
| 197 | 40 |
| 137 | 39 |
| 468 | 39 |
| 178 | 39 |
| 459 | 38 |
| 410 | 38 |
| 5 | 38 |
| 295 | 38 |
| 257 | 37 |
| 366 | 37 |
| 176 | 37 |
| 198 | 37 |
| 267 | 36 |
| 439 | 36 |
| 354 | 36 |
| 348 | 36 |
| 380 | 36 |
| 29 | 36 |
| 371 | 35 |
| 403 | 35 |
| 21 | 35 |
...
| 136 | 15 |
| 248 | 15 |
| 110 | 14 |
| 281 | 14 |
| 61 | 14 |
| 318 | 12 |
+-------------+-------------+
599 rows in set (0.16 sec)
结果显示排名第三和第四的客户都租了 42 部电影，那么他们是否都应该排名为三？如果是的话，租了 41 部电影的客户应该排名为四还是五呢？要查看每个函数在分配排名时如何处理关系，下一个查询将再添加三个列，每个列使用不同的排名函数：
mysql> SELECT customer_id, count(*) num_rentals,
-> row_number() over (order by count(*) desc) row_number_rnk,
-> rank() over (order by count(*) desc) rank_rnk,
-> dense_rank() over (order by count(*) desc) dense_rank_rnk
-> FROM rental -> GROUP BY customer_id
-> ORDER BY 2 desc;
+-------------+-------------+----------------+----------+----------------+
| customer_id | num_rentals | row_number_rnk | rank_rnk | dense_rank_rnk |
+-------------+-------------+----------------+----------+----------------+
| 148 | 46 | 1 | 1 | 1 |
| 526 | 45 | 2 | 2 | 2 |
| 144 | 42 | 3 | 3 | 3 |
| 236 | 42 | 4 | 3 | 3 |
| 75 | 41 | 5 | 5 | 4 |
| 197 | 40 | 6 | 6 | 5 |
| 469 | 40 | 7 | 6 | 5 |
| 468 | 39 | 10 | 8 | 6 |
| 137 | 39 | 8 | 8 | 6 |
| 178 | 39 | 9 | 8 | 6 |
| 5 | 38 | 11 | 11 | 7 |
| 295 | 38 | 12 | 11 | 7 |
| 410 | 38 | 13 | 11 | 7 |
| 459 | 38 | 14 | 11 | 7 |
| 198 | 37 | 16 | 15 | 8 |
| 257 | 37 | 17 | 15 | 8 |
| 366 | 37 | 18 | 15 | 8 |
| 176 | 37 | 15 | 15 | 8 |
| 348 | 36 | 21 | 19 | 9 |
| 354 | 36 | 22 | 19 | 9 |
| 380 | 36 | 23 | 19 | 9 |
| 439 | 36 | 24 | 19 | 9 |
| 29 | 36 | 19 | 19 | 9 |
| 267 | 36 | 20 | 19 | 9 |
| 50 | 35 | 26 | 25 | 10 |
| 506 | 35 | 37 | 25 | 10 |
| 368 | 35 | 32 | 25 | 10 |
| 91 | 35 | 27 | 25 | 10 |
| 371 | 35 | 33 | 25 | 10 |
| 196 | 35 | 28 | 25 | 10 |
| 373 | 35 | 34 | 25 | 10 |
| 204 | 35 | 29 | 25 | 10 |
| 381 | 35 | 35 | 25 | 10 |
| 273 | 35 | 30 | 25 | 10 |
| 21 | 35 | 25 | 25 | 10 |
| 403 | 35 | 36 | 25 | 10 |
| 274 | 35 | 31 | 25 | 10 |
| 66 | 34 | 42 | 38 | 11 |
...
| 136 | 15 | 594 | 594 | 30 |
| 248 | 15 | 595 | 594 | 30 |
| 110 | 14 | 597 | 596 | 31 |
| 281 | 14 | 598 | 596 | 31 |
| 61 | 14 | 596 | 596 | 31 |
| 318 | 12 | 599 | 599 | 32 |
+-------------+-------------+----------------+----------+----------------+
599 rows in set (0.01 sec)
第三列使用 row_number 函数为每一行分配唯一的排名而不考虑关系。这 599 行中的每一行都分配了一个从 1 到 599 的数字，对于租赁相同数量电影的客户，排名是任意分配的。不过在数量相等的情况下，后面两列分配的排名相同，然而区别在于这种情况下排名后名次有无空位。查看结果集的第 5 行，可以看到 rank 函数跳过值 4 并指定值 5，而 dense_rank 函数指定值为 4。
回到最初的例子——如何确定前十位客户？有三种可能的解决方案：
• 使用 row_number 函数来识别排名从 1 到 10 的客户，在本例中，结果正好是十个客户，但在其他情况下，可能会将与排名第十的客户拥有相同数量租赁数的客户排除在外；
• 使用 rank 函数来识别排名从 1 到 10 的客户，这样生成的客户数目也会是十；
• 使用 dense_rank 函数识别排名从 1 到 10 的客户，这将生成一个包含 37 个客户的列表。
如果结果集中不存在相等的情况，那么以上任意一个函数都能解决问题，但是在许多情况下，rank 函数可能是最好的选择。
生成多个排名
上一节中的示例在整个客户集中生成单个排名，但如果希望在同一结果集中生成多个排名又该怎么办呢？为了扩展前面的示例，假设市场部决定每月向前五名客户发放免费电影租赁券。要生成数据，可以将 rental_month 列添加到上一个查询中：
mysql> SELECT customer_id,
-> monthname(rental_date) rental_month,
-> count(*) num_rentals -> FROM rental
-> GROUP BY customer_id, monthname(rental_date)
-> ORDER BY 2, 3 desc;
+-------------+--------------+-------------+
| customer_id | rental_month | num_rentals |
+-------------+--------------+-------------+
| 119 | August | 18 |
| 15 | August | 18 |
| 569 | August | 18 |
| 148 | August | 18 |
| 141 | August | 17 |
| 21 | August | 17 |
| 266 | August | 17 |
| 418 | August | 17 |
| 410 | August | 17 |
| 342 | August | 17 |
| 274 | August | 16 |
...
| 281 | August | 2 |
| 318 | August | 1 |
| 75 | February | 3 |
| 155 | February | 2 |
| 175 | February | 2 |
| 516 | February | 2 |
| 361 | February | 2 |
| 269 | February | 2 |
| 208 | February | 2 |
| 53 | February | 2 |
...
| 22 | February | 1 |
| 472 | February | 1 |
| 148 | July | 22 |
| 102 | July | 21 |
| 236 | July | 20 |
| 75 | July | 20 |
| 91 | July | 19 |
| 30 | July | 19 |
| 64 | July | 19 |
| 137 | July | 19 |
...
| 339 | May | 1 |
| 485 | May | 1 |
| 116 | May | 1 |
| 497 | May | 1 |
| 180 | May | 1 |
+-------------+--------------+-------------+
2466 rows in set (0.02 sec)
要为每个月创建一组新排名，需要在 rank 函数中添加一些内容，以描述如何将结果集划分为不同的数据窗口（在本例中为月份），这是通过 partition by 子句实现的，该子句添加到 over 子句中：
mysql> SELECT customer_id,
-> monthname(rental_date) rental_month,
-> count(*) num_rentals,
-> rank() over (partition by monthname(rental_date)
-> order by count(*) desc) rank_rnk
-> FROM rental
-> GROUP BY customer_id, monthname(rental_date)
-> ORDER BY 2, 3 desc;
+-------------+--------------+-------------+----------+
| customer_id | rental_month | num_rentals | rank_rnk |
+-------------+--------------+-------------+----------+
| 569 | August | 18 | 1 |
| 119 | August | 18 | 1 |
| 148 | August | 18 | 1 |
| 15 | August | 18 | 1 |
| 141 | August | 17 | 5 |
| 410 | August | 17 | 5 |
| 418 | August | 17 | 5 |
| 21 | August | 17 | 5 |
| 266 | August | 17 | 5 |
| 342 | August | 17 | 5 |
| 144 | August | 16 | 11 |
| 274 | August | 16 | 11 |
...
| 164 | August | 2 | 596 |
| 318 | August | 1 | 599 |
| 75 | February | 3 | 1 |
| 457 | February | 2 | 2 |
| 53 | February | 2 | 2 |
| 354 | February | 2 | 2 |
| 352 | February | 1 | 24 |
| 373 | February | 1 | 24 |
| 148 | July | 22 | 1 |
| 102 | July | 21 | 2 |
| 236 | July | 20 | 3 |
| 75 | July | 20 | 3 |
| 91 | July | 19 | 5 |
| 354 | July | 19 | 5 |
| 30 | July | 19 | 5 |
| 64 | July | 19 | 5 |
| 137 | July | 19 | 5 |
| 526 | July | 19 | 5 |
| 366 | July | 19 | 5 |
| 595 | July | 19 | 5 |
| 469 | July | 18 | 13 |
...
| 457 | May | 1 | 347 |
| 356 | May | 1 | 347 |
| 481 | May | 1 | 347 |
| 10 | May | 1 | 347 |
+-------------+--------------+-------------+----------+
2466 rows in set (0.03 sec)
观察结果，你会发现每个月的排名都会重置为 1。为了生成市场营销部门所需的结果（每月排名前五位的客户），只需将上一个查询包装到子查询中，并添加过滤条件以排除排名高于 5 的所有行：
SELECT customer_id, rental_month, num_rentals,
rank_rnk ranking
FROM
(SELECT customer_id,
monthname(rental_date) rental_month,
count(*) num_rentals,
rank() over (partition by monthname(rental_date)
order by count(*) desc) rank_rnk
FROM rental
GROUP BY customer_id, monthname(rental_date) ) cust_rankings
WHERE rank_rnk <= 5
ORDER BY rental_month, num_rentals desc, rank_rnk;
由于分析函数只能在 SELECT 子句中使用，因此如果需要根据分析函数的结果进行过滤或分组操作，则通常需要嵌套查询。
报告函数
除了生成排名外，分析函数的另一个常见用途是查找异常值/离群值（例如，最小值或最大值），或生成整个数据集的总和或平均值。要处理这些类型的任务，需要使用聚合函数（min、max、avg、sum、count），但不是在 group by 子句中使用，而是与 over 子句一起使用。下面是一个为金额大于等于 10 美元的所有付款生成每月和以及总计的示例：
mysql> SELECT monthname(payment_date) payment_month,
-> amount,
-> sum(amount)
-> over (partition by monthname(payment_date)) monthly_total,
-> sum(amount) over () grand_total
-> FROM payment
-> WHERE amount >= 10
-> ORDER BY 1;
+---------------+--------+---------------+-------------+
| payment_month | amount | monthly_total | grand_total |
+---------------+--------+---------------+-------------+
| August | 10.99 | 521.53 | 1262.86 |
| August | 11.99 | 521.53 | 1262.86 |
| August | 10.99 | 521.53 | 1262.86 |
| August | 10.99 | 521.53 | 1262.86 |
...
| August | 10.99 | 521.53 | 1262.86 |
| August | 10.99 | 521.53 | 1262.86 |
| August | 10.99 | 521.53 | 1262.86 |
| July | 10.99 | 519.53 | 1262.86 |
| July | 10.99 | 519.53 | 1262.86 |
| July | 10.99 | 519.53 | 1262.86 |
| July | 10.99 | 519.53 | 1262.86 |
...
| July | 10.99 | 519.53 | 1262.86 |
| July | 10.99 | 519.53 | 1262.86 |
| July | 10.99 | 519.53 | 1262.86 |
| June | 10.99 | 165.85 | 1262.86 |
| June | 10.99 | 165.85 | 1262.86 |
| June | 10.99 | 165.85 | 1262.86 |
| June | 10.99 | 165.85 | 1262.86 |
| June | 10.99 | 165.85 | 1262.86 |
| June | 10.99 | 165.85 | 1262.86 |
| June | 10.99 | 165.85 | 1262.86 |
| June | 10.99 | 165.85 | 1262.86 |
| June | 11.99 | 165.85 | 1262.86 |
| June | 10.99 | 165.85 | 1262.86 |
| June | 10.99 | 165.85 | 1262.86 |
| June | 10.99 | 165.85 | 1262.86 |
| June | 10.99 | 165.85 | 1262.86 |
| June | 10.99 | 165.85 | 1262.86 |
| June | 10.99 | 165.85 | 1262.86 |
| May | 10.99 | 55.95 | 1262.86 |
| May | 10.99 | 55.95 | 1262.86 |
| May | 10.99 | 55.95 | 1262.86 |
| May | 10.99 | 55.95 | 1262.86 |
| May | 11.99 | 55.95 | 1262.86 |
+---------------+--------+---------------+-------------+
114 rows in set (0.01 sec)
因为 over 子句为空，它表示对整个结果集进行求和，所以 grand_total 列中的每一行都包含相同的值（$1262.86）。但是，monthly_total 列为不同月生成的值不同，这是因为 partition by 子句指定将结果集拆分为了多个数据窗口（每月一个）。
虽然每行包含一个值相同的列（如 grand_total）看起来似乎没什么用，但其实这些类型的列也可以用于计算，如下所示：
mysql> SELECT monthname(payment_date) payment_month,
-> sum(amount) month_total, -> round(sum(amount) / sum(sum(amount)) over ()
-> * 100, 2) pct_of_total -> FROM payment
-> GROUP BY monthname(payment_date);
+---------------+-------------+--------------+
| payment_month | month_total | pct_of_total |
+---------------+-------------+--------------+
| May | 4824.43 | 7.16 |
| June | 9631.88 | 14.29 |
| July | 28373.89 | 42.09 |
| August | 24072.13 | 35.71 |
| February | 514.18 | 0.76 |
+---------------+-------------+--------------+
5 rows in set (0.04 sec)
此查询通过对 amount 列求和来计算每个月的总付款额，然后对每月总付款额求和作为分母，计算每个月总付款额的百分比。
报告函数也可以用于比较操作。比如下一个查询，它使用一个 case 表达式来确定每月总额是最大值、最小值还是中间值：
mysql> SELECT monthname(payment_date) payment_month,
-> sum(amount) month_total,
-> CASE sum(amount)
-> WHEN max(sum(amount)) over () THEN 'Highest'
-> WHEN min(sum(amount)) over () THEN 'Lowest'
-> ELSE 'Middle'
-> END descriptor
-> FROM payment
-> GROUP BY monthname(payment_date);
+---------------+-------------+------------+
| payment_month | month_total | descriptor |
+---------------+-------------+------------+
| May | 4824.43 | Middle |
| June | 9631.88 | Middle |
| July | 28373.89 | Highest |
| August | 24072.13 | Middle |
| February | 514.18 | Lowest |
+---------------+-------------+------------+
5 rows in set (0.04 sec)
descriptor 列充当一个排序函数，有助于识别一组行集中的最高值和最低值。
窗口帧
如前所述，分析函数的数据窗口是使用 partition by 子句定义的，它允许按公共值对行进行分组。但如果你要更精确地控制数据窗口中包含的行，又要怎么做呢？例如，你可能希望生成从年初到当前行的运行总计。对于这些类型的计算，可以通过包含一个“frame”子句来实现，以精确定义要在数据窗口中包含哪些行。下面的查询对每周的付款进行求和，并包含一个用于计算滚动总和的报告函数：
mysql> SELECT yearweek(payment_date) payment_week,
-> sum(amount) week_total,
-> sum(sum(amount))
-> over (order by yearweek(payment_date)
-> rows unbounded preceding) rolling_sum
-> FROM payment -> GROUP BY yearweek(payment_date)
-> ORDER BY 1;
+--------------+------------+-------------+
| payment_week | week_total | rolling_sum |
+--------------+------------+-------------+
| 200521 | 2847.18 | 2847.18 |
| 200522 | 1977.25 | 4824.43 |
| 200524 | 5605.42 | 10429.85 |
| 200525 | 4026.46 | 14456.31 |
| 200527 | 8490.83 | 22947.14 |
| 200528 | 5983.63 | 28930.77 |
| 200530 | 11031.22 | 39961.99 |
| 200531 | 8412.07 | 48374.06 |
| 200533 | 10619.11 | 58993.17 |
| 200534 | 7909.16 | 66902.33 |
| 200607 | 514.18 | 67416.51 |
+--------------+------------+-------------+
11 rows in set (0.04 sec)
rolling_sum 列表达式包含 rows unbounded preceding 子类，用于定义从结果集开始到当前行（包括当前行）的数据窗口。数据窗口由结果集中到第一行的一行数据、到第二行的两行数据等组成。最后一行的值是整个结果集的总和。
计算滚动总和的过程中也可以计算滚动平均数。下面的查询计算三周总付款额的滚动平均值：
mysql> SELECT yearweek(payment_date) payment_week,
-> sum(amount) week_total,
-> avg(sum(amount))
-> over (order by yearweek(payment_date)
-> rows between 1 preceding and 1 following) rolling_3wk_avg
-> FROM payment
-> GROUP BY yearweek(payment_date)
-> ORDER BY 1;
+--------------+------------+-----------------+
| payment_week | week_total | rolling_3wk_avg |
+--------------+------------+-----------------+
| 200521 | 2847.18 | 2412.215000 |
| 200522 | 1977.25 | 3476.616667 |
| 200524 | 5605.42 | 3869.710000 |
| 200525 | 4026.46 | 6040.903333 |
| 200527 | 8490.83 | 6166.973333 |
| 200528 | 5983.63 | 8501.893333 |
| 200530 | 11031.22 | 8475.640000 |
| 200531 | 8412.07 | 10020.800000 |
| 200533 | 10619.11 | 8980.113333 |
| 200534 | 7909.16 | 6347.483333 |
| 200607 | 514.18 | 4211.670000 |
+--------------+------------+-----------------+
11 rows in set (0.03 sec)
rolling_3wk_avg 列定义了一个由当前行、前一行和下一行组成的数据窗口。因此，数据窗口将由三行组成，不过第一行和最后一行除外——这两行的数据窗口仅由两行组成（因为第一行没有前一行，最后一行没有下一行）。
在许多情况下也可以为数据窗口指定行数，但如果数据中存在间隙，则可能需要使用另一种方法。例如，在前面的结果集中，有 200521、200522 和 200524 周的数据，但没有 200523 周的数据。如果要指定日期间隔而不是行数，可以指定数据窗口的 range，如以下查询所示：
mysql> SELECT date(payment_date), sum(amount),
-> avg(sum(amount)) over (order by date(payment_date)
-> range between interval 3 day preceding
-> and interval 3 day following) 7_day_avg
-> FROM payment
-> WHERE payment_date BETWEEN '2005-07-01' AND '2005-09-01'
-> GROUP BY date(payment_date) -> ORDER BY 1;
+--------------------+-------------+-------------+
| date(payment_date) | sum(amount) | 7_day_avg |
+--------------------+-------------+-------------+
| 2005-07-05 | 128.73 | 1603.740000 |
| 2005-07-06 | 2131.96 | 1698.166000 |
| 2005-07-07 | 1943.39 | 1738.338333 |
| 2005-07-08 | 2210.88 | 1766.917143 |
| 2005-07-09 | 2075.87 | 2049.390000 |
| 2005-07-10 | 1939.20 | 2035.628333 |
| 2005-07-11 | 1938.39 | 2054.076000 |
| 2005-07-12 | 2106.04 | 2014.875000 |
| 2005-07-26 | 160.67 | 2046.642500 |
| 2005-07-27 | 2726.51 | 2206.244000 |
| 2005-07-28 | 2577.80 | 2316.571667 |
| 2005-07-29 | 2721.59 | 2388.102857 |
| 2005-07-30 | 2844.65 | 2754.660000 |
| 2005-07-31 | 2868.21 | 2759.351667 |
| 2005-08-01 | 2817.29 | 2795.662000 |
| 2005-08-02 | 2726.57 | 2814.180000 |
| 2005-08-16 | 111.77 | 1973.837500 |
| 2005-08-17 | 2457.07 | 2123.822000 |
| 2005-08-18 | 2710.79 | 2238.086667 |
| 2005-08-19 | 2615.72 | 2286.465714 |
| 2005-08-20 | 2723.76 | 2630.928571 |
| 2005-08-21 | 2809.41 | 2659.905000 |
| 2005-08-22 | 2576.74 | 2649.728000 |
| 2005-08-23 | 2523.01 | 2658.230000 |
+--------------------+-------------+-------------+
24 rows in set (0.03 sec)
7_day_avg 列指定了+/-3 天的范围，并且仅包括付款日期值在该范围内的行。例如，对于 2005-08-16 执行的计算只包括 08-16、08-17、08-18 和 08-19 的值，因为之前的三个日期（08-13 到 08-15）没有行数据。
lag 和 lead
除了计算数据窗口上的总和和平均值外，常见的任务还有比较两行的值。例如，如果你正在生成月度销售总额，则可能要创建一列来显示与上月的百分比差异，这需要使用一种方法来从上一行检索月度销售总额。可以通过使用 lag 函数（从结果集中的前一行检索列值）或 lead 函数（从后一行检索列值）来实现。下面的示例使用这两个函数：
mysql> SELECT yearweek(payment_date) payment_week,
-> sum(amount) week_total,
-> lag(sum(amount), 1)
-> over (order by yearweek(payment_date)) prev_wk_tot,
-> lead(sum(amount), 1)
-> over (order by yearweek(payment_date)) next_wk_tot
-> FROM payment
-> GROUP BY yearweek(payment_date)
-> ORDER BY 1;
+--------------+------------+-------------+-------------+
| payment_week | week_total | prev_wk_tot | next_wk_tot |
+--------------+------------+-------------+-------------+
| 200521 | 2847.18 | NULL | 1977.25 |
| 200522 | 1977.25 | 2847.18 | 5605.42 |
| 200524 | 5605.42 | 1977.25 | 4026.46 |
| 200525 | 4026.46 | 5605.42 | 8490.83 |
| 200527 | 8490.83 | 4026.46 | 5983.63 |
| 200528 | 5983.63 | 8490.83 | 11031.22 |
| 200530 | 11031.22 | 5983.63 | 8412.07 |
| 200531 | 8412.07 | 11031.22 | 10619.11 |
| 200533 | 10619.11 | 8412.07 | 7909.16 |
| 200534 | 7909.16 | 10619.11 | 514.18 |
| 200607 | 514.18 | 7909.16 | NULL |
+--------------+------------+-------------+-------------+
11 rows in set (0.03 sec)
从结果来看，200527 周的周总额 8490.43 也出现在 200525 周的 next_wk_tot 列和 200528 周的 prev_wk_tot 列中。由于结果集中没有 200521 之前的行，因此 lag 函数为第一行生成的值为空值 null。同样，lead 函数为最后一行生成的值为空值 null。lag 和 lead 都允许使用可选的第二个参数（默认值为 1）来描述要检索列值的前/后行数。
下面是使用 lag 函数生成与前一周的百分比差的查询：
mysql> SELECT yearweek(payment_date) payment_week,
-> sum(amount) week_total,
-> round((sum(amount) - lag(sum(amount), 1)
-> over (order by yearweek(payment_date)))
-> / lag(sum(amount), 1)
-> over (order by yearweek(payment_date))
-> * 100, 1) pct_diff
-> FROM payment
-> GROUP BY yearweek(payment_date)
-> ORDER BY 1;
+--------------+------------+----------+
| payment_week | week_total | pct_diff |
+--------------+------------+----------+
| 200521 | 2847.18 | NULL |
| 200522 | 1977.25 | -30.6 |
| 200524 | 5605.42 | 183.5 |
| 200525 | 4026.46 | -28.2 |
| 200527 | 8490.83 | 110.9 |
| 200528 | 5983.63 | -29.5 |
| 200530 | 11031.22 | 84.4 |
| 200531 | 8412.07 | -23.7 |
| 200533 | 10619.11 | 26.2 |
| 200534 | 7909.16 | -25.5 |
| 200607 | 514.18 | -93.5 |
+--------------+------------+----------+
11 rows in set (0.07 sec)
比较同一结果集中不同行的值是报表系统常常涉及的操作，因此你可能会发现 lag 和 lead 函数很有用。
列值连接
这里我还将演示一个函数，它虽然技术上讲不属于分析函数，但是它很重要，因为它可以处理数据窗口中的行组。group_concat 函数用于将一组列值转换为单个分隔字符串，可以方便地将生成 XML 或 JSON 文档的结果集反规范化。下面是使用此函数为每部电影生成以逗号分隔的演员列表的示例：
mysql> SELECT f.title,
-> group_concat(a.last_name order by a.last_name
-> separator ', ') actors
-> FROM actor a
-> INNER JOIN film_actor fa
-> ON a.actor_id = fa.actor_id
-> INNER JOIN film f
-> ON fa.film_id = f.film_id
-> GROUP BY f.title
-> HAVING count(*) = 3;
+------------------------+--------------------------------+
| title | actors |
+------------------------+--------------------------------+
| ANNIE IDENTITY | GRANT, KEITEL, MCQUEEN |
| ANYTHING SAVANNAH | MONROE, SWANK, WEST |
| ARK RIDGEMONT | BAILEY, DEGENERES, GOLDBERG |
| ARSENIC INDEPENDENCE | ALLEN, KILMER, REYNOLDS |
...
| WHISPERER GIANT | BAILEY, PECK, WALKEN |
| WIND PHANTOM | BALL, DENCH, GUINESS |
| ZORRO ARK | DEGENERES, MONROE, TANDY |
+------------------------+--------------------------------+
119 rows in set (0.04 sec)
这个查询按电影名对行进行分组，只包括正好有 3 个演员出现的电影。group_concat 函数的作用类似于一种特殊类型的聚合函数，它将每部电影中出现的所有演员的姓氏转换为一个字符串。如果你使用的是 SQL Server，则可以使用 string_agg 函数实现此功能，而 Oracle 用户可以使用 listagg 函数。
版权声明: 本文为 InfoQ 作者【数据与智能】的原创文章。
原文链接:【http://xie.infoq.cn/article/aa44595103a2d6bcae747b056】。文章转载请联系作者。
还未添加个人签名 2018.05.14 加入
公众号【数据与智能】主理人，个人微信：liuq4360 12 年大数据与 AI相关项目经验， 10 年推荐系统研究及实践经验，目前已经输出了40万字的推荐系统系列精品文章，并有新书即将出版。
评论