SQL分区窗口函数可以在不改变原表行数的前提下，对数据进行分组聚合、排名、前后行取值等操作，是处理复杂数据分析需求的核心工具，掌握其高效使用技巧能大幅提升SQL开发效率。
分区窗口函数基础语法
分区窗口函数的核心结构是函数() OVER (PARTITION BY 分区字段 ORDER BY 排序字段 [窗口范围])，其中PARTITION BY就是分区的关键，作用是将数据按照指定字段分成多个独立的组，函数会在每个组内独立计算。
常见的分区窗口函数分为几类：
- 排名类：
ROW_NUMBER()、RANK()、DENSE_RANK() - 聚合类：
SUM()、AVG()、COUNT()等聚合函数结合窗口使用 - 取值类：
LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE()
高效使用技巧
1. 合理选择分区字段减少计算量
分区字段的选择直接影响计算范围，尽量选择基数适中、和业务逻辑匹配的字段作为分区条件，避免无意义的全表分区。比如统计每个部门的员工薪资排名，分区字段选部门ID就比选全表更高效。
示例：统计每个部门的员工薪资排名
-- 按部门分区，每个部门内按薪资降序排名
SELECT
dept_id,
emp_id,
salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_salary_rank
FROM emp_table;
2. 避免不必要的ORDER BY排序
如果业务场景不需要组内排序，就不要加ORDER BY子句，排序操作会额外消耗CPU资源。比如只需要统计每个部门的总薪资，不需要组内顺序，就可以省略排序。
示例：统计每个部门的总薪资
-- 仅分区不排序，计算部门总薪资
SELECT
dept_id,
emp_id,
salary,
SUM(salary) OVER (PARTITION BY dept_id) AS dept_total_salary
FROM emp_table;
3. 结合索引提升查询性能
对分区字段和排序字段建立联合索引，可以大幅减少窗口函数的计算耗时。比如上面的部门薪资排名场景，建立(dept_id, salary DESC)的联合索引，数据库可以直接利用索引完成分区和排序，不需要额外做临时排序操作。
4. 控制窗口范围减少扫描行数
如果只需要计算相邻几行的数据，可以通过ROWS BETWEEN或者RANGE BETWEEN指定窗口范围，避免扫描整个分区的数据。比如计算近3个月的销售额移动平均，就不需要扫描整个分区的所有数据。
示例：计算每个员工近2次的薪资移动平均
-- 按员工分区，按发薪时间排序，取当前行和前1行计算平均
SELECT
emp_id,
pay_date,
salary,
AVG(salary) OVER (
PARTITION BY emp_id
ORDER BY pay_date
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS moving_avg_salary
FROM salary_record;
常见使用误区
很多开发者会混淆WHERE和窗口函数的执行顺序，窗口函数是在WHERE、GROUP BY、HAVING执行之后才计算的，所以不能在WHERE中直接使用窗口函数的计算结果做过滤，需要先套一层子查询或者使用CTE。
错误示例：
-- 错误，WHERE中不能直接使用窗口函数别名
SELECT
dept_id,
emp_id,
salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM emp_table
WHERE rn <= 3;
正确示例：
-- 用子查询包裹，再过滤
SELECT *
FROM (
SELECT
dept_id,
emp_id,
salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM emp_table
) t
WHERE t.rn <= 3;
实际业务场景示例
场景：统计每个用户的最近一次消费时间，以及该用户的总消费金额，同时标记出消费金额大于该用户平均消费金额的订单。
WITH user_order_info AS (
SELECT
user_id,
order_id,
order_amount,
order_time,
-- 每个用户的总消费
SUM(order_amount) OVER (PARTITION BY user_id) AS user_total_amount,
-- 每个用户的平均消费
AVG(order_amount) OVER (PARTITION BY user_id) AS user_avg_amount,
-- 每个用户最近一次消费时间
MAX(order_time) OVER (PARTITION BY user_id) AS user_last_order_time
FROM order_table
)
SELECT
user_id,
order_id,
order_amount,
order_time,
user_total_amount,
user_last_order_time,
-- 标记是否高于用户平均消费
CASE WHEN order_amount > user_avg_amount THEN 1 ELSE 0 END AS is_above_avg
FROM user_order_info;
通过以上技巧的合理使用，既能保证分区窗口函数的逻辑正确性，也能最大程度提升查询的执行效率，适配更多大数据量的业务场景。