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

Question

I have table in MySQL with times spent by customers and I need to find the most busy 30 mins.

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')
;

Expected result is something like multiplerows with time and number of customers:

   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

I can easily make 5 sql queries and get the result (I made some view in similar problem https://stackoverflow.com/a/59478411/11078894 ), but I do not know how to get the result with 1 query.

Please how to make subintervals in MySQL? Thx

I found the solution accordingly to your hint. I will write it down. Thx — Puser, Dec 30 '19 at 17:25

score 1 · Answer 1 · answered Dec 30 '19 at 15:51

Here is a solution based on union all and window function (available in SQL 8.0) that gets you quite close:

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

The logic is to increment a global counter on each arrival and decrement it on each leave. You can then aggregate and do a window sum.

The only difference with your expected results is that this query does not generate a fixed list of intervals, but instead a list of intervals for which the number of customer is constant, as you can see in 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

This works, great, I will try to find the solution for MySQL 5. Thank you — Puser, Dec 30 '19 at 16:54

Puser · Accepted Answer · 2019-12-30T18:43:41.423

Thanks to the post from GMB I found the solution also for the SQL 5 Some view:

CREATE OR REPLACE VIEW changeTimeView AS
select arrival AS changeTime, 1 cnt from Customer
union all
select leaving, -1 from Customer
ORDER BY changeTime

After the view is created:

SELECT DISTINCT chT2.changeTime,  (SELECT SUM(chT1.cnt) FROM changeTimeView chT1 WHERE TIMEDIFF(chT1.changeTime,chT2.changeTime)<=0) FROM changeTimeView chT2

Result:

2018-01-01 10:00:00     1
2018-01-01 11:00:00     2
2018-01-01 11:30:00     3
2018-01-01 12:00:00     1
2018-01-01 12:30:00     0
2018-01-01 13:30:00     1
2018-01-01 14:30:00     0

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

2 Answers2