SQL request to find if a period is fully covered

问题:

I have a set of periods like:

CREATE TABLE `periods` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `start_at` date DEFAULT NULL,
  `end_at` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `periods` WRITE;

INSERT INTO `periods` (`id`, `start_at`, `end_at`)
VALUES
    (1,'2013-04-29','2013-04-30'),
    (2,'2013-05-05','2013-05-10'),
    (3,'2013-05-10','2013-05-15'),
    (4,'2013-05-15','2013-05-16'),
    (5,'2013-05-18','2013-05-19'),
    (6,'2013-05-19','2013-05-25');

UNLOCK TABLES;

My intended desire, is the most optimized way to know if a given period is fully covered by one or multiple periods.

For instance:

1) to get null for a request from 2013-04-29 until 2013-05-10, cause no period covers from 2013-04=30 to 2013-05-05

2) to get the period ids (or at least true or any content) for a request from 2013-05-06 to 2013-05-15

UPDATE: The main goal is to define if the given period (from 2013-05-06 to 2013-05-15 as per example 2) is rentable. The periods in database are available rental seasons, so if any of the day are not covered, the entire stay can not be rented.

回答1:

EDIT: See here for a MySQL working SQL Fiddle: SQLFiddle, that actually works properly this time :-)

Try these. The bottom line is that if Shortfall > 0 then you can't book the rental.

MSSQL - this is how I worked it out

DECLARE @start DATETIME = '2013-04-29' -- this will depend on your dateformat
DECLARE @end DATETIME = '2013-05-10'
DECLARE @days INT = DATEDIFF(D,@start, @end) -- this is how many days we actually want to stay
DECLARE @unusedDays INT = 0 -- this will be the number of unused days from the rental periods in which our start and end dates fall
SELECT  @UnusedDays = DATEDIFF(D,@end,end_at) FROM PERIODS WHERE (@end > start_at AND @end <= end_at) -- how many spare days are there in the final period?
SELECT  @UnusedDays = @UnusedDays + DATEDIFF(D,start_at, @start) FROM PERIODS WHERE (@start >= start_at AND @start < end_at) -- how many spare days are there in the start period?
SELECT  @days + @UnusedDays - SUM(DATEDIFF(D,start_at,end_at)) AS Shortfall, -- total shortfall in days. Zero if we are okay to rent
        SUM(DATEDIFF(D,start_at,end_at)) AS AvailableDays, -- total number of days available in all periods covering our chosen rental period
        @days AS DesiredDays, -- number of days we want to rent
        @UnusedDays AS WastedDays -- number of wasted days (if we start or end our rental mid-period)
FROM    PERIODS 
WHERE   (@start >= start_at AND @start < end_at) -- period in which our selected rental starts
OR      (end_at < @end AND start_at > @start) -- period completely within our selected rental
OR      (@end > start_at AND @end <= end_at) -- period in which our selected rental ends

This provides output like this:

-- if you have @start = '2013-05-05'
-- and @end = '2013-05-13'
-- then you get
Shortfall AvailableDays DesiredDays WastedDays

0---------10------------8-----------2---------

-- if you have @start = '2013-04-29'
-- and @end = '2013-05-10'
-- then you get
Shortfall AvailableDays DesiredDays WastedDays

5---------6-------------11----------0---------

MySQL - this is what you actually want

SET @start = '2013-04-29';
SET @end = '2013-05-10';
SET @days = DATEDIFF(@end, @start); -- this is how many days we actually want to stay
SET @UnusedDays = 0; -- this will be the number of unused days from the rental periods in which our start and end dates fall
SELECT  @UnusedDays := DATEDIFF(end_at,@end) FROM PERIODS WHERE (@end > start_at AND @end <= end_at); -- how many spare days are there in the final period?
SELECT 'hello';
SELECT  @UnusedDays := @UnusedDays + DATEDIFF(@start, start_at) FROM PERIODS WHERE (@start >= start_at AND @start < end_at); -- how many spare days are there in the start period?
SELECT 'hello';
SELECT  @days + @UnusedDays - SUM(DATEDIFF(end_at, start_at)) AS Shortfall, -- total shortfall in days. Zero if we are okay to rent
        SUM(DATEDIFF(end_at, start_at)) AS AvailableDays, -- total number of days available in all periods covering our chosen rental period
        @days AS DesiredDays, -- number of days we want to rent
        @UnusedDays AS WastedDays -- number of wasted days (if we start or end our rental mid-period)
FROM    PERIODS 
WHERE   (@start >= start_at AND @start < end_at) -- period in which our selected rental starts
OR      (end_at < @end AND start_at > @start) -- period completely within our selected rental
OR      (@end > start_at AND @end <= end_at); -- period in which our selected rental ends

回答2:

While I like the approach of @Dommer and the details included (thanks a lot for that) I prefer the approach contributed by @snoyes on IRC#mysql.

SELECT IF(COUNT(*), false, true) AS rentable
FROM(
  SELECT
    a.end_at AS START,
    Min(b.start_at) AS END
  FROM periods AS a
  JOIN periods AS b ON a.end_at <= b.start_at
  GROUP BY a.end_at
  HAVING a.end_at < MIN(b.start_at)
) AS gaps
WHERE
  gaps.START < '2013-05-17' AND gaps.END > '2013-05-05';

A working SQLFiddle is also available.

For more details, the initial reference comes from http://www.artfulsoftware.com/infotree/qrytip.php?id=577

回答3:

Select ID from `periods`
where
    start_at >= <Your_given_start> 
    and end_at <= <Your_given_end>

This unfortunately only returns something, if there is a period covered.

回答4:

You can do this all in one query. The following query assumes that you have @StartDate and @EndDate defined.

The key idea is that you only need to test for one day before a start date or one day after an end date. If there is a period with no coverage, then it will appear during this time. The following query calculates the test dates and whether they are aavailable:

select TestDay, COUNT(p.id)     
from ((select p.Start_at - 1 as TestDay
       from #Periods p
       where p.start_at > @StartDate and p.start_at <= @EndDate
      )
      union all
      (select p.End_at
       from #periods p
       where p.start_at >= @StartDate and p.start_at < @EndDate
      )
     ) t left outer join
     #periods p
     on t.TestDay >= p.start_at and t.TestDay < p.end_at
group by TestDay;

This is more information than you need. You just want 0 in the first case and 1 in the second case. This is just a test of whether p.id in the above query is ever NULL:

select MAX(case when p.id is null then 1 else 0 end)  
from ((select p.Start_at - 1 as TestDay
       from #Periods p
       where p.start_at > @StartDate and p.start_at <= @EndDate
      )
      union all
      (select p.End_at
       from #periods p
       where p.start_at >= @StartDate and p.start_at < @EndDate
      )
     ) t left outer join
     #periods p
     on t.TestDay >= p.start_at and t.TestDay < p.end_at;

This query might have an off-by-one error (depending on whether there is availability on the end date). Such a problem is easily fixed.