· 7 years ago · Sep 15, 2018, 02:14 AM
1Aggregating overlapping events With MySQL
2drop table if exists views;
3create table views(id int primary key,start time,end time);
4insert into views values
5(1, '15:01', '15:04'),
6(2, '15:02', '15:09'),
7(3, '15:12', '15:15'),
8(4, '16:11', '16:23'),
9(5, '16:19', '16:25'),
10(6, '17:52', '17:59'),
11(7, '18:18', '18:22'),
12(8, '16:20', '16:22'),
13(9, '18:17', '18:23');
14
151 |-----|
162 |-----|
173 |--|
184 |-----|
195 |-----|
206 |---|
217 |---|
228 |---|
239 |-----|
24
25+---------------------------+
26| x |
27| x x xxx xxx |
28| x xx xx x xx x |
29+---------------------------+
30
31Time Start, Time End, Num_Views
3200:00, 00:05, 10
3300:06, 00:10, 3
3400:11, 00:15, 2
3500:16, 00:20, 8
36
37select
38 r.Time_Start,
39 r.Time_End,
40 sum(v.id is not null) as Num_Views
41from (
42 select
43 cast(from_unixtime((m.minstart + n.n + 0) * 300) as time) as Time_Start,
44 cast(from_unixtime((m.minstart + n.n + 1) * 300) as time) as Time_End
45 from (
46 select
47 unix_timestamp(date_format(minstart, '1970-01-01 %T')) div 300 as minstart,
48 unix_timestamp(date_format(maxend , '1970-01-01 %T')) div 300 as maxend
49 from (
50 select
51 min(start) as minstart,
52 max(end ) as maxend
53 from views
54 ) s
55 ) m
56 cross join numbers n
57 where n.n between 0 and m.maxend - minstart
58) r
59 left join views v on v.start < r.Time_End and v.end > r.Time_Start
60group by
61 r.Time_Start,
62 r.Time_End
63;
64
65Time_Start Time_End Num_Views
66---------- -------- ---------
6715:00:00 15:05:00 2
6815:05:00 15:10:00 1
6915:10:00 15:15:00 1
7015:15:00 15:20:00 0
7115:20:00 15:25:00 0
7215:25:00 15:30:00 0
7315:30:00 15:35:00 0
7415:35:00 15:40:00 0
7515:40:00 15:45:00 0
7615:45:00 15:50:00 0
7715:50:00 15:55:00 0
7815:55:00 16:00:00 0
7916:00:00 16:05:00 0
8016:05:00 16:10:00 0
8116:10:00 16:15:00 1
8216:15:00 16:20:00 2
8316:20:00 16:25:00 3
8416:25:00 16:30:00 0
8516:30:00 16:35:00 0
8616:35:00 16:40:00 0
8716:40:00 16:45:00 0
8816:45:00 16:50:00 0
8916:50:00 16:55:00 0
9016:55:00 17:00:00 0
9117:00:00 17:05:00 0
9217:05:00 17:10:00 0
9317:10:00 17:15:00 0
9417:15:00 17:20:00 0
9517:20:00 17:25:00 0
9617:25:00 17:30:00 0
9717:30:00 17:35:00 0
9817:35:00 17:40:00 0
9917:40:00 17:45:00 0
10017:45:00 17:50:00 0
10117:50:00 17:55:00 1
10217:55:00 18:00:00 1
10318:00:00 18:05:00 0
10418:05:00 18:10:00 0
10518:10:00 18:15:00 0
10618:15:00 18:20:00 2
10718:20:00 18:25:00 2
108
109create table numbers (n int);
110insert into numbers (n) select 0;
111insert into numbers (n) select cnt + n from numbers, (select count(*) as cnt from numbers) s;
112insert into numbers (n) select cnt + n from numbers, (select count(*) as cnt from numbers) s;
113insert into numbers (n) select cnt + n from numbers, (select count(*) as cnt from numbers) s;
114insert into numbers (n) select cnt + n from numbers, (select count(*) as cnt from numbers) s;
115insert into numbers (n) select cnt + n from numbers, (select count(*) as cnt from numbers) s;
116insert into numbers (n) select cnt + n from numbers, (select count(*) as cnt from numbers) s;
117insert into numbers (n) select cnt + n from numbers, (select count(*) as cnt from numbers) s;
118insert into numbers (n) select cnt + n from numbers, (select count(*) as cnt from numbers) s;
119/* repeat as necessary; every repeated line doubles the number of rows */