mysql中類似oracle的over分組實現
來源：程序員人生 發布時間：2015-02-06 09:18:21 閱讀次數：3793次
今天，看到他人問問題，需求大概是這樣的。
| 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次的開始時間相差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 |
=============================================================================
對上面這個需求，如果用oracle，那末應當比較好實現。用group by,over,lag的方式很輕松就可以弄定。但對mysql，似乎統計函數比較少。
本來對MySQL復雜的SQL利用也不算熟習。因而試著寫了寫。
1、我自己整理了1下思路，第1步目標：
1、需要整理出1個唯1字段分組
2、需要在下1條記錄顯示上1條記錄的結束時間
2、根據第1步整理的目標
1、對第1個小目標分解
1）首先每行的唯1行號，這是構成唯1字段分組可以借用的。
2）標記位要能夠辨別不同用戶，比如上1個用戶的結束時間和下1個用戶的開始時間恰好連了起來，要能辨別出是兩個用戶。
2、第2個小目標分解
1）把時間轉化為數字或字符，去掉沒必要要字符，這樣便于后續處理
3、創建測試
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）根據第1步目標
出來SQL
select @rownum:=@rownum+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:=@rownum+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
2）終究1步步處理，出來終究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:=@rowid+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:=@rownum+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:=⑴) r
) t where t.flag<> 0 order by rownum
sql沒有大量注釋，但1層層剝離，應當很容易理解，這也沒有優化。如果在項目開發中讓我選擇，我肯定用存儲進程。
生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
