Как подсчитать количество врачей, работающих в неделю?

Question

[gelirgwenn]

Доброго времени суток, есть две таблицы в MySQL:
Первая - список врачей, структура (id, full_name, specialty, status), около 1000 записей.
Вторая - выходные врачей (id, doctor_id, start_date, end_date), около 50 записей-интервалов времени на одного врача (всего около 50000 записей). start_date, end_date - unix timestamp формата.
Нужно подсчитать кол-во врачей, которые работают, т.е. не находятся в отпуске/выходном для заданного интервала времени, например, недели.
Если нужно найти кол-во хирургов для заданного интервала, например inputInterval с границами также в unix timestamp - все просто, запрос вида с использованием в условии проверки на непересечение интервалов работает отлично:

SELECT COUNT(doctors.id) AS total_doctors, doctors.speciality
FROM doctors
WHERE NOT EXISTS (
     SELECT holidays.id
     FROM holidays
     WHERE holidays.doctor_id = doctors.id
     AND holidays.start_date > inputInterval.end_date OR holidays.end_date < inputInterval.start_date
)
GROUP BY doctors.specialty
HAVING doctors.speciality = "хирург"

Но, если ни одного врача не найдено - то нужно предложить несколько других временных промежутков, когда хотя бы 1-2 хирурга работают. Вот тут у меня проблема, подскажите, куда копать, кто сталкивался с таким.
У меня назрел вариант решения, но мне кажется - кривой.
1. Сделать выборку всех хирургов из таблицы докторов
2. Выбрать все выходные, end_date которых больше сегодняшнего дня для хирургов
3. Сформировать массив вида doctor_id => список интервалов выходных
4. Выполнить в цикле слияние всех интервалов, чтобы из интервалов вида 01/02 - 01/03 и 01/02 - 01/04 получился один интервал 01/02 - 01/04, т.е. объединить интервалы, которые входят в другие интервалы.
5. Потом в цикле отсортировать все интервалы по start_date, и вычислить свободные интервалы, типа сложить end_date i-ой записи с start_date i+1 записи.
6. Пройтись циклом по всем свободным интервалам и вычислить, сколько докторов не сходится с данным интервалом, используя выборку из пункта 2.
Может кто знает иное решение? Буду очень благодарен за помощь! Хотя бы идею, реализовывать сам буду

Answer 1

[Boris Köln]

запрос вида с использованием в условии проверки на непересечение интервалов работает отлично

Значит, плохо тестировали. Этот запрос не работает для отпусков, которые...
- начались до диапазона и заканчиваются после диапазона
- ровно совпадают с диапазоном
- начинаются или заканчиваются внутри диапазона

Comments

[gelirgwenn]

Согласен, вы правы, я в попыхах неправильно составил условие, поскольку я использую здесь оператор NOT EXISTS, то мне следует искать пересечение, т.е. условие holidays.start_date < inputInterval.end_date AND holidays.end_date > inputInterval.start_date. Или, оставить условие прежним, но использовать скобки и оператор JOIN. Но вопрос тут не о том, хотя спасибо за то, что нашли ошибку в запросе.

[Boris Köln]

Для начала надо четко сформулировать задачу. Что такое "работает в промежуток"? Весь промежуток или хотя бы один день?
Потом написать для этого условие.

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

Можно написать и SQL, но он будет достаточно сложным. Проще это реализовать на PHP: вызвать слегка модифицированный предыдущий SQL, который возвращает не кол-во докторов, а диапазоны, где есть работающий доктор: group by ... having count(*) > 0

[gelirgwenn]

Boris Korobkov, Тут вот какая штука: на дворе январь и хирурги все в отпусках, до 17.02, к примеру. Пользователь запросил врачей на 18 января, по результатам поиска - пусто, все отдыхают. Странно, что все пошли в отпуск, но все-таки, это для примера. Мне нужно выдать пользователю, что ближайший день работы этих лентяев - 18.02, и работать их будет 2 штуки, к примеру. Исходя из этого я решил вытащить из графика отпусков все записи, начиная от сегодняшней даты до +3 месяца. Это я получил интервалы выходных. Я могу преобразовать их в интервалы рабочих дней, соединив конец интервала X с началом интервала Y и сформировать массив:

$results = array(
          0                  => array("start"=> 1455652222, "end"=>18885661),
          .......
)

А затем нужно пройти массив, если интервал входит в другой интервал, то докторов уже два работает на этом интервале. Скрипт получается довольно сложный. И из базы надо тянуть кучу записей и проходить по всем несколько раз. Это меня и смущает, больше ничего не смог придумать. Вы это имели ввиду?

[Boris Köln]

Вариант 1 (простой в реализации, но неэффективный).
Вызвать функцию подсчета кол-ва врачей в заданный интервал.
Если 0, то сдвинуть на неделю и вызвать еще раз с новым интервалом.
Повторять, пока не найдено или пока дата не превысит разумный дедлайн.

Вариант 2 (эффективный):
написать SQL, который находит все диапазоны за N месяцев вперед, где работает хоть один врач. В этом вам поможет group by ... having count(*) > 0, ну и учесть все мои замечания про пересечения диапазонов. Делать это бесплатно за вас нет желания.

[gelirgwenn]

Boris Korobkov, благодарствую, конечно, но
Вариант 1 жесткий до безобразия, обалдеть делать столько запросов, если период поиска до полугода. Любой сервер захлебнется.
Вариант 2 тоже жесткий, вы представьте, сколько диапазонов может быть за полгода? Чтобы предусмотреть все диапазоны - это надо написать целый генератор. Условие "хотя бы один" мне не подходит, мне надо знать точно, сколько парней работают.
Я реализую свое решение, при добавлении каждого отпуска буду высчитывать день последнего выходного для сотрудника и рассчитывать интервалы работы, их может быть от 1 до двух при каждом добавлении, записывать сие дело в таблицу рабочих дней, я потом написать скрипт поиска оптимальных диапазонов, когда нужное кол-во врачей работает - не составит труда. Это легче и менее ресурсоемко, чем генерировать все возможные интервалы за полгода и сравнивать с ними. Протестирую, проверю, может я и не прав. Только практика. Я вот ваше выпячивание ЧСВ мне совсем не понравилось. За меня никто вас ничего не просил делать. Мне нужен был совет, вы дали свои, я решил пойти по своему пути, возможно вернусь к вашим вариантам. Но, в любом случае, здесь не фриланс, во всяком случае, я ничего такого не просил.

[Boris Köln]

Во втором варианте не надо никаких генераторов. Нужен единственный SQL с group by.
100 врачей максимум по 4 отпуска в год - это 400 записей. Запрос будет работать пару миллисекунд.

мне надо знать точно, сколько парней работают.

И что в этом сложного? select count(*) from ... group by ...

Answer 2

[Павел Беляев]

SELECT COUNT(doctors.id) AS total_doctors, doctors.speciality
JOIN holidays ON holidays.doctor_id = doctors.id AND (holidays.start_date > inputInterval.end_date OR holidays.end_date < inputInterval.start_date)

Comments

[gelirgwenn]

Согласен с вами, как и с комментатором выше, спасибо за то, что нашли ошибку. Но вопрос в другом, если вспомнили статьи со схожей задачей (подсчет сотрудников не по заданному интервалу, а возможные интервалы, когда они свободны за полгода, скажем), буду благодарен за ссылочку.

[Boris Köln]

SELECT COUNT(doctors.id) AS total_doctors, doctors.speciality

Нельзя использовать одновременно аггрегатирующие функции и обычные поля без группировки.