小让の码场窗口函数
SQL 窗口函数为在线分析处理（OLAP）和商业智能（BI）提供了复杂分析和报表统计的功能，例如产品的累计销售额统计、分类排名、同比/环比分析等。这些功能通常很难通过聚合函数和分组操作来实现。
本篇将会介绍 SQL 窗口函数的定义和参数选项，以及各类窗口函数的作用，涉及的主要知识点包括：
- 窗口函数定义
- 聚合窗口函数
- 排名窗口函数
- 取值窗口函数
窗口函数定义
窗口函数（Window Function）可以像聚合函数一样对一组数据进行分析并返回结果，二者的不同之处在于，窗口函数不是将同一分组类的多行数据汇总成单个结果，而是为每一行数据都返回一个结果，保留了所有的原始记录。聚合函数和窗口函数的区别如图下所示：
咱们以 SUM 函数为例演示这两种函数的差异，以下语句中的 SUM() 是一个聚合函数：
sql
SELECT SUM(salary) AS '月薪总和'
FROM employee;以上 SUM 函数可作为聚合函数使用，表示将所有员工的数据汇总成一个结果。因此，查询返回了所有员工的月薪总和：
sh
+-----------+
| 月薪总和 |
+-----------+
| 245800.00 |
+-----------+以下语句中的 SUM() 是一个窗口函数：
sql
SELECT emp_name '员工姓名', SUM(salary) OVER () AS '月薪总和'
FROM employee;其中，关键字 OVER 表明 SUM() 是一个窗口函数。括号内为空，表示将所有数据作为一个分组进行汇总。该查询返回的结果如下：
sh
+----------+-----------+
| 员工姓名 | 月薪总和 |
+----------+-----------+
| 刘备 | 245800.00 |
| 关羽 | 245800.00 |
| 张飞 | 245800.00 |
| 诸葛亮 | 245800.00 |
| 黄忠 | 245800.00 |
| 魏延 | 245800.00 |
| 孙尚香 | 245800.00 |
| 孙丫鬟 | 245800.00 |
| 赵云 | 245800.00 |
| 廖化 | 245800.00 |
| 关平 | 245800.00 |
| 赵氏 | 245800.00 |
| 关兴 | 245800.00 |
| 张苞 | 245800.00 |
| 赵统 | 245800.00 |
| 周仓 | 245800.00 |
| 马岱 | 245800.00 |
| 法正 | 245800.00 |
| 庞统 | 245800.00 |
| 蒋琬 | 245800.00 |
| 黄权 | 245800.00 |
| 糜竺 | 245800.00 |
| 邓芝 | 245800.00 |
| 简雍 | 245800.00 |
| 孙乾 | 245800.00 |
+----------+-----------+以上查询结果返回了所有的员工姓名，并且通过聚合函数 SUM() 为每个员工都返回了相同的汇总结果。
从以上示例中可以看出，窗口函数的语法与聚合函数的不同之处在于，它包含了一个 OVER 子句。OVER 子句用于指定一个数据分析的窗口，完整的窗口函数定义如下：
sql
window_function([expression]) OVER (
[PARTITION BY 分组列1,分组列2,...]
[ORDER BY 排序列1,排序列2,... [ASC | DESC]]
[(ROWS | RANGE) BETWEEN 开始行 AND 结束行]
)其中 window_function 是窗口函数的名称，expression 是可选的分析对象（字段名或者表达式），OVER 子句包含分区（PARTITION BY）、排序（ORDER BY）以及窗口大小（frame_clause）3 个选项。
Tip
聚合函数将同一个分组内的多行数据汇总成单个结果，窗口函数则保留了所有的原始数据。在某些数据库中，窗口函数也被称为在线分析处理（OLAP）函数，或者分析函数（Analytic Function）。
创建数据分区
窗口函数 OVER 子句中的 PARTITION BY 选项用于定义分区，其作用类似于查询语句中的 GROUP BY 子句。
- 如果指定了分区选项，窗口函数将会针对每个分区单独进行分析。
- 如果没有指定的话，窗口函数会将全部数据作为一个整体进行分析。
举个栗子：按照不同部门分别统计员工的月薪合计。SQL 语句如下所示：
sql
SELECT emp_name '员工姓名', salary '月薪', dept_id '部门编号', SUM(salary) OVER (PARTITION BY dept_id) AS '部门合计'
FROM employee;其中，PARTITION BY 选项按照部门进行分区。查询结果如下所示：
sh
+----------+----------+----------+----------+
| 员工姓名 | 月薪 | 部门编号 | 部门合计 |
+----------+----------+----------+----------+
| 刘备 | 30000.00 | 1 | 80000.00 |
| 关羽 | 26000.00 | 1 | 80000.00 |
| 张飞 | 24000.00 | 1 | 80000.00 |
| 诸葛亮 | 24000.00 | 2 | 39500.00 |
| 黄忠 | 8000.00 | 2 | 39500.00 |
| 魏延 | 7500.00 | 2 | 39500.00 |
| 孙尚香 | 12000.00 | 3 | 18000.00 |
| 孙丫鬟 | 6000.00 | 3 | 18000.00 |
| 赵云 | 15000.00 | 4 | 68200.00 |
| 廖化 | 6500.00 | 4 | 68200.00 |
| 关平 | 6800.00 | 4 | 68200.00 |
| 赵氏 | 6600.00 | 4 | 68200.00 |
| 关兴 | 7000.00 | 4 | 68200.00 |
| 张苞 | 6500.00 | 4 | 68200.00 |
| 赵统 | 6000.00 | 4 | 68200.00 |
| 周仓 | 8000.00 | 4 | 68200.00 |
| 马岱 | 5800.00 | 4 | 68200.00 |
| 法正 | 10000.00 | 5 | 40100.00 |
| 庞统 | 4100.00 | 5 | 40100.00 |
| 蒋琬 | 4000.00 | 5 | 40100.00 |
| 黄权 | 4200.00 | 5 | 40100.00 |
| 糜竺 | 4300.00 | 5 | 40100.00 |
| 邓芝 | 4000.00 | 5 | 40100.00 |
| 简雍 | 4800.00 | 5 | 40100.00 |
| 孙乾 | 4700.00 | 5 | 40100.00 |
+----------+----------+----------+----------+查询结果中的前三行数据属于同一个部门，因此它们对应的部门合计字段都等于 80000（30000+26000+24000）。其他部门的员工也采用相同的方式进行统计。
Tip
在窗口函数 OVER 子句中指定了 PARTITION BY 选项之后，我们无须使用 GROUP BY 子句也能获得分组统计结果。如果不指定 PARTITION BY 选项，表示将全部数据作为一个整体进行分析。
分区内的排序
窗口函数 OVER 子句中的 ORDER BY 选项用于指定分区内数据的排序方式，其作用类似于查询语句中的 ORDER BY 子句。
排序选项通常用于数据的分类排名。
举个栗子：分析员工在部门类的月薪排名。SQL 语句如下所示：
sql
SELECT emp_name '员工姓名',
salary '月薪',
dept_id '部门编号',
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) '部门排名'
FROM employee;其中，PARTITION BY 选项表示按照部门进行分区，ORDER BY 选项表示对同一分区（部门）中的数据按照月薪从高到低进行排序，而 RANK() 函数则用于计算当前员工在部门中的薪水排名（从高到低）。查询结果如下所示：
sh
+----------+----------+----------+----------+
| 员工姓名 | 月薪 | 部门编号 | 部门排名 |
+----------+----------+----------+----------+
| 刘备 | 30000.00 | 1 | 1 |
| 关羽 | 26000.00 | 1 | 2 |
| 张飞 | 24000.00 | 1 | 3 |
| 诸葛亮 | 24000.00 | 2 | 1 |
| 黄忠 | 8000.00 | 2 | 2 |
| 魏延 | 7500.00 | 2 | 3 |
| 孙尚香 | 12000.00 | 3 | 1 |
| 孙丫鬟 | 6000.00 | 3 | 2 |
| 赵云 | 15000.00 | 4 | 1 |
| 周仓 | 8000.00 | 4 | 2 |
| 关兴 | 7000.00 | 4 | 3 |
| 关平 | 6800.00 | 4 | 4 |
| 赵氏 | 6600.00 | 4 | 5 |
| 廖化 | 6500.00 | 4 | 6 |
| 张苞 | 6500.00 | 4 | 6 |
| 赵统 | 6000.00 | 4 | 8 |
| 马岱 | 5800.00 | 4 | 9 |
| 法正 | 10000.00 | 5 | 1 |
| 简雍 | 4800.00 | 5 | 2 |
| 孙乾 | 4700.00 | 5 | 3 |
| 糜竺 | 4300.00 | 5 | 4 |
| 黄权 | 4200.00 | 5 | 5 |
| 庞统 | 4100.00 | 5 | 6 |
| 蒋琬 | 4000.00 | 5 | 7 |
| 邓芝 | 4000.00 | 5 | 7 |
+----------+----------+----------+----------+查询结果中的前三行数据属于同一个部门："刘备" 的月薪最高，在部门类排第一；"关羽" 排名第二; "张飞" 排名第三。其他部门的员工采用同样的方式进行排名。
Tip
窗口函数 OVER 子句中的 ORDER BY 选项和查询语句中的 ORDER BY 子句的使用方法相同。因此，对于 Oracle、PostgreSQL 以及 SQLite，我们也可以使用 NULLS FIRST 或者 NULLS LAST 选项指定空值的排序位置。
指定窗口大小
窗口函数 OVER 子句中的 frame_clause 选项用于指定一个移动的分析窗口，窗口总是位于分区的范围内，是分区的一个子集。在指定了分析窗口之后，窗口函数不再基于分区进行分析，而是基于窗口内的数据进行分析。
窗口选项可以用于实现各种复杂的分析功能，例如计算累到到当前日期为止的销售额总和，每个月及其前后各 N 个月的平均销售额等。
指定窗口大小的具体选项如下所示：
sql
{ ROWS | RANGE } frame_start
{ ROWS | RANGE } BETWEEN frame_start AND frame_end其中，ROWS 表示以数据行为单位计算窗口的偏移量；RANGE 表示以数值（例如 10 天、5km 等）为单位计算窗口的偏移量。
frame_start 选项用于指定窗口的起始位置，可以指定以下内容之一：
- UNBOUNDED PRECEDING：表示窗口从分区的第一行开始；
- N PRECEDING：N为常量，表示窗口从当前行的第 N 行开始；
- CURRENT ROW：表示窗口从当前行开始。
frame_end 选项用于指定窗口的结束位置，可以指定以下内容之一：
CURRENT ROW：表示窗口到当前行结束。
M FOLLOWING：M为常量，表示窗口到当前行之后的第M行结束；
UNBOUNDED FOLLOWING：表示窗口到分区的最后一行结束。
下图说明了这些窗口大小选项的含义。
随着窗口函数对每一行数据的分析，上图中的 CURRENT ROW 代表了当前正在处理的数据行，其他数据行则可以通过它们相对于当前行的位置进行表示。例如，以下窗口选项：
sql
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示分析窗口从当前分区的第一行开始，直到当前行结束。
Note
分析窗口的大小不会超过当前分区的范围，每个窗口函数支持的窗口大小选项不同，如上面示例中所用到的排名窗口函数（如 RANK() 函数）就不支持动态的窗口大小选项，而是以整个分区作为分析的窗口！接下来会分析每个窗口函数的具体用法，所以不用急，对于这个结论后面还会提到，这里只是简单提一嘴。
窗口函数分类
常见的窗口函数可以分为以下几类：
聚合窗口函数（Aggregate Window Function）：许多常见的聚合函数也可以作为窗口函数使用，包括 AVG()、SUM()、COUNT()、MAX() 以及 MIN() 等函数。
排名窗口函数（Ranking Window Function）：排名窗口函数用于对数据进行分组排名，主要包括以下几种：
ROW_NUMBER 函数可以为分区中的每行数据分配一个序列号，序列号从1开始。形如 1,2,3,... ，序号不重复，序号连续；
RANK 函数返回当前行在分区中的名次，如果存在名次相同的数据，后续的排名将会产生跳跃。形如 1,2,2,4,... ，序号可以重复，序号不连续；
DENSE_RANK 函数返回当前行在分区中的名次，即使存在名次相同的数据，后续的排名也是连续值。形如 1,2,2,3,... ，序号可以重复，序号连续；
PRECENT_RANK 函数以百分比的形式返回当前行在分区中的名次。如果存在名次相同的数据，后续的排名将会产生跳跃。
CUME_DIST 函数计算当前行在分区内的累计分布。
NTILE 函数将分区内的数据分为 N 等份，并返回当前行所在的分片位置。
Note
排名窗口函数不支持动态的窗口大小，它们以整个分区作为分析的窗口！！！
取值窗口函数（Value Window Function）：取值窗口函数用于返回窗口内指定位置上的数据行，主要包括以下几种：
- LAG 函数可以返回窗口内当前行之前的第 N 行数据；函数有三个参数：field（选择的字段），num（偏移量），default（默认值，即无数据时的填充值）。
- LEAD 函数可以返回窗口内当前行之后的第 N 行数据；参数同 LAG() 函数一样。
- FIRST_VALUE 函数可以返回窗口内的第一行数据；
- LAST_VALUE 函数可以返回窗口内的最后一行数据；
- NTH_VALUE 函数可以返回窗口内的第 N 行数据；
Note
LAG 和 LEAD 函数不支持动态的窗口大小，它们以整个分区作为分析的窗口！！！
接下来我们将会使用两个示例表，其中 sales_monthly 表中存储了不同产品（苹果、香蕉、橘子）每个月的销售额情况。
sql
-- 创建销量表sales_monthly
-- product表示产品名称，ym表示年月，amount表示销售金额（元）
CREATE TABLE sales_monthly(product VARCHAR(20), ym VARCHAR(10), amount NUMERIC(10, 2));
-- 生成测试数据
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201801',10159.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201802',10211.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201803',10247.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201804',10376.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201805',10400.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201806',10565.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201807',10613.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201808',10696.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201809',10751.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201810',10842.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201811',10900.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201812',10972.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201901',11155.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201902',11202.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201903',11260.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201904',11341.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201905',11459.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201906',11560.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201801',10138.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201802',10194.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201803',10328.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201804',10322.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201805',10481.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201806',10502.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201807',10589.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201808',10681.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201809',10798.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201810',10829.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201811',10913.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201812',11056.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201901',11161.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201902',11173.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201903',11288.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201904',11408.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201905',11469.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201906',11528.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201801',10154.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201802',10183.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201803',10245.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201804',10325.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201805',10465.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201806',10505.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201807',10578.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201808',10680.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201809',10788.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201810',10838.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201811',10942.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201812',10988.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201901',11099.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201902',11181.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201903',11302.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201904',11327.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201905',11423.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201906',11524.00);transfer_log 表中记录了一些银行账户的交易日志。
sql
-- 创建银行交易日志表transfer_log
CREATE TABLE transfer_log
( log_id INTEGER NOT NULL PRIMARY KEY, -- 交易日志编号
log_ts TIMESTAMP NOT NULL, -- 交易时间
from_user VARCHAR(50) NOT NULL, -- 交易发起账号
to_user VARCHAR(50), -- 交易接收账号
type VARCHAR(10) NOT NULL, -- 交易类型
amount NUMERIC(10) NOT NULL -- 交易金额（元）
);
-- 生成测试数据
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (1,'2021-01-02 10:31:40','62221234567890',NULL,'存款',50000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (2,'2021-01-02 10:32:15','62221234567890',NULL,'存款',100000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (3,'2021-01-03 08:14:29','62221234567890','62226666666666','转账',200000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (4,'2021-01-05 13:55:38','62221234567890','62226666666666','转账',150000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (5,'2021-01-07 20:00:31','62221234567890','62227777777777','转账',300000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (6,'2021-01-09 17:28:07','62221234567890','62227777777777','转账',500000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (7,'2021-01-10 07:46:02','62221234567890','62227777777777','转账',100000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (8,'2021-01-11 09:36:53','62221234567890',NULL,'存款',40000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (9,'2021-01-12 07:10:01','62221234567890','62228888888881','转账',10000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (10,'2021-01-12 07:11:12','62221234567890','62228888888882','转账',8000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (11,'2021-01-12 07:12:36','62221234567890','62228888888883','转账',5000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (12,'2021-01-12 07:13:55','62221234567890','62228888888884','转账',6000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (13,'2021-01-12 07:14:24','62221234567890','62228888888885','转账',7000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (14,'2021-01-21 12:11:16','62221234567890','62228888888885','转账',70000);聚合窗口函数
案例分析：移动平均值
AVG 函数在作为窗口函数使用时，可以用于计算随着当前行移动的窗口内数据行的平均值。例如，以下语句用于查找不同产品每个月以及截至当前月最近 3 个月的平均销售额：
sql
SELECT
product '产品',
ym '年月',
amount '销售额',
AVG( amount ) OVER ( PARTITION BY product ORDER BY ym ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) '最近3个月的平均销售额'
FROM
sales_monthly;AVG 函数 OVER 子句中的 PARTITION BY 选项表示按照产品进行分区；ORDER BY 选项表示按照月份进行排序；ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 表示窗口从当前行的前 2 行开始，直到当前行结束。该查询返回的结果如下：
sh
+------+--------+----------+-----------------------+
| 产品 | 年月 | 销售额 | 最近3个月的平均销售额 |
+------+--------+----------+-----------------------+
| 桔子 | 201801 | 10154.00 | 10154.000000 |
| 桔子 | 201802 | 10183.00 | 10168.500000 |
| 桔子 | 201803 | 10245.00 | 10194.000000 |
| 桔子 | 201804 | 10325.00 | 10251.000000 |
| 桔子 | 201805 | 10465.00 | 10345.000000 |
| 桔子 | 201806 | 10505.00 | 10431.666667 |
| 桔子 | 201807 | 10578.00 | 10516.000000 |
| 桔子 | 201808 | 10680.00 | 10587.666667 |
| 桔子 | 201809 | 10788.00 | 10682.000000 |
| 桔子 | 201810 | 10838.00 | 10768.666667 |
| 桔子 | 201811 | 10942.00 | 10856.000000 |
| 桔子 | 201812 | 10988.00 | 10922.666667 |
| 桔子 | 201901 | 11099.00 | 11009.666667 |
| 桔子 | 201902 | 11181.00 | 11089.333333 |
| 桔子 | 201903 | 11302.00 | 11194.000000 |
| 桔子 | 201904 | 11327.00 | 11270.000000 |
| 桔子 | 201905 | 11423.00 | 11350.666667 |
| 桔子 | 201906 | 11524.00 | 11424.666667 |
| 苹果 | 201801 | 10159.00 | 10159.000000 |
| 苹果 | 201802 | 10211.00 | 10185.000000 |
| 苹果 | 201803 | 10247.00 | 10205.666667 |
| 苹果 | 201804 | 10376.00 | 10278.000000 |
| 苹果 | 201805 | 10400.00 | 10341.000000 |
| 苹果 | 201806 | 10565.00 | 10447.000000 |
| 苹果 | 201807 | 10613.00 | 10526.000000 |
| 苹果 | 201808 | 10696.00 | 10624.666667 |
| 苹果 | 201809 | 10751.00 | 10686.666667 |
| 苹果 | 201810 | 10842.00 | 10763.000000 |
| 苹果 | 201811 | 10900.00 | 10831.000000 |
| 苹果 | 201812 | 10972.00 | 10904.666667 |
| 苹果 | 201901 | 11155.00 | 11009.000000 |
| 苹果 | 201902 | 11202.00 | 11109.666667 |
| 苹果 | 201903 | 11260.00 | 11205.666667 |
| 苹果 | 201904 | 11341.00 | 11267.666667 |
| 苹果 | 201905 | 11459.00 | 11353.333333 |
| 苹果 | 201906 | 11560.00 | 11453.333333 |
| 香蕉 | 201801 | 10138.00 | 10138.000000 |
| 香蕉 | 201802 | 10194.00 | 10166.000000 |
| 香蕉 | 201803 | 10328.00 | 10220.000000 |
| 香蕉 | 201804 | 10322.00 | 10281.333333 |
| 香蕉 | 201805 | 10481.00 | 10377.000000 |
| 香蕉 | 201806 | 10502.00 | 10435.000000 |
| 香蕉 | 201807 | 10589.00 | 10524.000000 |
| 香蕉 | 201808 | 10681.00 | 10590.666667 |
| 香蕉 | 201809 | 10798.00 | 10689.333333 |
| 香蕉 | 201810 | 10829.00 | 10769.333333 |
| 香蕉 | 201811 | 10913.00 | 10846.666667 |
| 香蕉 | 201812 | 11056.00 | 10932.666667 |
| 香蕉 | 201901 | 11161.00 | 11043.333333 |
| 香蕉 | 201902 | 11173.00 | 11130.000000 |
| 香蕉 | 201903 | 11288.00 | 11207.333333 |
| 香蕉 | 201904 | 11408.00 | 11289.666667 |
| 香蕉 | 201905 | 11469.00 | 11388.333333 |
| 香蕉 | 201906 | 11528.00 | 11468.333333 |
+------+--------+----------+-----------------------+对于 “橘子”，第一个月的分析窗口只有一行数据，因此平均销售额为 “10154”。第二个月的分析窗口为第一行和第二行数据，因此平均销售额为 “10168.5” （(10154+10183)/2）。第三个月的分析窗口为第一行到第三行数据，因此平均销售额为 “10194” （(10154+10183+10245)/3）。依次类推，直到计算完 “橘子” 所有月份的平均销售额，然后计算其他产品的平均销售额。
案例分析：累计求和
SUM 函数作为窗口函数时，可以用于统计指定窗口内的累计值。例如，以下语句用于查找不同产品截至当前月份的累计销售额：
sql
SELECT
product '产品',
ym '年月',
amount '销售额',
SUM( amount ) OVER ( PARTITION BY product ORDER BY ym ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) '累计销售额'
FROM
sales_monthly
ORDER BY
product,
ym;SUM 函数 OVER 子句中的 PARTITION BY 选项表示按照产品进行分区；ORDER BY 选项表示按照月份进行排序；ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示窗口从当前分区的第一行开始，直到当前行结束。该查询返回的结果如下：
sh
+------+--------+----------+------------+
| 产品 | 年月 | 销售额 | 累计销售额 |
+------+--------+----------+------------+
| 桔子 | 201801 | 10154.00 | 10154.00 |
| 桔子 | 201802 | 10183.00 | 20337.00 |
| 桔子 | 201803 | 10245.00 | 30582.00 |
| 桔子 | 201804 | 10325.00 | 40907.00 |
| 桔子 | 201805 | 10465.00 | 51372.00 |
| 桔子 | 201806 | 10505.00 | 61877.00 |
| 桔子 | 201807 | 10578.00 | 72455.00 |
| 桔子 | 201808 | 10680.00 | 83135.00 |
| 桔子 | 201809 | 10788.00 | 93923.00 |
| 桔子 | 201810 | 10838.00 | 104761.00 |
| 桔子 | 201811 | 10942.00 | 115703.00 |
| 桔子 | 201812 | 10988.00 | 126691.00 |
| 桔子 | 201901 | 11099.00 | 137790.00 |
| 桔子 | 201902 | 11181.00 | 148971.00 |
| 桔子 | 201903 | 11302.00 | 160273.00 |
| 桔子 | 201904 | 11327.00 | 171600.00 |
| 桔子 | 201905 | 11423.00 | 183023.00 |
| 桔子 | 201906 | 11524.00 | 194547.00 |
| 苹果 | 201801 | 10159.00 | 10159.00 |
| 苹果 | 201802 | 10211.00 | 20370.00 |
| 苹果 | 201803 | 10247.00 | 30617.00 |
| 苹果 | 201804 | 10376.00 | 40993.00 |
| 苹果 | 201805 | 10400.00 | 51393.00 |
| 苹果 | 201806 | 10565.00 | 61958.00 |
| 苹果 | 201807 | 10613.00 | 72571.00 |
| 苹果 | 201808 | 10696.00 | 83267.00 |
| 苹果 | 201809 | 10751.00 | 94018.00 |
| 苹果 | 201810 | 10842.00 | 104860.00 |
| 苹果 | 201811 | 10900.00 | 115760.00 |
| 苹果 | 201812 | 10972.00 | 126732.00 |
| 苹果 | 201901 | 11155.00 | 137887.00 |
| 苹果 | 201902 | 11202.00 | 149089.00 |
| 苹果 | 201903 | 11260.00 | 160349.00 |
| 苹果 | 201904 | 11341.00 | 171690.00 |
| 苹果 | 201905 | 11459.00 | 183149.00 |
| 苹果 | 201906 | 11560.00 | 194709.00 |
| 香蕉 | 201801 | 10138.00 | 10138.00 |
| 香蕉 | 201802 | 10194.00 | 20332.00 |
| 香蕉 | 201803 | 10328.00 | 30660.00 |
| 香蕉 | 201804 | 10322.00 | 40982.00 |
| 香蕉 | 201805 | 10481.00 | 51463.00 |
| 香蕉 | 201806 | 10502.00 | 61965.00 |
| 香蕉 | 201807 | 10589.00 | 72554.00 |
| 香蕉 | 201808 | 10681.00 | 83235.00 |
| 香蕉 | 201809 | 10798.00 | 94033.00 |
| 香蕉 | 201810 | 10829.00 | 104862.00 |
| 香蕉 | 201811 | 10913.00 | 115775.00 |
| 香蕉 | 201812 | 11056.00 | 126831.00 |
| 香蕉 | 201901 | 11161.00 | 137992.00 |
| 香蕉 | 201902 | 11173.00 | 149165.00 |
| 香蕉 | 201903 | 11288.00 | 160453.00 |
| 香蕉 | 201904 | 11408.00 | 171861.00 |
| 香蕉 | 201905 | 11469.00 | 183330.00 |
| 香蕉 | 201906 | 11528.00 | 194858.00 |
+------+--------+----------+------------+对于 “橘子”，第一个月的分析窗口只有一行数据，因此累计销售额为 “10154”。第二个月的分析窗口为第一行和第二行数据，因此累计销售额为 “20337” （10154+10183）。第三个月的分析窗口为第一行到第三行数据，因此累计销售额为 “30582” （10154+10183+10245）。依次类推，直到计算完 “橘子” 所有月份的累计销售额，然后计算其他产品的累计销售额。
Tip
对于聚合窗口函数，如果我们没有指定 ORDER BY 选项，默认的窗口大小就是整个分区。如果我们指定了 ORDER BY 选项，默认的窗口大小就是分区的第一行到当前行。因此，以上示例语句中的 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 选项可以省略。
除使用 ROWS 关键字以数据行为单位指定窗口的偏移量外，我们可以使用 RANGE 关键字以数值为单位指定窗口的偏移量。例如，以下语句用于查找短期之内（5 天）累计转账超过 100 万元的账户：
sql
SELECT log_ts, from_user, total_amount
FROM (SELECT log_ts,
from_user,
SUM(amount) OVER (PARTITION BY from_user ORDER BY log_ts RANGE INTERVAL '5' DAY PRECEDING) AS total_amount
FROM transfer_log
WHERE type = '转账') t
WHERE total_amount >= 1000000;其中，SUM 函数 OVER 子句中的 RANGE 选项指定了一个 5 天之内的时间窗口。该查询返回的结果如下所示：
sh
+---------------------+----------------+--------------+
| log_ts | from_user | total_amount |
+---------------------+----------------+--------------+
| 2021-01-10 07:46:02 | 62221234567890 | 1050000 |
+---------------------+----------------+--------------+排名窗口函数
案例分析：分类排名
以下查询使用 4 个不同的排名函数计算每个员工在其部门内的月薪排名：
sql
SELECT
d.dept_name '部门名称',
e.emp_name '姓名',
e.salary '月薪',
ROW_NUMBER() OVER ( PARTITION BY e.dept_id ORDER BY e.salary DESC ) AS 'row_number',
RANK() OVER ( PARTITION BY e.dept_id ORDER BY e.salary DESC ) AS 'rank',
DENSE_RANK() OVER ( PARTITION BY e.dept_id ORDER BY e.salary DESC ) AS 'dense_rank',
PERCENT_RANK() OVER ( PARTITION BY e.dept_id ORDER BY e.salary DESC ) AS 'percent_rank'
FROM
employee e
JOIN department d ON e.dept_id = d.dept_id;其中，4 个窗口函数的 OVER 子句完全相同，PARTITION BY 表示按照部门进行分区，ORDER BY 表示按照月薪从高到低进行排序。该查询返回的结果如下所示：
sh
+------------+--------+----------+------------+------+------------+---------------------+
| 部门名称 | 姓名 | 月薪 | row_number | rank | dense_rank | percent_rank |
+------------+--------+----------+------------+------+------------+---------------------+
| 行政管理部 | 刘备 | 30000.00 | 1 | 1 | 1 | 0 |
| 行政管理部 | 关羽 | 26000.00 | 2 | 2 | 2 | 0.5 |
| 行政管理部 | 张飞 | 24000.00 | 3 | 3 | 3 | 1 |
| 人力资源部 | 诸葛亮 | 24000.00 | 1 | 1 | 1 | 0 |
| 人力资源部 | 黄忠 | 8000.00 | 2 | 2 | 2 | 0.5 |
| 人力资源部 | 魏延 | 7500.00 | 3 | 3 | 3 | 1 |
| 财务部 | 孙尚香 | 12000.00 | 1 | 1 | 1 | 0 |
| 财务部 | 孙丫鬟 | 6000.00 | 2 | 2 | 2 | 1 |
| 研发部 | 赵云 | 15000.00 | 1 | 1 | 1 | 0 |
| 研发部 | 周仓 | 8000.00 | 2 | 2 | 2 | 0.125 |
| 研发部 | 关兴 | 7000.00 | 3 | 3 | 3 | 0.25 |
| 研发部 | 关平 | 6800.00 | 4 | 4 | 4 | 0.375 |
| 研发部 | 赵氏 | 6600.00 | 5 | 5 | 5 | 0.5 |
| 研发部 | 廖化 | 6500.00 | 6 | 6 | 6 | 0.625 |
| 研发部 | 张苞 | 6500.00 | 7 | 6 | 6 | 0.625 |
| 研发部 | 赵统 | 6000.00 | 8 | 8 | 7 | 0.875 |
| 研发部 | 马岱 | 5800.00 | 9 | 9 | 8 | 1 |
| 销售部 | 法正 | 10000.00 | 1 | 1 | 1 | 0 |
| 销售部 | 简雍 | 4800.00 | 2 | 2 | 2 | 0.14285714285714285 |
| 销售部 | 孙乾 | 4700.00 | 3 | 3 | 3 | 0.2857142857142857 |
| 销售部 | 糜竺 | 4300.00 | 4 | 4 | 4 | 0.42857142857142855 |
| 销售部 | 黄权 | 4200.00 | 5 | 5 | 5 | 0.5714285714285714 |
| 销售部 | 庞统 | 4100.00 | 6 | 6 | 6 | 0.7142857142857143 |
| 销售部 | 蒋琬 | 4000.00 | 7 | 7 | 7 | 0.8571428571428571 |
| 销售部 | 邓芝 | 4000.00 | 8 | 7 | 7 | 0.8571428571428571 |
+------------+--------+----------+------------+------+------------+---------------------+我们以 “研发部” 为例，ROW_NUMBER 函数为每个员工分配了一个连续的数字编号，其中 “廖化” 和 “张苞” 的月薪相同，但是编号不同。
RANK 函数为每个员工返回了一个名次，其中 “廖化” 和 “张苞” 的名次都是 6，在他们之后 “赵统” 的名次为 8，产生了跳跃。
DENSE_RANK 函数为每个员工返回了一个名次，其中 “廖化” 和 “张苞” 的名次都是 6，在他们之后 “赵统” 的名次为 7，没有产生跳跃。
PERCENT_RANK 函数按照百分比指定名次，取值位于 0 到 1 之间。其中 “赵统” 的百分比排名为 0.875，产生了跳跃。
Tip
我们也可以使用 COUNT() 窗口函数产生和 ROW_NUMBER 函数相同的结果。
另外，以上示例中 4 个窗口函数的 OVER 子句完全相同。此时，我们可以采用一种更简单的写法：
sql
SELECT d.dept_name '部门名称',
e.emp_name '姓名',
e.salary '月薪',
ROW_NUMBER() OVER w AS 'row_number',
RANK() OVER w AS 'rank',
DENSE_RANK() OVER w AS 'dense_rank',
PERCENT_RANK() OVER w AS 'percent_rank'
FROM employee e
JOIN department d ON e.dept_id = d.dept_id
WINDOW w AS (PARTITION BY e.dept_id ORDER BY e.salary DESC );我们在查询语句的最后使用 WINDOW 子句定义了一个窗口变量 w，然后在所有窗口函数的 OVER 子句中使用了该变量。该查询返回的结果和上面的示例相同。
这种使用窗口变量的写法可以简化窗口选项的输入，目前 Oracle 和 Microsoft SQL Server 还不支持这种语法。
基于排名窗口函数，我们还可以实现分类 Top-N 排行榜。例如，以下语句用于查找每个部门中最早入职的 2 名员工：
sql
WITH ranked_emp AS (SELECT d.dept_name,
e.emp_name,
e.hire_date,
ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.hire_date) AS rn
FROM employee e
JOIN department d ON e.dept_id = d.dept_id)
SELECT dept_name '部门名称', emp_name '姓名', hire_date '入职日期', rn '入职顺序'
FROM ranked_emp
WHERE rn <= 2;其中，ranked_emp 是一个通用表表达式，包含了员工在其部门内的入职顺序。然后，我们在主查询语句中返回了每个部门前 2 名入职的员工：
sh
+------------+--------+------------+----------+
| 部门名称 | 姓名 | 入职日期 | 入职顺序 |
+------------+--------+------------+----------+
| 行政管理部 | 刘备 | 2000-01-01 | 1 |
| 行政管理部 | 关羽 | 2000-01-01 | 2 |
| 人力资源部 | 诸葛亮 | 2006-03-15 | 1 |
| 人力资源部 | 魏延 | 2007-04-01 | 2 |
| 财务部 | 孙尚香 | 2002-08-08 | 1 |
| 财务部 | 孙丫鬟 | 2002-08-08 | 2 |
| 研发部 | 赵云 | 2005-12-19 | 1 |
| 研发部 | 廖化 | 2009-02-17 | 2 |
| 销售部 | 法正 | 2017-04-09 | 1 |
| 销售部 | 庞统 | 2017-06-06 | 2 |
+------------+--------+------------+----------+案例分析：累计分布
CUME_DIST 函数可以返回当前行在分区内的累计分布，也就是排名在当前行之前（包含当前行）所有数据所占的比率，取值返回为大于 0 且小于或等于 1。
例如，以下查询返回了所有员工按照月薪排名的累计分布情况：
sql
SELECT emp_name '姓名', salary '月薪', CUME_DIST() OVER (ORDER BY salary) '累计占比'
FROM employee;其中，OVER 子句没有指定分区选项，因此 CUME_DIST 函数会将全体员工作为一个整体进行分析。ORDER BY 选项表示按照月薪从低到高进行排序。该查询返回的结果如下：
sh
+--------+----------+----------+
| 姓名 | 月薪 | 累计占比 |
+--------+----------+----------+
| 蒋琬 | 4000.00 | 0.08 |
| 邓芝 | 4000.00 | 0.08 |
| 庞统 | 4100.00 | 0.12 |
| 黄权 | 4200.00 | 0.16 |
| 糜竺 | 4300.00 | 0.2 |
| 孙乾 | 4700.00 | 0.24 |
| 简雍 | 4800.00 | 0.28 |
| 马岱 | 5800.00 | 0.32 |
| 孙丫鬟 | 6000.00 | 0.4 |
| 赵统 | 6000.00 | 0.4 |
| 廖化 | 6500.00 | 0.48 |
| 张苞 | 6500.00 | 0.48 |
| 赵氏 | 6600.00 | 0.52 |
| 关平 | 6800.00 | 0.56 |
| 关兴 | 7000.00 | 0.6 |
| 魏延 | 7500.00 | 0.64 |
| 黄忠 | 8000.00 | 0.72 |
| 周仓 | 8000.00 | 0.72 |
| 法正 | 10000.00 | 0.76 |
| 孙尚香 | 12000.00 | 0.8 |
| 赵云 | 15000.00 | 0.84 |
| 张飞 | 24000.00 | 0.92 |
| 诸葛亮 | 24000.00 | 0.92 |
| 关羽 | 26000.00 | 0.96 |
| 刘备 | 30000.00 | 1 |
+--------+----------+----------+结果显示 8%（2/25）的员工月薪小于或等于 4000 元；或者也可以说，月薪 4000 元，意味着在公司中的月薪排名属于最低的 8%。
NTILE 函数用于将分区内的数据分为 N 等份，并计算当前行所在的分片位置。例如，以下语句将员工按照入职先后顺序分为 5 组，并计算每个员工所在的分组：
sql
SELECT emp_name '姓名', hire_date '入职日期', NTILE(5) OVER (ORDER BY hire_date) AS '分组位置'
FROM employee;其中，OVER 子句没有指定分区选项，因此 NTILE 函数会将全体员工作为一个整体进行分析。ORDER BY 选项表示按照入职先后进行排序。该查询返回的结果如下：
sh
+--------+------------+----------+
| 姓名 | 入职日期 | 分组位置 |
+--------+------------+----------+
| 刘备 | 2000-01-01 | 1 |
| 关羽 | 2000-01-01 | 1 |
| 张飞 | 2000-01-01 | 1 |
| 孙尚香 | 2002-08-08 | 1 |
| 孙丫鬟 | 2002-08-08 | 1 |
| 赵云 | 2005-12-19 | 2 |
| 诸葛亮 | 2006-03-15 | 2 |
| 魏延 | 2007-04-01 | 2 |
| 黄忠 | 2008-10-25 | 2 |
| 廖化 | 2009-02-17 | 2 |
| 周仓 | 2010-02-20 | 3 |
| 关平 | 2011-07-24 | 3 |
| 关兴 | 2011-07-30 | 3 |
| 赵氏 | 2011-11-10 | 3 |
| 赵统 | 2012-05-03 | 3 |
| 张苞 | 2012-05-31 | 4 |
| 马岱 | 2014-09-16 | 4 |
| 法正 | 2017-04-09 | 4 |
| 庞统 | 2017-06-06 | 4 |
| 蒋琬 | 2018-01-28 | 4 |
| 黄权 | 2018-03-14 | 5 |
| 糜竺 | 2018-03-27 | 5 |
| 孙乾 | 2018-10-09 | 5 |
| 邓芝 | 2018-11-11 | 5 |
| 简雍 | 2019-05-11 | 5 |
+--------+------------+----------+分组位置为 1 的是最早入职的 20% 员工，分组位置为 5 的是最晚入职的 20% 员工。
取值窗口函数
案例分析：环比、同比分析
环比增长指的是本期数据与上期数据相比的增长，例如，产品 2019 年 6 月的销售额与 2019 年 5 月的销售额相比增加的部分。以下语句统计了各种产品每个月的环比增长率：
sql
SELECT product '产品',
ym '年月',
amount ' 销售额',
((amount - LAG(amount, 1) OVER (PARTITION BY product ORDER BY ym)) /
LAG(amount, 1) OVER (PARTITION BY product ORDER BY ym)) * 100 '环比增长率'
FROM sales_monthly
ORDER BY product, ym;其中，LAG(amount, 1) 表示获取上一期的销售额，PARTITION BY 选项表示按照产品分区，ORDER BY 选项表示按照月份进行排序。当前月份的销售额 amount 减去上一期的销售额，再除以上一期的销售额，就是环比增长率。该查询返回的结果如下：
sh
+------+--------+----------+------------+
| 产品 | 年月 | 销售额 | 环比增长率 |
+------+--------+----------+------------+
| 桔子 | 201801 | 10154.00 | NULL |
| 桔子 | 201802 | 10183.00 | 0.285602 |
| 桔子 | 201803 | 10245.00 | 0.608858 |
| 桔子 | 201804 | 10325.00 | 0.780869 |
| 桔子 | 201805 | 10465.00 | 1.355932 |
| 桔子 | 201806 | 10505.00 | 0.382226 |
| 桔子 | 201807 | 10578.00 | 0.694907 |
| 桔子 | 201808 | 10680.00 | 0.964265 |
| 桔子 | 201809 | 10788.00 | 1.011236 |
| 桔子 | 201810 | 10838.00 | 0.463478 |
| 桔子 | 201811 | 10942.00 | 0.959587 |
| 桔子 | 201812 | 10988.00 | 0.420398 |
| 桔子 | 201901 | 11099.00 | 1.010193 |
| 桔子 | 201902 | 11181.00 | 0.738805 |
| 桔子 | 201903 | 11302.00 | 1.082193 |
| 桔子 | 201904 | 11327.00 | 0.221200 |
| 桔子 | 201905 | 11423.00 | 0.847532 |
| 桔子 | 201906 | 11524.00 | 0.884181 |
| 苹果 | 201801 | 10159.00 | NULL |
| 苹果 | 201802 | 10211.00 | 0.511861 |
| 苹果 | 201803 | 10247.00 | 0.352561 |
| 苹果 | 201804 | 10376.00 | 1.258905 |
| 苹果 | 201805 | 10400.00 | 0.231303 |
| 苹果 | 201806 | 10565.00 | 1.586538 |
| 苹果 | 201807 | 10613.00 | 0.454330 |
| 苹果 | 201808 | 10696.00 | 0.782060 |
| 苹果 | 201809 | 10751.00 | 0.514211 |
| 苹果 | 201810 | 10842.00 | 0.846433 |
| 苹果 | 201811 | 10900.00 | 0.534957 |
| 苹果 | 201812 | 10972.00 | 0.660550 |
| 苹果 | 201901 | 11155.00 | 1.667882 |
| 苹果 | 201902 | 11202.00 | 0.421336 |
| 苹果 | 201903 | 11260.00 | 0.517765 |
| 苹果 | 201904 | 11341.00 | 0.719361 |
| 苹果 | 201905 | 11459.00 | 1.040473 |
| 苹果 | 201906 | 11560.00 | 0.881403 |
| 香蕉 | 201801 | 10138.00 | NULL |
| 香蕉 | 201802 | 10194.00 | 0.552377 |
| 香蕉 | 201803 | 10328.00 | 1.314499 |
| 香蕉 | 201804 | 10322.00 | -0.058095 |
| 香蕉 | 201805 | 10481.00 | 1.540399 |
| 香蕉 | 201806 | 10502.00 | 0.200363 |
| 香蕉 | 201807 | 10589.00 | 0.828414 |
| 香蕉 | 201808 | 10681.00 | 0.868826 |
| 香蕉 | 201809 | 10798.00 | 1.095403 |
| 香蕉 | 201810 | 10829.00 | 0.287090 |
| 香蕉 | 201811 | 10913.00 | 0.775695 |
| 香蕉 | 201812 | 11056.00 | 1.310364 |
| 香蕉 | 201901 | 11161.00 | 0.949711 |
| 香蕉 | 201902 | 11173.00 | 0.107517 |
| 香蕉 | 201903 | 11288.00 | 1.029267 |
| 香蕉 | 201904 | 11408.00 | 1.063076 |
| 香蕉 | 201905 | 11469.00 | 0.534712 |
| 香蕉 | 201906 | 11528.00 | 0.514430 |
+------+--------+----------+------------+2018 年 1 月是第一期，因此其环比增长率为空。“橘子” 2018 年 2 月的环比增长率约为 0.2856%（(10183-10154)/10154*100），依此类推。
同比增长指的是本期数据与上一年度或历史同期相比的增长，例如，产品 2019 年 6 月的销售额与 2018 年 6 月的销售额相比增加的部分。以下语句统计了各种产品每个月的同比增长率：
sql
SELECT product '产品',
ym '年月',
amount ' 销售额',
((amount - LAG(amount, 12) OVER (PARTITION BY product ORDER BY ym)) /
LAG(amount, 12) OVER (PARTITION BY product ORDER BY ym)) * 100 '同比增长率'
FROM sales_monthly
ORDER BY product, ym;其中，LAG(amount, 12) 表示当前月份之前第 12 期的销售额，也就是去年同月份的销售额。PARTITION BY 选项表示按照产品分区，ORDER BY 选项表示按照月份进行排序。当前月份的销售额 amount 减去去年同期的销售额，再除以去年同期的销售额，就是同比增长率。该查询返回的结果如下：
sh
+------+--------+----------+------------+
| 产品 | 年月 | 销售额 | 同比增长率 |
+------+--------+----------+------------+
| 桔子 | 201801 | 10154.00 | NULL |
| 桔子 | 201802 | 10183.00 | NULL |
| 桔子 | 201803 | 10245.00 | NULL |
| 桔子 | 201804 | 10325.00 | NULL |
| 桔子 | 201805 | 10465.00 | NULL |
| 桔子 | 201806 | 10505.00 | NULL |
| 桔子 | 201807 | 10578.00 | NULL |
| 桔子 | 201808 | 10680.00 | NULL |
| 桔子 | 201809 | 10788.00 | NULL |
| 桔子 | 201810 | 10838.00 | NULL |
| 桔子 | 201811 | 10942.00 | NULL |
| 桔子 | 201812 | 10988.00 | NULL |
| 桔子 | 201901 | 11099.00 | 9.306677 |
| 桔子 | 201902 | 11181.00 | 9.800648 |
| 桔子 | 201903 | 11302.00 | 10.317228 |
| 桔子 | 201904 | 11327.00 | 9.704600 |
| 桔子 | 201905 | 11423.00 | 9.154324 |
| 桔子 | 201906 | 11524.00 | 9.700143 |
| 苹果 | 201801 | 10159.00 | NULL |
| 苹果 | 201802 | 10211.00 | NULL |
| 苹果 | 201803 | 10247.00 | NULL |
| 苹果 | 201804 | 10376.00 | NULL |
| 苹果 | 201805 | 10400.00 | NULL |
| 苹果 | 201806 | 10565.00 | NULL |
| 苹果 | 201807 | 10613.00 | NULL |
| 苹果 | 201808 | 10696.00 | NULL |
| 苹果 | 201809 | 10751.00 | NULL |
| 苹果 | 201810 | 10842.00 | NULL |
| 苹果 | 201811 | 10900.00 | NULL |
| 苹果 | 201812 | 10972.00 | NULL |
| 苹果 | 201901 | 11155.00 | 9.804115 |
| 苹果 | 201902 | 11202.00 | 9.705220 |
| 苹果 | 201903 | 11260.00 | 9.885820 |
| 苹果 | 201904 | 11341.00 | 9.300308 |
| 苹果 | 201905 | 11459.00 | 10.182692 |
| 苹果 | 201906 | 11560.00 | 9.417889 |
| 香蕉 | 201801 | 10138.00 | NULL |
| 香蕉 | 201802 | 10194.00 | NULL |
| 香蕉 | 201803 | 10328.00 | NULL |
| 香蕉 | 201804 | 10322.00 | NULL |
| 香蕉 | 201805 | 10481.00 | NULL |
| 香蕉 | 201806 | 10502.00 | NULL |
| 香蕉 | 201807 | 10589.00 | NULL |
| 香蕉 | 201808 | 10681.00 | NULL |
| 香蕉 | 201809 | 10798.00 | NULL |
| 香蕉 | 201810 | 10829.00 | NULL |
| 香蕉 | 201811 | 10913.00 | NULL |
| 香蕉 | 201812 | 11056.00 | NULL |
| 香蕉 | 201901 | 11161.00 | 10.090748 |
| 香蕉 | 201902 | 11173.00 | 9.603688 |
| 香蕉 | 201903 | 11288.00 | 9.295120 |
| 香蕉 | 201904 | 11408.00 | 10.521217 |
| 香蕉 | 201905 | 11469.00 | 9.426581 |
| 香蕉 | 201906 | 11528.00 | 9.769568 |
+------+--------+----------+------------+2018 年的 12 期数据都没有对应的同比增长率。“橘子” 2019 年 1 月的环比增长率约为 9.3067%（(11099-10154)/10154*100），依此类推。
案例分析：复合增长率
复合增长率是第 N 期的数据除以第一期的基准数据，然后开 N-1 次方再减去 1 得到的结果。假如 2018 年的产品销售额为 10000，2019 年的产品销售额为 12500，2020 年的产品销售额为 15000（销售额单位省略，下同）。那么这两年的复合增长率的计算方式如下：
(15000/10000)^(1/2) - 1 = 22.47%以年度为单位计算的复合增长率被称为年均复合增长率，以月度为单位计算的复合增长率被称为月均复合增长率。以下查询统计了自 2018 年 1 月以来不同产品的月均销售额复合增长率：
sql
WITH s ( product, ym, amount, first_amount, num ) AS (
SELECT
product,
ym,
amount,
FIRST_VALUE( amount ) OVER ( PARTITION BY product ORDER BY ym ),
ROW_NUMBER() OVER ( PARTITION BY product ORDER BY ym )
FROM
sales_monthly
) SELECT
product '产品',
ym '年月',
amount ' 销售额',
( POWER( 1.0 * amount / first_amount, 1.0 / NULLIF( num - 1, 0 )) - 1 ) * 100 '月均复合销售率（%）'
FROM
s
ORDER BY
product,
ym;我们首先定义了一个通用表表达式，其中 FIRST_VALUE(amount) 返回了第一期（201801）的销售额，ROW_NUMBER 函数返回了每一期的编号。主查询中的 POWER 函数用于执行开方运算，NULLIF 函数用于处理第一期数据的除零错误，常量 1.0 用于避免由整数除法所导致的精度丢失问题。该查询返回的结果如下：
sh
+------+--------+----------+---------------------+
| 产品 | 年月 | 销售额 | 月均复合销售率（%） |
+------+--------+----------+---------------------+
| 桔子 | 201801 | 10154.00 | NULL |
| 桔子 | 201802 | 10183.00 | 0.2856017333070726 |
| 桔子 | 201803 | 10245.00 | 0.44709978015609053 |
| 桔子 | 201804 | 10325.00 | 0.5582330899585486 |
| 桔子 | 201805 | 10465.00 | 0.7570673570692854 |
| 桔子 | 201806 | 10505.00 | 0.6819873698546308 |
| 桔子 | 201807 | 10578.00 | 0.6841405548516422 |
| 桔子 | 201808 | 10680.00 | 0.7241107615881148 |
| 桔子 | 201809 | 10788.00 | 0.7599567334352519 |
| 桔子 | 201810 | 10838.00 | 0.7269714845334896 |
| 桔子 | 201811 | 10942.00 | 0.7502088623154046 |
| 桔子 | 201812 | 10988.00 | 0.7201813848927152 |
| 桔子 | 201901 | 11099.00 | 0.7443171820965988 |
| 桔子 | 201902 | 11181.00 | 0.7438931740867183 |
| 桔子 | 201903 | 11302.00 | 0.7680198585853448 |
| 桔子 | 201904 | 11327.00 | 0.7314725430307867 |
| 桔子 | 201905 | 11423.00 | 0.7387223790708886 |
| 桔子 | 201906 | 11524.00 | 0.7472729569657055 |
| 苹果 | 201801 | 10159.00 | NULL |
| 苹果 | 201802 | 10211.00 | 0.5118614036814684 |
| 苹果 | 201803 | 10247.00 | 0.432179599392124 |
| 苹果 | 201804 | 10376.00 | 0.7070020377621056 |
| 苹果 | 201805 | 10400.00 | 0.5878660411253733 |
| 苹果 | 201806 | 10565.00 | 0.7868120035583903 |
| 苹果 | 201807 | 10613.00 | 0.731322068040785 |
| 苹果 | 201808 | 10696.00 | 0.7385687405051078 |
| 苹果 | 201809 | 10751.00 | 0.7104966528160617 |
| 苹果 | 201810 | 10842.00 | 0.7255916252433714 |
| 苹果 | 201811 | 10900.00 | 0.7065118729371989 |
| 苹果 | 201812 | 10972.00 | 0.7023326884637582 |
| 苹果 | 201901 | 11155.00 | 0.7824436777998489 |
| 苹果 | 201902 | 11202.00 | 0.7546200948467252 |
| 苹果 | 201903 | 11260.00 | 0.7376833619111567 |
| 苹果 | 201904 | 11341.00 | 0.7364617354021608 |
| 苹果 | 201905 | 11459.00 | 0.7554355959988657 |
| 苹果 | 201906 | 11560.00 | 0.7628410974228617 |
| 香蕉 | 201801 | 10138.00 | NULL |
| 香蕉 | 201802 | 10194.00 | 0.5523771947129719 |
| 香蕉 | 201803 | 10328.00 | 0.9327186350557293 |
| 香蕉 | 201804 | 10322.00 | 0.6013609469889936 |
| 香蕉 | 201805 | 10481.00 | 0.835303204318727 |
| 香蕉 | 201806 | 10502.00 | 0.7079940138587482 |
| 香蕉 | 201807 | 10589.00 | 0.7280539559113341 |
| 香蕉 | 201808 | 10681.00 | 0.7481522313370847 |
| 香蕉 | 201809 | 10798.00 | 0.7914932743322778 |
| 香蕉 | 201810 | 10829.00 | 0.7353234394735786 |
| 香蕉 | 201811 | 10913.00 | 0.7393598576287186 |
| 香蕉 | 201812 | 11056.00 | 0.7911360374552867 |
| 香蕉 | 201901 | 11161.00 | 0.8043410660433459 |
| 香蕉 | 201902 | 11173.00 | 0.7505674522999994 |
| 香蕉 | 201903 | 11288.00 | 0.7704490436124756 |
| 香蕉 | 201904 | 11408.00 | 0.789931105093733 |
| 香蕉 | 201905 | 11469.00 | 0.7739609841810768 |
| 香蕉 | 201906 | 11528.00 | 0.75867592930563 |
+------+--------+----------+---------------------+2018 年 1 月是第一期，因此产品月均销售额复合增长率为空。“橘子” 2018 年 2 月的月均销售额复合增长率等于它的环比增长率，2018 年 3 月的月均销售额复合增长率等于 0.4471%，依此类推。
以下语句统计了不同产品最低销售额、最高销售额以及第三高销售额所在的月份：
sql
SELECT product '产品',
ym '年月',
amount ' 销售额',
FIRST_VALUE(ym) OVER w '最高销售额月份',
LAST_VALUE(ym) OVER w '最低销售额月份',
NTH_VALUE(ym, 3) OVER w '第三高销售额月份'
FROM sales_monthly
WINDOW w AS ( PARTITION BY product ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
ORDER BY product, ym;三个窗口函数的 OVER 子句相同，PARTITION BY 选项表示按照产品分区，ORDER BY 选项表示按照销售额从高到低进行排序。以上三个函数的默认窗口都是从分区的第一行到当前行，因此我们将窗口扩展到了整个分区。该查询返回的结果如下：
sh
+------+--------+----------+----------------+----------------+------------------+
| 产品 | 年月 | 销售额 | 最高销售额月份 | 最低销售额月份 | 第三高销售额月份 |
+------+--------+----------+----------------+----------------+------------------+
| 桔子 | 201801 | 10154.00 | 201906 | 201801 | 201904 |
| 桔子 | 201802 | 10183.00 | 201906 | 201801 | 201904 |
| 桔子 | 201803 | 10245.00 | 201906 | 201801 | 201904 |
| 桔子 | 201804 | 10325.00 | 201906 | 201801 | 201904 |
| 桔子 | 201805 | 10465.00 | 201906 | 201801 | 201904 |
| 桔子 | 201806 | 10505.00 | 201906 | 201801 | 201904 |
| 桔子 | 201807 | 10578.00 | 201906 | 201801 | 201904 |
| 桔子 | 201808 | 10680.00 | 201906 | 201801 | 201904 |
| 桔子 | 201809 | 10788.00 | 201906 | 201801 | 201904 |
| 桔子 | 201810 | 10838.00 | 201906 | 201801 | 201904 |
| 桔子 | 201811 | 10942.00 | 201906 | 201801 | 201904 |
| 桔子 | 201812 | 10988.00 | 201906 | 201801 | 201904 |
| 桔子 | 201901 | 11099.00 | 201906 | 201801 | 201904 |
| 桔子 | 201902 | 11181.00 | 201906 | 201801 | 201904 |
| 桔子 | 201903 | 11302.00 | 201906 | 201801 | 201904 |
| 桔子 | 201904 | 11327.00 | 201906 | 201801 | 201904 |
| 桔子 | 201905 | 11423.00 | 201906 | 201801 | 201904 |
| 桔子 | 201906 | 11524.00 | 201906 | 201801 | 201904 |
| 苹果 | 201801 | 10159.00 | 201906 | 201801 | 201904 |
| 苹果 | 201802 | 10211.00 | 201906 | 201801 | 201904 |
| 苹果 | 201803 | 10247.00 | 201906 | 201801 | 201904 |
| 苹果 | 201804 | 10376.00 | 201906 | 201801 | 201904 |
| 苹果 | 201805 | 10400.00 | 201906 | 201801 | 201904 |
| 苹果 | 201806 | 10565.00 | 201906 | 201801 | 201904 |
| 苹果 | 201807 | 10613.00 | 201906 | 201801 | 201904 |
| 苹果 | 201808 | 10696.00 | 201906 | 201801 | 201904 |
| 苹果 | 201809 | 10751.00 | 201906 | 201801 | 201904 |
| 苹果 | 201810 | 10842.00 | 201906 | 201801 | 201904 |
| 苹果 | 201811 | 10900.00 | 201906 | 201801 | 201904 |
| 苹果 | 201812 | 10972.00 | 201906 | 201801 | 201904 |
| 苹果 | 201901 | 11155.00 | 201906 | 201801 | 201904 |
| 苹果 | 201902 | 11202.00 | 201906 | 201801 | 201904 |
| 苹果 | 201903 | 11260.00 | 201906 | 201801 | 201904 |
| 苹果 | 201904 | 11341.00 | 201906 | 201801 | 201904 |
| 苹果 | 201905 | 11459.00 | 201906 | 201801 | 201904 |
| 苹果 | 201906 | 11560.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201801 | 10138.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201802 | 10194.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201803 | 10328.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201804 | 10322.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201805 | 10481.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201806 | 10502.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201807 | 10589.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201808 | 10681.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201809 | 10798.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201810 | 10829.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201811 | 10913.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201812 | 11056.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201901 | 11161.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201902 | 11173.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201903 | 11288.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201904 | 11408.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201905 | 11469.00 | 201906 | 201801 | 201904 |
| 香蕉 | 201906 | 11528.00 | 201906 | 201801 | 201904 |
+------+--------+----------+----------------+----------------+------------------+“橘子” 的最高销售额出现在 2019 年 6 月，最低销售额出现在 2018 年 1 月，第三高销售额出现在 2019 年 4 月。