SQL窗口函数之排名窗口函数怎么使用

54次阅读

共计 7389 个字符，预计需要花费 19 分钟才能阅读完成。

这篇文章主要介绍“SQL 窗口函数之排名窗口函数怎么使用”的相关知识，丸趣 TV 小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“SQL 窗口函数之排名窗口函数怎么使用”文章能帮助大家解决问题。

取值窗口函数可以用于返回窗口内指定位置的数据行。常见的取值窗口函数如下：

LAG 函数可以返回窗口内当前行之前的第 N 行数据。LEAD 函数可以返回窗口内当前行之后的第 N 行数据。FIRST_VALUE 函数可以返回窗口内第一行数据。LAST_VALUE 函数可以返回窗口内最后一行数据。NTH_VALUE 函数可以返回窗口内第 N 行数据。

其中，LAG 函数和 LEAD 函数不支持动态的窗口大小，它们以整个分区作为分析的窗口。

案例分析案例使用的示例表

下面的查询中会用到一张表，sales_monthly 表中存储了商品销量信息，product 表示产品名称，ym 表示年月，amount 表示销售金额（元）。

以下是该表中的部分数据：

这个表的初始化脚本可以在文章底部获取。

1. 环比分析

环比增长指的是本期数据与上期数据相比的增长，例如，产品 2019 年 6 月的销售额与 2019 年 5 月的销售额相比增加的部分。

以下语句统计了各种产品每个月的环比增长率：

SELECT s.product AS  产品 , s.ym AS  年月 , s.amount AS  销售额 ,
 ( 
 (s.amount - LAG(s.amount,1) OVER (PARTITION BY product ORDER BY s.ym))/
 LAG(s.amount,1) OVER (PARTITION BY product ORDER BY s.ym)
 ) * 100 AS  环比增长率 (%) 
FROM sales_monthly s
ORDER BY s.product,s.ym

其中，LAG（amount，1）表示获取上一期的销售额，PARTITION BY 选项表示按照产品分区，ORDER BY 选项表示按照月份进行排序。

当前月份的销售额 amount 减去上一期的销售额，再除以上一期的销售额，就是环比增长率。

该查询返回的结果如下：

2018 年 1 月是第一期，因此其环比增长率为空。

“桔子”2018 年 2 月的环比增长率约为 0.2856%（（10183-10154）/10154×100），依此类推。

2. 同比分析

同比增长指的是本期数据与上一年度或历史同期相比的增长，例如，产品 2019 年 6 月的销售额与 2018 年 6 月的销售额相比增加的部分。

以下语句统计了各种产品每个月的同比增长率：

SELECT s.product AS  产品 , s.ym AS  年月 , s.amount AS  销售额 ,
 ( 
 (s.amount - LAG(s.amount,12) OVER (PARTITION BY product ORDER BY s.ym))/
 LAG(s.amount,12) OVER (PARTITION BY product ORDER BY s.ym)
 ) * 100 AS  同比增长率 (%) 
FROM sales_monthly s
ORDER BY s.product,s.ym

其中，LAG（amount，12）表示当前月份之前第 12 期的销售额，也就是去年同月份的销售额。

PARTITION BY 选项表示按照产品分区，ORDER BY 选项表示按照月份进行排序。

当前月份的销售额 amount 减去去年同期的销售额，再除以去年同期的销售额，就是同比增长率。

该查询返回的结果如下：

2018 年的 12 期数据都没有对应的同比增长率，“桔子”2019 年 1 月的同比增长率约为 9.3067%（（11099-10154）/10154×100），依此类推。

提示：LEAD 函数与 LAG 函数的使用方法类似，不过它的返回结果是当前行之后的第 N 行数据。

3. 复合增长率

复合增长率是第 N 期的数据除以第一期的基准数据，然后开 N - 1 次方再减去 1 得到的结果。

假如 2018 年的产品销售额为 10000，2019 年的产品销售额为 12500，2020 年的产品销售额为 15000。那么这两年的复合增长率的计算方式如下：

以年度为单位计算的复合增长率被称为年均复合增长率，以月度为单位计算的复合增长率被称为月均复合增长率。

以下查询统计了自 2018 年 1 月以来不同产品的月均销售额复合增长率：

WITH s (product,ym,amount,first_amount,num) AS (
 SELECT m.product, m.ym, m.amount,
 FIRST_VALUE(m.amount) OVER (PARTITION BY m.product ORDER BY m.ym),
 ROW_NUMBER() OVER (PARTITION BY m.product ORDER BY m.ym)
 FROM sales_monthly m
 
SELECT product AS  产品 , ym AS  年月 ,amount AS  销售额 ,
 (POWER( amount/first_amount, 1.0/NULLIF(num-1,0)) -1)*100 AS  月均复合增长率 (%) 
FROM s
ORDER BY product, ym

首先定义了一个通用表表达式，其中 FIRST_VALUE（amount）返回了第一期（201801）的销售额，ROW_NUMBER 函数返回了每一期的编号。

主查询中的 POWER 函数用于执行开方运算，NULLIF 函数用于处理第一期数据的除零错误，常量 1.0 用于避免由整数除法所导致的精度丢失问题。

该查询返回的结果如下：

2018 年 1 月是第一期，因此其产品月均销售额复合增长率为空。

“桔子”2018 年 2 月的月均销售额复合增长率等于它的环比增长率，2018 年 3 月的月均销售额复合增长率等于 0.4471%，依此类推。

4. 不同产品最高和最低销售额

以下语句统计了不同产品最低销售额、最高销售额以及第三高销售额所在的月份：

 SELECT product AS  产品 , ym AS  年月 ,amount AS  销售额 ,
 
 FIRST_VALUE(m.ym) OVER (
 PARTITION BY m.product ORDER BY m.amount DESC
 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 ) AS  最高销售额月份 ,
 
 LAST_VALUE(m.ym) OVER (
 PARTITION BY m.product ORDER BY m.amount DESC
 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 ) AS  最低销售额月份 ,
 
 NTH_VALUE(m.ym,3) OVER (
 PARTITION BY m.product ORDER BY m.amount DESC
 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 ) AS  第三高销售额月份 
 
 FROM sales_monthly m
 ORDER BY product, ym;

三个窗口函数的 OVER 子句相同，PARTITION BY 选项表示按照产品进行分区，ORDER BY 选项表示按照销售额从高到低排序。

以上三个函数的默认窗口都是从分区的第一行到当前行，因此我们将窗口扩展到了整个分区。

该查询返回的结果如下：

“桔子”的最高销售额出现在 2019 年 6 月，最低销售额出现在 2018 年 1 月，第三高销售额出现在 2019 年 4 月。

示例表和脚本

--  创建销量表 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);

关于“SQL 窗口函数之排名窗口函数怎么使用”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识，可以关注丸趣 TV 行业资讯频道，丸趣 TV 小编每天都会为大家更新不同的知识点。

正文完