При интеграции 1С со сторонними системами (например, телефонией, АТС, системами видеонаблюдения или логами внешних сервисов) часто возникает необходимость сопоставить данные по дате и времени. Однако время фиксации одного и того же события в разных источниках может не совпадать на несколько секунд из-за рассинхронизации системных часов, задержек при обработке или специфики фиксации звонка. Разберем, как реализовать сопоставление таких записей с допустимым отклонением времени с помощью запроса и программных алгоритмов.
Проанализируем типовую ситуацию: у нас есть таблица значений с файлами записей звонков (содержащая колонку ДатаФайла) и регистр сведений ДетализацияЗвонков (с колонкой Дата). Требуется связать каждую запись файла со звонком в регистре, если разница между их датами составляет не более $\pm 5$ секунд.
Платформа 1С:Предприятие позволяет использовать сложные логические условия и функции для соединения таблиц в секции ПО. Для вычисления интервала между моментами времени применим функцию РАЗНОСТЬДАТ с точностью до секунды.
Рассмотрим базовый пример формирования временной таблицы и её левого соединения с регистром сведений:
ТаблицаСоответствий = Объект.ТаблицаСоответствий.Выгрузить();
Запрос = Новый Запрос;
Запрос.УстановитьПараметр("ТЗ", ТаблицаСоответствий);
Запрос.Текст = "
|ВЫБРАТЬ
| ТЗ.ИмяФайла КАК ИмяФайла,
| ТЗ.ДатаФайла КАК ДатаФайла
|ПОМЕСТИТЬ ВТ_Файлы
|ИЗ
| &ТЗ КАК ТЗ
|;
|/////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТ_Файлы.ИмяФайла КАК ИмяФайла,
| ВТ_Файлы.ДатаФайла КАК ДатаФайла,
| ДетализацияЗвонков.Дата КАК ДатаЗвонка,
| ДетализацияЗвонков.Номер КАК НомерТелефона,
| ДетализацияЗвонков.Регистратор КАК Регистратор
|ИЗ
| ВТ_Файлы КАК ВТ_Файлы
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДетализацияЗвонков КАК ДетализацияЗвонков
| ПО РАЗНОСТЬДАТ(ВТ_Файлы.ДатаФайла, ДетализацияЗвонков.Дата, СЕКУНДА) МЕЖДУ -5 И 5
|";
Выборка = Запрос.Выполнить().Выбрать();
В данном запросе условие связи проверяет, что разница между временем файла и временем звонка укладывается в диапазон от -5 до +5 секунд включительно. Конструкция МЕЖДУ -5 И 5 эквивалентна выражению с двумя сравнениями через оператор И, но делает текст запроса более компактным и читаемым.
Выясним причину, по которой базовый вариант запроса может работать медленно на клиент-серверных базах большого объема. Использование функции РАЗНОСТЬДАТ над полем реальной таблицы базы данных (ДетализацияЗвонков.Дата) делает условие невычисляемым по индексу (Non-SARGable). В этом случае СУБД вынуждена выполнять полное сканирование таблицы регистра (Table Scan) либо проверять условие построчно для всех записей.
Для ускорения выполнения запроса применим следующие оптимизации:
ДОБАВИТЬКДАТЕ к полю временной таблицы, чтобы колонка регистра осталась «чистой» и СУБД смогла применить индекс по дате.ИНДЕКСИРОВАТЬ ПО для полей, участвующих в условиях.Посмотрим на оптимизированный текст запроса:
Запрос = Новый Запрос;
Запрос.УстановитьПараметр("ТЗ", ТаблицаСоответствий);
Запрос.Текст = "
|ВЫБРАТЬ
| ТЗ.ИмяФайла КАК ИмяФайла,
| ТЗ.ДатаФайла КАК ДатаФайла
|ПОМЕСТИТЬ ВТ_Файлы
|ИЗ
| &ТЗ КАК ТЗ
|ИНДЕКСИРОВАТЬ ПО
| ДатаФайла
|;
|/////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТ_Файлы.ИмяФайла КАК ИмяФайла,
| ВТ_Файлы.ДатаФайла КАК ДатаФайла,
| ДетализацияЗвонков.Дата КАК ДатаЗвонка,
| ДетализацияЗвонков.Номер КАК НомерТелефона,
| ДетализацияЗвонков.Регистратор КАК Регистратор
|ИЗ
| ВТ_Файлы КАК ВТ_Файлы
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДетализацияЗвонков КАК ДетализацияЗвонков
| ПО ДетализацияЗвонков.Дата МЕЖДУ ДОБАВИТЬКДАТЕ(ВТ_Файлы.ДатаФайла, СЕКУНДА, -5)
| И ДОБАВИТЬКДАТЕ(ВТ_Файлы.ДатаФайла, СЕКУНДА, 5)
|";
При соединении по диапазону времени в условиях высокой интенсивности событий (например, в кол-центре) возможна ситуация, когда в интервал $\pm 5$ секунд попадут сразу несколько звонков. Это приведет к дублированию строк в результирующей выборке.
Для устранения неоднозначности рекомендуется:
ПО.ВЫБОР КОГДА и сгруппировать данные по минимальному отклонению.Если требуется обработать большой массив данных без риска дублирования записей при коллизиях времени, эффективным решением является программный обход двух отсортированных таблиц за линейное время $O(N + M)$.
Разберем по шагам, как реализовать этот алгоритм:
ТаблицаФайлов.Сортировать("ДатаФайла Возр").
// Предполагается, что ТаблицаФайлов и ТаблицаЗвонков отсортированы по дате по возрастанию
ИндексФайла = 0;
ИндексЗвонка = 0;
ВсегоФайлов = ТаблицаФайлов.Количество();
ВсегоЗвонков = ТаблицаЗвонков.Количество();
Пока ИндексФайла < ВсегоФайлов И ИндексЗвонка < ВсегоЗвонков Цикл
СтрокаФайла = ТаблицаФайлов[ИндексФайла];
СтрокаЗвонка = ТаблицаЗвонков[ИндексЗвонка];
Дельта = СтрокаЗвонка.Дата - СтрокаФайла.ДатаФайла; // Разница в секундах
Если Дельта >= -5 И Дельта <= 5 Тогда
// Найдено соответствие
СтрокаФайла.Регистратор = СтрокаЗвонка.Регистратор;
ИндексФайла = ИндексФайла + 1;
ИндексЗвонка = ИндексЗвонка + 1;
ИначеЕсли Дельта < -5 Тогда
// Звонок произошел значительно раньше файла — переходим к следующему звонку
ИндексЗвонка = ИндексЗвонка + 1;
Иначе
// Звонок произошел позже файла — для текущего файла пары нет
ИндексФайла = ИндексФайла + 1;
КонецЕсли;
КонецЦикла;
Такой подход исключает дублирование записей, работает за минимальное время в оперативной памяти и не нагружает СУБД сложными расчетами неточных соединений.