数据与智能 本公众号关注大数据与人工智能技术。由一批具备多年实战经验的技术极客参与运营管理，持续输出大数据、数据分析、推荐系统、机器学习、人工智能等方向的原创文章，每周至少输出7篇精品原创。同时，我们会关注和分享大数据与人工智能行业动态。欢迎关注。

来源 | Learning SQL Generate, Manipulate, and Retrieve Data， Third Edition

作者 | Alan Beaulieu

译者 | Liangchu

校对 | gongyouliu

编辑 | auroral-L

全文共11330字，预计阅读时间65分钟。

第十六章分析函数

1. 分析函数概念

1.1 数据窗口

1.2 局部排序

2. 排名

2.1 排名分析函数

2.2 生成多个排名

3. 报告函数

3.1 窗口帧

3.2 lag 和 lead

3.3 列值连接

数据量一直在以惊人的速度增长，组织很难存储所有数据，更不用说试图理解这些海量数据了。数据分析一般是在数据库服务器之外使用专门的工具或语言（如Excel、R和Python）进行的，而SQL语言包含一组有利于分析处理的强大函数。如果要对公司销售人员进行排名选出前十名，或者要为客户生成财务报告并且需要计算三个月的滚动平均数，则可以使用SQL内置分析函数来执行这些类型的计算。

1. 分析函数概念

在数据库服务器完成评估查询所需的所有步骤（包括连接、过滤、分组和排序）之后就可以将生成的结果集返回给调用者了。想象一下，如果此时可以中止查询，并在结果集仍保留在内存中的情况下遍历结果集，你会希望执行什么类型的分析？如果结果集包含销售数据，可能你会想为销售人员或地区生成排名，或者计算两个时间段之间的百分比差异。如果要生成财务报表，则可能需要计算每个报表部分的小计，以及最后生成总计。你可以使用分析函数做到包括但不限于以上情况的所有事情。在深入讨论细节之前，以下小节将介绍几种最常用的分析函数机制。

1.1 数据窗口

假设你编写了一个查询，用于生成给定时间段的每月销售总额。例如，以下查询汇总了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.2 局部排序

除了将结果集划分到分析函数的数据窗口外，还可以指定排序的顺序。例如，如果要为给每个月定义排名，值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子句的用法，以下各节将详细介绍各种分析函数。

2. 排名

人们喜欢给事物排序。如果要访问你最喜欢的新闻/体育/旅游网站，你会看到类似下面的标题：

? 度假的十大价值；

? 最好的共同基金回报；

? 大学足球季前赛排名；

? 有史以来的前100首歌曲。

公司会出于更实际的目的使用排名（比如了解哪些产品最畅销/最滞销，或者哪些地区的收入最少/最多），有助于做出战略决策。

2.1 排名分析函数

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函数可能是最好的选择。

2.2 生成多个排名

上一节中的示例在整个客户集中生成单个排名，但如果希望在同一结果集中生成多个排名又该怎么办呢？为了扩展前面的示例，假设市场部决定每月向前五名客户发放免费电影租赁券。要生成数据，可以将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子句中使用，因此如果需要根据分析函数的结果进行过滤或分组操作，则通常需要嵌套查询。

3. 报告函数

除了生成排名外，分析函数的另一个常见用途是查找异常值/离群值（例如，最小值或最大值），或生成整个数据集的总和或平均值。要处理这些类型的任务，需要使用聚合函数（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列充当一个排序函数，有助于识别一组行集中的最高值和最低值。

3.1 窗口帧

如前所述，分析函数的数据窗口是使用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）没有行数据。

3.2 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函数很有用。

3.3 列值连接

这里我还将演示一个函数，它虽然技术上讲不属于分析函数，但是它很重要，因为它可以处理数据窗口中的行组。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函数。

「SQL数据分析系列」16. 分析函数

1. 分析函数概念

1.1 数据窗口

1.2 局部排序

2. 排名

2.1 排名分析函数

2.2 生成多个排名

3. 报告函数

3.1 窗口帧

3.2 lag 和 lead

3.3 列值连接

相关推荐

取消回复欢迎你发表评论:

Google 黑客常用搜索语句一览原力计划

npx简介（npxvip是哪国的）

在 Android 模拟器上运行 ARM 应用（android模拟器原理）

GB28181,B接口协议之SIPRTSPRTPRTMP协议从入门到精通

手机实时提取SIM卡打电话的信令和声音-辅助外设与商用通话方案

安装使用Hoppscotch构建API请求访问与测试

轻松转换!AppleNumbers到Excel的快捷教程

Python自动化办公——后台截图（python 自动截图）

电脑端腾讯文档如何导出excel

网络流媒体经典开源软件宝典webRTC, FFMpeg, SIP_流媒体开发教程

「SQL数据分析系列」16. 分析函数

1. 分析函数概念

1.1 数据窗口

1.2 局部排序

2. 排名

2.1 排名分析函数

2.2 生成多个排名

3. 报告函数

3.1 窗口帧

3.2 lag 和 lead

3.3 列值连接

相关推荐

取消回复欢迎 你 发表评论:

Google 黑客常用搜索语句一览 原力计划

npx简介（npxvip是哪国的）

在 Android 模拟器上运行 ARM 应用（android模拟器原理）

GB28181,B接口协议之SIPRTSPRTPRTMP协议从入门到精通

手机实时提取SIM卡打电话的信令和声音-辅助外设与商用通话方案

安装使用Hoppscotch构建API请求访问与测试

轻松转换!AppleNumbers到Excel的快捷教程

Python自动化办公——后台截图（python 自动截图）

电脑端腾讯文档如何导出excel

网络流媒体经典开源软件宝典webRTC, FFMpeg, SIP_流媒体开发教程

取消回复欢迎你发表评论:

Google 黑客常用搜索语句一览原力计划