Как в запросе 1С свернуть записи по непрерывным периодам с одинаковым значением характеристики?

← На главную

При формировании аналитических отчетов и подготовке данных для движений периодически возникает задача объединения смежных строк, в которых ключевое аналитическое значение (например, характеристика номенклатуры, статус или подразделение) остается неизменным в течение некоторого непрерывного периода дат. Если выполнить обычную группировку СГРУППИРОВАТЬ ПО, то платформа объединит абсолютно все строки с одинаковой характеристикой за весь период, потеряв хронологию их чередования. Разберем, как правильно реализовать свертку только непрерывных интервалов, рассмотрим суть проблемы и проанализируем практические способы её решения.

Суть проблемы: задача об «островах и промежутках»

Посмотрим на исходный пример данных, в котором по товару ведутся записи в хронологическом порядке:


01.10  Товар А  Характеристика 1  2
05.10  Товар А  Характеристика 1  3
06.10  Товар А  Характеристика 2  1
09.10  Товар А  Характеристика 1  5

Если мы применим стандартное выражение:


ВЫБРАТЬ
    МИНИМУМ(ТаблицаКоличеств.Дата) КАК Дата,
    ТаблицаКоличеств.Номенклатура КАК Номенклатура,
    ТаблицаКоличеств.Характеристика КАК Характеристика,
    СУММА(ТаблицаКоличеств.Колво) КАК Колво
ИЗ
    ТаблицаКоличеств КАК ТаблицаКоличеств
СГРУППИРОВАТЬ ПО
    ТаблицаКоличеств.Номенклатура,
    ТаблицаКоличеств.Характеристика

То в результате записи от 01.10, 05.10 и 09.10 свернутся в одну общую строку с датой 01.10 и количеством 10. Однако в бизнес-логике требуется иное поведение: с 01.10 по 05.10 характеристика не менялась (ее нужно свернуть в количество 5), затем 06.10 возникло новое состояние (Характеристика 2 в количестве 1), а 09.10 характеристика снова сменилась на прежнюю (Характеристика 1 в количестве 5).

В теории реляционных баз данных такая проблема называется задачей об «островах и промежутках» (gaps and islands). В классическом SQL она решается через оконные функции (LAG(), LEAD() или разность ROW_NUMBER()). В языке запросов платформы 1С оконные функции отсутствуют, поэтому для нахождения непрерывных групп применяются обходные алгоритмы.

Способ 1. Реализация через пакетный запрос 1С

Разберем по шагам алгоритм решения задачи исключительно средствами языка запросов 1С. Основная идея заключается в вычислении даты предыдущей записи, фиксации моментов изменения характеристики и привязке каждой исходной строки к ближайшей дате начала интервала.

Подготовка исходного набора: Помещаем анализируемые данные во временную таблицу и обязательно индексируем ключевые поля. В хронологических выборках важно обеспечить уникальность сортировки. Если в одну и ту же дату возможны разные движения, к полю даты необходимо добавлять поле строгого порядка (например, номер строки или ссылку).
Поиск даты предыдущего движения: Соединяем временную таблицу саму с собой левым соединением по условию равенства аналитики (Номенклатура) и строго меньшей дате: ТабПред.Дата < ТабТек.Дата. С помощью агрегатной функции МАКСИМУМ(ТабПред.Дата) находим дату непосредственно предшествующей строки.
Определение точек смены аналитики (границ интервалов): Соединяем исходную таблицу с полученными предыдущими датами и сравниваем характеристики текущей и предыдущей записи. Если предыдущая дата пустая (первая запись ряда) либо характеристика предыдущей записи не равна текущей, значит, текущая дата является началом нового непрерывного интервала (границей группы).
Привязка строк к границе группы: Для каждой записи исходных данных ищем ближайшую дату границы интервала, которая меньше или равна текущей дате: МАКСИМУМ(Границы.ДатаНачалаИнтервала) ПО Границы.ДатаНачалаИнтервала <= Исходные.Дата. Полученное поле становится уникальным идентификатором непрерывной группы.
Итоговая группировка: Выполняем группировку исходных строк по номенклатуре, характеристике и вычисленной дате начала интервала, суммируя количественные ресурсы.

