如果我有预定的开始和结束时间,如何计算每分钟的预定数量?我做了一个简化的数据库表,看起来像这样:
If I have a start and stop time for a booking, how can I calculate the number of bookings there are each minute? I made a simplified version of my database table looks like here:
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)之间进行预订.结果应该是这样的:
I want to have the bookings between a given time interval like 10:02 - 10:09. It should be something like this as result:
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中实现.
Question
How can this be achieved? Today I export it to python however I think it should be possible to achieve directly in SQL.
推荐答案您可以直接在数据上使用递归CTE:
You can use a recursive CTE directly on your data:
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小提琴.
在早期版本的MySQL中,拥有一个理货表格会有所帮助.您可以使用以下方法即时创建一个:
In earlier versions of MySQL, it helps to have a tally table. You can create one on the fly, using something like:
(select @rn := @rn + 1 as n from t cross join (select @rn := 0) params ) tally您需要足够的数字才能达到最大跨度,但是您可以这样做:
You need enough numbers for your maximum span, but then you can do:
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;更多推荐
如何计算每分钟时间间隔中sql中的并发预订数?
发布评论