При разработке отчетов и обработок в 1С нередко возникает задача: есть таблица с контрольными (граничными) датами и детальная таблица с ежедневными данными и числовыми показателями. Требуется распределить показатели второй таблицы по интервалам, заданным первой таблицей, и получить итоговые суммы для каждой граничной даты.
Проанализируем ситуацию на конкретном примере. Имеются две таблицы:
В результате мы должны получить:
Выясним причину, почему эта задача вызывает затруднения: в языке запросов платформы 1С:Предприятие отсутствуют стандартные оконные функции SQL (такие как LAG, LEAD или OVER (PARTITION BY...)). Поэтому расчет интервалов и накопительных сумм строится через группировки и соединения по неравенству. Рассмотрим подробнее несколько способов решения этой задачи.
Разберем по шагам наиболее универсальный подход в запросе. Чтобы распределить детальные записи по интервалам, для каждой даты из первой таблицы необходимо определить дату, предшествующую ей. Тогда каждая контрольная дата сформирует полуинтервал: (ПредыдущаяДата, ТекущаяДата].
ВТ_Таблица1 и ВТ_Таблица2 и обязательно создадим индексы по полям дат.ТаблицаПредыдущих.Дата < Таблица1.Дата.МАКСИМУМ(ТаблицаПредыдущих.Дата). Для самой первой даты интервала предыдущей даты не найдется, поэтому функция вернет NULL, который мы заменим на минимальную дату через функцию ЕСТЬNULL.Посмотрим на пример текста запроса, реализующего данный алгоритм:
ВЫБРАТЬ
Таблица1.Дата КАК ДатаКонца,
ЕСТЬNULL(МАКСИМУМ(ТаблицаПредыдущих.Дата), ДАТАВРЕМЯ(1, 1, 1)) КАК ДатаНачала
ПОМЕСТИТЬ ВТ_Интервалы
ИЗ
ВТ_Таблица1 КАК Таблица1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Таблица1 КАК ТаблицаПредыдущих
ПО (ТаблицаПредыдущих.Дата < Таблица1.Дата)
СГРУППИРОВАТЬ ПО
Таблица1.Дата
ИНДЕКСИРОВАТЬ ПО
ДатаНачала,
ДатаКонца
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Интервалы.ДатаКонца КАК Дата,
ЕСТЬNULL(СУММА(Таблица2.Значение), 0) КАК Значение
ИЗ
ВТ_Интервалы КАК Интервалы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Таблица2 КАК Таблица2
ПО (Таблица2.Дата > Интервалы.ДатаНачала)
И (Таблица2.Дата <= Интервалы.ДатаКонца)
СГРУППИРОВАТЬ ПО
Интервалы.ДатаКонца
УПОРЯДОЧИТЬ ПО
Дата
Такой подход нагляден, легко отлаживается и позволяет однозначно определить границы периодов любой длительности, включая неравномерные шаги дат.
Рассмотрим альтернативную логику, основанную на расчете нарастающего итога. Сначала для каждой даты первой таблицы рассчитывается полная сумма всех записей из второй таблицы от начала времен до указанной даты включительно. Затем из каждого нарастающего итога вычитается нарастающий итог предыдущей точки.
Разберем по шагам расчет на числовом примере:
Посмотрим, как выглядит реализация расчета накопительного итога в запросе 1С:
ВЫБРАТЬ
Таблица1.Дата КАК Дата,
ЕСТЬNULL(СУММА(Таблица2.Значение), 0) КАК НакопленныйИтог
ПОМЕСТИТЬ ВТ_Накопления
ИЗ
ВТ_Таблица1 КАК Таблица1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Таблица2 КАК Таблица2
ПО (Таблица2.Дата <= Таблица1.Дата)
СГРУППИРОВАТЬ ПО
Таблица1.Дата
ИНДЕКСИРОВАТЬ ПО
Дата
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Текущие.Дата КАК Дата,
Текущие.НакопленныйИтог - ЕСТЬNULL(МАКСИМУМ(Предыдущие.НакопленныйИтог), 0) КАК Значение
ИЗ
ВТ_Накопления КАК Текущие
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Накопления КАК Предыдущие
ПО (Предыдущие.Дата < Текущие.Дата)
СГРУППИРОВАТЬ ПО
Текущие.Дата,
Текущие.НакопленныйИтог
УПОРЯДОЧИТЬ ПО
Дата
Проанализируем важный технический аспект работы СУБД с такими запросами. Соединения по условиям <=, < или МЕЖДУ выполняются значительно медленнее, чем эквисоединения по точному равенству (=). СУБД не может применить алгоритм соединений хешированием (Hash Match) и вынуждена использовать соединения вложенными циклами (Nested Loops) или слиянием (Merge Join), что при больших объемах данных приводит к резкому росту нагрузки на процессор и диск.
Чтобы избежать падения производительности, соблюдайте следующие правила:
ИНДЕКСИРОВАТЬ ПО ПолеДата позволяет оптимизатору СУБД использовать поиск по индексу вместо сканирования всей таблицы целиком.Если обе таблицы уже получены во встроенном языке (например, хранятся в объектах ТаблицаЗначений) и содержат сотни тысяч строк, то выполнять самосоединения по неравенству в запросе неэффективно. Гораздо быстрее обработать такие массивы алгоритмом за один линейный проход.
Разберем по шагам алгоритм последовательного обхода:
Сортировать("Дата Возр").Посмотрим на пример реализации алгоритма на встроенном языке 1С:
// Подготовим таблицы к последовательному обходу
Таблица1.Сортировать("Дата Возр");
Таблица2.Сортировать("Дата Возр");
ТаблицаРезультат = Новый ТаблицаЗначений;
ТаблицаРезультат.Колонки.Добавить("Дата", Новый ОписаниеТипов("Дата"));
ТаблицаРезультат.Колонки.Добавить("Значение", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)));
ИндексТ2 = 0;
ВсегоСтрокТ2 = Таблица2.Количество();
Для Каждого СтрТ1 Из Таблица1 Цикл
ТекущаяСумма = 0;
Пока ИндексТ2 < ВсегоСтрокТ2 Цикл
СтрТ2 = Таблица2[ИндексТ2];
Если СтрТ2.Дата <= СтрТ1.Дата Тогда
ТекущаяСумма = ТекущаяСумма + СтрТ2.Значение;
ИндексТ2 = ИндексТ2 + 1;
Иначе
Прервать;
КонецЕсли;
КонецЦикла;
НовСтр = ТаблицаРезультат.Добавить();
НовСтр.Дата = СтрТ1.Дата;
НовСтр.Значение = ТекущаяСумма;
КонецЦикла;
Такой программный обход выполняется за время O(N + M), где N и M — количество строк в исходных таблицах, что исключает декартово умножение и гарантирует мгновенную обработку больших коллекций в оперативной памяти.
Если задача решается в рамках отчета, то логику нарастающих итогов и сдвига строк можно полностью перенести в настройки Системы компоновки данных. В СКД для выражений ресурсов и пользовательских полей доступна функция ВычислитьВыражение. С ее помощью можно получить значение поля из предыдущей строки группировки (например, ВычислитьВыражение("Сумма(Поле)",,,"Предыдущая")) и вычесть его из текущего значения без усложнения исходного текста запроса.