Покрытие смен в SQL: когда сотрудников меньше необходимого

В расписании есть все смены, но в понедельник с 11 до 12 на линии не хватает двух операторов. Общее число сотрудников за день этого не показывает. Для проверки покрытия надо сопоставить потребность каждого временного слота с людьми, которые работают в этот слот, и сохранить интервалы даже при полном отсутствии смен.

Определите единицу проверки

Пусть план составлен по часам, а начала и окончания смен совпадают с границами этих часов. Тогда сотрудник покрывает слот, если его смена начинается не позже начала слота и заканчивается не раньше его конца. Границы интервалов считаем полуоткрытыми: смена до 10:00 не покрывает слот с 10:00.

Если в системе есть перерывы или начало смены в 10:30, разбейте покрытие на более мелкие интервалы. Для точной проверки создайте границы в каждой точке изменения расписания и потребности. Иначе человек, работавший половину часа, может ошибочно выглядеть доступным весь час.

Считайте людей от таблицы потребности

Начинайте запрос с demand, чтобы слот с нулём сотрудников не исчез. LEFT JOIN находит покрывающие смены, COUNT(DISTINCT employee) считает разных людей. DISTINCT защищает от повторной строки одной смены, но не исправляет саму ошибку расписания: повторения всё равно стоит вынести в контроль качества.

Пример полностью выполняется в SQLite. Время задано минутами от начала одного дня: 540 — 09:00, 600 — 10:00. Для разных дат нужен полный временной ключ либо отдельный день в условии соединения. Здесь три слота и два сотрудника.

WITH demand(start_min,end_min,required) AS (
 VALUES (540,600,2),(600,660,3),(660,720,1)
), shifts(employee,start_min,end_min) AS (
 VALUES ('A',540,660),('B',540,600),('A',540,660)
), coverage AS (
 SELECT d.start_min,d.end_min,d.required,
        COUNT(DISTINCT s.employee) AS actual
 FROM demand d LEFT JOIN shifts s
   ON s.start_min <= d.start_min AND s.end_min >= d.end_min
 GROUP BY d.start_min,d.end_min,d.required
)
SELECT start_min,end_min,required,actual,required-actual AS shortage
FROM coverage WHERE actual < required ORDER BY start_min;

Прочитайте список дефицита

В 09:00 работают A и B, потребность равна двум — дефицита нет. В 10:00 остаётся A при потребности три, не хватает двух. В 11:00 смен нет, поэтому не хватает одного. Результат содержит только последние два слота, с началом, концом, потребностью, фактическим числом людей и дефицитом.

Число назначенных сотрудников ещё не означает взаимозаменяемость. Если нужны разные навыки, добавьте роль в потребность и правила допуска сотрудника. Человека с двумя навыками нельзя одновременно засчитать как двух людей для независимых очередей без правила распределения.

Проверьте крайние ситуации

Добавьте смену, которая заканчивается ровно в начале слота: она не должна попасть в покрытие. Добавьте повтор смены A: число людей не меняется, но отдельная сверка покажет дубль. Затем уберите все смены: каждый слот обязан остаться в отчёте с нулём факта.

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

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

От запроса к рабочей витрине

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

Откройте программу платного курса «Продвинутый SQL: сложные запросы и задачи для собеседований».

Продолжить разбор: SQL: как найти двойное бронирование переговорной.