窗口函数

🪄 一、窗口函数简介

窗口函数（Window Function）用于对查询结果集中的每一行计算“相对排名”或“顺序编号”。

三大常用函数：

函数	功能描述	相同值处理	是否跳号	示例序号
ROW_NUMBER()	为每行生成唯一连续编号	不并列	不跳号	1, 2, 3, 4, ...
RANK()	并列时相同排名	并列	跳号	1, 2, 2, 4, ...
DENSE_RANK()	并列时相同排名	并列	不跳号	1, 2, 2, 3, ...

---

🧱 二、示例表

CREATE TABLE scores (
    student_name VARCHAR(10),
    score INT
);

INSERT INTO scores VALUES
('张三', 95),
('李四', 92),
('王五', 92),
('赵六', 88),
('孙七', 85);

---

💻 三、使用窗口函数实现排名

SELECT
    student_name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
    RANK()       OVER (ORDER BY score DESC) AS rk,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dr
FROM scores;

📊 查询结果

student_name	score	ROW_NUMBER	RANK	DENSE_RANK
张三	95	1	1	1
李四	92	2	2	2
王五	92	3	2	2
赵六	88	4	4	3
孙七	85	5	5	4

---

🧠 四、区别详解

函数	并列情况	下一名	解释
ROW_NUMBER()	不并列	连续	每行唯一编号，即使分数相同
RANK()	并列	跳号	类似比赛排名（两个第二名后直接第四名）
DENSE_RANK()	并列	不跳号	等级划分更紧密（1,2,2,3,4）

---

⚙️ 五、在不支持窗口函数的数据库中用自联结实现

有些旧版本数据库（如 MySQL 5.x）不支持窗口函数，我们可以通过自联结 (Self Join) 实现相同逻辑。

---

🧩 1️⃣ 模拟 RANK()

思路：
计算比当前行分数更高的行数（包括当前行自己）
→ 直接用 >= 替代 > + 1。

SELECT 
    a.student_name,
    a.score,
    COUNT(b.student_name) AS rank
FROM scores a
LEFT JOIN scores b
  ON b.score >= a.score
GROUP BY a.student_name, a.score
ORDER BY a.score DESC;

📈 结果：

student_name	score	rank
张三	95	1
李四	92	2
王五	92	2
赵六	88	4
孙七	85	5

✅ 等价于 RANK() OVER (ORDER BY score DESC)。

---

🧩 2️⃣ 模拟 DENSE_RANK()

思路：
统计“比当前行分数高的不同分数”数量，再加上自己。

SELECT 
    a.student_name,
    a.score,
    COUNT(DISTINCT b.score) AS dense_rank
FROM scores a
LEFT JOIN scores b
  ON b.score >= a.score
GROUP BY a.student_name, a.score
ORDER BY a.score DESC;

📈 结果：

student_name	score	dense_rank
张三	95	1
李四	92	2
王五	92	2
赵六	88	3
孙七	85	4

✅ 等价于 DENSE_RANK() OVER (ORDER BY score DESC)。

---

🧩 3️⃣ 模拟 ROW_NUMBER()

思路：
ROW_NUMBER() 必须每行唯一编号，不能并列。
所以在分数相同的情况下，还需要用另一个唯一字段打破并列（如 student_name 或 id）。

SELECT 
    a.student_name,
    a.score,
    COUNT(b.student_name) AS row_number
FROM scores a
LEFT JOIN scores b
  ON b.score > a.score
   OR (b.score = a.score AND b.student_name < a.student_name)
GROUP BY a.student_name, a.score
ORDER BY a.score DESC, a.student_name;

📈 结果：

student_name	score	row_number
张三	95	1
李四	92	2
王五	92	3
赵六	88	4
孙七	85	5

✅ 等价于 ROW_NUMBER() OVER (ORDER BY score DESC, student_name ASC)。

 

PARTITION BY 详解

PARTITION BY用于在窗口函数中进行分组计算，类似于 GROUP BY的概念，但关键区别是：它不会合并行，而是在每个分组内独立进行计算。

基本语法

函数名() OVER (
    PARTITION BY 分组字段 
    ORDER BY 排序字段
)

---

具体示例

假设我们有一个更详细的学生成绩表：

student_id	student_name	class_name	score
1	张三	一班	95
2	李四	一班	92
3	王五	一班	88
4	赵六	二班	96
5	孙七	二班	90
6	周八	二班	90
7	吴九	二班	85

查询语句：计算每个班级内部的排名

SELECT
    student_id,
    student_name,
    class_name,
    score,
    ROW_NUMBER() OVER (PARTITION BY class_name ORDER BY score DESC) AS rn,
    RANK() OVER (PARTITION BY class_name ORDER BY score DESC) AS rk,
    DENSE_RANK() OVER (PARTITION BY class_name ORDER BY score DESC) AS dr
FROM students;

查询结果：

student_id	student_name	class_name	score	rn	rk	dr
1	张三	一班	95	1	1	1
2	李四	一班	92	2	2	2
3	王五	一班	88	3	3	3
4	赵六	二班	96	1	1	1
5	孙七	二班	90	2	2	2
6	周八	二班	90	3	2	2
7	吴九	二班	85	4	4	3

---

结果分析

一班的情况：

• 张三：95分，班级第1名（rn=1, rk=1, dr=1）
• 李四：92分，班级第2名（rn=2, rk=2, dr=2）
• 王五：88分，班级第3名（rn=3, rk=3, dr=3）

二班的情况（注意排名重置）：

• 赵六：96分，在二班内重新从第1名开始排名（rn=1, rk=1, dr=1）
• 孙七和周八：都是90分，出现并列
  • ROW_NUMBER()：强制分配2和3（不确定顺序）
  • RANK()：都排名第2，下一个吴九排名第4（跳过第3名）
  • DENSE_RANK()：都排名第2，下一个吴九排名第3（连续）

---

PARTITION BY 的更多实用例子

1. 查询每个班级的前2名学生

SELECT * FROM (
    SELECT
        student_name,
        class_name,
        score,
        ROW_NUMBER() OVER (PARTITION BY class_name ORDER BY score DESC) as rank_in_class
    FROM students
) AS ranked_students
WHERE rank_in_class <= 2;

2. 计算每个学生的成绩在班级内的百分比排名

SELECT
    student_name,
    class_name,
    score,
    ROUND(
        PERCENT_RANK() OVER (PARTITION BY class_name ORDER BY score) * 100, 2
    ) as percentile_in_class
FROM students;

3. 与其他窗口函数结合使用

SELECT
    student_name,
    class_name,
    score,
    -- 班级内排名
    RANK() OVER (PARTITION BY class_name ORDER BY score DESC) as class_rank,
    -- 班级内平均分
    AVG(score) OVER (PARTITION BY class_name) as class_avg,
    -- 班级内最高分
    MAX(score) OVER (PARTITION BY class_name) as class_max
FROM students;

---

PARTITION BY 的核心优势

1. 分组不合并：在每个分组内独立计算，但保持所有原始数据行
2. 排名重置：每个分组的排名都从1重新开始
3. 灵活组合：可以按多个字段分区：PARTITION BY class_name, subject_name
4. 性能优化：比使用多个子查询或自连接更高效

PARTITION BY它实现了"既要分组计算，又要保留明细数据"的需求。