mysql 大量数据计算 日期 int,计算MySQL中重叠日期范围的最大数量

I've having a big headache on the following situation. In MySQL I have a table with more than 40000 entries that look like that:

create table if not exists sessions

(

startt datetime null,

endt datetime null,

id int auto_increment

primary key

);

INSERT INTO sessions (startt, endt, id) VALUES

('2020-02-06 10:33:55', '2020-02-06 10:34:41', 20356),

('2020-02-06 10:33:14', '2020-02-06 10:33:57', 20355),

('2020-02-06 10:32:55', '2020-02-06 10:33:32', 20354),

('2020-02-06 10:33:03', '2020-02-06 10:33:12', 20353),

('2020-02-06 10:31:38', '2020-02-06 10:32:41', 20352),

('2020-02-06 09:48:44', '2020-02-06 09:50:37', 20351);

SELECT * FROM sessions;

+---------------------+---------------------+-------+

| startt | endt | id |

+---------------------+---------------------+-------+

| 2020-02-06 10:33:55 | 2020-02-06 10:34:41 | 20356 |

| 2020-02-06 10:33:14 | 2020-02-06 10:33:57 | 20355 |

| 2020-02-06 10:32:55 | 2020-02-06 10:33:32 | 20354 |

| 2020-02-06 10:33:03 | 2020-02-06 10:33:12 | 20353 |

| 2020-02-06 10:31:38 | 2020-02-06 10:32:41 | 20352 |

| 2020-02-06 09:48:44 | 2020-02-06 09:50:37 | 20351 |

+---------------------+---------------------+-------+

6 rows in set (0.00 sec)

The date and time ranges are sessions. What I want to find out is: what is the maximum number of sessions that existed at one time?

I found a lot of things like how to find out if a date is in the range of other dates etc. which didn't really help as I want to find out how many users there were at the maximum peak.

解决方案

Here is one option using window functions (available in MySQL 8.0):

select dt, sum(nb) over(order by dt) sum_nb

from (

select starttt dt, 1 nb from mytable

union all select endt, -1 from mytable

) t

order by sum_nb desc

limit 1

The idea is to unpivot the dataset; the count of concurrent sessions increases by 1 at the beginning of each session, and decreses by 1 at its end.

You can then compute the number of concurrent sessions at each point in time with a window sum.

The last step is ordering by session count and keeping the first row only.