При решении задач планирования, прогнозирования остатков или расчета скользящих средних часто возникает необходимость рассчитать значение в текущей строке на основе результата предыдущей строки. В классическом SQL для этого применяются оконные функции или рекурсивные CTE, однако в языке запросов 1С они отсутствуют. Разберем основные подходы к решению этой задачи: с помощью языка запросов, системы компоновки данных (СКД) и постобработки в коде.
Если нам необходимо получить значение из непосредственно предшествующей строки, мы можем пронумеровать строки выборки и связать таблицу саму с собой по условию смещения номера на единицу.
Посмотрим на пример базового запроса со смещением:
ВЫБРАТЬ
ТЗ.НомерСтроки КАК НомерСтроки,
ТЗ.Номенклатура КАК Номенклатура,
ТЗ.Количество КАК Количество
ПОМЕСТИТЬ ВтИсходная
ИЗ
&ТаблицаДанных КАК ТЗ
;
ВЫБРАТЬ
Текущая.НомерСтроки КАК НомерСтроки,
Текущая.Номенклатура КАК Номенклатура,
Текущая.Количество КАК КоличествоТекущее,
Предыдущая.Количество КАК КоличествоПредыдущее
ИЗ
ВтИсходная КАК Текущая
ЛЕВОЕ СОЕДИНЕНИЕ ВтИсходная КАК Предыдущая
ПО Текущая.НомерСтроки - 1 = Предыдущая.НомерСтроки
И Текущая.Номенклатура = Предыдущая.Номенклатура
Однако, если требуется вычислить реккурентную формулу на произвольную глубину (например, где плановый остаток каждой последующей недели равен остатку предыдущей недели за вычетом расхода), последовательное соединение строк заменяется расчетом нарастающего итога.
Проанализируем математическую суть: зависимость вида Остаток2 = Остаток1 - Расход2, Остаток3 = Остаток2 - Расход3 сводится к вычитанию накопленной суммы всех расходов: ОстатокN = НачальныйОстаток - (Расход1 + Расход2 + ... + РасходN).
Рассмотрим комплексный пример запроса, в котором вычисляются плановые продажи по скользящему среднему окну и строится прогноз остатков на несколько недель вперед:
ВЫБРАТЬ
ФактическиеПродажи.Период КАК Период,
ФактическиеПродажи.Номенклатура КАК Номенклатура,
ФактическиеПродажи.КоличествоОборот КАК Количество,
ЛОЖЬ КАК План
ПОМЕСТИТЬ НедельныеПродажи
ИЗ
РегистрНакопления.Продажи.Обороты(
НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Дата1, НЕДЕЛЯ, -&Окно), НЕДЕЛЯ),
КОНЕЦПЕРИОДА(ДОБАВИТЬКДАТЕ(&Дата1, НЕДЕЛЯ, -1), НЕДЕЛЯ),
Неделя, ) КАК ФактическиеПродажи
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ПланируемыеПродажи.Период,
ПланируемыеПродажи.Номенклатура,
ПланируемыеПродажи.КоличествоОборот,
ИСТИНА
ИЗ
РегистрНакопления.ПланыПродаж.Обороты(
НАЧАЛОПЕРИОДА(&Дата1, НЕДЕЛЯ),
&Дата2,
Неделя, ) КАК ПланируемыеПродажи
;
ВЫБРАТЬ
НедельныеПродажи.Период КАК Период,
НедельныеПродажи.Номенклатура КАК Номенклатура,
СРЕДНЕЕ(ПродажиВОкнеНедель.Количество) КАК Расход
ПОМЕСТИТЬ СреднеНедельныеПродажи
ИЗ
НедельныеПродажи КАК НедельныеПродажи
ВНУТРЕННЕЕ СОЕДИНЕНИЕ НедельныеПродажи КАК ПродажиВОкнеНедель
ПО (НедельныеПродажи.План)
И НедельныеПродажи.Номенклатура = ПродажиВОкнеНедель.Номенклатура
И (ПродажиВОкнеНедель.Период МЕЖДУ ДОБАВИТЬКДАТЕ(НедельныеПродажи.Период, НЕДЕЛЯ, -&Окно) И НедельныеПродажи.Период)
СГРУППИРОВАТЬ ПО
НедельныеПродажи.Период,
НедельныеПродажи.Номенклатура
;
ВЫБРАТЬ
Периоды.Период КАК Период,
ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
ТоварыНаСкладахОстатки.КоличествоОстаток - СУММА(Расходы.Расход) КАК ПлановыйОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(НАЧАЛОПЕРИОДА(&Дата1, НЕДЕЛЯ), ) КАК ТоварыНаСкладахОстатки
ВНУТРЕННЕЕ СОЕДИНЕНИЕ СреднеНедельныеПродажи КАК Периоды
ПО ТоварыНаСкладахОстатки.Номенклатура = Периоды.Номенклатура
ВНУТРЕННЕЕ СОЕДИНЕНИЕ СреднеНедельныеПродажи КАК Расходы
ПО (Периоды.Номенклатура = Расходы.Номенклатура)
И (Расходы.Период <= Периоды.Период)
СГРУППИРОВАТЬ ПО
Периоды.Период,
ТоварыНаСкладахОстатки.Номенклатура,
ТоварыНаСкладахОстатки.КоличествоОстаток
Разберем по шагам логику работы этого запроса:
НедельныеПродажи собирает фактические продажи за период истории и планируемые продажи из соответствующего регистра.СреднеНедельныеПродажи происходит соединение таблицы с собой по интервалу дат МЕЖДУ для вычисления среднего значения за заданное количество недель.Расходы.Период <= Периоды.Период. При группировке функция СУММА(Расходы.Расход) собирает все расходы с начала периода по текущую строку включительно, вычитая их из начального остатка.Если отчет строится с использованием системы компоновки данных, вычисления можно вынести на уровень процессора компоновки, избегая тяжелых самосоединений в СУБД.
Для обращения к предыдущим строкам и накопительным итогам используются встроенные функции языка выражений СКД:
ВычислитьВыражение("МояКолонка", , , "Предыдущая", "Предыдущая")ВычислитьВыражение("Сумма(Количество)", , , "Первая", "Текущая")ВычислитьВыражениеСГруппировкойМассив(), которая собирает подчиненные значения в массив для дальнейшей математической обработки.Самосоединение таблиц по условию неравенства (Таб1.Период >= Таб2.Период) имеет алгоритмическую сложность O(N²). Если в выборке участвуют тысячи строк, СУБД будет тратить значительные ресурсы на построение промежуточных декартовых произведений в tempdb.
В таких случаях наиболее производительным и надежным решением является последовательный расчет в цикле со сложностью O(N):
ТаблицаЗначений или используем ВыборкаИзРезультатаЗапроса.ТаблицуЗначений в МенеджерВременныхТаблиц с обязательным созданием индексов через ИНДЕКСИРОВАТЬ ПО.Посмотрим на пример программного расчета:
ТаблицаРезультат = Запрос.Выполнить().Выгрузить();
ТекущийОстаток = 0;
ТекущаяНоменклатура = Неопределено;
Для Каждого Стр Из ТаблицаРезультат Цикл
Если Стр.Номенклатура <> ТекущаяНоменклатура Тогда
ТекущаяНоменклатура = Стр.Номенклатура;
ТекущийОстаток = Стр.НачальныйОстаток;
КонецЕсли;
ТекущийОстаток = ТекущийОстаток - Стр.Расход;
Стр.ПлановыйОстаток = ТекущийОстаток;
КонецЦикла;
Такой подход гарантирует минимальное время выполнения, линейную масштабируемость и простоту отладки алгоритма.