问题描述
我对以下情况感到头疼.在 MySQL 中,我有一个包含 40000 多个条目的表,如下所示:
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)
小提琴 https://www.db-fiddle.com/f/49bNZ7863gv6RThoPpuiid/0
日期和时间范围是会话.我想知道的是:一次存在的最大会话数是多少?
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.
推荐答案
这里有一个使用窗口函数的选项(在 MySQL 8.0 中可用):
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
这个想法是对数据集进行反透视;并发会话数在每个会话开始时增加 1,并在结束时减少 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.
这篇关于计算 MySQL 中重叠日期范围的最大数量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!