Интересная идея с использованием последовательности, мне такое в голову не приходило. И ведь работает! Из недостатков укажу, что требуются права на создание последовательности.
Работает. Хотя преобразования чисел и дат в строку и обратно — это не очень надёжное допущение, вдруг формат даты в настройках базы изменён — и приплыли.
Можно. Для этого даже JOIN не нужен, достаточно оконной функции first_value() over (...). Но Вы недостаточно внимательно условие прочитали, строк с реальной ценой может быть несколько…
Увы, Вы не заметили такого подвоха, что R-строчек может быть несколько, и данные нужно брать из последней. Сравните Ваш результат с эталонным в статье. Видите?
Что-то я думал тут будет про рекурсию и подобное, даже с любопытством заглянул посмотреть. А тут какие-то измышления частного порядка, которые не то чтобы всем программистам знать не надо, а даже и один раз прочитать необязательно. Извините, но заголовок не соответствует.
Конечно можно и так, я же специально подчеркнул, что не претендую на лучшее ни в каком смысле. Ну не min/max, а greatest/least, но суть та же. Получится точно компактнее, но пожалуй что запутаннее.
Но главную мысль Вы уловили совершенно правильно — с tsrange лучше. (:
Я посчитал сразу количество рабочих часов на каждую дату и расчёт длительности для периодов в рабочих часах стал одной операцией вычитания. А у Вас в результате остались итерации по каждому из периодов. Посмотрите на планы исполнения мой и Ваш. Именно по этой причине я и сказал, что Вы не дошли до конца. В философском смысле. Уж если от внутреннего цикла избавляться, то полностью, чего уж там.
И, кстати, именно Ваше решение сподвигло меня на эти размышления и в конечном итоге реализацию такого подхода, как самого оптимального по времени.
Да, Вы пошли именно в этом направлении, но немного не дошли до конца. Можно посчитать один раз количество рабочих часов на каждый день в календаре и дальше уже брать готовый ответ, не считая каждый раз количества рабочих и выходных дней, попадающих в период.
Вот так, например:
with recursive calendar(d, is_working) as (
select '2017-12-31'::date, 0
union all
select d+1
, case when extract(dow from d+1) not in (0,6)
and d+1 <> all('{2019-01-01,2019-01-02,2019-01-03,2019-01-04,2019-01-07,2019-01-08,2019-03-08,2019-05-01,2019-05-02,2019-05-03,2019-05-09,2019-05-10,2019-06-12,2019-11-04,2018-01-01,2018-01-02,2018-01-03,2018-01-04,2018-01-05,2018-01-08,2018-02-23,2018-03-08,2018-03-09,2018-04-30,2018-05-01,2018-05-02,2018-05-09,2018-06-11,2018-06-12,2018-11-05,2018-12-31}')
or d+1 = any('{2018-04-28,2018-06-09,2018-12-29}')
then 1 else 0 end
from calendar where d < '2020-01-01'
), calendar_w(d, is_working, work_hours_acc) as (
select d, is_working
, sum(is_working*'9 hours'::interval) over (order by d range between unbounded preceding and current row)
from calendar
)
select p.*
, c2.work_hours_acc - c1.work_hours_acc
+ ('19:00:00'::time - least(greatest(p.start_time::time,'10:00:00'::time),'19:00:00'::time)) * c1.is_working
- ('19:00:00'::time - least(greatest(p.stop_time::time, '10:00:00'::time),'19:00:00'::time)) * c2.is_working as work_hours
from periods p, calendar_w c1, calendar_w c2
where c1.d = p.start_time::date
and c2.d = p.stop_time::date
Не занимайтесь демагогией. Ваше решение даёт неправильный результат, потому что Вы не все даты из рабочего календаря включили в свой запрос. И чтобы получить правильный результат, надо сперва вдумчиво Ваш запрос дописать. Я на это указывал ещё в комментариях к предыдущей статье. Мне во время проверки приходилось делать исправления (что, признаться, несколько раздражало), так как я вроде бы был заводилой всей этой движухи, а прочим окружающим этим заниматься совершенно неинтересно.
Только другие значения вытаскивать будет тяжеловато, и логику работы тяжело разглядеть.
Для range нужно использовать GiST-индексы: postgrespro.ru/docs/postgresql/11/rangetypes#RANGETYPES-INDEXING
Кстати, да. Куда-то я это потерял во время подготовки статьи… Спасибо, исправил.
Но главную мысль Вы уловили совершенно правильно — с tsrange лучше. (:
И, кстати, именно Ваше решение сподвигло меня на эти размышления и в конечном итоге реализацию такого подхода, как самого оптимального по времени.