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

WBOY

发布时间：2022-09-08 17:44:47

2299人浏览过

来源于脚本之家

转载

本篇文章给大家带来了关于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选项表示按照月份进行排序。

AI Web Designer

AI网页设计师，快速生成个性化的网站设计

下载

当前月份的销售额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敏感数据保护_数据脱敏与加密实践

SQL如何对字符串进行按位或聚合_BIT_OR和BIT_AND位运算

SQL表结构怎么设计_表设计基本规范

SQL报表查询内存不足_内存参数调优

SQL如何统计最大连续登录次数_差值法分组与COUNT累计

本站声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

上一篇：SQL查询给表起别名要点（总结分享）下一篇：SqlServer创建自动收缩事务日志任务的图文详解

作者最新文章

微信朋友圈能定时发送吗微信朋友圈定时发送功能开启方法

2026-01-09 08:15

微信朋友圈草稿箱怎么用微信朋友圈定时发送隐藏技巧

2026-01-10 08:41

微信朋友圈定时发送是真的吗微信朋友圈预约发布实现方法

2026-01-15 10:19

微信定时发朋友圈怎么弄微信朋友圈自动推送设置流程

2026-01-21 04:27

微信如何定时发朋友圈微信朋友圈自动定时发送设置步骤【汇总】

2026-01-23 10:22

微信怎么设置自动发朋友圈微信朋友圈定时托管操作详解

2026-02-03 04:49

2026微信定时发朋友圈教程微信朋友圈延迟发送设置技巧

2026-02-04 08:23

mysql如何获取系统时间_mysql now与sysdate区别

2026-03-02 11:02

mysql如何进行内连接_mysql inner join匹配逻辑

2026-03-06 07:33

mysql如何清空表数据_mysql truncate table性能优势

2026-03-06 10:03

热门AI工具

DeepSeek

幻方量化公司旗下的开源大模型平台

AI编程开发 AI聊天问答

豆包大模型

字节跳动自主研发的一系列大型语言模型

AI编程开发 AI大模型

WorkBuddy

腾讯云推出的AI原生桌面智能体工作台

AI办公学习 Agent智能体

腾讯元宝

腾讯混元平台推出的AI助手

文档处理 Excel 表格

文心一言

文心一言是百度开发的AI聊天机器人，通过对话可以生成各种形式的内容。

AI编程开发 AI文本写作

讯飞写作

基于讯飞星火大模型的AI写作工具，可以快速生成新闻稿件、品宣文案、工作总结、心得体会等各种文文稿

AI文本写作中文写作

即梦AI

一站式AI创作平台，免费AI图片和视频生成。

图片拼接图画生成

ChatGPT

最最强大的AI聊天机器人程序，ChatGPT不单是聊天机器人，还能进行撰写邮件、视频脚本、文案、翻译、代码等任务。

AI编程开发 AI文本写作

智谱清言 - 免费全能的AI助手

AI编程开发 Agent智能体

相关专题

TypeScript类型系统进阶与大型前端项目实践

本专题围绕 TypeScript 在大型前端项目中的应用展开，深入讲解类型系统设计与工程化开发方法。内容包括泛型与高级类型、类型推断机制、声明文件编写、模块化结构设计以及代码规范管理。通过真实项目案例分析，帮助开发者构建类型安全、结构清晰、易维护的前端工程体系，提高团队协作效率与代码质量。

2026.03.13

Python异步编程与Asyncio高并发应用实践

本专题围绕 Python 异步编程模型展开，深入讲解 Asyncio 框架的核心原理与应用实践。内容包括事件循环机制、协程任务调度、异步 IO 处理以及并发任务管理策略。通过构建高并发网络请求与异步数据处理案例，帮助开发者掌握 Python 在高并发场景中的高效开发方法，并提升系统资源利用率与整体运行性能。

2026.03.12

C# ASP.NET Core微服务架构与API网关实践

本专题围绕 C# 在现代后端架构中的微服务实践展开，系统讲解基于 ASP.NET Core 构建可扩展服务体系的核心方法。内容涵盖服务拆分策略、RESTful API 设计、服务间通信、API 网关统一入口管理以及服务治理机制。通过真实项目案例，帮助开发者掌握构建高可用微服务系统的关键技术，提高系统的可扩展性与维护效率。

178

2026.03.11

Go高并发任务调度与Goroutine池化实践

本专题围绕 Go 语言在高并发任务处理场景中的实践展开，系统讲解 Goroutine 调度模型、Channel 通信机制以及并发控制策略。内容包括任务队列设计、Goroutine 池化管理、资源限制控制以及并发任务的性能优化方法。通过实际案例演示，帮助开发者构建稳定高效的 Go 并发任务处理系统，提高系统在高负载环境下的处理能力与稳定性。

2026.03.10

Kotlin Android模块化架构与组件化开发实践

本专题围绕 Kotlin 在 Android 应用开发中的架构实践展开，重点讲解模块化设计与组件化开发的实现思路。内容包括项目模块拆分策略、公共组件封装、依赖管理优化、路由通信机制以及大型项目的工程化管理方法。通过真实项目案例分析，帮助开发者构建结构清晰、易扩展且维护成本低的 Android 应用架构体系，提升团队协作效率与项目迭代速度。

2026.03.09

JavaScript浏览器渲染机制与前端性能优化实践

本专题围绕 JavaScript 在浏览器中的执行与渲染机制展开，系统讲解 DOM 构建、CSSOM 解析、重排与重绘原理，以及关键渲染路径优化方法。内容涵盖事件循环机制、异步任务调度、资源加载优化、代码拆分与懒加载等性能优化策略。通过真实前端项目案例，帮助开发者理解浏览器底层工作原理，并掌握提升网页加载速度与交互体验的实用技巧。

102

2026.03.06

Rust内存安全机制与所有权模型深度实践

本专题围绕 Rust 语言核心特性展开，深入讲解所有权机制、借用规则、生命周期管理以及智能指针等关键概念。通过系统级开发案例，分析内存安全保障原理与零成本抽象优势，并结合并发场景讲解 Send 与 Sync 特性实现机制。帮助开发者真正理解 Rust 的设计哲学，掌握在高性能与安全性并重场景中的工程实践能力。

227

2026.03.05

PHP高性能API设计与Laravel服务架构实践

本专题围绕 PHP 在现代 Web 后端开发中的高性能实践展开，重点讲解基于 Laravel 框架构建可扩展 API 服务的核心方法。内容涵盖路由与中间件机制、服务容器与依赖注入、接口版本管理、缓存策略设计以及队列异步处理方案。同时结合高并发场景，深入分析性能瓶颈定位与优化思路，帮助开发者构建稳定、高效、易维护的 PHP 后端服务体系。

532

2026.03.04