Как в запросе 1С объединить записи входа и выхода в одну строку?

Программист 1С v8.3 (Управляемые формы) Управленческий учет IT и автоматизация бизнеса
← На главную

В практике разработки на платформе 1С:Предприятие 8 часто возникает задача трансформации линейного лога событий в структурированный отчет. Типичный пример — данные из систем контроля и управления доступом (СКУД). В регистр сведения события записываются последовательно: одна строка — «Вход», другая строка — «Выход». Для анализа рабочего времени нам необходимо «схлопнуть» эти записи, чтобы в одной строке была информация о сотруднике, времени его прихода и времени ухода.

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

Способ 1. Соединение по условию соответствия дат с группировкой

Этот метод является наиболее интуитивно понятным. Его суть заключается в том, чтобы для каждого события «Вход» найти все последующие события «Выход» того же сотрудника, а затем из всех найденных выходов выбрать самый ранний (минимальный).

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

  1. Поместим все записи о входах во временную таблицу ВТ_Входы.
  2. Поместим все записи о выходах во временную таблицу ВТ_Выходы.
  3. Выполним ЛЕВОЕ СОЕДИНЕНИЕ входов с выходами по сотруднику и условию, что дата выхода больше даты входа.
  4. Сгруппируем результат по сотруднику и дате входа, вычислив МИНИМУМ(ДатаВыхода).

Посмотрим на пример кода, реализующий эту логику:


// 1. Формируем исходные данные (например, из таблицы значений)
ВЫБРАТЬ
    ТЗ.Организация КАК Организация,
    ТЗ.Сотрудник КАК Сотрудник,
    ТЗ.Дата КАК Дата,
    ТЗ.Направление КАК Событие
ПОМЕСТИТЬ ВТ_Данные
ИЗ
    &ТЗСписка КАК ТЗ;

////////////////////////////////////////////////////////////////////////////////
// 2. Отбираем только входы
ВЫБРАТЬ
    ВТ_Данные.Организация,
    ВТ_Данные.Сотрудник,
    ВТ_Данные.Дата КАК ДатаВхода
ПОМЕСТИТЬ ВТ_Входы
ИЗ
    ВТ_Данные КАК ВТ_Данные
ГДЕ
    ВТ_Данные.Событие = "Вход";

////////////////////////////////////////////////////////////////////////////////
// 3. Отбираем только выходы
ВЫБРАТЬ
    ВТ_Данные.Организация,
    ВТ_Данные.Сотрудник,
    ВТ_Данные.Дата КАК ДатаВыхода
ПОМЕСТИТЬ ВТ_Выходы
ИЗ
    ВТ_Данные КАК ВТ_Данные
ГДЕ
    ВТ_Данные.Событие = "Выход";

////////////////////////////////////////////////////////////////////////////////
// 4. Соединяем и находим ближайший выход
ВЫБРАТЬ
    ВТ_Входы.Организация,
    ВТ_Входы.Сотрудник,
    ВТ_Входы.ДатаВхода,
    МИНИМУМ(ВТ_Выходы.ДатаВыхода) КАК ДатаВыхода
ИЗ
    ВТ_Входы КАК ВТ_Входы
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Выходы КАК ВТ_Выходы
        ПО ВТ_Входы.Организация = ВТ_Выходы.Организация
            И ВТ_Входы.Сотрудник = ВТ_Выходы.Сотрудник
            И ВТ_Входы.ДатаВхода < ВТ_Выходы.ДатаВыхода
СГРУППИРОВАТЬ ПО
    ВТ_Входы.Организация,
    ВТ_Входы.Сотрудник,
    ВТ_Входы.ДатаВхода

Важный нюанс: использование ЛЕВОЕ СОЕДИНЕНИЕ критически важно. Если использовать внутреннее соединение, то сотрудники, которые вошли, но еще не вышли (например, в конце смены), вообще не попадут в отчет. При левом соединении поле ДатаВыхода у них останется пустым, что позволит увидеть незавершенные события.

Способ 2. Использование нумерации строк (Автономерзаписи)

Если система СКУД работает строго и гарантирует последовательность «Вход-Выход-Вход-Выход» без пропусков, можно использовать более производительный метод нумерации. Соединение по целым числам (номерам строк) работает быстрее, чем сравнение дат.

