窗口函数

窗口函数：它们都用于为查询结果集中的每一行分配一个排名或序号

核心区别总结表

函数	功能描述	排序特点（针对相同值）	序号示例
ROW_NUMBER()	为每一行生成一个唯一的、连续的序号。	即使值相同，也会分配不同序号（顺序不确定）。	1, 2, 3, 4, ...
RANK()	为每一行生成一个排名，允许并列。	相同值的行排名相同，但下一个不同值的排名会跳过并列占用的序号。	1, 2, 2, 4, ...
DENSE_RANK()	为每一行生成一个排名，允许并列。	相同值的行排名相同，但下一个不同值的排名连续，不跳过序号。	1, 2, 2, 3, ...

---

详细解释和示例

假设我们有一张学生成绩表 scores：

student_name	score
张三	95
李四	92
王五	92
赵六	88
孙七	85

现在我们使用这三个函数，按分数 score降序排列。 SQL 查询语句：

SELECTstudent_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	rn (ROW_NUMBER)	rk (RANK)	dr (DENSE_RANK)
张三	95	1	1	1
李四	92	2	2	2
王五	92	3	2	2
赵六	88	4	4	3
孙七	85	5	5	4

---

逐行分析结果

1. 第一行（张三，95分）：
   • 三个函数都从 1 开始，所以结果都是 1。
2. 第二行和第三行（李四和王五，都是92分）：
   • ROW_NUMBER()：必须生成唯一序号，所以即使分数相同，它也强制分配了 2和 3。至于谁得2谁得3，在没有其他排序条件的情况下是不确定的。
   • RANK()：遇到相同分数（92分），它赋予它们相同的排名 2。
   • DENSE_RANK()：同样赋予它们相同的排名 2。
3. 第四行（赵六，88分）：这是关键区别所在！
   • ROW_NUMBER()：继续连续编号，所以是 4。
   • RANK()：之前有两个并列第2名，它们占用了排名2和3。所以下一个排名（赵六的排名）应该从 4开始。这就是 “跳过”的含义。
   • DENSE_RANK()：它不关心有多少个并列。上一个有效的排名是 2，所以下一个排名就是 3。序号是 “密集的”，连续不间断。
4. 第五行（孙七，85分）：
   • ROW_NUMBER()：编号为 5。
   • RANK()：由于没有新的并列，它继续编号为 5。
   • DENSE_RANK()：继续编号为 4。

什么时候用？

• ROW_NUMBER()：
  • 需要绝对唯一序号时。例如，给用户列表编号，或者进行分页查询（如每页10条，用 rn BETWEEN 11 AND 20来取第二页）。
  • 需要从每组中选取Top N记录时（通常与 PARTITION BY联用）。
• RANK()：
  • 标准的排名，符合大多数体育比赛或竞赛的规则。比如奥运会奖牌榜，如果有两个金牌，下一个就是铜牌（排名第3）。
• DENSE_RANK()：
  • 当你关心“价值”的等级而不是位置时。比如，想知道95分是第1等，92分是第2等，88分是第3等，而不在乎中间有多少人并列。

关键语法点回顾

所有这些函数都必须与 OVER()子句一起使用。OVER()中的 ORDER BY决定了排名的依据。

FUNCTION_NAME() OVER (ORDER BY column_name [ASC|DESC])

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

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

SELECTstudent_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 (SELECTstudent_name,class_name,score,ROW_NUMBER() OVER (PARTITION BY class_name ORDER BY score DESC) as rank_in_classFROM students
) AS ranked_students
WHERE rank_in_class <= 2;

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

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

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

SELECTstudent_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它实现了"既要分组计算，又要保留明细数据"的需求。