当前位置:主页 > IT技术新闻 >

mysql中类似oracle的over分组实现

作者:环亚发表时间:2018-12-13

mysql中类似oracle的over分组实现

今天,看到别人问问题,需求大概是这样的。

id s 开始时间 结束时间
1 20 2001-01-01 08:10:20 2001-01-01 08:10:40
1 9 2001-01-01 08:10:41 2001-01-01 08:10:50
1 60 2001-01-01 08:10:51 2001-01-01 08:11:51
1 2 2001-01-01 08:12:51 2001-01-01 08:12:53
2 51 2001-01-01 08:10:00 2001-01-01 08:10:51
2 60 2001-01-01 08:11:00 2001-01-01 08:12:00
2 5 2001-01-01 08:13:00 2001-01-01 08:13:05
2 15 2001-01-01 08:13:06 2001-01-01 08:13:21
2 5 2001-01-01 08:13:25 2001-01-01 08:13:30
要统计用户相同,时间连续(也就是结束时间和下一次的开始时间相差1秒)的结果,最终呈现

id,总的时间间隔,这个时间段的开始时间,这个时间段的结束时间。

如上面id=1,出来结果应该是1,

1 89 2001-01-01 08:10:20 2001-01-01 08:11:51
1 2 2001-01-01 08:12:51 2001-01-01 08:12:53
==AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA>对于上面这个需求,如果用oracle,那么应该比较好实现。用group by,over,lag的方式很轻松就能搞定。但对于mysql,似乎统计函数比较少。

本来对于MySQL复杂的SQL应用也不算熟悉。于是试着写了写。

一、我自己整理了一下思路,第一步目AAAAAAAAA>1、需要整理出一个唯一字段分组

2、需要在下一条记录显示上一条记录的结束时间

二、根据第一步整理的目标

1、对第一个小目标分解

1)首先每行的唯一行号,这是形成唯一字段分组可以借用的。

2)标记位要能够区分不同用户,比如上一个用户的结束时间和下一个用户的开始时间刚好连了起来,要能区分出是两个用户。

2、第二个小目标分解

1)把时间转化为数字或者字符,去掉不必要字符,这样便于后续处理

三、创建测试

1、添加表

create table time_log(
  id int, --用户id
  s int,  --时间间隔
  start_t varchar(20),  --开始时间
  end_t varchar(20)  --结束时间
)
2、添加测试数据

insert into time_log(id,s,start_t,end_t) 
  values(1,20,'2001-01-01 08:10:20','2001-01-01 08:10:40');
insert into time_log(id,s,start_t,end_t) 
  values(1,9,'2001-01-01 08:10:41','2001-01-01 08:10:50');
insert into time_log(id,s,start_t,end_t) 
  values(1,60,'2001-01-01 08:10:51','2001-01-01 08:11:51');
insert into time_log(id,s,start_t,end_t) 
  values(1,2,'2001-01-01 08:12:51','2001-01-01 08:12:53');
insert into time_log(id,s,start_t,end_t) 
  values(2,51,'2001-01-01 08:10:00','2001-01-01 08:10:51');
insert into time_log(id,s,start_t,end_t) 
  values(2,60,'2001-01-01 08:11:00','2001-01-01 08:12:00');
insert into time_log(id,s,start_t,end_t) 
  values(2,5,'2001-01-01 08:13:00','2001-01-01 08:13:05');
insert into time_log(id,s,start_t,end_t) 
  values(2,15,'2001-01-01 08:13:06','2001-01-01 08:13:21');
insert into time_log(id,s,start_t,end_t) 
  values(2,5,'2001-01-01 08:13:25','2001-01-01 08:13:30');

3、SQL

1)根据第一步目标

出来SQL

