SQL窗口函数有哪些强大功能？入门到进阶怎么学

SQL窗口函数是一类特殊的SQL函数，它不会对查询结果集进行分组聚合后减少行数，而是在每一行数据上基于指定的窗口范围进行计算，同时保留原表的所有数据行。这种特性让它在处理排名、同比环比计算、移动平均等场景时比传统GROUP BY加子查询的方式更加简洁高效。

窗口函数基础概念

窗口函数的核心语法结构是函数名加上OVER子句，OVER子句用来定义数据的划分规则和排序规则。基本语法格式如下：

-- 窗口函数基础语法
函数名(参数) OVER (
    [PARTITION BY 分组列]
    [ORDER BY 排序列 [ASC|DESC]]
    [ROWS|RANGE 窗口范围]
)

其中PARTITION BY用来将数据按照指定列分成不同的组，类似GROUP BY的分组效果，但不会合并行；ORDER BY用来指定每个分组内的数据排序规则；ROWS|RANGE用来定义计算时的窗口范围，比如取当前行的前N行和后N行数据。

常用入门窗口函数

排名类窗口函数

排名类窗口函数用来对分组内的数据进行排序并生成排名，常见的有三种：

• ROW_NUMBER()：为每一行生成唯一的连续排名，相同值的行也会得到不同的排名
• RANK()：相同值的行排名相同，下一个排名会跳过占用的位数
• DENSE_RANK()：相同值的行排名相同，下一个排名不会跳过占用的位数

下面通过一个学生成绩表来演示这三个函数的区别，假设表名为student_score，包含student_id、subject、score三个字段，按科目分组对成绩排名：

SELECT 
    student_id,
    subject,
    score,
    -- 生成连续排名
    ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS row_num,
    -- 排名相同则跳过后续位次
    RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rank_num,
    -- 排名相同不跳过后续位次
    DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS dense_rank_num
FROM student_score;

聚合类窗口函数

普通的聚合函数如SUM、AVG、COUNT等加上OVER子句后，就会变成聚合窗口函数，可以在每一行上计算对应窗口的聚合结果。比如要计算每个学生的成绩在对应科目中的平均分占比，就可以用如下语句：

SELECT 
    student_id,
    subject,
    score,
    -- 计算当前科目所有学生的平均分
    AVG(score) OVER (PARTITION BY subject) AS subject_avg_score,
    -- 计算当前成绩占科目平均分的比例
    score / AVG(score) OVER (PARTITION BY subject) AS score_ratio
FROM student_score;

窗口函数进阶用法

自定义窗口范围

默认情况下如果不指定窗口范围，ROWS的范围是分区内从第一行到当前行。我们可以通过ROWS BETWEEN来手动指定窗口的上下边界，常见的边界标识有：

• UNBOUNDED PRECEDING：分区内的第一行
• N PRECEDING：当前行的上N行
• CURRENT ROW：当前行
• N FOLLOWING：当前行的下N行
• UNBOUNDED FOLLOWING：分区内的最后一行

比如要计算每个学生成绩的3行移动平均分，也就是当前行加上前一行和后一行的平均分，可以用如下语句：

SELECT 
    student_id,
    subject,
    score,
    -- 计算当前行前后各1行的移动平均分
    AVG(score) OVER (
        PARTITION BY subject 
        ORDER BY score DESC
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg_score
FROM student_score;

取值类窗口函数

取值类窗口函数可以获取窗口内其他行的数据，常用的有：

• LAG(列名, N, 默认值)：获取当前行往前第N行的数据，没有则返回默认值
• LEAD(列名, N, 默认值)：获取当前行往后第N行的数据，没有则返回默认值
• FIRST_VALUE(列名)：获取窗口内第一行的数据
• LAST_VALUE(列名)：获取窗口内最后一行的数据

比如要计算每个学生成绩和上一个成绩的差距，就可以用LAG函数：

SELECT 
    student_id,
    subject,
    score,
    -- 获取上一个成绩
    LAG(score, 1, 0) OVER (PARTITION BY subject ORDER BY score DESC) AS prev_score,
    -- 计算成绩差值
    score - LAG(score, 1, 0) OVER (PARTITION BY subject ORDER BY score DESC) AS score_diff
FROM student_score;

窗口函数使用注意事项

窗口函数不能直接用在WHERE子句中，因为SQL的执行顺序是FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY，窗口函数是在SELECT阶段执行的，所以如果需要对窗口函数的结果进行过滤，需要把查询作为子查询，在外层使用WHERE条件。

另外不同数据库对窗口函数的支持程度略有差异，比如MySQL从8.0版本开始才支持窗口函数，使用前需要确认数据库的版本是否支持对应的窗口函数功能。

SQL_window_functionover子句排名函数聚合窗口函数修改时间：2026-06-07 00:51:39