SQL窗口函数详解之排名窗口函数的使用
本篇文章给大家带来了关于SQL server的相关知识，其中主要介绍了SQL Server主键约束(PRIMARY KEY),主键是唯一标识表中每一行的一列或一组列,文章围绕主题展开详情，下面一起来看一下，希望对大家有帮助。
推荐学习：《SQL教程》
关于窗口函数的基础，请看文章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教程》
以上是SQL窗口函数详解之排名窗口函数的使用的详细内容。更多信息请关注PHP中文网其他相关文章！
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
热门话题
Hibernate 框架中 HQL 和 SQL 的区别是什么？
Apr 17, 2024 pm 02:57 PM
HQL和SQL在Hibernate框架中进行比较：HQL（1.面向对象语法，2.数据库无关的查询，3.类型安全），而SQL直接操作数据库（1.与数据库无关的标准，2.可执行复杂查询和数据操作）。
Oracle SQL中除法运算的用法
Mar 10, 2024 pm 03:06 PM
《OracleSQL中除法运算的用法》在OracleSQL中，除法运算是常见的数学运算之一。在数据查询和处理过程中，除法运算可以帮助我们计算字段之间的比例或者得出特定数值的逻辑关系。本文将介绍OracleSQL中除法运算的用法，并提供具体的代码示例。一、OracleSQL中除法运算的两种方式在OracleSQL中，除法运算可以使用两种不同的方式进行
Oracle和DB2的SQL语法比较与区别
Mar 11, 2024 pm 12:09 PM
Oracle和DB2是两个常用的关系型数据库管理系统，它们都有自己独特的SQL语法和特点。本文将针对Oracle和DB2的SQL语法进行比较与区别，并提供具体的代码示例。数据库连接在Oracle中，使用以下语句连接数据库：CONNECTusername/password@database而在DB2中，连接数据库的语句如下：CONNECTTOdataba
详解MyBatis动态SQL标签中的Set标签功能
Feb 26, 2024 pm 07:48 PM
MyBatis动态SQL标签解读：Set标签用法详解MyBatis是一个优秀的持久层框架，它提供了丰富的动态SQL标签，可以灵活地构建数据库操作语句。其中，Set标签是用于生成UPDATE语句中SET子句的标签，在更新操作中非常常用。本文将详细解读MyBatis中Set标签的用法，以及通过具体的代码示例来演示其功能。什么是Set标签Set标签用于MyBati
SQL中的identity属性是什么意思？
Feb 19, 2024 am 11:24 AM
SQL中的Identity是什么，需要具体代码示例在SQL中，Identity是一种用于生成自增数字的特殊数据类型，它常用于唯一标识表中的每一行数据。Identity列通常与主键列配合使用，可以确保每条记录都有一个独一无二的标识符。本文将详细介绍Identity的使用方式以及一些实际的代码示例。Identity的基本使用方式在创建表时，可以使用Identit
Springboot+Mybatis-plus不使用SQL语句进行多表添加怎么实现
Jun 02, 2023 am 11:07 AM
在Springboot+Mybatis-plus不使用SQL语句进行多表添加操作我所遇到的问题准备工作在测试环境下模拟思维分解一下:创建出一个带有参数的BrandDTO对象模拟对后台传递参数我所遇到的问题我们都知道,在我们使用Mybatis-plus中进行多表操作是极其困难的,如果你不使用Mybatis-plus-join这一类的工具,你只能去配置对应的Mapper.xml文件,配置又臭又长的ResultMap,然后再去写对应的sql语句,这种方法虽然看上去很麻烦,但具有很高的灵活性,可以让我们
如何使用SQL语句在MySQL中进行数据聚合和统计？
Dec 17, 2023 am 08:41 AM
如何使用SQL语句在MySQL中进行数据聚合和统计？在进行数据分析和统计时，数据聚合和统计是非常重要的步骤。MySQL作为一个功能强大的关系型数据库管理系统，提供了丰富的聚合和统计函数，可以很方便地进行数据聚合和统计操作。本文将介绍使用SQL语句在MySQL中进行数据聚合和统计的方法，并提供具体的代码示例。一、使用COUNT函数进行计数COUNT函数是最常用
SQL出现5120错误怎么解决
Mar 06, 2024 pm 04:33 PM
解决办法：1、检查登录用户是否具有足够的权限来访问或操作该数据库，确保该用户具有正确的权限；2、检查SQL Server服务的帐户是否具有访问指定文件或文件夹的权限，确保该帐户具有足够的权限来读取和写入该文件或文件夹；3、检查指定的数据库文件是否已被其他进程打开或锁定，尝试关闭或释放该文件，并重新运行查询；4、尝试以管理员身份运行Management Studio等等。
