窗口函数Window Function是 SQL 中非常强大的一类函数它能在不改变原始行数的前提下对每一行计算一个基于**行集合窗口**的聚合值或排名值。它解决了传统GROUP BY丢失细节行的问题。一、窗口函数的定义与核心概念窗口函数的基本语法函数名(列)OVER([PARTITIONBY分组列][ORDERBY排序列[ASC|DESC]][frame_clause])函数可以是聚合函数SUM,AVG,COUNT,MAX,MIN或专用窗口函数ROW_NUMBER,RANK,DENSE_RANK,LEAD,LAG,NTILE,FIRST_VALUE,LAST_VALUE等。OVER关键字定义窗口。PARTITION BY将数据分成多个“分区”窗口函数在每个分区内独立计算。相当于GROUP BY但不合并行。ORDER BY指定分区内的排序顺序影响排名类函数及窗口框架frame。frame_clause定义窗口的行范围如ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING用于滑动窗口聚合。二、窗口函数 vs GROUP BY特性GROUP BY窗口函数输出行数每个分组一行每一行都保留可访问明细否是同时可查看聚合值典型场景报表汇总排名、累计值、同比环比、移动平均三、常用窗口函数分类1. 排名函数ROW_NUMBER()为每行分配唯一的连续整数从1开始排序相同时编号随机决定。RANK()相同值获得相同排名下一个排名跳空缺位1,2,2,4。DENSE_RANK()相同值获得相同排名下一个排名连续1,2,2,3。示例SELECTname,department,salary,ROW_NUMBER()OVER(PARTITIONBYdepartmentORDERBYsalaryDESC)ASrn,RANK()OVER(PARTITIONBYdepartmentORDERBYsalaryDESC)ASrk,DENSE_RANK()OVER(PARTITIONBYdepartmentORDERBYsalaryDESC)ASdrFROMemployees;2. 取值函数偏移分析LAG(列, offset, default)返回当前行之前第 offset 行的值。LEAD(列, offset, default)返回当前行之后第 offset 行的值。FIRST_VALUE(列)返回窗口内第一行的值。LAST_VALUE(列)返回窗口内最后一行的值需注意 frame 默认行为。示例计算同比销售额SELECTyear,month,sales,LAG(sales,1)OVER(ORDERBYyear,month)ASprev_month_sales,sales-LAG(sales,1)OVER(ORDERBYyear,month)ASmom_growthFROMsales_table;3. 聚合函数作为窗口函数SUM(列) OVER(…)累计和。AVG(列) OVER(…)移动平均。COUNT(列) OVER(…)分组计数。示例计算每个部门的累计工资按入职时间SELECTname,department,salary,hire_date,SUM(salary)OVER(PARTITIONBYdepartmentORDERBYhire_date)ASrunning_totalFROMemployees;4. 分布与分桶函数NTILE(n)将分区内的行尽可能均匀地分成 n 个桶返回桶编号 1~n。PERCENT_RANK()相对排名(rank-1)/(total rows - 1)。CUME_DIST()累积分布小于等于当前行的行数占比。示例将员工按薪资分三个等级SELECTname,salary,NTILE(3)OVER(ORDERBYsalaryDESC)ASsalary_groupFROMemployees;四、窗口框架Frame详解框架决定了相对于当前行的行范围。常用写法ROWSBETWEEN[UNBOUNDEDPRECEDING|NPRECEDING|CURRENTROW]AND[UNBOUNDEDFOLLOWING|NFOLLOWING|CURRENTROW]UNBOUNDED PRECEDING从分区第一行开始。UNBOUNDED FOLLOWING到分区最后一行结束。N PRECEDING当前行之前的 N 行。N FOLLOWING当前行之后的 N 行。CURRENT ROW当前行。默认框架当有ORDER BY时默认框架是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW包含当前行之前所有行相同排序值的行视为同一范围。为了明确滑动窗口通常显式使用ROWS。示例计算当前行及前后各一行的平均薪资SELECTname,salary,AVG(salary)OVER(ORDERBYsalaryROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASmoving_avgFROMemployees;五、实际应用场景分组排名各部门内按销售额排名。同比环比用LAG计算上期值。累计统计累计销售额、累计用户数。移动平均平滑时间序列数据。去重保留ROW_NUMBER()为每组保留一条记录。填充缺失值LAST_VALUE(column IGNORE NULLS)向前填充。分页查询ROW_NUMBER()结合WHERE rn BETWEEN ...实现高效分页。六、注意事项与性能窗口函数不能出现在 WHERE 子句中逻辑顺序原因。可以嵌套子查询再过滤。多数数据库支持窗口函数MySQL 8.0, PostgreSQL, SQL Server, Oracle, Hive, Spark SQL。性能窗口函数通常需要一次排序和分区若数据量大且PARTITION BY列倾斜可能产生性能问题。可考虑提前过滤或索引。内存使用某些实现如 Spark会将分区数据加载到内存需注意内存配置。七、综合示例问题计算每个部门薪资排名前2的员工同时显示部门平均薪资。WITHrankedAS(SELECTname,department,salary,ROW_NUMBER()OVER(PARTITIONBYdepartmentORDERBYsalaryDESC)ASrn,AVG(salary)OVER(PARTITIONBYdepartment)ASdept_avg_salaryFROMemployees)SELECTname,department,salary,dept_avg_salaryFROMrankedWHERErn2;八、小结你需要什么用什么窗口函数排名不跳号ROW_NUMBER()排名允许跳号RANK()排名不跳号但并列连续DENSE_RANK()上一行值LAG()下一行值LEAD()累计和SUM() OVER(ORDER BY ...)移动平均AVG() OVER(ROWS BETWEEN ...)分桶NTILE(n)窗口函数极大地简化了以前需要自连接或复杂子查询才能完成的任务是现代 SQL 开发者必须掌握的核心技能。