MySQL开窗函数

  在面试中被问到过MySQL开窗函数。开窗函数在8.0以前的MySQL中是没有的，8.0才有这个功能。

准备数据

  为了学习测试开窗函数的功能，首先要建表，准备测试数据。在学习之间做好数据上的准备是非常必要的。

create schema learn collate utf8mb4_general_ci;
use learn;
create table score (
    id integer primary key auto_increment comment '主键',
    name varchar(50) comment '姓名',
    subject varchar(50) comment '科目',
    score integer comment '分数'
);
INSERT INTO learn.score (name, subject, score) VALUES ('宝哥', '语文', 91);
INSERT INTO learn.score (name, subject, score) VALUES ('宝哥', '数学', 92);
INSERT INTO learn.score (name, subject, score) VALUES ('宝哥', '英语', 93);
INSERT INTO learn.score (name, subject, score) VALUES ('红姐', '语文', 81);
INSERT INTO learn.score (name, subject, score) VALUES ('红姐', '数学', 82);
INSERT INTO learn.score (name, subject, score) VALUES ('红姐', '英语', 83);
INSERT INTO learn.score (name, subject, score) VALUES ('小花', '语文', 71);
INSERT INTO learn.score (name, subject, score) VALUES ('小花', '数学', 72);
INSERT INTO learn.score (name, subject, score) VALUES ('小花', '英语', 73);

原理

  开窗函数window function，也叫窗口函数，是作用在行上的函数，进行多行相对一行的计算，在MySQL官方文档是这样说的：

for each row from a query, perform a calculation using rows related to that row.

  这样说，说不太明白。首先仔细分析原文，多行关联一行，再举一个例子，有一个人口数据，起始于2012年，那么2013年相对于2012的人口增长率，2014年相对于2012年的人口增长率就是多行相对于一行。但是这个例子也不恰当，因为现实中的人口增长率的计算需求，都是相对于上一年的人口增长率。
  再说说窗口函数和聚合函数的区分，大部分聚合函数都可以用作窗口函数。但是窗口函数是不能用在聚合函数的，这类窗口函数也叫做非聚合窗口函数nonaggregate window function。非聚合窗口函数，和聚合函数很相似，一个是分组，一个是分区。分组，语法上是group by，是要写在where后面的，而分区，是over partition by，是要写在select后面where之前的。先写一个简单的试试。以下是我第一次用开窗函数的语句：

select name, subject, rank() over (partition by subject order by score desc) as  `rank` from score;

  结果如下：

  我是看明白了，分组是一个组合并为一条数据，而分区是对组进行展开，其实效果相当于排序。而函数rank，相当于做个一个计数器，在下一个分区时计数器重新计数。如果没有开窗函数，那么我们就需要用order by排序，然后用变量自己做一个计数器，非常麻烦。并且，改变行的排序，不会影响窗口函数的值，如以下例子：

select name, subject, rank() over (partition by subject order by score desc) as  `rank` from score
order by name;

  结果如图，图中不在按学科排序，但是每位同学的学科名次依旧是正确的：

应用

  面试时问过如果说查各科目第二名是谁，怎么写SQL，那这个就简单了。在MySQL中，开窗函数字段不能直接用于where语句中，必须再包一层，做成子查询，才能使用，如下面语句就是只查第二名：

select *
from (
         select name, subject, rank() over (partition by subject order by score desc) as `rank` from score) sub
where sub.rank = 2;

  查询结果：

备选方案

  如果没有MySQL8.0版本，但是要实现开窗函数的需求，改怎么办呢？比如说输出每个人在数学学科的排名，对，就是这么简单的需求。我们首先想到的是计数器。MySQL可以在select语句中定义变量。对于上述需求，我们需要两个变量：一个是计数器，每次+1；另一个存储上一行的学科，如果学科发生变化，则重新计数。思路清楚了，那么代码就好写了，SQL代码如下：

select name,
       @subject_rank :=
               case
                   when @subject_rank is null then 1
                   when @current != score.subject then 1
                   else @subject_rank + 1 end as `rank`,
       @current := `subject` as subject
from score
order by subject, score desc;

  查询结果如下，完全正确：