При решении задач анализа событийной информации (проходы через турникеты СКУД, трекинг датчиков, история статусов документов) часто возникает потребность получить в одной строке текущую дату события и дату непосредственно предшествующего ему события по тому же объекту. Классическое решение задачи в языке запросов 1С — соединение таблицы самой с собой с условием неравенства и последующей группировкой.
Посмотрим на исходный пример реализации в лоб:
ВЫБРАТЬ
tmp.Турникет КАК Турникет,
tmp.ДатаСобытия КАК ДатаСобытия,
МАКСИМУМ(tmp1.ДатаСобытия) КАК ДатаПредыдущегоСобытия
ИЗ
РегистрСведений.СобытияСКУД КАК tmp
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СобытияСКУД КАК tmp1
ПО tmp.Турникет = tmp1.Турникет
И tmp.ДатаСобытия > tmp1.ДатаСобытия
СГРУППИРОВАТЬ ПО
tmp.Турникет,
tmp.ДатаСобытия
УПОРЯДОЧИТЬ ПО
Турникет,
ДатаСобытия УБЫВ
На небольших тестовых базах этот запрос работает мгновенно. Однако при росте объема таблицы до сотен тысяч или миллионов записей запрос либо выполняется недопустимо долго, либо приводит к аварийному завершению работы платформы. Разберем причины такого поведения и рассмотрим несколько эффективных способов решения проблемы.
Проанализируем технические ограничения платформы и СУБД, с которыми мы сталкиваемся при таком подходе:
LAG(), LEAD() и конструкции OVER (PARTITION BY ...). Соединение по неравенству (Theta Join) заставляет СУБД сканировать огромное число строк для каждой записи, порождая квадратичную сложность алгоритма O(N²).ТаблицаЗначений на клиенте или 32-разрядном сервере приложение падает при достижении порога памяти 3.5–4 ГБ.Nested Loops в целевой СУБД.Если данные уже находятся внутри информационной базы 1С или могут быть помещены во временные таблицы пакета, ни в коем случае нельзя соединять реальную таблицу регистра саму с собой напрямую. Сначала необходимо отобрать только требуемый диапазон дат во временную таблицу и обязательно построить по ней индекс.
Разберем построение запроса по шагам:
ВЫБРАТЬ
События.Турникет КАК Турникет,
События.ДатаСобытия КАК ДатаСобытия
ПОМЕСТИТЬ ВТ_События
ИЗ
РегистрСведений.СобытияСКУД КАК События
ГДЕ
События.ДатаСобытия МЕЖДУ &ДатаНачала И &ДатаОкончания
ИНДЕКСИРОВАТЬ ПО
Турникет,
ДатаСобытия
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Турникет КАК Турникет,
Т1.ДатаСобытия КАК ДатаСобытия,
МАКСИМУМ(Т2.ДатаСобытия) КАК ДатаПредыдущегоСобытия
ИЗ
ВТ_События КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_События КАК Т2
ПО Т1.Турникет = Т2.Турникет
И Т1.ДатаСобытия > Т2.ДатаСобытия
СГРУППИРОВАТЬ ПО
Т1.Турникет,
Т1.ДатаСобытия
Конструкция ИНДЕКСИРОВАТЬ ПО создает физический индекс во временной базе tempdb, что кардинально ускоряет операцию поиска предыдущих дат по сравнению с прямым обращением к таблице регистра.
Когда число записей превышает несколько миллионов, даже оптимизированный запрос с временными таблицами может занять много времени и перегрузить оперативную память. Зачастую быстрее и надежнее отказаться от самосоединения в запросе и выполнить поиск предыдущей даты алгоритмически при последовательном обходе выборки.
Важное правило: никогда не используйте метод Запрос.Выполнить().Выгрузить() на миллионных массивах! Это мгновенно съедает гигабайты ОЗУ. Используйте метод Выбрать(), который читает данные потоком через серверный курсор.
Посмотрим на пример потокового расчета интервалов:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| События.Турникет КАК Турникет,
| События.ДатаСобытия КАК ДатаСобытия
|ИЗ
| РегистрСведений.СобытияСКУД КАК События
|ГДЕ
| События.ДатаСобытия МЕЖДУ &ДатаНачала И &ДатаОкончания
|УПОРЯДОЧИТЬ ПО
| События.Турникет,
| События.ДатаСобытия ВОЗР";
Запрос.УстановитьПараметр("ДатаНачала", ПериодНачало);
Запрос.УстановитьПараметр("ДатаОкончания", ПериодКонец);
Выборка = Запрос.Выполнить().Выбрать();
ТекущийТурникет = Неопределено;
ПредыдущаяДата = '00010101';
Пока Выборка.Следующий() Цикл
Если Выборка.Турникет <> ТекущийТурникет Тогда
ТекущийТурникет = Выборка.Турникет;
ПредыдущаяДата = '00010101';
КонецЕсли;
Если ЗначениеЗаполнено(ПредыдущаяДата) Тогда
ИнтервалСекунд = Выборка.ДатаСобытия - ПредыдущаяДата;
Если ИнтервалСекунд > 60 Тогда
// Обрабатываем событие, удовлетворяющее критерию
КонецЕсли;
КонецЕсли;
ПредыдущаяДата = Выборка.ДатаСобытия;
КонецЦикла;
Такой цикл имеет линейную сложность O(N). Обработка 3–5 миллионов строк упорядоченной выборки занимает считанные минуты и требует минимального объема оперативной памяти, так как в каждый момент времени в памяти хранится только одна текущая строка.
Если требуется получить результат исключительно запросом, эффективен метод каскадного ограничения. Вместо того чтобы соединять весь миллионный диапазон за месяц или год «все со всеми», соединение выполняется порциями по коротким временным окнам.
Рассмотрим логику метода:
ЕСТЬ NULL).Такой подход заменяет одно тяжелое квадратичное соединение пачкой легких операций по индексу даты, сокращая число сканируемых страниц данных в десятки раз.
Если данные изначально хранятся во внешнем источнике (например, в базе данных системы СКУД на MS SQL Server), переносить миллионы «сырых» строк в 1С для вычисления разницы дат нецелесообразно.
Рассмотрим, как правильно организовать вычисления на стороне источника:
LAG():
CREATE VIEW dbo.vw_TrafficIntervals AS
SELECT
SensorID,
DateCreated,
LAG(DateCreated, 1) OVER (PARTITION BY SensorID ORDER BY DateCreated) AS PrevDateCreated,
DATEDIFF(second, LAG(DateCreated, 1) OVER (PARTITION BY SensorID ORDER BY DateCreated), DateCreated) AS IntervalSec
FROM dbo.Traffic;
dbo.vw_TrafficIntervals.IntervalSec. В 1С попадут только итоговые отфильтрованные данные без необходимости передавать по сети миллионы промежуточных строк.Начиная с версии платформы 8.3.7, появилась возможность использовать временные таблицы непосредственно внутри внешнего источника данных. Платформа создает временную таблицу на стороне СУБД источника (например, в tempdb MS SQL Server).
Синтаксис обращения к такой временной таблице выглядит следующим образом:
ВЫБРАТЬ
Трафик.SensorID КАК SensorID,
Трафик.DateCreated КАК DateCreated
ПОМЕСТИТЬ ВнешнийИсточникДанных.БазаСКУД.ВременнаяТаблица.ВТ_Данные
ИЗ
ВнешнийИсточникДанных.БазаСКУД.Таблица.dbo_Traffic КАК Трафик
ГДЕ
Трафик.DateCreated МЕЖДУ &ДатаНачала И &ДатаОкончания
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.SensorID КАК SensorID,
Т1.DateCreated КАК DateCreated,
МАКСИМУМ(Т2.DateCreated) КАК PrevDateCreated
ИЗ
ВнешнийИсточникДанных.БазаСКУД.ВременнаяТаблица.ВТ_Данные КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВнешнийИсточникДанных.БазаСКУД.ВременнаяТаблица.ВТ_Данные КАК Т2
ПО Т1.SensorID = Т2.SensorID
И Т1.DateCreated > Т2.DateCreated
СГРУППИРОВАТЬ ПО
Т1.SensorID,
Т1.DateCreated
Для корректной работы этого механизма у пользователя СУБД, под которым 1С подключается к внешнему источнику, должны быть явные права на создание временных таблиц в целевой базе данных.