Как в запросе 1С выбрать сотрудников, у которых менялась должность?

← На главную

При работе с кадровыми данными в конфигурациях на платформе 1С:Предприятие (например, в «Управлении производственным предприятием» или «Зарплате и управлении персоналом») разработчики часто сталкиваются с необходимостью анализа истории изменений реквизитов сотрудников. Одной из типовых задач является поиск сотрудников, у которых за время работы менялась должность. На первый взгляд задача кажется простой, но при работе с большими базами данных стандартные решения могут приводить к серьезным проблемам с производительностью.

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

Исходная задача и анализ обсуждения на форуме

Рассмотрим ситуацию, с которой столкнулся автор темы. Ему необходимо получить список сотрудников, у которых изменялась должность в регистре сведений РаботникиОрганизаций. Автор написал первоначальный запрос, в котором попытался сгруппировать данные по сотруднику и должности, ограничив выборку только документами кадрового перемещения:


ВЫБРАТЬ
    РаботникиОрганизаций.Сотрудник,
    РаботникиОрганизаций.Должность
ИЗ
    РегистрСведений.РаботникиОрганизаций КАК РаботникиОрганизаций
ГДЕ
    РаботникиОрганизаций.Регистратор ССЫЛКА Документ.КадровоеПеремещениеОрганизаций
СГРУППИРОВАТЬ ПО
    РаботникиОрганизаций.Сотрудник,
    РаботникиОрганизаций.Должность

Однако такой запрос выводит все уникальные комбинации сотрудника и должности, не давая ответа на вопрос: у кого именно этих должностей было больше одной. Попытка применить условие группировки по сотруднику без включения должности в группу привела к закономерной ошибке СУБД: "Поле не входит в группу...".

Выясним причину этой ошибки. Когда мы используем предложение СГРУППИРОВАТЬ ПО, все поля, выбираемые в секции ВЫБРАТЬ, должны быть либо указаны в группировке, либо обернуты в агрегатные функции (такие как СУММА, КОЛИЧЕСТВО, МАКСИМУМ и др.).

Решение через группировку и конструкцию ИМЕЮЩИЕ

Для того чтобы оставить в результате только тех сотрудников, у которых количество различных должностей превышает единицу, необходимо сгруппировать записи только по полю Сотрудник и применить условие фильтрации к результату группировки с помощью предложения ИМЕЮЩИЕ.

Посмотрим на рабочий вариант запроса, к которому в итоге пришел автор темы:


ВЫБРАТЬ
    РаботникиОрганизаций.Сотрудник,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ РаботникиОрганизаций.Должность) КАК КолвоДолжностей
ИЗ
    РегистрСведений.РаботникиОрганизаций КАК РаботникиОрганизаций
ГДЕ
    РаботникиОрганизаций.Регистратор ССЫЛКА Документ.КадровоеПеремещениеОрганизаций
СГРУППИРОВАТЬ ПО
    РаботникиОрганизаций.Сотрудник
ИМЕЮЩИЕ
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ РаботникиОрганизаций.Должность) > 1

Давайте проанализируем, как работает этот механизм:

Запрос выбирает записи из регистра сведений РаботникиОрганизаций, отсекая их по типу регистратора (только документы перемещения).
Вся таблица группируется по полю Сотрудник. Это означает, что для каждого сотрудника формируется одна строка.
С помощью конструкции КОЛИЧЕСТВО(РАЗЛИЧНЫЕ РаботникиОрганизаций.Должность) СУБД подсчитывает, сколько уникальных должностей зафиксировано для этого сотрудника в отобранных записях.
Инструкция ИМЕЮЩИЕ накладывает пост-фильтр на сгруппированные данные. В результате в итоговую выборку попадают только те сотрудники, у которых количество уникальных должностей строго больше 1.

