MySQL下实现窗口分析函数

MySQL下实现窗口分析函数

窗口分析函数在做数据分析时十分常用，但是MySQL却不支持。不过我们可以通过sql的形式实现这个功能

构造表数据
create table rank_over(id int,subid int,curd timestamp);
……
insert into rank_over values(8,3,now());
insert into rank_over values(9,2,now());

表数据如下
mysql> select * from rank_over;
+------+-------+---------------------+
| id  | subid | curd                |
+------+-------+---------------------+
|    1 |    1 | 2018-09-24 00:47:12 |
|    2 |    1 | 2018-09-24 00:47:38 |
|    3 |    1 | 2018-09-24 00:47:42 |
|    4 |    2 | 2018-09-24 00:47:50 |
|    5 |    2 | 2018-09-24 00:47:54 |
|    6 |    3 | 2018-09-24 00:48:00 |
|    7 |    4 | 2018-09-24 00:48:06 |
|    8 |    3 | 2018-09-24 01:12:10 |
|    9 |    2 | 2018-09-24 01:12:11 |
+------+-------+---------------------+

该例子实现以下sql的逻辑，该sql的逻辑为根据subid分组，每个subid下取出curd最大的1条
select t.id,t.subid,t.curd
from(
SELECT id,subid,curd,RANK() OVER(PARTITION BY subid ORDER BY curd DESC) RK
FROM rank_over) t
where t.RK<2

使用一般sql实现如下
select t1.*  from
(select (@rowNum1:=@rowNum1+1) as rowNo,id,subid,curd from rank_over a,(Select (@rowNum1 :=0)) b order by a.subid,a.curd desc) t1 left join
(select (@rowNum2:=@rowNum2+1) as rowNo,id,subid,curd from rank_over c,(Select (@rowNum2 :=1)) d order by c.subid,c.curd desc) t2 on t1.rowNo=t2.rowNO
where t1.subid<>t2.subid or t2.subid is null
结果如下
+-------+------+-------+---------------------+
| rowNo | id  | subid | curd                |
+-------+------+-------+---------------------+
|    1 |    3 |    1 | 2018-09-24 00:47:42 |
|    4 |    9 |    2 | 2018-09-24 01:12:11 |
|    7 |    8 |    3 | 2018-09-24 01:12:10 |
|    9 |    7 |    4 | 2018-09-24 00:48:06 |
+-------+------+-------+---------------------+
4 rows in set (0.00 sec)

解释说明
@rowNum2 :=1即是最大的一条，如果改为@rowNum2 :=2即为最大的两条
order by a.subid,a.curd desc中order by的是分组的条件+排序的列，分组不要求升降序，排序的列的升降序要与需求一致
where t1.subid<>t2.subid or t2.subid is null即为分组条件
举例：根据country,part分区，取出sales最小的三个
@rowNum2 :=3
order by country,part,sales asc
where( t1.country<>t1.country and t1.part<> t2.part) or t1. country is null

linuxboy的RSS地址：https://www.linuxboy.net/rssFeed.aspx

本文永久更新链接地址：https://www.linuxboy.net/Linux/2018-09/154356.htm