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模拟窗口函数功能

DROP TABLE IF EXISTS T1; CREATE TABLE T1( ID INT, DATAVALUE INT ); INSERT INTO T1 VA...
  • jc_benben
  • jc_benben
  • 2016年12月12日 13:40
  • 1286

MySQL 以及 Python 实现排名窗口函数

MySQL 以及Python 实现排名窗口函数一例。
  • yueliangdao0608
  • yueliangdao0608
  • 2014年05月14日 09:16
  • 3249

MySQL 入门(十二)—— MySQL内部函数

丰富的函数可以简化用户的操作,让操作更加灵活,此外,由于函数的执行速度非常快,还可以提高MySQL的处理速度。 前面介绍到的Select语句及其条件表达式,Insert、Update和Delete语...
  • lipengcn
  • lipengcn
  • 2016年04月13日 18:45
  • 3932

MySQL函数介绍

MySQL数据库提供了很多函数包括: 数学函数;字符串函数;日期和时间函数;条件判断函数;系统信息函数;加密函数;格式化函数; 一、数学函数   数学函数主要用于处理数字,包括整型、...
  • dodott
  • dodott
  • 2016年04月07日 09:51
  • 680

Mysql中模拟分析函数

[sql] view plain copySET @add_sal=0;    SELECT deptno, empno, ename, sal, @add_sal := @add_sal + sal...
  • lichangzai
  • lichangzai
  • 2017年02月14日 17:36
  • 510

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

今天收到同事的一需求,要求实现以下功能: drop table test; create table test(name varchar(10),name1 varchar(10),cou...
  • jayxujia123
  • jayxujia123
  • 2014年10月18日 12:33
  • 1589

窗口管理函数向应用程序提供了一些创建和管理用户界面的方法。你可以使用窗口管理函数创建和使用窗口来显示输出、提示用户进行输入以及完成其他一些与用户进行交互所需的工作。大多数应用程序都至少要创建一个窗口。

  • 2009年08月07日 16:32
  • 23KB
  • 下载

MySql——常用函数

(1)所需头文件: #include      功能: 获得或初始化一个MYSQL结构      函数原型: MYSQL *mysql_init(MYSQL *mysql)      函数返回...
  • perfectguyipeng
  • perfectguyipeng
  • 2017年04月18日 21:44
  • 561

mysql实现oracle的分析函数lag和rank

利用mysql的变量来实现: set @lag:=0,@currs:=0,@rank:=0; SELECT name,@lag:=@currs as lag,(@currs:=acct_no) a...
  • lihengfang
  • lihengfang
  • 2017年01月04日 15:45
  • 1280

mysql实现oracle分析函数功能 over

部分内容来源:http://blog.csdn.net/jgmydsai/article/category/3139929 Mysql的if函数 格式:IF(Condition,A,B) 意...
  • mengtianyalll
  • mengtianyalll
  • 2015年05月16日 13:03
  • 11982
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql下实现窗口分析函数
举报原因:
原因补充:

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