SQL窗口函数详解之排名窗口函数的使用-SQL-PHP中文网

首页

数据库

SQL

SQL窗口函数详解之排名窗口函数的使用

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Sep 08, 2022 pm 05:44 PM

sql

本篇文章给大家带来了关于SQL server的相关知识，其中主要介绍了SQL Server主键约束(PRIMARY KEY),主键是唯一标识表中每一行的一列或一组列,文章围绕主题展开详情，下面一起来看一下，希望对大家有帮助。

SQL窗口函数详解之排名窗口函数的使用

推荐学习：《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 (&#39;苹果&#39;,&#39;201801&#39;,10159.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201802&#39;,10211.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201803&#39;,10247.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201804&#39;,10376.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201805&#39;,10400.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201806&#39;,10565.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201807&#39;,10613.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201808&#39;,10696.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201809&#39;,10751.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201810&#39;,10842.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201811&#39;,10900.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201812&#39;,10972.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201901&#39;,11155.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201902&#39;,11202.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201903&#39;,11260.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201904&#39;,11341.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201905&#39;,11459.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201906&#39;,11560.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201801&#39;,10138.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201802&#39;,10194.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201803&#39;,10328.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201804&#39;,10322.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201805&#39;,10481.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201806&#39;,10502.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201807&#39;,10589.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201808&#39;,10681.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201809&#39;,10798.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201810&#39;,10829.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201811&#39;,10913.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201812&#39;,11056.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201901&#39;,11161.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201902&#39;,11173.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201903&#39;,11288.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201904&#39;,11408.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201905&#39;,11469.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201906&#39;,11528.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201801&#39;,10154.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201802&#39;,10183.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201803&#39;,10245.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201804&#39;,10325.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201805&#39;,10465.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201806&#39;,10505.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201807&#39;,10578.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201808&#39;,10680.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201809&#39;,10788.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201810&#39;,10838.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201811&#39;,10942.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201812&#39;,10988.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201901&#39;,11099.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201902&#39;,11181.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201903&#39;,11302.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201904&#39;,11327.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201905&#39;,11423.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201906&#39;,11524.00);

推荐学习：《SQL教程》

以上是SQL窗口函数详解之排名窗口函数的使用的详细内容。更多信息请关注PHP中文网其他相关文章！

声明

本文转载于：脚本之家。如有侵权，请联系admin@php.cn删除

SQL与MySQL：澄清两者之间的关系Apr 24, 2025 am 12:02 AM

SQL是一种用于管理关系数据库的标准语言，而MySQL是一个使用SQL的数据库管理系统。SQL定义了与数据库交互的方式，包括CRUD操作，而MySQL实现了SQL标准并提供了额外的功能，如存储过程和触发器。

SQL的重要性：数字时代的数据管理Apr 23, 2025 am 12:01 AM

SQL在数据管理中的作用是通过查询、插入、更新和删除操作来高效处理和分析数据。1.SQL是一种声明式语言，允许用户以结构化方式与数据库对话。2.使用示例包括基本的SELECT查询和高级的JOIN操作。3.常见错误如忘记WHERE子句或误用JOIN，可通过EXPLAIN命令调试。4.性能优化涉及使用索引和遵循最佳实践如代码可读性和可维护性。

SQL入门：基本概念和技能Apr 22, 2025 am 12:01 AM

SQL是一种用于管理和操作关系数据库的语言。1.创建表：使用CREATETABLE语句，如CREATETABLEusers(idINTPRIMARYKEY,nameVARCHAR(100),emailVARCHAR(100));2.插入、更新、删除数据：使用INSERTINTO、UPDATE、DELETE语句，如INSERTINTOusers(id,name,email)VALUES(1,'JohnDoe','john@example.com');3.查询数据：使用SELECT语句，如SELEC

SQL：语言，MySQL：数据库管理系统Apr 21, 2025 am 12:05 AM

SQL和MySQL的关系是：SQL是用于管理和操作数据库的语言，而MySQL是支持SQL的数据库管理系统。1.SQL允许进行数据的CRUD操作和高级查询。2.MySQL提供索引、事务和锁机制来提升性能和安全性。3.优化MySQL性能需关注查询优化、数据库设计和监控维护。

SQL的作用：管理和操纵数据Apr 20, 2025 am 12:02 AM

SQL用于数据库管理和数据操作，核心功能包括CRUD操作、复杂查询和优化策略。1)CRUD操作：使用INSERTINTO创建数据，SELECT读取数据，UPDATE更新数据，DELETE删除数据。2)复杂查询：通过GROUPBY和HAVING子句处理复杂数据。3)优化策略：使用索引、避免全表扫描、优化JOIN操作和分页查询来提升性能。

SQL：对数据管理的初学者友好方法？Apr 19, 2025 am 12:12 AM

SQL适合初学者，因为它语法简单，功能强大，广泛应用于数据库系统。1.SQL用于管理关系数据库，通过表格组织数据。2.基本操作包括创建、插入、查询、更新和删除数据。3.高级用法如JOIN、子查询和窗口函数增强数据分析能力。4.常见错误包括语法、逻辑和性能问题，可通过检查和优化解决。5.性能优化建议包括使用索引、避免SELECT*、使用EXPLAIN分析查询、规范化数据库和提高代码可读性。

SQL在行动中：现实世界中的示例和用例Apr 18, 2025 am 12:13 AM

SQL在实际应用中主要用于数据查询与分析、数据整合与报告、数据清洗与预处理、高级用法与优化以及处理复杂查询和避免常见错误。1)数据查询与分析可用于找出销售量最高的产品；2)数据整合与报告通过JOIN操作生成客户购买报告；3)数据清洗与预处理可删除异常年龄记录；4)高级用法与优化包括使用窗口函数和创建索引；5)处理复杂查询可使用CTE和JOIN，避免常见错误如SQL注入。