Найти пересечение между диапазонами дат в PostgreSQL

Предположения

Решение сильно зависит от точного определения таблицы, включая все ограничения. Из-за отсутствия информации в вопросе я приму эту таблицу:

CREATE TABLE booking (
  booking_id serial PRIMARY KEY
, check_in   timestamptz NOT NULL
, check_out  timestamptz NOT NULL
, CONSTRAINT valid_range CHECK (check_out > check_in)
);

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

Также предполагается текущая версия Postgres, по крайней мере, 9.2.

Запрос

Один из способов сделать это только с SQL, используя UNION ALL и оконные функции:

SELECT ts AS check_id, next_ts As check_out
FROM  (
   SELECT *, lead(ts) OVER (ORDER BY ts) AS next_ts
   FROM  (
      SELECT *, lag(people_ct, 1 , 0) OVER (ORDER BY ts) AS prev_ct
      FROM  (
         SELECT ts, sum(sum(change)) OVER (ORDER BY ts)::int AS people_ct
         FROM  (
            SELECT check_in AS ts, 1 AS change FROM booking
            UNION ALL
            SELECT check_out, -1 FROM booking
            ) sub1
         GROUP  BY 1
         ) sub2
      ) sub3
   WHERE  people_ct > 1 AND prev_ct < 2 OR  -- start overlap
          people_ct < 2 AND prev_ct > 1     -- end overlap
   ) sub4
WHERE  people_ct > 1 AND prev_ct < 2;

Скрипт SQL.

Объяснение

• В подзапросе sub1 выведите таблицу check_in и check_out в одном столбце. check_in добавляет один к толпе, check_out вычитает один.

• В sub2 суммируйте все события за один и тот же момент времени и вычислите текущее количество с помощью оконной функции: это оконная функция sum() по агрегату sum() - и приведите к integer или мы получим numeric из этого:

     sum(sum(change)) OVER (ORDER BY ts)::int
  

• В sub3 посмотрите на количество предыдущей строки

• В sub4 сохраняйте только строки, в которых начинаются и заканчиваются перекрывающиеся временные диапазоны, и перетаскивайте конец временного диапазона в ту же строку с lead().

• Наконец, оставьте только строки, в которых начинаются временные диапазоны.

Чтобы оптимизировать производительность, я просматривал таблицу один раз в функции plpgsql, как показано в этом связанном ответе на dba.SE:

• Расчет разницы во времени перекрытия в PostgreSQL/SSRS

Идея состоит в том, чтобы разделить время на периоды и сохранить их как битовые значения с заданной степенью детализации.

• 0 - в одном зерне никто не проверяется
• 1 - кто-то проверен в одно зерно

Предположим, что гранулярность составляет 1 час, а период — 1 день.

• 000000000000000000000000 означает, что в этот день никто не проверялся
• 000000000000000000000110 означает, что кто-то проверен между 21 и 23
• 000000000000011111000000 означает, что кто-то проверен в возрасте от 13 до 18 лет.
• 000000000000000111111100 означает, что кто-то проверен в возрасте от 15 до 22 лет.

После этого мы выполняем двоичное ИЛИ для каждого значения в диапазоне и получаем ответ.

• 000000000000011111111110

Это можно сделать за линейное время. Вот пример из Oracle, но его можно легко преобразовать в PostgreSQL.

with rec (checkin, checkout)
as ( select 13, 18 from dual 
   union all 
    select 15, 22 from dual 
   union all 
    select 21, 23 from dual )
,spanempty ( empt)
 as ( select '000000000000000000000000' from dual) ,
 spanfull( full)
 as ( select '111111111111111111111111' from dual)
, bookingbin( binbook) as ( select  substr(empt, 1, checkin) || 
        substr(full, checkin, checkout-checkin) || 
        substr(empt, checkout, 24-checkout) 
 from rec 
 cross join spanempty
 cross join spanfull ),
 bookingInt (rn, intbook) as 
 ( select rownum, bin2dec(binbook) from bookingbin),
 bitAndSum (bitAndSumm) as (
 select sum(bitand(b1.intbook, b2.intbook)) from bookingInt b1 
 join bookingInt b2 
 on b1.rn = b2.rn -1 ) ,
 SumAll (sumall) as (
 select sum(bin2dec(binbook)) from bookingBin  )
select lpad(dec2bin(sumall - bitAndSumm), 24, '0')
from SumAll, bitAndSum

Результат:

000000000000011111111110