SQL последовательных вхождений для запроса на основе доступности

Question

Я немного застрял, пытаясь создать довольно сложный на SQL, а точнее MySQL.

База данных посвящена аренде автомобилей, а главная таблица, в которой изображены снежинки, выглядит примерно так:

  id   | rent_start | rent_duration | rent_end     |  customerID |    carId
  -----------------------------------------------------------------------------------
  203  | 2016-10-03 |       5       |  2016-11-07  |    16545    |    4543
  125  | 2016-10-20 |       9       |  2016-10-28  |    54452    |    5465  
  405  | 2016-11-01 |       2       |  2016-01-02  |    43565    |    346

Моя цель - создать запрос, который позволяет

1) Диапазон периода, например: с 2016-10-03 по 2016-11-03 2) Количество дней, например: 10

позволяет мне находить автомобили, которые действительно доступны, по крайней мере, в 10 последовательных дней с 10 октября по 11 октября. Список идентификаторов для этих автомобилей более чем достаточно... Я просто не знаю, как настроить такой запрос.

Если это может помочь: у меня есть список всех идентификаторов автомобилей в другой таблице.

В любом случае, спасибо!

Answer 1

Я думаю, что для этой цели гораздо проще работать с доступностью, чем с арендой.

Так:

select r.car_id, r.rent_end as avail_start,
       (select min(r2.rent_start
        from rentals r2
        where r2.car_id = r.car_id and r2.rent_start > r.rent_start
       ) as avail_end
from rentals r;

Тогда для вашего запроса вам потребуется не менее 10 дней. Вы можете использовать having пункт или подзапрос для этой цели:

select r.*
from (select r.car_id, r.rent_end as avail_start,
             (select min(r2.rent_start
              from rentals r2
              where r2.car_id = r.car_id and r2.rent_start > r.rent_start
             ) as avail_end
      from rentals r
     ) r
where datediff(avail_end, avail_start) >= $days;

И, наконец, вам нужно, чтобы этот период был в указанные вами даты:

select r.*
from (select r.car_id, r.rent_end as avail_start,
             (select min(r2.rent_start
              from rentals r2
              where r2.car_id = r.car_id and r2.rent_start > r.rent_start
             ) as avail_end
      from rentals r
     ) r
where datediff(avail_end, avail_start) >= $days and
      ( (avail_end > $end and avail_start < $start) or
        (avail_start <= $start and avail_end >= $start + interval 10 day) or
        (avail_start > $start and avail_start + interval 10 day <= $end)
      );

Это обрабатывает различные условия, когда свободный период охватывает весь диапазон или начинается / заканчивается во время диапазона.

В этой логике нет никаких сомнений в том, что эта машина доступна в тот же день, когда она возвращается. Это должно дать вам твердый подход к решению проблемы.

Кстати, вы должны также включать автомобили, которые никогда не были арендованы. Но это невозможно с таблицами, которые вы описываете в вопросе.