使用窗口函数

在复杂数据分析、报表统计、排名计算等场景中经常使用。窗口函数是 SQL 中非常强大的功能可以在不改变行数的情况下对每一行进行计算同时保留原始数据。下面我从核心概念、语法结构、常用函数分类、实战案例、性能优化几个维度来系统说明。 窗口函数是什么窗口函数是在一个“窗口”记录集合上执行计算但不会将多行合并为一行每行都保留自己的身份同时可以获得聚合结果。对比聚合函数特性普通聚合函数窗口函数行数变化多行 → 一行行数不变典型函数SUM(),AVG(),COUNT()ROW_NUMBER(),RANK(),SUM() OVER()使用场景汇总统计排名、累计、移动平均、环比同比-- ❌ 聚合函数多行合并成一行SELECTdepartment,AVG(salary)FROMemployeesGROUPBYdepartment;-- ✅ 窗口函数每行保留同时显示部门平均工资SELECTname,department,salary,AVG(salary)OVER(PARTITIONBYdepartment)ASdept_avgFROMemployees; 语法结构窗口函数OVER([PARTITIONBY分区字段]-- 可选将数据分组[ORDERBY排序字段]-- 可选定义排序影响排名类函数[ROWS/RANGE 窗口范围]-- 可选定义帧滑动窗口)三种核心组成部分窗口函数PARTITION BY分组ORDER BY排序窗口帧滑动范围计算每个部门内按工资排序取前三行/累计到当前 窗口函数分类1️⃣ 排名类函数函数说明示例结果ROW_NUMBER()连续排名不跳号1,2,3,4RANK()跳跃排名相同值同排名1,2,2,4DENSE_RANK()密集排名相同值同排名连续1,2,2,3NTILE(n)分成 n 组1,1,2,2,3,3SELECTname,department,salary,ROW_NUMBER()OVER(ORDERBYsalaryDESC)ASrow_num,RANK()OVER(ORDERBYsalaryDESC)ASrank_num,DENSE_RANK()OVER(ORDERBYsalaryDESC)ASdense_rank_num,NTILE(4)OVER(ORDERBYsalaryDESC)ASquartileFROMemployees;结果示例name | department | salary | row_num | rank_num | dense_rank_num | quartile -------|------------|--------|---------|----------|----------------|---------- 张三 | 技术部 | 50000 | 1 | 1 | 1 | 1 李四 | 技术部 | 45000 | 2 | 2 | 2 | 1 王五 | 销售部 | 45000 | 3 | 2 | 2 | 2 赵六 | 销售部 | 40000 | 4 | 4 | 3 | 22️⃣ 聚合类窗口函数在窗口上使用聚合函数相当于“移动汇总”或“分组汇总但不合并行”。函数说明SUM() OVER()累计求和AVG() OVER()移动平均COUNT() OVER()累计计数MAX() / MIN() OVER()窗口内最大/最小值-- 累计销售额按日期SELECTorder_date,amount,SUM(amount)OVER(ORDERBYorder_date)AScumulative_amount,AVG(amount)OVER(ORDERBYorder_dateROWSBETWEEN6PRECEDINGANDCURRENTROW)ASma_7dFROMorders;3️⃣ 取值类窗口函数获取窗口内指定位置的值。函数说明LAG(column, n)获取当前行前第 n 行的值LEAD(column, n)获取当前行后第 n 行的值FIRST_VALUE(column)窗口内第一行的值LAST_VALUE(column)窗口内最后一行的值NTH_VALUE(column, n)窗口内第 n 行的值-- 环比增长计算SELECTorder_date,amount,LAG(amount,1)OVER(ORDERBYorder_date)ASprev_day_amount,(amount-LAG(amount,1)OVER(ORDERBYorder_date))/LAG(amount,1)OVER(ORDERBYorder_date)*100ASgrowth_rate_pctFROMdaily_sales; 实战案例案例 1员工工资排名部门内-- 需求查询每个部门工资前 3 名的员工WITHranked_employeesAS(SELECTname,department,salary,DENSE_RANK()OVER(PARTITIONBYdepartmentORDERBYsalaryDESC)ASrank_in_deptFROMemployees)SELECT*FROMranked_employeesWHERErank_in_dept3;案例 2用户购买累计金额时间序列-- 需求计算每个用户的累计消费金额SELECTuser_id,order_date,amount,SUM(amount)OVER(PARTITIONBYuser_idORDERBYorder_dateROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)AScumulative_amountFROMorders;案例 37 日移动平均平滑曲线-- 需求计算每日销售额的 7 日移动平均SELECTorder_date,daily_amount,AVG(daily_amount)OVER(ORDERBYorder_dateROWSBETWEEN6PRECEDINGANDCURRENTROW)ASma_7daysFROMdaily_sales;案例 4同比环比计算-- 需求计算每月销售额的环比和同比增长WITHmonthly_salesAS(SELECTDATE_FORMAT(order_date,%Y-%m)ASmonth,SUM(amount)AStotal_amountFROMordersGROUPBYDATE_FORMAT(order_date,%Y-%m))SELECTmonth,total_amount,LAG(total_amount,1)OVER(ORDERBYmonth)ASprev_month_amount,(total_amount-LAG(total_amount,1)OVER(ORDERBYmonth))/LAG(total_amount,1)OVER(ORDERBYmonth)*100ASmom_growth,LAG(total_amount,12)OVER(ORDERBYmonth)ASprev_year_amount,(total_amount-LAG(total_amount,12)OVER(ORDERBYmonth))/LAG(total_amount,12)OVER(ORDERBYmonth)*100ASyoy_growthFROMmonthly_sales;案例 5填充空值使用 LAG/LEAD-- 需求将 NULL 值填充为前一个非 NULL 值SELECTdate,original_value,COALESCE(original_value,LAG(original_value)OVER(ORDERBYdate))ASfilled_valueFROMincomplete_data; 窗口帧Frame详解窗口帧定义了滑动窗口的范围是高级窗口函数的关键。语法{ROWS|RANGE }BETWEENframe_startANDframe_end-- frame_start 可以是UNBOUNDEDPRECEDING-- 从分区第一行开始NPRECEDING-- 当前行前 N 行CURRENTROW-- 当前行-- frame_end 可以是CURRENTROW-- 到当前行NFOLLOWING-- 当前行后 N 行UNBOUNDEDFOLLOWING-- 到分区最后一行常见窗口帧模式模式写法用途累计到当前ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW累计求和移动窗口ROWS BETWEEN 6 PRECEDING AND CURRENT ROW7 日移动平均中心滑动ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING平滑算法到末尾ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING剩余累计-- 累计求和从开始到当前SUM(amount)OVER(ORDERBYdateROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)-- 7日移动平均AVG(amount)OVER(ORDERBYdateROWSBETWEEN6PRECEDINGANDCURRENTROW)-- 未来3天预测基准AVG(amount)OVER(ORDERBYdateROWSBETWEENCURRENTROWAND3FOLLOWING) 不同数据库的差异特性MySQL 8.0PostgreSQLSQL ServerOracle窗口函数支持✅ 完整✅ 完整✅ 完整✅ 完整窗口帧 ROWS/RANGE✅✅✅✅PERCENT_RANK✅✅✅✅窗口函数在 UPDATE 中❌✅✅✅命名窗口❌✅✅✅MySQL 8.0 前的替代方案使用变量-- MySQL 5.7 模拟 ROW_NUMBER()SELECTname,salary,row_num:row_num1ASrow_numFROMemployees,(SELECTrow_num:0)rORDERBYsalaryDESC;⚡ 性能优化建议1. 合理使用 PARTITION BY-- ✅ 好分区字段有索引ROW_NUMBER()OVER(PARTITIONBYdepartment_idORDERBYsalaryDESC)-- ❌ 差分区过多或分区字段无索引可能导致大量排序2. 减少不必要的窗口函数-- ❌ 同一窗口重复计算SELECTROW_NUMBER()OVER(ORDERBYsalary)ASrn1,RANK()OVER(ORDERBYsalary)ASrn2FROMemployees;-- ✅ 使用命名窗口部分数据库支持SELECTROW_NUMBER()OVERwASrn1,RANK()OVERwASrn2FROMemployees WINDOW wAS(ORDERBYsalary);3. 使用索引优化-- 窗口函数需要排序创建相应索引CREATEINDEXidx_dept_salaryONemployees(department_id,salaryDESC);4. 避免大窗口下的 ROWS BETWEEN-- 大表避免使用过大的窗口范围-- ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW 可能很慢 实际业务场景场景窗口函数方案排行榜ROW_NUMBER() OVER (ORDER BY score DESC)分组 Top NROW_NUMBER() OVER (PARTITION BY category ORDER BY score DESC)累计统计SUM(amount) OVER (ORDER BY date)环比/同比LAG(amount) OVER (ORDER BY date)移动平均AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)去重保留最新ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)用户行为序列LAG(page) OVER (PARTITION BY session_id ORDER BY event_time) 总结问题答案窗口函数是什么在保持行数不变的情况下对每一行进行窗口内计算核心语法是什么函数() OVER (PARTITION BY ... ORDER BY ... 窗口帧)什么时候用排名、累计、移动平均、环比同比、分组 Top N与 GROUP BY 的区别GROUP BY 合并行窗口函数保留行性能如何比子查询/自连接快但需要合理使用索引