Здравствуйте, в эфире снова Радио SQL! Разминайте ганглии, расправляйте псевдоподии (или наоборот?) и настраивайтесь на нашу гравитационную волну!
В прошлый раз меня чуть было не подвергли остракизму за разбор (https://habr.com/ru/post/359064/) олимпиадной задачи на SQL, якобы она была недостаточно приближена к жизни. Как будто теги «ненормальное программирование» и «олимпиада» не говорят сами за себя. Но, очевидно, теги никто не читает! И тем не менее, я всё равно продолжу тему разбора задачек на замечательном языке программирования SQL. Потому что лапки (чешутся).
Сегодня нас ждёт задача исключительно жЫзненная, и даже практически рабочая. С ней я столкнулся, пытаясь посчитать выполнение SLA по заявкам от любивных пользователей. Суть исходной проблемы в следующем: надо было посчитать продолжительность работы по каждой заявке и сравнить с тем, что мы обещали. Всё бы ничего, но время в обязательствах было декларировано рабочее, а из изменений статусов в заявках я мог получить только календарное. И тут – мысль! Вот же она, задачечка! Не слишком сложная, но и не совсем тривиальная. Как раз чтобы размять центральные отделы ваших вегетативных нервных систем, сделав их более симпатическими!
Итак, сформулирую условие.
Есть несколько временных интервалов, заданных датой-временем своего начала и конца (пример в синтаксисе PostgreSQL):
Требуется в один SQL-запрос (ц) вычислить продолжительность каждого интервала в рабочих часах. Считаем, что рабочими у нас являются будние дни с понедельника по пятницу, рабочее время всегда с 10:00 до 19:00. Кроме того, в соответствии с производственным календарём РФ существует некоторое количество официальных праздничных дней, которые рабочими не являются, а какие-то из выходных дней, наоборот, являются рабочими из-за переноса тех самых праздников. Укороченность предпраздничных дней учитывать не надо, считаем их полными. Так как праздничные дни год от года меняются, то есть задаются явным перечислением, то ограничимся датами только из 2018 и 2019 годов. Уверен, что при необходимости решение можно будет легко дополнить.
Надо к исходным периодам из periods добавить один столбец с продолжительностью в рабочих часах. Вот какой должен получиться результат:
Исходные данные на корректность не проверяем, считаем всегда start_time <= stop_time.
Специальными конструкциями диалекта SQL от PostgreSQL пользоваться можно, но злоупотреблять не надо. Для полной корректности условия дополню, что запрос должен выполняться на PostgreSQL версии 10 или старше.
Через месяц будет разбор задачи. Решения не привожу, чтобы был стимул порешать самостоятельно. Убедительная просьба — код в комментариях размещайте под спойлерами!
Last but not least. Если уж меня угораздило разместить эту статью в корпоративном блоге Postgres Professional, то воспользуемся некоторыми корпоративными плюшками: за самое интересное решение этой задачи разыграем бесплатный поход на PGConf.Russia 2020. Критерии интересности будут лично мои, плюс тех из коллег, с кем я сочту нужным посоветоваться. Удачи!
UPDATE! Что-то я смотрю, что рабочие часы воспринимаются почему-то исключительно без рабочих минут. Минуты не забывайте! Я поправил исходные интервалы и ожидаемый ответ, чтобы подчеркнуть наличие минут.
UPDATE2 подведение итогов: https://habr.com/ru/company/postgrespro/blog/457722/.
В прошлый раз меня чуть было не подвергли остракизму за разбор (https://habr.com/ru/post/359064/) олимпиадной задачи на SQL, якобы она была недостаточно приближена к жизни. Как будто теги «ненормальное программирование» и «олимпиада» не говорят сами за себя. Но, очевидно, теги никто не читает! И тем не менее, я всё равно продолжу тему разбора задачек на замечательном языке программирования SQL. Потому что лапки (чешутся).
Сегодня нас ждёт задача исключительно жЫзненная, и даже практически рабочая. С ней я столкнулся, пытаясь посчитать выполнение SLA по заявкам от любивных пользователей. Суть исходной проблемы в следующем: надо было посчитать продолжительность работы по каждой заявке и сравнить с тем, что мы обещали. Всё бы ничего, но время в обязательствах было декларировано рабочее, а из изменений статусов в заявках я мог получить только календарное. И тут – мысль! Вот же она, задачечка! Не слишком сложная, но и не совсем тривиальная. Как раз чтобы размять центральные отделы ваших вегетативных нервных систем, сделав их более симпатическими!
Итак, сформулирую условие.
Есть несколько временных интервалов, заданных датой-временем своего начала и конца (пример в синтаксисе PostgreSQL):
with periods(id, start_time, stop_time) as (
values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
)Требуется в один SQL-запрос (ц) вычислить продолжительность каждого интервала в рабочих часах. Считаем, что рабочими у нас являются будние дни с понедельника по пятницу, рабочее время всегда с 10:00 до 19:00. Кроме того, в соответствии с производственным календарём РФ существует некоторое количество официальных праздничных дней, которые рабочими не являются, а какие-то из выходных дней, наоборот, являются рабочими из-за переноса тех самых праздников. Укороченность предпраздничных дней учитывать не надо, считаем их полными. Так как праздничные дни год от года меняются, то есть задаются явным перечислением, то ограничимся датами только из 2018 и 2019 годов. Уверен, что при необходимости решение можно будет легко дополнить.
Надо к исходным периодам из periods добавить один столбец с продолжительностью в рабочих часах. Вот какой должен получиться результат:
id | start_time | stop_time | work_hrs
----+---------------------+---------------------+----------
1 | 2019-03-29 07:00:00 | 2019-04-08 14:00:00 | 58:00:00
2 | 2019-04-10 07:00:00 | 2019-04-10 20:00:00 | 09:00:00
3 | 2019-04-11 12:00:00 | 2019-04-12 16:07:12 | 13:07:12
4 | 2018-12-28 12:00:00 | 2019-01-16 16:00:00 | 67:00:00Исходные данные на корректность не проверяем, считаем всегда start_time <= stop_time.
Специальными конструкциями диалекта SQL от PostgreSQL пользоваться можно, но злоупотреблять не надо. Для полной корректности условия дополню, что запрос должен выполняться на PostgreSQL версии 10 или старше.
Через месяц будет разбор задачи. Решения не привожу, чтобы был стимул порешать самостоятельно. Убедительная просьба — код в комментариях размещайте под спойлерами!
Last but not least. Если уж меня угораздило разместить эту статью в корпоративном блоге Postgres Professional, то воспользуемся некоторыми корпоративными плюшками: за самое интересное решение этой задачи разыграем бесплатный поход на PGConf.Russia 2020. Критерии интересности будут лично мои, плюс тех из коллег, с кем я сочту нужным посоветоваться. Удачи!
UPDATE! Что-то я смотрю, что рабочие часы воспринимаются почему-то исключительно без рабочих минут. Минуты не забывайте! Я поправил исходные интервалы и ожидаемый ответ, чтобы подчеркнуть наличие минут.
UPDATE2 подведение итогов: https://habr.com/ru/company/postgrespro/blog/457722/.
