在日常SQL开发中窗口函数是处理分组排名、连续统计、跨行计算等复杂业务场景的核心工具相比普通GROUP BY分组聚合窗口函数不会合并行能保留原始数据细节同时实现分组计算是数据分析师、后端开发必备技能。本文从基础语法、核心分类、实战案例、表格速查四个维度全面讲解SQL窗口函数附带可直接运行的代码示例新手也能快速上手。一、窗口函数基础认知1. 什么是窗口函数窗口函数Window Function是对数据窗口一组相关行 执行计算的函数它为每一行返回一个计算结果不会减少原数据行数区别于聚合函数GROUP BY会合并分组行。简单理解窗口函数分组PARTITION BY排序ORDER BY范围ROWS/RANGE计算逻辑。2. 基础语法sql窗口函数() OVER (PARTITION BY 分组字段ORDER BY 排序字段 ASC/DESCROWS/RANGE 窗口范围) AS 别名- PARTITION BY可选按指定字段分组类似GROUP BY但不合并行- ORDER BY可选组内排序决定计算的顺序- ROWS/RANGE可选定义窗口的行范围默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 从分组第一行到当前行二、窗口函数核心分类实战代码本文以学生成绩表score 为例表结构如下id name class subject score1 张三 1班 数学 952 李四 1班 数学 883 王五 1班 数学 954 赵六 2班 数学 925 孙七 2班 数学 856 周八 2班 数学 92分类1排序类窗口函数最常用用于分组内排名、行号计算是面试和业务中高频场景。1. ROW_NUMBER()连续不重复排名功能组内连续排序无并列、无跳跃即使分数相同排名也不同。sql-- 按班级分组数学成绩降序排名SELECTname,class,score,ROW_NUMBER() OVER(PARTITION BY class ORDER BY score DESC) AS row_numFROM score WHERE subject 数学;结果1班张三(1)、王五(2)、李四(3)2班赵六(1)、周八(2)、孙七(3)2. RANK()跳跃排名功能分数相同排名相同下一名排名跳跃并列第一后是第三。sqlSELECTname,class,score,RANK() OVER(PARTITION BY class ORDER BY score DESC) AS rank_numFROM score WHERE subject 数学;结果1班张三(1)、王五(1)、李四(3)2班赵六(1)、周八(1)、孙七(3)3. DENSE_RANK()连续并列排名功能分数相同排名相同下一名排名连续并列第一后是第二。sqlSELECTname,class,score,DENSE_RANK() OVER(PARTITION BY class ORDER BY score DESC) AS dense_rank_numFROM score WHERE subject 数学;结果1班张三(1)、王五(1)、李四(2)2班赵六(1)、周八(1)、孙七(2)分类2聚合类窗口函数支持SUM/AVG/COUNT/MAX/MIN等聚合函数实现分组内累计计算、滑动统计。1. 累计求和SUMsql-- 按班级分组成绩降序累计分数统计SELECTname,class,score,SUM(score) OVER(PARTITION BY class ORDER BY score DESC) AS sum_scoreFROM score WHERE subject 数学;效果1班第一行累计95第二行累计9595190第三行累计19088278。2. 分组平均值/最大值/计数sqlSELECTname,class,score,AVG(score) OVER(PARTITION BY class) AS avg_score, -- 班级平均分MAX(score) OVER(PARTITION BY class) AS max_score, -- 班级最高分COUNT(*) OVER(PARTITION BY class) AS stu_count -- 班级人数FROM score WHERE subject 数学;分类3取值类窗口函数用于获取分组内指定行的数据处理跨行引用场景。1. LAG()取上一行数据功能获取当前行上方第N行的字段值常用于同比、环比计算。sql-- 按班级排序获取上一名学生的成绩SELECTname,class,score,LAG(score,1,0) OVER(PARTITION BY class ORDER BY score DESC) AS last_scoreFROM score WHERE subject 数学;- 参数1要取值的字段- 参数2向上取第几行默认1- 参数3无数据时的默认值默认NULL2. LEAD()取下一行数据功能获取当前行下方第N行的字段值与LAG相反。sqlSELECTname,class,score,LEAD(score,1,0) OVER(PARTITION BY class ORDER BY score DESC) AS next_scoreFROM score WHERE subject 数学;3. FIRST_VALUE()/LAST_VALUE()功能获取分组内第一行/最后一行的字段值。sqlSELECTname,class,score,FIRST_VALUE(score) OVER(PARTITION BY class ORDER BY score DESC) AS first_score, -- 班级最高分LAST_VALUE(score) OVER(PARTITION BY class ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_score -- 班级最低分FROM score WHERE subject 数学;三、窗口函数核心知识点表格速查为方便快速记忆整理窗口函数核心功能、区别、使用场景表格建议收藏函数类型 函数名称 核心功能 关键区别 典型使用场景排序类 ROW_NUMBER() 组内连续排名无并列、无跳跃 排名唯一不考虑重复值 分页查询、取分组前N条排序类 RANK() 组内并列排名下一名排名跳跃 并列后排名跳跃1,1,3 竞赛排名、成绩并列展示排序类 DENSE_RANK() 组内并列排名下一名排名连续 并列后排名连续1,1,2 奖项评选、等级划分聚合类 SUM()/AVG() 分组内累计求和/平均值 支持滑动窗口、累计计算 累计销售额、班级平均分聚合类 MAX()/MIN() 分组内最大值/最小值 不合并行保留原始数据 分组极值查询取值类 LAG() 获取当前行上一行数据 向上取值默认取前1行 环比、同比、跨行对比取值类 LEAD() 获取当前行下一行数据 向下取值默认取后1行 预测数据、后续值对比取值类 FIRST_VALUE() 获取分组第一行数据 取分组开头值 分组第一名、初始值取值类 LAST_VALUE() 获取分组最后一行数据 需指定窗口范围取分组结尾值 分组最后一名、结束值四、窗口函数使用注意事项1. 窗口函数不支持WHERE子句过滤如果需要过滤窗口函数计算结果需用子查询/CTE包裹。sql-- 错误示例SELECT name,ROW_NUMBER() OVER(PARTITION BY class ORDER BY score DESC) AS rn FROM score WHERE rn1;-- 正确示例SELECT * FROM (SELECT name,ROW_NUMBER() OVER(PARTITION BY class ORDER BY score DESC) AS rn FROM score) t WHERE rn1;2. PARTITION BY可省略省略后按全表数据作为一个窗口计算。3. ROWS与RANGE的区别ROWS按物理行定义窗口RANGE按值范围定义窗口数值计算常用ROWS。4. 支持数据库MySQL 8.0、PostgreSQL、Oracle、SQL Server均支持低版本MySQL需升级。五、总结窗口函数是SQL进阶的核心技能排序类解决排名问题聚合类解决累计统计问题取值类解决跨行数据引用问题熟练掌握后能轻松处理90%的复杂数据计算场景。建议大家把文中代码复制到本地数据库运行结合表格速查记忆快速吃透窗口函数本文原创码字不易欢迎点赞、收藏、关注后续会持续更新SQL实战、数据分析相关干货如果有疑问欢迎在评论区交流~
SQL窗口函数超全总结：语法+场景+代码示例，一篇吃透！
在日常SQL开发中窗口函数是处理分组排名、连续统计、跨行计算等复杂业务场景的核心工具相比普通GROUP BY分组聚合窗口函数不会合并行能保留原始数据细节同时实现分组计算是数据分析师、后端开发必备技能。本文从基础语法、核心分类、实战案例、表格速查四个维度全面讲解SQL窗口函数附带可直接运行的代码示例新手也能快速上手。一、窗口函数基础认知1. 什么是窗口函数窗口函数Window Function是对数据窗口一组相关行 执行计算的函数它为每一行返回一个计算结果不会减少原数据行数区别于聚合函数GROUP BY会合并分组行。简单理解窗口函数分组PARTITION BY排序ORDER BY范围ROWS/RANGE计算逻辑。2. 基础语法sql窗口函数() OVER (PARTITION BY 分组字段ORDER BY 排序字段 ASC/DESCROWS/RANGE 窗口范围) AS 别名- PARTITION BY可选按指定字段分组类似GROUP BY但不合并行- ORDER BY可选组内排序决定计算的顺序- ROWS/RANGE可选定义窗口的行范围默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 从分组第一行到当前行二、窗口函数核心分类实战代码本文以学生成绩表score 为例表结构如下id name class subject score1 张三 1班 数学 952 李四 1班 数学 883 王五 1班 数学 954 赵六 2班 数学 925 孙七 2班 数学 856 周八 2班 数学 92分类1排序类窗口函数最常用用于分组内排名、行号计算是面试和业务中高频场景。1. ROW_NUMBER()连续不重复排名功能组内连续排序无并列、无跳跃即使分数相同排名也不同。sql-- 按班级分组数学成绩降序排名SELECTname,class,score,ROW_NUMBER() OVER(PARTITION BY class ORDER BY score DESC) AS row_numFROM score WHERE subject 数学;结果1班张三(1)、王五(2)、李四(3)2班赵六(1)、周八(2)、孙七(3)2. RANK()跳跃排名功能分数相同排名相同下一名排名跳跃并列第一后是第三。sqlSELECTname,class,score,RANK() OVER(PARTITION BY class ORDER BY score DESC) AS rank_numFROM score WHERE subject 数学;结果1班张三(1)、王五(1)、李四(3)2班赵六(1)、周八(1)、孙七(3)3. DENSE_RANK()连续并列排名功能分数相同排名相同下一名排名连续并列第一后是第二。sqlSELECTname,class,score,DENSE_RANK() OVER(PARTITION BY class ORDER BY score DESC) AS dense_rank_numFROM score WHERE subject 数学;结果1班张三(1)、王五(1)、李四(2)2班赵六(1)、周八(1)、孙七(2)分类2聚合类窗口函数支持SUM/AVG/COUNT/MAX/MIN等聚合函数实现分组内累计计算、滑动统计。1. 累计求和SUMsql-- 按班级分组成绩降序累计分数统计SELECTname,class,score,SUM(score) OVER(PARTITION BY class ORDER BY score DESC) AS sum_scoreFROM score WHERE subject 数学;效果1班第一行累计95第二行累计9595190第三行累计19088278。2. 分组平均值/最大值/计数sqlSELECTname,class,score,AVG(score) OVER(PARTITION BY class) AS avg_score, -- 班级平均分MAX(score) OVER(PARTITION BY class) AS max_score, -- 班级最高分COUNT(*) OVER(PARTITION BY class) AS stu_count -- 班级人数FROM score WHERE subject 数学;分类3取值类窗口函数用于获取分组内指定行的数据处理跨行引用场景。1. LAG()取上一行数据功能获取当前行上方第N行的字段值常用于同比、环比计算。sql-- 按班级排序获取上一名学生的成绩SELECTname,class,score,LAG(score,1,0) OVER(PARTITION BY class ORDER BY score DESC) AS last_scoreFROM score WHERE subject 数学;- 参数1要取值的字段- 参数2向上取第几行默认1- 参数3无数据时的默认值默认NULL2. LEAD()取下一行数据功能获取当前行下方第N行的字段值与LAG相反。sqlSELECTname,class,score,LEAD(score,1,0) OVER(PARTITION BY class ORDER BY score DESC) AS next_scoreFROM score WHERE subject 数学;3. FIRST_VALUE()/LAST_VALUE()功能获取分组内第一行/最后一行的字段值。sqlSELECTname,class,score,FIRST_VALUE(score) OVER(PARTITION BY class ORDER BY score DESC) AS first_score, -- 班级最高分LAST_VALUE(score) OVER(PARTITION BY class ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_score -- 班级最低分FROM score WHERE subject 数学;三、窗口函数核心知识点表格速查为方便快速记忆整理窗口函数核心功能、区别、使用场景表格建议收藏函数类型 函数名称 核心功能 关键区别 典型使用场景排序类 ROW_NUMBER() 组内连续排名无并列、无跳跃 排名唯一不考虑重复值 分页查询、取分组前N条排序类 RANK() 组内并列排名下一名排名跳跃 并列后排名跳跃1,1,3 竞赛排名、成绩并列展示排序类 DENSE_RANK() 组内并列排名下一名排名连续 并列后排名连续1,1,2 奖项评选、等级划分聚合类 SUM()/AVG() 分组内累计求和/平均值 支持滑动窗口、累计计算 累计销售额、班级平均分聚合类 MAX()/MIN() 分组内最大值/最小值 不合并行保留原始数据 分组极值查询取值类 LAG() 获取当前行上一行数据 向上取值默认取前1行 环比、同比、跨行对比取值类 LEAD() 获取当前行下一行数据 向下取值默认取后1行 预测数据、后续值对比取值类 FIRST_VALUE() 获取分组第一行数据 取分组开头值 分组第一名、初始值取值类 LAST_VALUE() 获取分组最后一行数据 需指定窗口范围取分组结尾值 分组最后一名、结束值四、窗口函数使用注意事项1. 窗口函数不支持WHERE子句过滤如果需要过滤窗口函数计算结果需用子查询/CTE包裹。sql-- 错误示例SELECT name,ROW_NUMBER() OVER(PARTITION BY class ORDER BY score DESC) AS rn FROM score WHERE rn1;-- 正确示例SELECT * FROM (SELECT name,ROW_NUMBER() OVER(PARTITION BY class ORDER BY score DESC) AS rn FROM score) t WHERE rn1;2. PARTITION BY可省略省略后按全表数据作为一个窗口计算。3. ROWS与RANGE的区别ROWS按物理行定义窗口RANGE按值范围定义窗口数值计算常用ROWS。4. 支持数据库MySQL 8.0、PostgreSQL、Oracle、SQL Server均支持低版本MySQL需升级。五、总结窗口函数是SQL进阶的核心技能排序类解决排名问题聚合类解决累计统计问题取值类解决跨行数据引用问题熟练掌握后能轻松处理90%的复杂数据计算场景。建议大家把文中代码复制到本地数据库运行结合表格速查记忆快速吃透窗口函数本文原创码字不易欢迎点赞、收藏、关注后续会持续更新SQL实战、数据分析相关干货如果有疑问欢迎在评论区交流~
