MySQL 5 : How to find the peak of customers during working time

Question

我在 MySQL 中有一个表，记录了客户花费的时间，我需要找到最繁忙的 30 分钟。

CREATE TABLE Customer
   (id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    customerId int NOT NULL,
    arrival datetime,
    leaving datetime);

INSERT INTO Customer
   (customerId, arrival, leaving)
VALUES
   (1, '2018-01-01 10:00:00', '2018-01-01 12:00:00'),
   (2, '2018-01-01 11:00:00', '2018-01-01 12:00:00'),
   (3, '2018-01-01 11:30:00', '2018-01-01 12:30:00'),
   (4, '2018-01-01 13:30:00', '2018-01-01 14:30:00')
;

预期结果类似于包含时间和客户数量的多行:

   10:00    10:30    1
   10:30    11:00    1
   11:00    11:30    2
   11:30    12:00    3
   12:00    12:30    1

我可以轻松地进行5个sql查询并获得结果(我在类似问题https://stackoverflow.com/a/59478411/11078894中做了一些查看)，但我不知道如何通过1个查询获得结果。

请问如何在MySQL中创建子区间？谢谢

Answer 1

这是一个基于 union all 和窗口函数(在 SQL 8.0 中提供)的解决方案，可以让您非常接近:

select 
    dt start_dt,
    lead(dt) over(order by dt) end_dt, 
    sum(sum(cnt)) over(order by dt) cnt
from (
    select arrival dt, 1 cnt from Customer
    union all
    select leaving, -1 from Customer
) t
group by dt
order by dt

逻辑是在每次到达时增加全局计数器并在每次离开时减少它。然后您可以聚合并进行窗口求和。

与预期结果的唯一区别是，此查询不会生成固定的间隔列表，而是生成客户数量恒定的间隔列表，如 this demo 中所示。 :

start_dt            | end_dt              | cnt
:------------------ | :------------------ | --:
2018-01-01 10:00:00 | 2018-01-01 11:00:00 |   1
2018-01-01 11:00:00 | 2018-01-01 11:30:00 |   2
2018-01-01 11:30:00 | 2018-01-01 12:00:00 |   3
2018-01-01 12:00:00 | 2018-01-02 12:30:00 |   1
2018-01-02 12:30:00 |                     |   0