Рассмотрим пример готового текста запроса:


ВЫБРАТЬ
    Исходные.Дата КАК Дата,
    Исходные.Номенклатура КАК Номенклатура,
    Исходные.Характеристика КАК Характеристика,
    Исходные.Колво КАК Колво
ПОМЕСТИТЬ ВТ_Исходные
ИЗ
    &ТаблицаКоличеств КАК Исходные
ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    Дата
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Текущая.Номенклатура КАК Номенклатура,
    Текущая.Дата КАК Дата,
    МАКСИМУМ(Предыдущая.Дата) КАК ПредыдущаяДата
ПОМЕСТИТЬ ВТ_ПредыдущиеДаты
ИЗ
    ВТ_Исходные КАК Текущая
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Исходные КАК Предыдущая
        ПО Текущая.Номенклатура = Предыдущая.Номенклатура
        И Предыдущая.Дата < Текущая.Дата
СГРУППИРОВАТЬ ПО
    Текущая.Номенклатура,
    Текущая.Дата
ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    Дата,
    ПредыдущаяДата
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Текущая.Номенклатура КАК Номенклатура,
    Текущая.Дата КАК ДатаНачалаИнтервала,
    Текущая.Характеристика КАК Характеристика
ПОМЕСТИТЬ ВТ_ТочкиСмены
ИЗ
    ВТ_Исходные КАК Текущая
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ПредыдущиеДаты КАК ПредыдущиеДаты
        ПО Текущая.Номенклатура = ПредыдущиеДаты.Номенклатура
        И Текущая.Дата = ПредыдущиеДаты.Дата
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Исходные КАК ПредыдущаяЗапись
        ПО ПредыдущиеДаты.Номенклатура = ПредыдущаяЗапись.Номенклатура
        И ПредыдущиеДаты.ПредыдущаяДата = ПредыдущаяЗапись.Дата
ГДЕ
    ПредыдущиеДаты.ПредыдущаяДата ЕСТЬ NULL
    ИЛИ Текущая.Характеристика <> ПредыдущаяЗапись.Характеристика
ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    ДатаНачалаИнтервала
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Исходные.Номенклатура КАК Номенклатура,
    Исходные.Характеристика КАК Характеристика,
    Исходные.Дата КАК ДатаЗаписи,
    Исходные.Колво КАК Колво,
    МАКСИМУМ(ТочкиСмены.ДатаНачалаИнтервала) КАК ИдентификаторИнтервала
ПОМЕСТИТЬ ВТ_РазмеченныеДанные
ИЗ
    ВТ_Исходные КАК Исходные
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ТочкиСмены КАК ТочкиСмены
        ПО Исходные.Номенклатура = ТочкиСмены.Номенклатура
        И ТочкиСмены.ДатаНачалаИнтервала <= Исходные.Дата
СГРУППИРОВАТЬ ПО
    Исходные.Номенклатура,
    Исходные.Характеристика,
    Исходные.Дата,
    Исходные.Колво
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Размеченные.ИдентификаторИнтервала КАК Дата,
    Размеченные.Номенклатура КАК Номенклатура,
    Размеченные.Характеристика КАК Характеристика,
    СУММА(Размеченные.Колво) КАК Колво
ИЗ
    ВТ_РазмеченныеДанные КАК Размеченные
СГРУППИРОВАТЬ ПО
    Размеченные.ИдентификаторИнтервала,
    Размеченные.Номенклатура,
    Размеченные.Характеристика
УПОРЯДОЧИТЬ ПО
    Номенклатура,
    Дата

Ограничения чистого запроса и вычислительная сложность

Проанализируем техническую сторону такого решения. Конструкция соединения таблицы самой с собой по условию неравенства (Предыдущая.Дата < Текущая.Дата) порождает квадратичную сложность выполнения (O(N²)). На небольших выборках (до нескольких сотен строк по одной номенклатуре) запрос отрабатывает быстро. Однако при обработке десятков тысяч записей нагрузка на сервер СУБД резко возрастает, увеличивается размер служебной базы TempDB, а время выполнения может стать неприемлемым.

