Перекрывающиеся диапазоны дат для всех работников

Я пытаюсь выяснить, как рассчитать диапазоны дат (острова), в которых все работники работали все вместе. Другими словами, если один из рабочих не находится на какой-то дате, то эту дату следует исключить из результата. Ниже пример данных:

insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(1, '2019-10-01', '2020-04-30');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(1, '2020-05-01', '2020-07-19');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(1, '2020-10-01', '9999-01-01');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(2, '2019-10-01', '2020-04-30');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(2, '2020-05-01', '2020-07-31');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(2, '2020-11-01', '9999-01-01');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(3, '2018-03-12', '2018-08-20');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(3, '2019-10-01', '2020-04-15');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(3, '2020-07-01', '2020-07-31');
insert into WORK_DAYS(WORKER_ID, DATE_FROM, DATE_TO) VALUES(3, '2020-11-01', '9999-01-01');

пример данных

Я работаю с базой данных Firebird, но вы можете показать результат в любой базе данных, такой как SQL Server (но, пожалуйста, без перекрестного применения только то, что определено в стандарте SQL). Это упрощенные данные, полученные уже из проблемы пробелов/островов. Поскольку действительно worker_id в моем образце — это целая команда.

Я знаю, как найти перекрывающиеся диапазоны дат, но я не знаю, как найти перекрывающиеся диапазоны дат, применяемые ко всем работникам одновременно.


sql firebird firebird-3.0
person Livius    schedule 11.02.2021    source источник

Ответы (1)


arrow_upward
2
arrow_downward

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

На самом деле вам не нужно каждое свидание. Предполагая, что date_to включен в качестве рабочего дня, вы можете развернуть данные и использовать кумулятивную сумму.

Следующее в логике, выраженной в Postgres (ваш вопрос указывает, что решение в любой базе данных приемлемо, и я считаю, что Postges ближе всего к стандартному SQL):

with wd as (
      select worker_id, date_from as dte, 1 as inc
      from work_days wd
      union all
      select worker_id, date_to + interval '1 day', -1 as inc
      from work_days wd
     ),
     wd_cnt as (
      select wd.dte, sum(sum(inc)) over (order by dte) as num_on_date,
             lead(wd.dte) over (order by wd.dte) as next_dte
      from wd
      group by wd.dte
     )
select dte, next_dte - interval '1 day'
from wd_cnt
where num_on_date = (select count(distinct worker_id) from work_days);

Это все стандартный SQL, но функции даты/времени различаются в разных базах данных. Обратите внимание, что это добавляет один день к date_to, поэтому не используйте абсолютную максимальную дату для этого значения.

Вот скрипт db‹›.

person Gordon Linoff    schedule 11.02.2021
comment
Отличное решение! Спасибо. Я вижу, что вы суммируете количество дат и сравниваете с количеством рабочих. Очень просто. Вот ссылка на модифицированное решение скрипки для Firebird 3 dbfiddle.uk/ - person Livius; 11.02.2021
comment
@ Ливиус. . . Я забыл, что db‹›fiddle поддерживает Firebird. Я бы использовал это для начала. Ваше здоровье! - person Gordon Linoff; 11.02.2021
comment
Возможно, я бы даже материализовал это и сделал таблицу-аккумулятор (в FB нет материализованных представлений) структуры {DATE, WORKERS_COUNT}. Затем я бы поставил AFTER UPDATE OR INSERT OR DELETE SQL-триггер на таблицу FB WORK_DAYS, чтобы эта новая таблица всегда автоматически обновлялась. Или лучше {DATE, WORKERS_COUNT_EMPLOYED, WORKERS_COUNT_WORKING}. Конечно, если бы детализация сокращала дни до часов или секунд, этот подход был бы все менее и менее эффективным. Но для простых дней должно быть хорошо. - person Arioch 'The; 12.02.2021