select @rownum:[email protected]+1 as rownum,@preEndTime as preendnum,@preEndTime:=dendnum ,t.* from
(
select t.*
,CONCAT(id,'-',date_format(str_to_date(t.START_T,'%Y-%m-%d %h:%i:%s'),'%Y%m%d%h%i%s'))as dstartnum
,CONCAT(id,'-',date_format(str_to_date(t.end_T,'%Y-%m-%d %h:%i:%s'),'%Y%m%d%h%i%s')+1) as dendnum
,date_format(str_to_date(t.START_T,'%Y-%m-%d %h:%i:%s'),'%Y%m%d%h%i%s') istart
,date_format(str_to_date(t.end_T,'%Y-%m-%d %h:%i:%s'),'%Y%m%d%h%i%s') iend
from time_log t
) t,(SELECT @preEndTime:='',@rownum:=0) r

1)根据出来的列整理,生成id,标记连续

select t.*,case when preendnum=dstartnum then 0 else rownum end as di

from 
(
select @rownum:[email protected]+1 as rownum,@preEndTime as preendnum,@preEndTime:=dendnum ,t.* from
(
select t.*
,CONCAT(id,'-',date_format(str_to_date(t.START_T,'%Y-%m-%d %h:%i:%s'),'%Y%m%d%h%i%s'))as dstartnum
,CONCAT(id,'-',date_format(str_to_date(t.end_T,'%Y-%m-%d %h:%i:%s'),'%Y%m%d%h%i%s')+1) as dendnum
,date_format(str_to_date(t.START_T,'%Y-%m-%d %h:%i:%s'),'%Y%m%d%h%i%s') istart
,date_format(str_to_date(t.end_T,'%AAAAAAA %h:%i:%s'),'%Y%m%d%h%i%s') iend
from time_log t
) t,(SELECT @preEndTime:='',@rownum:=0) r
) t

2)最终一步步处理,出来最终SQL

select id,s_nums 时间s
,str_to_date(istarttimes,'%Y-%m-%d %h:%i:%s') as 开始时间
,end_t as 结束时间 from
(
select case when @knum=dirow then 0 else dirow end as flag,@knum:=dirow,t.* from 
(
select * from (
select t.*,date_sub(end_t, interval totals day_second) as istarttimes from
(
select t.*,@rowid:[email protected]+di as dirow,@sums:=case when di=0 then @sums+s+1 else s end as totals 
,@sums2:=case when di=0 then @sums2+s+0 else s end as s_nums from
(
select t.*,case when preendnum=dstartnum then 0 else rownum end as di

from 
(
select @rownum:[email protected]+1 as rownum,@preEndTime as preendnum,@preEndTime:=dendnum ,t.* from
(
select t.*
,CONCAT(id,'-',date_format(str_to_date(t.START_T,'%Y-%m-%d %h:%i:%s'),'%Y%m%d%h%i%s'))as dstartnum
,CONCAT(id,'-',date_format(str_to_date(t.end_T,'%Y-%m-%d %h:%i:%s'),'%Y%m%d%h%i%s')+1) as dendnum
,date_format(str_to_date(t.START_T,'%Y-%m-%d %h:%i:%s'),'%Y%m%d%h%i%s') istart
,date_format(str_to_date(t.end_T,'%Y-%m-%d %h:%i:%s'),'%Y%m%d%h%i%s') iend
from time_log t
) t,(SELECT @preEndTime:='',@rownum:=0) r
) t
) t,(SELECT @rowid:=0) r
) t
) t order by rownum desc
) t,(SELECT @knum:=-1) r
) t where t.flag<> 0 order by rownum

sql没有大量注释,但一层层剥离,应该很容易理解,这也没有优化。如果在项目开发中让我选择,我肯定用存储过程。

http://www.bkjia.com/Mysql/947286.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/947286.htmlTechArticlemysql中类似oracle的over分组实现 今天,看到别人问问题,需求大概是这样的。 id s 开始时间 结束时间 1 20 2001-01-01 08:10:20 2001-01-01 08:10:40 1 9...

本文源自: 环亚娱乐

IT技术新闻
联系我们