时间戳

首页 > 解决方案 > 如何以每分钟的时间间隔计算sql中的同时预订？

mysql - 如何以每分钟的时间间隔计算sql中的同时预订？

问题描述

如果我有预订的开始和停止时间，我如何计算每分钟的预订数量？我制作了一个简化版的数据库表，如下所示：

Start time        | End time         | booking   |
--------------------------------------------------
2020-09-01 10:00  | 2020-09-01 10:10 | Booking 1 |
2020-09-01 10:00  | 2020-09-01 10:05 | Booking 2 |
2020-09-01 10:05  | 2020-09-01 10:10 | Booking 3 |
2020-09-01 10:09  | 2020-09-01 10:10 | Booking 4 |

我想在给定的时间间隔（如 10:02 - 10:09）之间进行预订。结果应该是这样的：

期望的结果

Time  | count
-----------
10:02 | 2 |
10:03 | 2 |
10:04 | 2 |
10:05 | 3 |
10:06 | 2 |
10:07 | 2 |
10:08 | 2 |
10:09 | 3 |

问题

如何做到这一点？今天我将它导出到 python 但是我认为应该可以直接在 SQL 中实现。

标签： mysqlsqldatetimecountrecursive-query

解决方案

您可以直接在数据上使用递归 CTE：

with recursive cte as (
      select start_time, end_time
      from t
      union all
      select start_time + interval 1 minute, end_time
      from cte
      where start_time < end_time
     )
select start_time, count(*)
from cte
group by start_time
order by start_time;

这是一个 db<>fiddle。

编辑：

在早期版本的 MySQL 中，有一个计数表会有所帮助。您可以使用以下方法即时创建一个：

(select @rn := @rn + 1 as n
 from t cross join
      (select @rn := 0) params
) tally

您需要足够的数字来实现最大跨度，但是您可以这样做：

select t.start_time + interval tally.n hour, count(*)
from t join
     (select @rn := @rn + 1 as n
      from t cross join
           (select @rn := -1) params  -- so it starts from 0
      limit 100
     ) tally
     on t.start_time + interval tally.n hour <= t.end_time
group by t.start_time + interval tally.n hour;

推荐阅读