SQL窗口函数是数据分析场景下非常实用的功能，它可以在不改变原表行数的前提下，对每一行数据基于指定的窗口范围做计算，避免了传统写法中大量子查询嵌套的问题。下面我们先通过一张示意图了解窗口函数的基本逻辑，再逐步深入探讨它的高级应用。
窗口函数基础语法
窗口函数的基本语法结构如下，其中over子句就是定义窗口范围的核心部分：
-- 窗口函数基本语法
函数名(参数) over (
[partition by 分组列1, 分组列2...]
[order by 排序列1 [asc|desc], 排序列2 [asc|desc]...]
[rows|range between 边界1 and 边界2]
)其中partition by用来指定分组的列，类似group by但不会压缩行数；order by指定窗口内的排序规则；最后的边界指定用来控制窗口的计算范围，比如是计算当前行及之前的所有行，还是当前行前后几行的数据。
常见高级应用场景
1 分组排名与 Top N 查询
排名类窗口函数是使用频率最高的场景，比如要查询每个部门薪资最高的前3名员工，传统写法需要先分组计算每个部门的最高薪资再关联，而用窗口函数只需要一行计算：
-- 查询每个部门薪资前3的员工
select
dept_name,
emp_name,
salary,
rank_num
from (
select
dept_name,
emp_name,
salary,
-- row_number 相同薪资会生成不同排名，rank 相同薪资排名相同后续跳过，dense_rank 相同薪资排名相同后续不跳过
dense_rank() over (partition by dept_name order by salary desc) as rank_num
from emp_salary
) t
where rank_num <= 3;2 同比环比与偏移量计算
分析业务数据的增长情况时，经常需要拿当前数据和上一周期、上一年同期的数据做对比，这时候可以用lag和lead偏移函数，直接获取当前行之前或之后的数据：
-- 计算月度销售额的环比和同比
select
month_id,
sale_amount,
-- 取上一月的销售额，没有的话用0填充
lag(sale_amount, 1, 0) over (order by month_id) as last_month_sale,
-- 计算环比增长率
round((sale_amount - lag(sale_amount, 1, 0) over (order by month_id)) / lag(sale_amount, 1, 0) over (order by month_id) * 100, 2) as mom_growth_rate,
-- 取上一年同月的销售额
lag(sale_amount, 12, 0) over (order by month_id) as last_year_same_month_sale,
-- 计算同比增长率
round((sale_amount - lag(sale_amount, 12, 0) over (order by month_id)) / lag(sale_amount, 12, 0) over (order by month_id) * 100, 2) as yoy_growth_rate
from monthly_sale;3 移动平均与累计统计
做趋势分析时经常需要计算移动平均或者累计值，通过指定窗口的边界就能快速实现，比如计算近3个月的移动平均销售额：
-- 计算近3个月的移动平均销售额
select
month_id,
sale_amount,
-- rows between 2 preceding and current row 表示窗口包含当前行和前2行，共3行数据
avg(sale_amount) over (order by month_id rows between 2 preceding and current row) as moving_avg_3m
from monthly_sale
where month_id >= '202401';窗口函数对比传统写法的优势
我们可以通过一个表格直观对比两种写法的差异：
| 对比维度 | 传统子查询写法 | 窗口函数写法 |
|---|---|---|
| 代码复杂度 | 需要多层嵌套子查询，逻辑分散 | 单条语句完成，逻辑集中 |
| 数据粒度 | 分组后会压缩行数，无法直接保留原表所有字段 | 不改变原表行数，可直接关联原表其他字段 |
| 执行效率 | 多次扫描表，性能较低 | 一次扫描完成计算，性能更优 |
| 可维护性 | 嵌套层级多，后期修改难度大 | 结构清晰，修改调整方便 |
注意事项
- 窗口函数只能出现在
select子句和order by子句中，不能用于where、group by等子句 - 排名函数和偏移函数不需要传参数，聚合函数作为窗口函数使用时需要指定聚合的列
- 如果没有指定
partition by，则整个表作为一个大的窗口进行计算 - 边界指定中，
unbounded preceding表示窗口从第一行开始，unbounded following表示窗口到最后一行结束
熟练掌握SQL窗口函数的高级应用，能大幅提升数据分析场景下的查询编写效率，减少冗余代码，是数据分析人员必备的技能之一。日常使用中可以根据具体的计算需求，灵活组合分区、排序、边界三个部分，适配不同的业务场景。