Oracle-like over-packet implementations in MySQL

Today, the need to see people asking questions is probably the case.

Id	S	Start time	End time
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

To count the same user, the result of a continuous time (that is, the end time and the next start time differs by 1 seconds), the final rendering

ID, the total time interval, the start time of this time period, the end time of this time period.

As above id=1, the result should be 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

=============================================================================

For the above requirement, if Oracle is used, it should be better implemented. It's easy to get it done with group By,over,lag. But for MySQL, it seems that the statistical function is relatively small.

It was not familiar to MySQL's complex SQL application. So I tried to write it.

I arranged my own ideas, the first step of the goal:

1, need to sort out a unique field grouping

2. You need to show the end time of the previous record in the next record

Second, according to the first step to organize the goal

1. Decomposition of the first small target

1) First the unique line number of each line, which is the form of a unique field grouping that can be borrowed.

2) Mark Bit to be able to distinguish between different users, such as the last user's end time and the next user's start time is exactly connected, to be able to distinguish between two users.

2. Decomposition of the second small target

1) Convert time into numbers or characters, remove unnecessary characters, so that they are easy to follow

Iii. Creating Tests

1. Add a table

CREATE TABLE Time_log (  ID int,--User ID  s int,  --time interval  start_t varchar),  --Start time  end_t varchar ()  --end time)

2. Add test data

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) According to the first step goal

Out 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 ') Iendfrom Time_log t) T, (SELECT @preEndTime: = ", @rownum: =0) r

1) based on the column collation, generate ID, Mark continuous

Select T.*,case when Preendnum=dstartnum then 0 else rownum end as Difrom (select @rownum: [Email protected]+1 as Rownum,@p Reendtime 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 ') iendfrom time_log t) T, (SELECT @preEndTime: = ", @ rownum:=0) r) t

2) Final step-by-step processing, out of the final SQL

Select Id,s_nums Time S,str_to_date (istarttimes, '%y-%m-%d%h:%i:%s ') as start time, end_t as end time from (select @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 if 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 Difrom (select @rownum: [email protected]+1 as RowNum, @preEndTime as Preendnum, @preEndTime: =dend Num, 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 ') iendfrom time_log t) T, (Select @preEndTime: = "', @rownum: =0) R) T, (select @rowid: =0) r) t) (the ORDER by rownum Desc) T, (select @knum: =-1) r) T wher E t.flag<> 0 ORDER by RowNum

SQL does not have a lot of comments, but a layer of stripping, it should be easy to understand, this is not optimized. If I choose in project development, I am sure to use stored procedures.

Oracle-like over-packet implementations in MySQL