SQL窗口函数破解：如何优雅获取各分组极值
一、问题拆解：从分组数据中筛选极值
1.1 业务需求分析
题目：运营希望找到每个学校GPA最低的同学进行调研。
关键分析点：
- 需要按学校分组（
university） - 每个分组内按GPA排序（升序）
- 提取每个分组的第一条记录（GPA最低）
- 需同时返回学生的设备ID（
device_id）等其他信息
1.2 示例数据与目标
假设user_profile表结构及部分数据：
| device_id | university | gpa |
|---|---|---|
| 1001 | 北京大学 | 3.5 |
| 1002 | 北京大学 | 3.2 |
| 1003 | 清华大学 | 3.8 |
| 1004 | 清华大学 | 3.6 |
预期结果：
| device_id | university | gpa |
|---|---|---|
| 1002 | 北京大学 | 3.2 |
| 1004 | 清华大学 | 3.6 |
二、核心SQL解析：窗口函数的精妙应用
2.1 完整SQL语句
SELECT device_id, university, gpa
FROM (SELECT device_id, university, gpa,RANK() OVER (PARTITION BY university ORDER BY gpa) AS rkFROM user_profile
) AS up
WHERE up.rk = 1;
2.2 窗口函数执行逻辑
1. 窗口函数工作原理
RANK() OVER (PARTITION BY university ORDER BY gpa)
- PARTITION BY university：按学校分组（逻辑上把表拆分为多个子表）
- ORDER BY gpa：在每个分组内按GPA升序排序
- RANK()：为排序后的记录分配排名（相同值排名相同，后续排名跳跃）
2. 子查询结果示例
| device_id | university | gpa | rk |
|---|---|---|---|
| 1002 | 北京大学 | 3.2 | 1 |
| 1001 | 北京大学 | 3.5 | 2 |
| 1004 | 清华大学 | 3.6 | 1 |
| 1003 | 清华大学 | 3.8 | 2 |
3. 外层查询过滤
通过WHERE rk = 1筛选每个分组排名第一的记录，即GPA最低的学生。
三、窗口函数语法精讲
3.1 窗口函数基本语法
函数名() OVER ([PARTITION BY 分组字段1, 分组字段2...][ORDER BY 排序字段1 [ASC|DESC], 排序字段2...][窗口子句]
)
3.2 常用窗口函数分类
-
排序函数：
RANK()：排名跳跃（如1,1,3）DENSE_RANK()：排名连续（如1,1,2）ROW_NUMBER()：生成唯一行号（如1,2,3）
-
聚合函数：
SUM()：分组求和AVG()：分组平均MAX()/MIN()：分组最大/最小值
-
分布函数：
PERCENT_RANK()：百分比排名NTILE()：分桶函数
3.3 关键特性
- 不改变结果集行数：窗口函数不会合并或减少记录，只是附加计算结果
- 支持多字段分组与排序：可按多个字段进行灵活分组和排序
- 窗口子句扩展：可通过
ROWS BETWEEN定义滑动窗口范围
四、执行流程与数据流转
4.1 分步执行过程
步骤1：窗口函数计算排名
SELECT device_id, university, gpa,RANK() OVER (PARTITION BY university ORDER BY gpa) AS rk
FROM user_profile;
中间结果：
| device_id | university | gpa | rk |
|---|---|---|---|
| 1002 | 北京大学 | 3.2 | 1 |
| 1001 | 北京大学 | 3.5 | 2 |
| 1004 | 清华大学 | 3.6 | 1 |
| 1003 | 清华大学 | 3.8 | 2 |
步骤2：外层查询过滤
WHERE rk = 1
最终结果：
| device_id | university | gpa |
|---|---|---|
| 1002 | 北京大学 | 3.2 |
| 1004 | 清华大学 | 3.6 |
五、性能优化策略
5.1 索引优化
-- 创建复合索引加速分组和排序
CREATE INDEX idx_university_gpa ON user_profile(university, gpa);
索引作用：
- 按
university快速分组 - 在每个分组内按
gpa快速排序 - 避免文件排序（filesort）操作
5.2 执行计划分析
使用EXPLAIN关键字分析SQL执行计划：
EXPLAIN
SELECT ... (原SQL) ...;
关键指标解读：
Using filesort：若出现则表示未使用索引，性能较差Using temporary：若出现则表示使用了临时表，需优化Rows：预估扫描行数，应尽量减少
六、常见问题与解决方案
6.1 处理并列排名问题
若多个学生GPA相同且均为最低，需返回所有并列记录：
-- 使用RANK()函数（排名跳跃）
RANK() OVER (PARTITION BY university ORDER BY gpa) AS rk
结果示例：
| device_id | university | gpa | rk |
|---|---|---|---|
| 1002 | 北京大学 | 3.2 | 1 |
| 1005 | 北京大学 | 3.2 | 1 |
| 1001 | 北京大学 | 3.5 | 3 |
若只需随机返回一条：
-- 使用ROW_NUMBER()函数
ROW_NUMBER() OVER (PARTITION BY university ORDER BY gpa) AS rn
6.2 处理NULL值
若GPA字段存在NULL值，需明确排序规则：
-- 将NULL视为最低值
ORDER BY gpa ASC NULLS FIRST-- 将NULL视为最高值
ORDER BY gpa ASC NULLS LAST
6.3 扩展需求：获取GPA最高的学生
只需调整排序方向：
RANK() OVER (PARTITION BY university ORDER BY gpa DESC) AS rk
七、窗口函数与聚合函数的对比
| 特性 | 窗口函数 | 聚合函数 |
|---|---|---|
| 结果集行数 | 不改变原表行数 | 合并为分组行数 |
| 保留原始字段 | 可以保留 | 必须通过GROUP BY保留 |
| 支持排序 | 支持 | 不支持 |
| 常用场景 | 分组内排名、Top N查询 | 统计汇总 |
| 典型函数 | RANK(), ROW_NUMBER(), SUM() | SUM(), AVG(), COUNT() |
八、总结与技术要点
8.1 核心技术点回顾
- 窗口函数工作原理：分组、排序、计算排名
- RANK()与ROW_NUMBER()差异：处理并列排名的不同策略
- 子查询过滤逻辑：通过外层WHERE子句筛选目标记录
- 索引优化技巧：复合索引加速分组和排序操作
8.2 技术决策树
开始
│
├── 是否需要在分组内进行排序或计算？
│ │
│ └── 是 → 是否需要保留原始记录？
│ │
│ ├── 是 → 使用窗口函数
│ │ │
│ │ ├── 需要唯一排名 → ROW_NUMBER()
│ │ │
│ │ └── 允许并列排名 → RANK()/DENSE_RANK()
│ │
│ └── 否 → 使用聚合函数+GROUP BY
│
├── 是否存在性能问题？
│ │
│ └── 是 → 创建(PARTITION BY字段, ORDER BY字段)复合索引
│
└── 结束
掌握窗口函数的高级用法后，可以轻松应对各种复杂的分组统计和排名需求，避免传统自连接和子查询的性能瓶颈，提升SQL开发效率和代码可读性。