Выясним причину эффективности этого метода: СУБД гораздо проще сопоставить Номер = Номер + 1, чем выполнять поиск минимального значения в диапазоне дат. Для реализации в 1С используется ключевое слово АВТОНОМЕРЗАПИСИ() во временных таблицах.

Рассмотрим шаги реализации:

  1. Создаем временную таблицу со всеми событиями, отсортированными по сотруднику и дате, и добавляем поле АВТОНОМЕРЗАПИСИ().
  2. Выбираем из этой таблицы только «Входы».
  3. Присоединяем к ней эту же таблицу, но фильтруем по «Выходам» и условию ВТ1.НомерЗаписи = ВТ2.НомерЗаписи - 1.

Пример реализации нумерации:


ВЫБРАТЬ
    События.Сотрудник,
    События.Период,
    События.Направление,
    АВТОНОМЕРЗАПИСИ() КАК НомерСтроки
ПОМЕСТИТЬ ВТ_Нумерованная
ИЗ
    РегистрСведений.СобытияСКУД КАК События
УПОРЯДОЧИТЬ ПО
    События.Сотрудник,
    События.Период;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ1.Сотрудник,
    ВТ1.Период КАК ВремяВхода,
    ВТ2.Период КАК ВремяВыхода
ИЗ
    ВТ_Нумерованная КАК ВТ1
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Нумерованная КАК ВТ2
        ПО ВТ1.Сотрудник = ВТ2.Сотрудник
            И ВТ1.НомерСтроки = ВТ2.НомерСтроки - 1
ГДЕ
    ВТ1.Направление = ЗНАЧЕНИЕ(Перечисление.Направления.Вход)

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

Способ 3. Коррелированные подзапросы

В некоторых случаях, особенно при работе на SQL Server, использование подзапроса непосредственно в списке выбора (ВЫБРАТЬ) может оказаться эффективнее группировки. Проанализируем такую структуру:


ВЫБРАТЬ
    Входы.Сотрудник,
    Входы.ДатаВхода КАК ДатаВхода,
    (ВЫБРАТЬ МИНИМУМ(Выходы.Дата) 
     ИЗ РегистрСведений.События КАК Выходы 
     ГДЕ Выходы.Сотрудник = Входы.Сотрудник 
     И Выходы.Событие = "Выход"
     И Выходы.Дата > Входы.ДатаВхода) КАК ДатаВыхода
ИЗ
    ВТ_Входы КАК Входы

Такой подход избавляет от необходимости делать массивное соединение «всех со всеми» перед группировкой. Система для каждой строки входа выполнит точечный поиск одного минимального значения.

Оптимизация и решение проблем производительности

Рассмотрим ситуацию, когда данных в регистре очень много (сотни тысяч строк). Соединение по условию ДатаВхода < ДатаВыхода создает так называемую квадратичную сложность. Для каждого входа в начале месяца программа будет просматривать все выходы до конца месяца.

Чтобы избежать замедления, рекомендуется ограничить «окно поиска» выхода. Если мы знаем, что смена не может длиться более 24 часов, добавим условие:

ПО ВТ_Входы.Сотрудник = ВТ_Выходы.Сотрудник И ВТ_Выходы.ДатаВыхода МЕЖДУ ВТ_Входы.ДатаВхода И ДОБАВИТЬКДАТЕ(ВТ_Входы.ДатаВхода, ЧАС, 24)

Это значительно сузит объем данных, которые СУБД нужно перебирать при соединении таблиц.

Алгоритмический подход (обработка в коде)

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

  1. Запросом получаем все записи, отсортированные по Сотрудник, Дата.
  2. Выгружаем результат в ТаблицаЗначений.
  3. Циклом Для Каждого Стр Из ТЗ Цикл обходим таблицу.
  4. Запоминаем время входа в переменную. Когда встречаем следующую строку с типом «Выход» для того же сотрудника — формируем строку в результирующей таблице.

Этот метод работает со скоростью $O(n)$, то есть пропорционально количеству строк, и часто оказывается быстрее самых оптимизированных запросов на огромных массивах данных, так как выполняется всего один проход по выборке.

Итоги

Мы рассмотрели три основных способа объединения записей входа и выхода. Выбор конкретного метода зависит от объемов ваших данных и строгости контроля в системе СКУД. Для небольших отчетов идеально подходит Способ 1 с группировкой по минимальной дате. Для высоконагруженных систем с миллионами записей лучше использовать Способ 2 с нумерацией строк или ограничение интервалов дат в соединениях.

← На главную