Способ 2. Линейная постобработка во встроенном языке (Оптимальный для больших объемов)

Если записей много, наиболее производительный подход — получить простым запросом упорядоченные данные и выполнить свертку за один линейный проход во встроенном языке 1С. Сложность такого алгоритма составляет всего O(N).

Выполняем простой запрос с обязательной сортировкой: УПОРЯДОЧИТЬ ПО Номенклатура, Дата ВОЗР.
Создаем пустую результирующую ТаблицаЗначений.
В цикле обхода выборки сохраняем текущие значения в переменные. Если в следующей строке номенклатура и характеристика совпадают, накапливаем сумму количества. Если значение изменилось — записываем накопленный результат в таблицу и начинаем новую группу.

Посмотрим на пример кода линейной обработки:


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	Исходные.Дата КАК Дата,
    |	Исходные.Номенклатура КАК Номенклатура,
    |	Исходные.Характеристика КАК Характеристика,
    |	Исходные.Колво КАК Колво
    |ИЗ
    |	&ТаблицаКоличеств КАК Исходные
    |УПОРЯДОЧИТЬ ПО
    |	Номенклатура,
    |	Дата";

Запрос.УстановитьПараметр("ТаблицаКоличеств", ТаблицаКоличеств);
Выборка = Запрос.Выполнить().Выбрать();

ТаблицаРезультат = Новый ТаблицаЗначений;
ТаблицаРезультат.Колонки.Добавить("Дата", Новый ОписаниеТипов("Дата"));
ТаблицаРезультат.Колонки.Добавить("Номенклатура");
ТаблицаРезультат.Колонки.Добавить("Характеристика");
ТаблицаРезультат.Колонки.Добавить("Колво", Новый ОписаниеТипов("Число"));

ТекущаяНоменклатура = Неопределено;
ТекущаяХарактеристика = Неопределено;
ДатаНачала = Неопределено;
СуммаКолво = 0;

Пока Выборка.Следующий() Цикл
    Если Выборка.Номенклатура <> ТекущаяНоменклатура ИЛИ Выборка.Характеристика <> ТекущаяХарактеристика Тогда
        Если ТекущаяНоменклатура <> Неопределено Тогда
            НоваяСтрока = ТаблицаРезультат.Добавить();
            НоваяСтрока.Дата = ДатаНачала;
            НоваяСтрока.Номенклатура = ТекущаяНоменклатура;
            НоваяСтрока.Характеристика = ТекущаяХарактеристика;
            НоваяСтрока.Колво = СуммаКолво;
        КонецЕсли;

        ТекущаяНоменклатура = Выборка.Номенклатура;
        ТекущаяХарактеристика = Выборка.Характеристика;
        ДатаНачала = Выборка.Дата;
        СуммаКолво = Выборка.Колво;
    Иначе
        СуммаКолво = СуммаКолво + Выборка.Колво;
    КонецЕсли;
КонецЦикла;

Если ТекущаяНоменклатура <> Неопределено Тогда
    НоваяСтрока = ТаблицаРезультат.Добавить();
    НоваяСтрока.Дата = ДатаНачала;
    НоваяСтрока.Номенклатура = ТекущаяНоменклатура;
    НоваяСтрока.Характеристика = ТекущаяХарактеристика;
    НоваяСтрока.Колво = СуммаКолво;
КонецЕсли;

Выводы и рекомендации

Выбор конкретного решения зависит от архитектуры подсистемы:

Если расчет является частью сложного отчета на базе Системы компоновки данных (СКД) или виртуальной таблицы без возможности постобработки в коде, используем решение на уровне пакетного запроса с обязательной индексацией временных таблиц.
Если обработка выполняется в рамках сервисного алгоритма или проведения документов над большими массивами данных, рекомендуется выполнять линейный обход во встроенном языке — это защитит СУБД от неоптимальных планов выполнения и обеспечит стабильную скорость работы.