Важное замечание по стилю кодирования: В оригинальном сообщении автора использовался псевдоним поля КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...) КАК Должность. Это плохая практика. Называя числовое поле именем ссылочного типа, вы усложняете чтение кода другими разработчиками и рискуете получить ошибки при дальнейшей обработке выборки в коде на встроенном языке. Используйте понятные имена, например КолвоДолжностей.

Критика и слабые места простого решения

Хотя предложенный выше запрос решает задачу "в лоб", в реальных условиях эксплуатации крупной базы данных (особенно УПП) он имеет существенные недостатки:

Проблема производительности: Использование функции КОЛИЧЕСТВО(РАЗЛИЧНЫЕ) заставляет СУБД выполнять ресурсоемкую операцию сортировки и удаления дублей в памяти для каждого сотрудника. На таблицах с сотнями тысяч записей этот запрос будет выполняться крайне медленно.
Ограниченность фильтра по регистратору: Фильтрация по типу документа КадровоеПеремещениеОрганизаций упускает случаи, когда сотрудник увольнялся и принимался вновь на другую должность через документ ПриемНаРаботуОрганизаций. Также кадровое перемещение может оформляться без изменения должности (например, при изменении оклада или графика работы), из-за чего запрос будет выполнять лишнюю работу.
Отсутствие деталей: Такой запрос не позволяет узнать, какая должность была у сотрудника изначально, на какую она изменилась и в какой момент времени это произошло. Мы получаем лишь сухой список сотрудников-«бегунков».

Для создания надежного и быстрого решения рассмотрим более продвинутые подходы.

Современный подход: Использование оконных функций (Платформа 1С 8.3.21+)

Если ваша база данных развернута на современной платформе 1С:Предприятие (версии 8.3.21 и выше), мы можем использовать мощный инструмент — оконные функции. Они позволяют сравнивать значения текущей строки со значениями предыдущих или последующих строк без выполнения тяжелых соединений и группировок.

С помощью оконной функции LAG() мы можем получить должность сотрудника из хронологически предыдущей записи и сравнить её с текущей должностью. Разберем этот запрос подробнее:


ВЫБРАТЬ
    РаботникиОрганизаций.Сотрудник КАК Сотрудник,
    РаботникиОрганизаций.Период КАК Период,
    РаботникиОрганизаций.Должность КАК Должность,
    // Оконная функция для получения предыдущего значения должности
    LAG(РаботникиОрганизаций.Должность) OVER (
        PARTITION BY РаботникиОрганизаций.Сотрудник 
        ORDER BY РаботникиОрганизаций.Период
    ) КАК ПредыдущаяДолжность
ПОМЕСТИТЬ ВТ_ИсторияДолжностей
ИЗ
    РегистрСведений.РаботникиОрганизаций КАК РаботникиОрганизаций
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_ИсторияДолжностей.Сотрудник,
    ВТ_ИсторияДолжностей.Период КАК ДатаИзменения,
    ВТ_ИсторияДолжностей.ПредыдущаяДолжность КАК ПредыдущаяДолжность,
    ВТ_ИсторияДолжностей.Должность КАК НоваяДолжность
ИЗ
    ВТ_ИсторияДолжностей КАК ВТ_ИсторияДолжностей
ГДЕ
    // Исключаем первую запись (прием на работу), где предыдущей должности еще не было
    ВТ_ИсторияДолжностей.ПредыдущаяДолжность ЕСТЬ НЕ NULL
    // Фильтруем только те строки, где должность действительно изменилась
    И ВТ_ИсторияДолжностей.Должность <> ВТ_ИсторияДолжностей.ПредыдущаяДолжность

Рассмотрим по шагам работу этого алгоритма:

