Comments 12
оптимизированный вариант несомненно быстрее, но требует куда как больше времени на понимание того, как он работает, то есть сильно ухудшилась читаемость.
Критерий оптимизации по читаемости кода — хм, первый раз слышу :).
P.S. Проголосовал за Заумь. :)
...
union all
select p.start_time, s.stop_time -- case #2
from periods p, schedule s
where p.start_time >= s.start_time
and p.stop_time > s.stop_time
and p.start_time < s.stop_time
union all
select s.start_time, p.stop_time -- case #3
from periods p, schedule s
where p.start_time <= s.start_time
and p.stop_time < s.stop_time
and p.stop_time > s.start_time
union all
...
SELECT
GREATEST(s.start_time,p.start_time) as start_time,
LEAST(s.stop_time,p.stop_time) as stop_time
FROM
periods p, schedule s
WHERE
not(( s.stop_time < p.start_time) or (s.start_time > p.stop_time))
CREATE INDEX idx_sсhedule_time ON schedule (start_time, stop_time);
Nested Loop (cost=0.27..30.23 rows=220 width=16) (actual time=0.049..0.085 rows=18 loops=1)
-> Seq Scan on periods p (cost=0.00..1.04 rows=4 width=16) (actual time=0.019..0.019 rows=4 loops=1)
-> Index Only Scan using idx_schedule_time on schedule s (cost=0.27..6.47 rows=55 width=16) (actual time=0.014..0.014 rows=5 loops=4)
Index Cond: ((start_time <= p.stop_time) AND (stop_time >= p.start_time))
Heap Fetches: 0
Planning Time: 0.264 ms
Execution Time: 0.115 ms
У вас ошибка в первом запросе с UNION — case2 кроме ожидаемого результата добавляет непересекающиеся диапазоны слева, а case3 — справа
Кстати, да. Куда-то я это потерял во время подготовки статьи… Спасибо, исправил.
Вариант с tsrange индексы у меня не использует (попробовал разные варианты, возможно не те создавал).
Для range нужно использовать GiST-индексы: postgrespro.ru/docs/postgresql/11/rangetypes#RANGETYPES-INDEXING
Спасибо за статью, сэкономила много времени. Решал похожую задачу, как раз склонялся к тому, чтобы использовать интервалы, ну а здесь уже все разжевано. Так что применил для своих целей. Мы используем MS SQL, но это скорее частности, идеи, принципы и подходы общие
В развитии темы - если стоит посмотреть "в будущее", а не о анализировать прошлое (состоявшийся факт). Есть запись с дампом поступления (обращения\запрос, да что угодно) и нужно в своответствии с SLA рассчитать максимальный срок обработки обращения. Например, обращение должно быть рассмотрено и обработена не позднее 24 рабочих часов с момента поступления. Как расчитать "дедлайн" - момент, с которого начнется просрочка (т.е. до какого момента должен времени быть обработан документ). Решение для себя нашел, оно также на принципах интервалов, но не такое изящное, как описано в статье, если решать задачу "с конца". Поэтому если есть советы, как стоит подойти к решению такой задачи, буду признателен.
Рад, что пригодилось!
Для откладывания времени вперёд должен подойти оптимизированный подход, описанный в конце статьи. Нужно нарастающим итогом посчитать рабочее время вперёд, пока не превысите нужное значение. И дальше аккуратно посчитать, сколько осталось прибавить в последнем найденном дне.
Спасибо, примерно так и реализовали. Взяли условно нулевую точку и от неё нарастающим итогом рассчитали календарное и рабочее время для всего календаря. Единовременно. Для удобства и решения иных запросов сделали хранение накопленных значений на начало и на конец каждой даты. Далее уже все проще становится - по заданной дате находим её накопленное рабочее время, определяем по продолжительности искомое накопительное значение. Ищем дату где впервые достигается или превышается пороговое значение и уже точно рассчитываем момент времени возникновения "просрочки". В общем, сделали в виде функции на MS SQL, спрятав логику внутрь её. Результат и скорость работы на наших объёмах полностью устраивают, а фуннкия дала гибкость - возможность использовать в различных местах, в зависимости о им контекста и не усложняячего логику.
SQL: решение задачи о рабочем времени