开窗函数在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;

运行结果为:

mysql 函数 开区间 mysql可以用开窗函数吗_mysql


说明:

  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
查询出来的结果
两条信息都会输出。

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

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