Срез штатного расписания

Материал из Eludia.

Постановка задачи

Допустим, в вашей MySQL-БД имеется таблица кадровых приказов orders, имеющая следующие поля:

dt 

дата;

id_user 

сотрудник;

id_department 

отдел.

Для простоты предположим, что каждую её запись можно считать приказом о приёме (id_department > 0) или увольнении (id_department = 0). На каждого сотрудника в один день не может быть более одного приказа.

Требуется определить множество сотрудников, работавших в отделе $id_department в день $dt.

Таблица orders была бы обычной реляцией между людьми и отделами, если бы не поле dt и, соответственно, параметр $dt. Попытка применить стандартный WHERE-фильтр быстро заводит в тупик.

Конечно, задача решается очень просто (с точки зрения программиста) полным перебором записей на клиенте. Однако такой вариант будет нам стоить как минимум линейного роста времени и сетевого трафика в зависимости от объёма таблицы.

Конретизируем нашу цель: требуется найти список (номеров) сотрудников отдела $id_department на дату $dt за один SQL-запрос без создания временной таблицы.

Случай единственного сотрудника

Рассмотрим для начала двойственную, гораздо более простую задачу: определение места работы сотрудника id_user на день dt. Её решение сразу следует из постановки:

SELECT 
 id_department 
FROM 
 orders
WHERE
 id_user = $id_user
 AND dt <= $dt
ORDER BY
 dt DESC
LIMIT
 1

Этот SQL-запрос исполняется моментально и без промежуточной буферизации, если, конечно, у таблицы есть B-tree индекс по полям (id_user, dt).

Полный срез на заданную дату

Теперь попробуем узнать на дату $dt последние приказы одновременно для всех сотрудников. LIMIT тут не подходит, поскольку на одного человека может выходить по приказу в день, а на другого — один в жизни. Тем не менее, применение GROUP BY позволяет определить по крайней мере дату последнего приказа для каждого сотрудника:

SELECT 
 id_user
 , MAX(dt) 
FROM 
 orders
WHERE
 dt <= $dt
GROUP BY
 id_user

С одной стороны, это хорошо. Такой запрос выполняется за один прямой проход по тому же индексу (id_user, dt), а его результат можно использовать (в силу наших предположений) как ключ в таблице orders. Однако ни в выражение SELECT, ни в фильтр при этом невозможно добавить интересующий нас номер отдела. А строить вложенный SQL не хочется, поскольку в этом случае управлять оптимизацией удаётся очень редко.

Применение синтетических полей

Теперь вспомним, что:

• даты в формате ISO упорядочены по алфавиту;
• если приписать справа к 2 неравным строкам произвольные наборы символов, то их алфавитный порядок не изменится.

Поскольку при фиксированном id_user дубли по дате исключены, мы можем в предыдущем запросе использовать вместо поля dt его конкатенацию с чем угодно. В частности, запрос

SELECT 
 id_user
 , MAX(dt || ' ' id_department) 
FROM 
 orders
WHERE
 dt <= $dt
GROUP BY
 id_user

выдаст записи вида

'1' | '2007-10-01 15'
'2' | '2007-12-05 3'

где присутствует интересующий нас номер отдела. Его легко отделить от даты функцией SUBSTR:

SELECT 
 id_user
 , SUBSTR(MAX(dt || ' ' id_department), 12) AS id_department 
FROM 
 orders
WHERE
 dt <= $dt
GROUP BY
 id_user

'1' | '15'
'2' | '3'

Но в исходной задаче это поле интересовало нас не как результат, а как фильтр. И его вполне можно использовать в этом качестве, подставив в HAVING:

SELECT 
 id_user
FROM 
 orders
WHERE
 dt <= $dt
GROUP BY
 id_user
HAVING
 SUBSTR(MAX(dt || ' ' id_department), 12) = $id_department

Это уже почти то, что нужно... Только комбинация GROUP BY с негрупповыми строковыми функциями гарантирует 'Using temporary; using filesort', от чего, собственно, мы и хотели уйти. Впрочем, осталось совсем немного.

Оптимальное решение

Снова конкретизируем нашу цель. Мы хотим получить тот же результат, что в предыдущем разделе, но только при помощи быстрого прохода по индексу. Как следует из документации, для этого нам следует свести все фильтры и группировку к полям одного индекса. В нашем случае, прежде чем строить индекс, придётся добавить в таблицу специальное поле (назовём его dt_id_department), в которое будем писать соответственно, dt || ' ' || id_department (можно триггером, можно на уровне приложения). Кроме того, определим ключ по (id_user, dt_id_department). После этого запрос

SELECT 
 id_user
FROM 
 orders
WHERE
 dt_id_department <= '$dt z'
GROUP BY
 id_user
HAVING
 SUBSTR(MAX(dt_id_department), 12) = $id_department

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

Заключение

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

dt || ' ' id_department || ' ' id

и включить MAX(dt_id_department) в список SELECT, HAVING-фильтр преобразовать в

MAX(dt_id_department) LIKE '% $id_department %'

В конкретном приложении приказы могут ссылаться не на отделы как таковые, а на связанные с ними должности. Однако это не мешает дублировать данные в нашем дополнительном текстовом поле, так что описанная техника остаётся вполне применимой.

Если в таблице приказов присутствуют записи, которые не должны влиять на срез штатного расписания ( фиктивные записи или, скажем, приказы на отпуска), то для них следует писать в поле dt_id_department значения, заведомо отвергаемые фильтром по дате (например, 'z').