MySQL预订可用性，日期范围重叠 [英] MySQL booking availability, overlapping date ranges

问题描述

我有一张可以预订的桌子:

I have a table with bookings:

• id(int)
• 数量(tinyint)
• date_start(时间戳)
• date_end(时间戳)

预订可能会重叠，因此例如可能同时有20个预订.现在，我想做两个查询:

Bookings can overlap, so there can for example be 20 bookings at the same time. Now I want to do two queries:

1)检索给定时间段内的最大数量.

1) Retrieve the maximum sum of quantities within a given period.

2)检索给定期间内的最小数量.

2) Retrieve the minimum sum of quantities within a given period.

我不知道如何将仅重叠预订的数量加在一起，而不是不重叠预订的数量.任何帮助将不胜感激.

I have no idea how to add the quantities of ONLY the overlapping bookings together, and not of non-overlapping bookings. Any help would be greatly appreciated.

为了清楚起见，我画了一张照片: http://i.imgur.com/8GDLV.png

edit: i drew a picture for clarity: http://i.imgur.com/8GDLV.png

推荐答案

1. 构造一个在所需时间段内发生的所有预订边界(即开始日期和结束日期)的列表:

1. Construct a list of all booking boundaries (i.e. start and end dates) which occur in the desired period:

  SELECT date_start AS boundary
  FROM   bookings
  WHERE  date_start BETWEEN @start AND @end
UNION
  SELECT date_end
  FROM   bookings
  WHERE  date_end BETWEEN @start AND @end

添加到紧接所需时间段之前的边界:

Add to that the boundary which occurs immediately before the desired period:

-- [ from part 1 above ]
UNION
  SELECT MAX(boundary)
  FROM (
    SELECT MAX(date_start) AS boundary
    FROM   bookings
    WHERE  date_start <= @start
  UNION ALL
    SELECT MAX(date_end)
    FROM   bookings
    WHERE  date_end <= @end
  ) t

在此结果与bookings表之间进行外部联接，保留所有边界，但仅包括预订(如果它有助于边界之后 的同时人数):

Make an outer join between this result and the bookings table, keeping all of the boundaries but only including a booking if it contributes to the number of simultaneous people after the boundary:

FROM bookings RIGHT JOIN (
  -- [ from part 2 above ]
) t ON date_start <= boundary AND boundary < date_end

每个边界处的人数总和:

Sum the number of people at each boundary:

SELECT IFNULL(SUM(quantity),0) AS simultaneous_people
-- [ from part 3 above ]
GROUP BY boundary

找到最大值和最小值:

Find the maximum and minimum:

SELECT MIN(simultaneous_people),
       MAX(simultaneous_people)
FROM (
  -- [ from part 4 above ]
) t

将它们放在一起:

SELECT MIN(simultaneous_people),
       MAX(simultaneous_people)
FROM (
  SELECT IFNULL(SUM(quantity),0) AS simultaneous_people
  FROM   bookings RIGHT JOIN (
    SELECT date_start AS boundary
    FROM   bookings
    WHERE  date_start BETWEEN @start AND @end
  UNION
    SELECT date_end
    FROM   bookings
    WHERE  date_end BETWEEN @start AND @end
  UNION
    SELECT MAX(boundary)
    FROM (
      SELECT MAX(date_start) AS boundary
      FROM   bookings
      WHERE  date_start <= @start
    UNION ALL
      SELECT MAX(date_end)
      FROM   bookings
      WHERE  date_end <= @end
    ) t
  ) t ON date_start <= boundary AND boundary < date_end
  GROUP BY boundary
) t

在 sqlfiddle 上查看.

这篇关于MySQL预订可用性，日期范围重叠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！