窗口分析函数在做数据分析时十分常用,但是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