SQL 窗口函数

学习OVER/PARTITION · 难度：高级 · +15XP

SQL 窗口函数

窗口函数（Window Function）是 SQL 中最强大的分析工具之一。与普通聚合函数（如 SUM、AVG）将多行折叠为一行不同，窗口函数在保留每一行数据的同时进行跨行计算。这意味着你可以在同一行中看到原始数据和基于多行的统计结果，例如每个学生的分数和全班排名、每笔订单的金额和当天的累计销售额等。窗口函数是实现复杂数据分析报表的利器。

窗口函数基本语法

函数名() OVER (
    PARTITION BY 分组列      -- 可选：按某列分区
    ORDER BY 排序列          -- 可选：排序
    ROWS/RANGE 窗口范围      -- 可选：指定窗口范围
)

排名类窗口函数

排名函数是最常用的窗口函数类型：

-- RANK(): 并列排名会跳号（1,2,2,4）
SELECT name, score,
       RANK() OVER (ORDER BY score DESC) AS rank
FROM students;
-- DENSE_RANK(): 并列排名不跳号（1,2,2,3）
SELECT name, score,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;
-- ROW_NUMBER(): 每行唯一序号（1,2,3,4）
SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

聚合类窗口函数

-- 按年龄分组，计算每组内的分数总和
SELECT name, age, score,
       SUM(score) OVER (PARTITION BY age) AS age_total
FROM students;
-- 计算累计分数（按id排序滚动求和）
SELECT name, score,
       SUM(score) OVER (ORDER BY id) AS running_total
FROM students;
-- 计算移动平均值（当前行+前后各一行）
SELECT name, score,
       AVG(score) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM students;

偏移类窗口函数

-- LAG: 获取前一行的值
SELECT name, score,
       LAG(score, 1, 0) OVER (ORDER BY id) AS prev_score
FROM students;
-- LEAD: 获取后一行的值
SELECT name, score,
       LEAD(score, 1) OVER (ORDER BY id) AS next_score
FROM students;
-- FIRST_VALUE / LAST_VALUE: 窗口首尾值
SELECT name, score,
       FIRST_VALUE(score) OVER (PARTITION BY age ORDER BY score DESC) AS best_in_age
FROM students;

三种排名函数的区别

函数	并列处理	后续序号	示例
ROW_NUMBER	不并列，强制顺序	连续	1,2,3,4,5
RANK	允许并列	跳号	1,2,2,4,5
DENSE_RANK	允许并列	不跳号	1,2,2,3,4

实战任务

1. 使用 RANK() 为学生按分数排名
2. 使用 SUM() OVER(PARTITION BY) 计算每个年龄段的总分
3. 使用 LAG() 计算每个学生与前一个学生的分数差