В первой части запроса мы выбираем сотрудников, периоды и должности из регистра сведений.
Конструкция LAG(РаботникиОрганизаций.Должность) OVER (PARTITION BY ... ORDER BY ...) разбивает всю выборку на "окна" (секции) по каждому сотруднику (PARTITION BY РаботникиОрганизаций.Сотрудник) и сортирует данные внутри окна по дате события (ORDER BY РаботникиОрганизаций.Период). Функция возвращает значение поля Должность из строки, предшествующей текущей. Результат мы помещаем во временную таблицу ВТ_ИсторияДолжностей.
Во втором запросе пакет мы выбираем данные из временной таблицы и накладываем простое условие: Должность <> ПредыдущаяДолжность. Это гарантирует, что мы получим только те события, при которых произошло фактическое изменение должности.

Преимущества метода оконных функций:

Высокая скорость выполнения на стороне СУБД за счет отсутствия многократных сканирований таблиц.
Возможность получить полную картину перевода: точную дату изменения, старую и новую должности.
Корректная обработка циклических изменений. Если сотрудник сначала был Менеджером, потом стал Директором, а затем снова вернулся на должность Менеджера, оконная функция зафиксирует оба эти изменения, тогда как метод с КОЛИЧЕСТВО(РАЗЛИЧНЫЕ) покажет только общее число уникальных должностей (2 штуки).

Классический подход: Соединение таблицы с самой собой (для старых платформ)

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

Проанализируем этот подход по шагам:

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

Посмотрим на пример реализации такого запроса:


ВЫБРАТЬ
    Работники.Сотрудник КАК Сотрудник,
    Работники.Период КАК Период,
    Работники.Должность КАК Должность
ПОМЕСТИТЬ ВТ_ВсеЗаписи
ИЗ
    РегистрСведений.РаботникиОрганизаций КАК Работники
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Т1.Сотрудник КАК Сотрудник,
    Т1.Период КАК ТекущийПериод,
    Т1.Должность КАК ТекущаяДолжность,
    МАКСИМУМ(Т2.Период) КАК ПредыдущийПериод
ПОМЕСТИТЬ ВТ_СопоставлениеПериодов
ИЗ
    ВТ_ВсеЗаписи КАК Т1
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ВсеЗаписи КАК Т2
        ПО Т1.Сотрудник = Т2.Сотрудник
        И Т2.Период < Т1.Период
СГРУППИРОВАТЬ ПО
    Т1.Сотрудник,
    Т1.Период,
    Т1.Должность
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ.Сотрудник КАК Сотрудник,
    ВТ.ТекущийПериод КАК ДатаИзменения,
    РаботникиПредыдущие.Должность КАК ПредыдущаяДолжность,
    ВТ.ТекущаяДолжность КАК НоваяДолжность
ИЗ
    ВТ_СопоставлениеПериодов КАК ВТ
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.РаботникиОрганизаций КАК РаботникиПредыдущие
        ПО ВТ.Сотрудник = РаботникиПредыдущие.Сотрудник
        И ВТ.ПредыдущийПериод = РаботникиПредыдущие.Период
ГДЕ
    ВТ.ТекущаяДолжность <> РаботникиПредыдущие.Должность

Разберем логику этого пакета запросов:

В таблице ВТ_ВсеЗаписи мы собираем сырые данные по сотрудникам, датам и должностям.
Во временной таблице ВТ_СопоставлениеПериодов мы соединяем таблицу саму с собой по условию Т2.Период < Т1.Период. Использование группировки и агрегатной функции МАКСИМУМ(Т2.Период) позволяет нам для каждого кадрового события найти дату его непосредственно предшествующего события.
В финальном запросе мы берем полученную пару дат (текущую и предыдущую) и с помощью внутреннего соединения с регистром РаботникиОрганизаций получаем наименование должности на предыдущую дату. Условие в секции ГДЕ оставляет только те записи, в которых должности различаются.

Этот способ работает стабильно на любых версиях платформы 1С и СУБД. Он значительно превосходит по производительности решение с КОЛИЧЕСТВО(РАЗЛИЧНЫЕ) на больших массивах данных, так как СУБД гораздо эффективнее работает со связями по индексам (а поля Сотрудник и Период в периодических регистрах сведений всегда проиндексированы платформой).

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