二十二、高级报表之分类排名和环比/同比分析

上一篇我们介绍了窗口函数的概念和语法，以及聚合窗口函数的使用。
今天我们继续讨论 SQL 中的排名窗口函数和取值窗口函数，它们分别可以用于统计产品的分类排名和数据的环比/同比分析。

排名窗口函数

排名窗口函数用于对数据进行分组排名。常见的排名窗口函数包括：

• ROW_NUMBER，为分区中的每行数据分配一个序列号，序列号从 1 开始分配。
• RANK，计算每行数据在其分区中的名次；如果存在名次相同的数据，后续的排名将会产生跳跃。
• DENSE_RANK，计算每行数据在其分区中的名次；即使存在名次相同的数据，后续的排名也是连续的值。
• PERCENT_RANK，以百分比的形式显示每行数据在其分区中的名次；如果存在名次相同的数据，后续的排名将会产生跳跃。
• CUME_DIST，计算每行数据在其分区内的累积分布。
• NTILE，将分区内的数据分为 N 等份，为每行数据计算其所在的位置。

排名窗口函数不支持动态的窗口大小（frame_clause），而是以整个分区（PARTITION BY）作为分析的窗口。接下来我们通过示例了解一下这些函数的作用。

按照分类进行排名

以下示例按照部门分组，并计算每个员工在其部门中的月薪排名，分别使用了 4 个不同的排名函数：

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 表示按照月薪从高到低进行排序。该语句的结果如下（显示部分内容）：

我们以结果中的“研发部”为例进行分析：

• ROW_NUMBER 函数为每个员工分配了一个连续的数字编号，可以看作是一种排名。其中“廖化”和“张苞”的月薪相同，但是编号不同；
• RANK 函数为每个员工指定了一个名次，其中“廖化”和“张苞”的名次都是 6。在他们之后的“赵统”的名次为 8，产生了跳跃；
• DENSE_RANK 函数为每个员工指定了一个名次，其中“廖化”和“张苞”的名次都是 6。在他们之后的“赵统”的名次为 7，名次是连续值；
• PERCENT_RANK 函数按照百分比指定名次，取值位于 0 到 1 之间。其中“赵统”的百分比排名为 0.875，也产生了跳跃。

其他部门的数据也按照相同的方式进行分析。
在以上示例中，4 个窗口函数的 OVER 子句完全相同。此时可以采用一种更简单的写法：
— MySQL 以及 PostgreSQL 实现 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 和 SQL Server 目前还不支持这种写法。
另外，利用排名窗口函数可以获得每个类别中的 Top-N 排行榜。以下示例返回每个部门中前 2 名入职的员工：
SELECT dept_name “部门名称”, emp_name “姓名”, hire_date “入职日期”, rn “入职顺序” FROM (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)) tmp WHERE tmp.rn <= 2; 复制
其中，FROM 中的子查询按照部门计算每个员工的入职顺序；然后利用外查询找出每个部门中的前 2 名入职的员工。该查询的结果如下：

除了获取数据的排名之外，排名窗口函数还可以用于获取数据的累积分布和所在的分片位置。

累积分布与分片位置

CUME_DIST 函数计算数据对应的累积分布，也就是排在该行数据之前的所有数据所占的比率；取值范围为大于 0 并且小于等于 1。
以下示例统计了员工按照月薪排名的累积占比：
SELECT emp_name AS “姓名”, salary AS “月薪”, CUME_DIST() OVER (ORDER BY salary) AS “累积占比” FROM employee; 复制
其中，OVER 子句没有指定分区选项，因此所有的员工作为一个整体进行分析；ORDER BY 按照月薪从低到高进行排序。该语句的结果如下（显示部分内容）：

从结果可以看出，20% 的员工月薪小于等于 4300；或者说，月薪 4300 意味着在公司中的排名属于最低的 20%。
另外，NTILE 函数将分区内的数据分为 N 等份，并计算数据所在的分片位置。以下语句将员工按照入职先后分为 5 组，并计算每个员工所在的分组位置：
SELECT emp_name AS “姓名”, hire_date AS “入职日期”, NTILE(5) OVER (ORDER BY hire_date) AS “相对位置” FROM employee; 复制
其中，OVER 子句没有指定分区选项，因此所有的员工作为一个整体进行分析；ORDER BY 按照入职日期进行排序。该语句的结果如下（显示部分内容）：

相对位置为 1 的员工是最早入职的 20% 员工；其他的数据依次类推。

取值窗口函数

取值窗口函数用于返回指定位置上的数据。常见的取值窗口函数包括：

