sql窗口函数

mysql窗口函数：告别繁琐子查询

在处理排名统计、分组内对比、累计计算这类需求时，你是不是还在写多层子查询、反复关联表？mysql窗口函数就是为解决这类问题而生的高效工具——它能在不聚合数据的前提下，对指定数据集（窗口）内的行做计算，让代码更简洁、性能更优。

窗口函数的核心语法：

<函数名>([expr]) over(

[partition by <列名>]

[order by <列名> [asc|desc]]

[rows|range <窗口范围>]

)

- partition by：可选，按指定列分组，每组是一个独立窗口，不分组则整个结果集为一个窗口。

- order by：可选，定义窗口内数据的排序规则，决定排名、累计等计算的顺序。

- rows|range：可选，限定窗口内的行范围，比如“当前行的前2行到后1行”。

mysql窗口函数主要分三类，日常开发够用了：

1. 排名函数： rank() 、 dense_rank() 、 row_number()

三者的核心区别在于处理并列排名的方式：

- row_number() ：不管是否并列，都生成连续序号（1,2,3,4）。

- rank() ：并列会跳过后续序号（1,2,2,4）。

- dense_rank() ：并列不跳过序号（1,2,2,3）。

举个例子，统计每个部门员工的薪资排名：

select

dept, name, salary,

row_number() over(partition by dept order by salary desc) as rn,

rank() over(partition by dept order by salary desc) as rk,

dense_rank() over(partition by dept order by salary desc) as dr

from emp;

2. 聚合窗口函数： sum() 、 avg() 、 max() 、 min()

和普通聚合函数的区别是，它不会将多行合并为一行，而是保留每行数据，同时计算窗口内的聚合结果。

比如计算每个员工的薪资，以及其所在部门的平均薪资：

select

dept, name, salary,

avg(salary) over(partition by dept) as dept_avg_sal

from emp;

3. 取值函数： lag() 、 lead() 、 first_value() 、 last_value()

用于获取窗口内指定位置的行数据，解决“和上一条/下一条数据对比”的需求。

- lag(expr, n) ：获取当前行的前n行的expr值。

- lead(expr, n) ：获取当前行的后n行的expr值。

比如查看每个员工的薪资，以及上一位员工的薪资：

select

name, salary,

lag(salary, 1) over(order by salary) as prev_sal

from emp;

最后说个关键：窗口函数和 group by 的区别。 group by 是聚合分组，每组只返回一行结果；窗口函数是开窗计算，会保留所有行，同时附加计算结果。

窗口函数: 用更少的代码、更高的效率，搞定那些之前需要绕弯子的统计需求