mysql中使用分析函数(开窗函数)

开窗函数在mysql中的使用

sql的分析函数用于复杂的统计方面非常好用，但mysql8之前不支持分析函数（开窗函数），怎么办呢？
我们可以利用变量来实现，先创建一个表：

create table income_tl(
	user_id int,
	create_date date,
	income int
);
 
insert into income_tl values(1,'2016-03-01',100);
insert into income_tl values(1,'2016-03-02',300);
insert into income_tl values(1,'2016-03-03',200);
insert into income_tl values(1,'2016-03-04',500);
insert into income_tl values(1,'2016-03-05',500);
 
insert into income_tl values(2,'2016-03-01',200);
insert into income_tl values(2,'2016-03-01',300);
insert into income_tl values(2,'2016-03-03',300);
insert into income_tl values(2,'2016-03-04',500);
insert into income_tl values(2,'2016-03-05',400);

关于mysql的变量：
可以用set来定于变量，定义变量的形式是以”@”开始，如：”@变量名”。

mysql> SET @t1=0, @t2=1, @t3=2;

mysql> select @t1，@t2， @t3;
+------++------++------+
| @t1  || @t2  || @t3  |
+------++------++------+
| 0    || 1    || 2    |
+------++------++------+

复杂一点的实例：

mysql> SET @t1=0, @t2=1, @t3=2;

mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 | 5    | 1    | 4    |
+----------------------+------+------+------+

从上表要得到每个用户每个月的总收入：

select it.user_id, 
       it.create_date, 
       it.income,
       case
          when @preVal = it.user_id then @curVal := @curVal + it.income
          when @preVal := it.user_id then @curVal := it.income
       end AS sum_income
from income_tl it, (select @preVal:=null, @curVal:=null) r
order by it.user_id asc, it.create_date asc;

运行结果为：

说明：

1. @preVal和@curVal为用户变量，仅针对当前客户端有效；
2. (select @preVal:=null, @curVal:=null) r 初始化@preVal和@curVal变量；
3. 第二个when仅用作为@preVal赋值，表示条件一直为true。（mysql中‘’、0、null均为false）；
4. = 只有在set和update时才是和:=一样，赋值的作用，其它都是等于的作用；
5. := 不只在set和update时时赋值的作用，在select也是赋值的作用；

再一个例子：
一个学习性任务：每个人有不同次数的成绩，统计出每个人的最高成绩。
这个问题相对还是简单，用聚合函数就好了。

select id,name,max(score) from Student group by id,name order by name

上边这种情况只适用id 和name是一一对应的，否则查询出来的数据是不正确的。
例如 ： 1 张三 100
2 张三 90
查询出来的结果
两条信息都会输出。

避免这种情况，可以使用开窗函数。
个人理解就是，开窗函数和聚合函数功能是相反的。
聚合函数，将多行数据合并成一行数据；而开窗函数则是将一行数据拆分成多行。
开窗函数可以满足上述问题，同时也可以满足其他问题。例如：求每个班最高成绩学生的信息。
分析：每个人学号一定是不同的，名字可能有重名，最大复杂的情况是，每个班最高成绩可能不止一个。

如果继续使用开始的方式，那么是不能满足要求的。
使用开窗函数就能很好的解决这个问题。