• FIRST_VALUE，返回窗口内第一行的数据。
• LAG，返回分区中当前行之前的第 N 行的数据。
• LAST_VALUE，返回窗口内最后一行的数据。
• LEAD，返回分区中当前行之后第 N 行的数据。
• NTH_VALUE，返回窗口内第 N 行的数据。

其中，LAG 和 LEAD 函数不支持动态的窗口大小（frame_clause），而是以分区（PARTITION BY）作为分析的窗口。
接下来我们需要使用一个新的数据表：sales_monthly，它记录了不同产品按月统计的销售金额。该示例表和初始化数据可以点击 GitHub 下载。

计算环比/同比增长

环比增长是指本期数据与上期数据相比的增长。例如，2019 年 6 月的销量与 2019 年 5 月的销量相比增加的部分。以下语句按照产品统计每个月的环比增长率：
SELECT product AS “产品”, ym “年月”, amount “销量”, (amount - LAG(amount, 1) OVER (PARTITION BY product ORDER BY ym))/ LAG(amount, 1) OVER (PARTITION BY product ORDER BY ym) 100 AS “环比增长率（%）” FROM sales_monthly ORDER BY product, ym; 复制
其中，LAG(amount, 1) 表示获取上一期的销量；PARTITION BY 表示按照产品分区；ORDER BY 表示按照月份进行排序；当前月份的销量减去上个月的销量，再除以上个月的销量，就是环比增长率。该查询的结果如下（显示部分内容）：

查询结果中的 201801 期没有上一期，因此环比增长率为空。201802 期的环比增长率为 (10183 - 10154) / 10154 100 = 0.2856；其他数据依此类推。
另外，同比增长是指本期数据与上一年度或历史同期相比的增长。例如，2019 年 6 月的销量与 2018 年 6 月的销量相比增加的部分。以下查询按照产品统计每个月的同比增长率：
SELECT product AS “产品”, ym “年月”, amount “销量”, (amount - LAG(amount, 12) OVER (PARTITION BY product ORDER BY ym))/ LAG(amount, 12) OVER (PARTITION BY product ORDER BY ym) 100 AS “同比增长率（%）” FROM sales_monthly ORDER BY product, ym; 复制
其中，LAG(amount, 12) 表示获取前面第 12 期的销量，也就是去年同月份的销量；其他的参数与环比类似。该查询的结果如下（显示部分内容）：

查询结果中，2018 年的 12 期数据都没有对应的同比增长率。201901 期的同比增长率为 (11099 - 10154) / 10154 100 = 9.3067；其他数据依此类推。
LEAD 函数与 LAG 函数类似，但它返回的是当前行之后的第 N 行数据。

获取指定名次的数据

FIRSTVALUE、LAST_VALUE 以及 NTH 函数分别用于返回窗口内第一行、最后一行以及第 N 行数据。
以下语句按照产品统计最低销量、最高销量以及第三名销量：
SELECT product AS “产品”, ym “年月”, amount “销量”, FIRST_VALUE(amount) OVER (PARTITION BY product ORDER BY amount DESC) AS “最高销量”, LAST_VALUE(amount) OVER (PARTITION BY product ORDER BY amount DESC) AS “最低销量”, — SQL Server 不支持 NTHVALUE NTH_VALUE(amount, 3) OVER (PARTITION BY product ORDER BY amount DESC) AS “第三名销量” FROM sales_monthly ORDER BY product, amount DESC; 复制
其中，三个窗口函数的 OVER 子句相同；PARTITION BY 表示按照产品分区；ORDER BY 表示按照销量从高到低排序。该查询的结果如下（显示部分内容）：

以上三个函数的默认窗口是从分区的第一行到当前行。由于按照销量从高到低排序，因此最高的销量就是 201906 期的 11524；最低的销量就是当前月份的销量；销量第三名就是 201904 期的 11327，对于最前面两条记录而言没有第三名。
SQL Server 目前不支持 NTH_VALUE 函数。
如果想要获取整个分区范围内的最低销量和第三名销量，可以在 OVER 子句中使用以下窗口选项：
[ROWS | RANGE] BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING; 复制
此时，LAST_VALUE 和 NTH_VALUE 函数返回的结果会略有不同，大家可以自己尝试一下。

小结

本篇我们学习了另外两类窗口函数：用于计算分类排名的排名窗口函数，以及获取指定位置数据的取值窗口函数。SQL 分析函数为数据仓库和在线分析系统（OLAP）提供了强大易用的分析和报表功能，并且在各种数据库中可以通用。
练习题 1：如何获得 sales_monthly 表中按照产品统计的销量前三名和相应的月份？
练习题 2：在 sales_monthly 表中，如何知道按照产品统计的最低销量、最高销量以及第三名销量所在的月份？