我正在努力找出最好的方法，找出一天中聊天室中用户最多的时间。
我试着把日期分组，但由于时间间隔的原因，这并不奏效。有什么窗口函数可以在MySQL中使用吗？也许将这些数据转移到ClickHouse是获取这些信息的更有效方式吗？
有一些方法可以找出重叠时间范围的总量，即enter_time和leave_time之间发生的碰撞量(请参阅：如何在Mysql中找到最大时间范围碰撞发生次数(，但这不允许您找到聊天室中用户最多的确切时间。
描述不同用户时间叠加的图片
建立一个显示用户最多的时间的查询最有效的方法是
我正在努力实现的结果
busy_time_start | busy_time_end
--------------------------------------------
2022-09-10T03:10:00Z | 2022-09-10T05:59:00Z
2022-09-10T06:05:00Z | 2022-09-10T09:59:00Z
我有一个表格，里面有进入聊天室的用户列表。它描述了user_id、enter_time和leave_time。
我想试着弄清楚的是，某一天聊天室里最多人的持续时间是多少。
我的数据是什么样子的
MySQL架构设置：
CREATE TABLE `user_log` (
`user_id` int DEFAULT NULL,
`enter_time` datetime DEFAULT NULL,
`leave_time` datetime DEFAULT NULL);
insert into user_log values
(1, '2022-09-10 01:00:00', '2022-09-10 12:00:00'),
(2, '2022-09-10 02:10:00', '2022-09-10 11:59:00'),
(3, '2022-09-10 03:30:00', '2022-09-10 10:59:00'),
(4, '2022-09-10 04:10:00', '2022-09-10 09:59:00'),
(5, '2022-09-10 02:05:00', '2022-09-10 05:59:00'),
(6, '2022-09-10 06:05:00', '2022-09-10 08:59:00');
查询1：
select * from user_log
结果：
| user_id | enter_time | leave_time |
|---------|----------------------|----------------------|
| 1 | 2022-09-10T01:00:00Z | 2022-09-10T12:00:00Z |
| 2 | 2022-09-10T02:10:00Z | 2022-09-10T11:59:00Z |
| 3 | 2022-09-10T03:30:00Z | 2022-09-10T10:59:00Z |
| 4 | 2022-09-10T04:10:00Z | 2022-09-10T09:59:00Z |
| 5 | 2022-09-10T02:05:00Z | 2022-09-10T05:59:00Z |
| 6 | 2022-09-10T06:05:00Z | 2022-09-10T08:59:00Z |
首先，我们找到用户计数可能发生变化的所有时间。看起来你总是四舍五入到最近的一分钟，你的结束时间是包容性的(直到那个时间，不到但不到那个时间(。因此：
select enter_time as time from user_log
union distinct
select leave_time + interval 1 minute from user_log
对于每一次，找到用户数量和结束时间(使用线索(：
select
boundaries.time as busy_time_start,
count(user_log.user_id) as user_count,
lead(boundaries.time) over (order by boundaries.time) - interval 1 minute as busy_time_end
from (
select enter_time as time from user_log
union distinct
select leave_time + interval 1 minute from user_log
) boundaries
left join user_log on boundaries.time between user_log.enter_time and user_log.leave_time
group by boundaries.time
现在我们只需要将其缩小到具有最大用户数的行：
select busy_time_start, busy_time_end
from (
select
boundaries.time as busy_time_start,
count(user_log.user_id) as user_count,
lead(boundaries.time) over (order by boundaries.time) - interval 1 minute as busy_time_end,
max(count(user_log.user_id)) over () as max_user_count
from (
select enter_time as time from user_log
union distinct
select leave_time + interval 1 minute from user_log
) boundaries
left join user_log on boundaries.time between user_log.enter_time and user_log.leave_time
group by boundaries.time
) busy_times
where user_count = max_user_count
如果有用户离开而另一个用户进入下一分钟的情况，这可能会产生可以合并在一起的相邻范围；要做到这一点，您需要将其视为间隙和孤岛问题，并按具有相同计数的行的每个范围进行分组，为每个范围选择最小开始时间和最大结束时间。
到user_log的联接将是O(n^2(；如果用户在聊天中的最长时间是已知的，并且远小于数据的总时间范围，则可以在输入时间上为userlog建立索引，并在on子句中添加一个输入时间范围。