企业官网建设流程全解析

在复杂数据分析、报表统计、排名计算等场景中经常使用。窗口函数是 SQL 中非常强大的功能，可以在不改变行数的情况下对每一行进行计算，同时保留原始数据。

下面我从核心概念、语法结构、常用函数分类、实战案例、性能优化几个维度来系统说明。

🎯 窗口函数是什么？

窗口函数是在一个“窗口”（记录集合）上执行计算，但不会将多行合并为一行，每行都保留自己的身份，同时可以获得聚合结果。

对比聚合函数

-- ❌ 聚合函数：多行合并成一行SELECTdepartment,AVG(salary)FROMemployeesGROUPBYdepartment;-- ✅ 窗口函数：每行保留，同时显示部门平均工资SELECTname,department,salary,AVG(salary)OVER(PARTITIONBYdepartment)ASdept_avgFROMemployees;

📖 语法结构

窗口函数OVER([PARTITIONBY分区字段]-- 可选：将数据分组[ORDERBY排序字段]-- 可选：定义排序，影响排名类函数[ROWS/RANGE 窗口范围]-- 可选：定义帧（滑动窗口）)

三种核心组成部分

📚 窗口函数分类

1️⃣ 排名类函数

SELECTname,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 | 2

2️⃣ 聚合类窗口函数

在窗口上使用聚合函数，相当于“移动汇总”或“分组汇总但不合并行”。

-- 累计销售额（按日期）SELECTorder_date,amount,SUM(amount)OVER(ORDERBYorder_date)AScumulative_amount,AVG(amount)OVER(ORDERBYorder_dateROWSBETWEEN6PRECEDINGANDCURRENTROW)ASma_7dFROMorders;

3️⃣ 取值类窗口函数

获取窗口内指定位置的值。

-- 环比增长计算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_dept<=3;

案例 2：用户购买累计金额（时间序列）

-- 需求：计算每个用户的累计消费金额SELECTuser_id,order_date,amount,SUM(amount)OVER(PARTITIONBYuser_idORDERBYorder_dateROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)AScumulative_amountFROMorders;

案例 3：7 日移动平均（平滑曲线）

-- 需求：计算每日销售额的 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-- 到分区最后一行

常见窗口帧模式

-- 累计求和（从开始到当前）SUM(amount)OVER(ORDERBYdateROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)-- 7日移动平均AVG(amount)OVER(ORDERBYdateROWSBETWEEN6PRECEDINGANDCURRENTROW)-- 未来3天预测基准AVG(amount)OVER(ORDERBYdateROWSBETWEENCURRENTROWAND3FOLLOWING)

🔧 不同数据库的差异

MySQL 8.0 前的替代方案（使用变量）

-- MySQL 5.7 模拟 ROW_NUMBER()SELECTname,salary,@row_num:=@row_num+1ASrow_numFROMemployees,(SELECT@row_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 可能很慢

📊 实际业务场景

💡 总结