mysql下实现窗口分析函数

原创 2016年08月30日 11:41:54

窗口分析函数在做数据分析时十分常用,但是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 | 2016-08-29 00:47:12 |
|    2 |     1 | 2016-08-29 00:47:38 |
|    3 |     1 | 2016-08-29 00:47:42 |
|    4 |     2 | 2016-08-29 00:47:50 |
|    5 |     2 | 2016-08-29 00:47:54 |
|    6 |     3 | 2016-08-29 00:48:00 |
|    7 |     4 | 2016-08-29 00:48:06 |
|    8 |     3 | 2016-08-29 01:12:10 |
|    9 |     2 | 2016-08-29 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 | 2016-08-29 00:47:42 |
|     4 |    9 |     2 | 2016-08-29 01:12:11 |
|     7 |    8 |     3 | 2016-08-29 01:12:10 |
|     9 |    7 |     4 | 2016-08-29 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

mysql分析函数的实现(阿里DBA博客,大开眼界)

今天收到同事的一需求,要求实现以下功能: drop table test; create table test(name varchar(10),name1 varchar(10),cou...

【MySQL】变量实现分析函数

作者: kelvin19840813 出处:http://www.cnblogs.com/kelvin19840813/articles/5701017.html 1. row_number...

mysql分析函数的实现

http://hidba.org/?p=295 mysql分析函数的实现 May 10th, 2011hidba 今天收到同事的一需求,要求实现以下功能: drop table t...

分析函数用法及窗口子句 range/rows差别

分析函数的语法结构一般是: 分析函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)。 即由以下三部分组成: 分析函数名:如sum、max、...

oracle分析函数:一、窗口子句的使用

/************************ *标题:分析函数analytic functions *时间:2016-07-07 *作者:clark ******************...

HIVE 窗口及分析函数 应用场景

窗口函数应用场景: (1)用于分区排序 (2)动态Group By (3)Top N (4)累计计算 (5)层次查询   一、分析函数 用于等级、百分点...

HIVE 窗口及分析函数 应用场景

窗口函数应用场景:   (1)用于分区排序   (2)动态Group By   (3)Top N   (4)累计计算   (5)层次查询 ...

oracle 每日一题-分析函数的RANGE/ROW窗口

作者:        Kim Berg Hansen   运行环境:SQLPLUS, SERVEROUTPUT已打开 我有一张表,登记着我在哪些日期有手机漫游(漫游的意思是我在国外)。我还有一个...

Spark1.4.0-SparkSQL与Hive整合-支持窗口分析函数

关键字: Spark读取HBase、SparkSQL读取HBase、SparkSQL整合Hive读取HBase表、Spark任务本地化调度机制 这里的SparkSQL是指整合了Hive的spar...
  • jarth
  • jarth
  • 2016年09月29日 11:16
  • 561

HIVE 窗口及分析函数 应用场景

窗口函数应用场景: (1)用于分区排序 (2)动态Group By (3)Top N (4)累计计算 (5)层次查询   一、分析函数 用于等级、百分点...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql下实现窗口分析函数
举报原因:
原因补充:

(最多只允许输入30个字)