Implementation of over groups similar to oracle in mysql

Implementation of over groups similar to oracle in mysql

Today, when someone asks a question, the requirement is probably like this.

Id	S	Start Time	End Time
1	20	08:10:20	08:10:40
1	9	08:10:41	08:10:50
1	60	08:10:51	08:11:51
1	2	08:12:51	08:12:53
2	51	08:10:00	08:10:51
2	60	08:11:00	08:12:00
2	5	08:13:00	08:13:05
2	15	08:13:06	08:13:21
2	5	08:13:25	08:13:30

To calculate the result that the user is the same, the time is continuous (that is, the end time is 1 second different from the next start time), and the final result is displayed.

Id, the total time interval, the start time of the time period, and the end time of the time period.

As shown in the above id = 1, the output result should be 1,

1	89	08:10:20	08:11:51
1	2	08:12:51	08:12:53

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

For the above requirement, if oracle is used, it should be better implemented. Using group by, over, and lag is easy. However, for mysql, it seems that the number of statistical functions is relatively small.

I was not familiar with complicated MySQL SQL applications. So I tried to write it.

1. I have organized my own ideas. The first step is:

1. A unique field group needs to be sorted out.

2. the end time of the previous record needs to be displayed in the next record.

Ii. Goals organized according to Step 1

1. Break down the first small target

1) First, the unique row number of each line, which is used to form a unique field group.

2) The tag space must be able to distinguish different users. For example, if the end time of the previous user is connected to the start time of the Next user, it must be able to distinguish between two users.

2. decomposition of the Second Small Target

1) convert the time to a number or character, remove unnecessary characters, so as to facilitate subsequent processing

3. Create a test

1. Add a table

Create table time_log (id int, -- User id s int, -- Time Interval start_t varchar (20), -- start time end_t varchar (20) -- 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 Step 1 goal

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') iendfrom time_log t) t,(SELECT @preEndTime:='',@rownum:=0) r

1) sort out columns, generate IDS, and Mark consecutive

select t.*,case when preendnum=dstartnum then 0 else rownum end as difrom (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') iendfrom time_log t) t,(SELECT @preEndTime:='',@rownum:=0) r) t

2) process the SQL statement step by step.

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 case when @ knum = dirow then 0 else dirow end as flag, @ knum: = dirow, t. * 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 difrom (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 ') iendfrom time_log t) t, (SELECT @ preEndTime: = '', @ rownum: = 0) r) 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 does not have a lot of comments, but it should be easy to understand and not optimized. If I have to select a project during project development, I will certainly use the stored procedure.