Работает. Хотя преобразования чисел и дат в строку и обратно — это не очень надёжное допущение, вдруг формат даты в настройках базы изменён — и приплыли.
Можно. Для этого даже 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
Не занимайтесь демагогией. Ваше решение даёт неправильный результат, потому что Вы не все даты из рабочего календаря включили в свой запрос. И чтобы получить правильный результат, надо сперва вдумчиво Ваш запрос дописать. Я на это указывал ещё в комментариях к предыдущей статье. Мне во время проверки приходилось делать исправления (что, признаться, несколько раздражало), так как я вроде бы был заводилой всей этой движухи, а прочим окружающим этим заниматься совершенно неинтересно.
Не ждите слишком многого. Разбор решения будет интересен скорее тем, кто не представляет себе, как такие задачи вообще решаются. Для Вас, соответственно, это будет слишком тривиально.
Интереса ради померял оба ваших решения на скорость. Да, ваши запросы действительно на (десятичный) порядок быстрее. 10% не уловил, более-менее равная производительность на количестве периодов около 3 тыс. Ну может чуть быстрее, но это уже сравнимо с погрешностями измерений.
Но пожалуй производительность тут не была основным критерием, если только запрос не тормозил слишком уж чрезмерно. Интереса ради я написал запрос, который работает быстрее ваших ещё на (десятичный) порядок, избавившись от суммы по интервалам, и получив скорость работы линейно зависящую только от количества периодов. Видимо придётся добавить в разбор задачи слова про производительность.
Только другие значения вытаскивать будет тяжеловато, и логику работы тяжело разглядеть.
Для range нужно использовать GiST-индексы: postgrespro.ru/docs/postgresql/11/rangetypes#RANGETYPES-INDEXING
Кстати, да. Куда-то я это потерял во время подготовки статьи… Спасибо, исправил.
Но главную мысль Вы уловили совершенно правильно — с tsrange лучше. (:
И, кстати, именно Ваше решение сподвигло меня на эти размышления и в конечном итоге реализацию такого подхода, как самого оптимального по времени.
Но пожалуй производительность тут не была основным критерием, если только запрос не тормозил слишком уж чрезмерно. Интереса ради я написал запрос, который работает быстрее ваших ещё на (десятичный) порядок, избавившись от суммы по интервалам, и получив скорость работы линейно зависящую только от количества периодов. Видимо придётся добавить в разбор задачи слова про производительность.