При работе с данными систем контроля и управления доступом (СКУД) часто возникает задача расчета фактического времени нахождения сотрудников на рабочем месте. Особенность заключается в том, что графики работы могут быть ночными или ненормированными, а сотрудник может покидать территорию несколько раз в течение дня. За базовое правило принимается следующее условие: если сотрудник отсутствует на территории более 6 часов подряд, рабочий день считается завершенным, а следующее событие относится уже к новой смене.
В теории баз данных эта задача относится к известному классу Islands and Gaps («Острова и Пропуски»). Разберем подробно, как реализовать подобную группировку средств языка запросов 1С.
Перед тем как перейти к решениям, проанализируем ограничения платформы. В языке запросов 1С отсутствуют аналитические (оконные) функции, такие как LAG, LEAD или ROW_NUMBER() OVER (...). По этой причине прямое сравнение значений из текущей и предыдущей строк выборки «в один проход» невозможно.
Любые решения этой задачи средствами языка запросов сводятся к неэквивалентным соединениям таблиц (например, Т1.Дата < Т2.Дата) или нумерации временных таблиц. На больших объемах данных (сотни тысяч проходов) неэквивалентные соединения приводят к выполнению операций со сложностью $O(N^2)$, что может создавать значительную нагрузку на СУБД. Поэтому рассмотрим несколько подходов, включая оптимальные варианты построения запросов.
Рассмотрим подход, в котором любое событие прохода расценивается как проявление активности. Если между двумя соседними точками активности проходит более 6 часов, этот интервал фиксируется как «Отсутствие» (дом/отдых). Все периоды между отсутствиями принимаются за рабочее время.
Для корректной работы ночных смен объединим дату и время прохода в единый реквизит Дата.
Разберем структуру запроса по шагам:
Дано, где для каждого события запишем точный момент времени (объединив дату и время).Дано самой с собой.Проанализируем пример запроса:
// 1. Формируем исходные данные с точным моментом времени
ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(Проходы.Период, СЕКУНДА, РАЗНОСТЬДАТ(ДАТАВРЕМЯ(1, 1, 1), Проходы.Время, СЕКУНДА)) КАК Дата,
Проходы.Сотрудник КАК Сотрудник
ПОМЕСТИТЬ ВТ_Дано
ИЗ
РегистрСведений.ПроходыСигур КАК Проходы
ГДЕ
Проходы.Период МЕЖДУ &ДатаН И &ДатаО
;
////////////////////////////////////////////////////////////////////////////////
// 2. Вычисляем интервалы отсутствия более 6 часов (21600 сек)
ВЫБРАТЬ
МАКСИМУМ(Раньше.Дата) КАК От,
Дано.Дата КАК До,
Дано.Сотрудник КАК Сотрудник
ПОМЕСТИТЬ ВТ_Отсутствия
ИЗ
ВТ_Дано КАК Раньше
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Дано КАК Дано
ПО Раньше.Сотрудник = Дано.Сотрудник
И Раньше.Дата < Дано.Дата
СГРУППИРОВАТЬ ПО
Дано.Дата,
Дано.Сотрудник
ИМЕЮЩИЕ
РАЗНОСТЬДАТ(МАКСИМУМ(Раньше.Дата), Дано.Дата, ЧАС) >= 6
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ РАЗЛИЧНЫЕ
&ДатаНачала,
&ДатаНачала,
Проходы.Сотрудник
ИЗ
ВТ_Дано КАК Проходы
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ РАЗЛИЧНЫЕ
&ДатаОкончания,
&ДатаОкончания,
Проходы.Сотрудник
ИЗ
ВТ_Дано КАК Проходы
;
////////////////////////////////////////////////////////////////////////////////
// 3. Формируем итоговые рабочие смены между интервалами отсутствия
ВЫБРАТЬ
МАКСИМУМ(Раньше.До) КАК НачалоСмены,
Отсутствия.От КАК ОкончаниеСмены,
Отсутствия.Сотрудник КАК Сотрудник
ИЗ
ВТ_Отсутствия КАК Отсутствия
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Отсутствия КАК Раньше
ПО Отсутствия.Сотрудник = Раньше.Сотрудник
И Раньше.До < Отсутствия.От
СГРУППИРОВАТЬ ПО
Отсутствия.От,
Отсутствия.Сотрудник
Другой способ сравнения соседних строк — предварительная сортировка и нумерация записей. В этом случае мы связываем запись с номером N и запись с номером N + 1.
Рассмотрим последовательность действий:
ЛЕВОЕ СОЕДИНЕНИЕ таблицы с самой собой по условию: Т1.НомерСтроки + 1 = Т2.НомерСтроки и Т1.Сотрудник = Т2.Сотрудник.N (Выход) и событием N + 1 (Вход). Если разница превышает 6 часов, текущая смена считается закрытой.Пример структуры объединения пар событий:
ВЫБРАТЬ
Т1.Сотрудник КАК Сотрудник,
Т1.Период КАК ВремяВыхода,
Т2.Период КАК ВремяСледующегоВхода,
РАЗНОСТЬДАТ(Т1.Период, Т2.Период, ЧАС) КАК ПерерывВЧасах
ИЗ
ВТ_ПроходыПронумерованные КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПроходыПронумерованные КАК Т2
ПО Т1.Сотрудник = Т2.Сотрудник
И (Т1.НомерСтроки + 1 = Т2.НомерСтроки)
ГДЕ
Т1.Направление = ЗНАЧЕНИЕ(Перечисление.НаправлениеПроходаСигур.Выход)
И Т2.Направление = ЗНАЧЕНИЕ(Перечисление.НаправлениеПроходаСигур.Вход)
С точки зрения производительности эффективен подход поиска «Точек начала смен». Событие гарантированно является началом новой смены, если за последние 6 часов до этого момента у данного сотрудника не было ни одного другого события.
Выясним, как это выглядит в коде запроса:
// Поиск событий, являющихся началом новой смены
ВЫБРАТЬ
Текущее.Сотрудник КАК Сотрудник,
Текущее.Дата КАК ДатаНачалаСмены
ПОМЕСТИТЬ ВТ_НачалаСмен
ИЗ
ВТ_Дано КАК Текущее
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Дано КАК Предшествующее
ПО Текущее.Сотрудник = Предшествующее.Сотрудник
И Предшествующее.Дата < Текущее.Дата
И Предшествующее.Дата >= ДОБАВИТЬКДАТЕ(Текущее.Дата, ЧАС, -6)
ГДЕ
Предшествующее.Дата ЕСТЬ NULL
Получив таблицу ВТ_НачалаСмен, мы можем для каждого прохода сотрудника найти ближайшую к нему дату начала смены (минимальное значение ДатаНачалаСмены, которое больше или равно дате прохода). Это поле используется для итоговой группировки данных по сценарным интервалам.
Если запросы с неэквивалентными соединениями выполняются медленно из-за большого массива данных СКУД, стоит рассмотреть альтернативные инструменты платформы 1С:
Сотрудник, Дата. Далее в цикле Для Каждого Стр Из ТаблицаЗначений за один линейный проход со сложностью $O(N)$ сравниваем время текущей строки с предыдущей. Это решение работает быстрее неэквивалентных соединений на стороне СУБД.ВычислитьВыражение("Дата", , "Предыдущая").