窗口分析函数在做数据分析时十分常用, 但是 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
来源: http://www.linuxidc.com/Linux/2018-09/154356.htm