如何计算每分钟时间间隔中sql中的并发预订数? [英] How to count concurrently bookings in sql in time interval per minute?

问题描述

如果我有预定的开始和结束时间，如何计算每分钟的预定数量?我做了一个简化的数据库表，看起来像这样:

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中的并发预订数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！