SQL窗口函数实践

当年在校招面试时，面试官出了一道SQL题：一次年级考试，求每个班级内的成绩前10名。

最近又遇到一个查询需求，假设一张表有(id,a,b,c)字段，需要一次批量查出：

• 查询 a = 1 and b = 2 and c = 3 条件下的max(id)；
• 查询 a = 2 and b = 1 and c = 4 条件下的max(id);
• 查询 a = 4 and b = 3 and c = 6 条件下的max(id);
• … 以此类推，需要批量查多条件下的最大id。

使用窗口函数即可以一次满足上面两个查询需求。

先看下窗口函数的定义：

In SQL, a window function or analytic function is a function which uses values from one or multiple rows to return a value for each row. (This contrasts with an aggregate function, which returns a single value for multiple rows.) Window functions have an OVER clause; any function without an OVER clause is not a window function, but rather an aggregate or single-row (scalar) function.

可以看出，窗口函数即是对每一个row都会生成对应的row数据(n:n)，而aggregate function可以聚合多行成一行数据(n:1)。

窗口函数表达式如下：

function(args) OVER ([PARTITION BY expression] [ORDER By expression [ASC|DESC]] [frame])

其中 partition by 表示根据字段进行分组求窗口函数，不加则表示所有行；order by 表示一个分组内的排序规则；[frame]表示滑动行范围。

接下来根据不同类型的function结合案例讲解使用方式。

排序函数

下面给出了3种不同规则的排序函数

• row_number() : 1,2,3,…
• rank() : 1,2,2,4,…
• dense_rank() : 1,2,2,3,…

---

假设有一张考试成绩表：

CREATE TABLE `exam_result` (
  `student_name` varchar(15) COMMENT '学生姓名',
  `grade` int COMMENT '分数',
  `class` int COMMENT '班级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

其中有数据：

student_name	grade	class
张三	89	1
李四	93	1
王五	78	1
小明	87	2
小赵	55	2
小方	100	2

需要求每个班级下的分数前2名，则SQL如下：

select ranks.rank_in_class, ranks.student_name , ranks.class from
(select  student_name, class, row_number() over (partition by class order by grade desc) as rank_in_class
from exam_result ) ranks where  ranks.rank_in_class <= 2;

则输出如下：

rank_in_class	student_name	class
1	李四	1
2	张三	1
1	小方	1
2	小明	2

符合预期。

---

假设在班级1中有两个分数为89的学生：

student_name	grade	class
张三	89	1
张四	89	1
李四	93	1
王五	78	1
小明	87	2
小赵	55	2
小方	100	2

我们要查出每个班级的第二名，且分数一样就都是第二名，SQL如下：

select ranks.student_name, ranks.class from
-- 使用rank()和dense_rank()都可以
( select  student_name, class, rank() over (partition by class order by grade desc) as rank_in_class
from exam_result ) ranks where ranks.rank_in_class = 2;

则输出如下：

student_name	class
张三	1
张四	1
小明	2

---

那如果存在同分的情况下要查处第三名呢？按照rank()我们发现第二名同分两个，所以第三名没有，而dense_rank()有

聚合函数

聚合函数：

• COUNT
• AVG
• SUM
• MAX
• MIN
• FIRST
• LAST

滑动行范围模式：

• rows模式
• range模式

滑动行范围的常用表达：

{RANGE|ROWS} frame_start
{RANGE|ROWS} BETWEEN frame_start AND frame_end

frame_start和frame_end可以取值：
UNBOUNDED PRECEDING
expression PRECEDING --only allowed in ROWS mode
CURRENT ROW
express FOLLOWING --only allowed in ROWS mode
UNBOUNDED FOLLOWING

默认：
BETWEEN unbounded preceding AND CURRENT ROW

---

同样是上面的exam_result表，表中有数据：

student_name	grade	class
张三	89	1
李四	93	1
王五	78	1
小明	87	2
小赵	55	2
小方	100	2

现在要求每个班的最高分，SQL如下：

-- 最高分
select distinct  class, max(grade) over (partition by class ) as max_grade
from exam_result;

输出如下：

class	max_grade
1	93
2	100

-- 平均分
select distinct class, avg(grade) over (partition by class) as avg_grade
from exam_result

class	avg_grade
1	86.6667
2	80.6667

-- 但是这里根据grade order一下，输出了奇怪的数据
select distinct class, avg(grade) over (partition by class order by grade ) as avg_grade
from exam_result

奇怪的数据如下：

class	avg_grade
1	78.0000
1	83.5000
1	86.6667
2	55.0000
2	71.0000
2	80.6667

为什么，因为指定了order没有指定滑动行范围，则默认为第一行到当前行，比如这里 78.0000是按照class为1时的grade升序后第一个做平均，83.5000是前两个做平均，86.6667前3个做平均，所以别乱加order。

---

同样有以下数据：

student_name	grade	class
张三	89	1
李四	93	1
王五	78	1
小明	87	2
小赵	55	2
小方	100	2

奇怪一点的case，想求当前同学的分数和班级内比自己小的一个分数的这两个分数的平均值（最小的就自己和自己平均）。 比如班级1，分数78最低，和自己的平均值就是78； 分数89，和比自己低的78取平均就是83.5000；分数93，和89取平均，就是91.0000。

则使用滑动行的ROWS模式，SQL如下：

select class, 
       student_name, 
       avg(grade) over (partition by class order by grade ROWS 1 PRECEDING) as avg_grade
from exam_result;
-- 等价于
select class,
       student_name,
       avg(grade) over (partition by class order by grade ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as avg_grade
from exam_result;

输出如下：

class	student_name	avg_grade
1	王五	78.0000
1	张三	83.5000 (89和78取平均)
1	李四	91.0000 (93和89取平均)
2	小赵	55.0000
2	小明	71.0000 (87和55取平均)
2	小方	93.5000 (100和87取平均)

---

同样有以下数据：

student_name	grade	class
张三	89	1
李四	93	1
王五	78	1
小明	87	2
小赵	55	2
小方	100	2

更奇怪一点的case，求与自己相差+-10分以内的分数平均值。比如班级1的89分，和93分算平均值，而78和89差了超过10，就和89算平均。SQL如下：

select class,
       student_name,
       avg(grade) over (partition by class order by grade RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING) as avg_grade
from exam_result;

输出如下：

class	student_name	avg_grade
1	王五	78.0000
1	张三	91.0000(89和93取平均)
1	李四	91.0000 (89和93取平均)
2	小赵	55.0000
2	小明	87.0000
2	小方	100.0000