Как объединить и сгруппировать строки по разным колонкам и датам в запросе 1С?

← На главную

При подготовке печатных форм документов разработчики нередко сталкиваются с необходимостью развернуть табличную часть так, чтобы данные по разным аналитикам (например, по направлениям поступления или отгрузки) выводились в одной строке параллельно друг другу. Рассмотрим практический пример: в табличной части документа присутствуют изделие, номенклатура, материал, количество, дата поступления и признак направления («Отгрузка», «СГП», «На склад»). Если по одному изделию есть одна запись со статусом «СГП» и две записи со статусом «На склад», требуется вывести их компактно — без образования лишних пустых строк и без декартова перемножения.

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

В чем заключается сложность классической группировки

Проанализируем исходный подход, который часто применяют начинающие разработчики. В запросе создаются вычисляемые поля через конструкцию ВЫБОР КОГДА ... ТОГДА ... КОНЕЦ:


ВЫБРАТЬ
    ЗаявкаТовары.Изделие КАК Изделие,
    ЗаявкаТовары.Номенклатура КАК Номенклатура,
    ЗаявкаТовары.Материал КАК Материал,
    ВЫБОР
        КОГДА ЗаявкаТовары.НаправлениеПоЗаявке = ЗНАЧЕНИЕ(Перечисление.НаправлениеПоЗаявке.Отгрузка)
            ТОГДА ЗаявкаТовары.Количество
    КОНЕЦ КАК КоличествоОтгрузка,
    ВЫБОР
        КОГДА ЗаявкаТовары.НаправлениеПоЗаявке = ЗНАЧЕНИЕ(Перечисление.НаправлениеПоЗаявке.Отгрузка)
            ТОГДА ЗаявкаТовары.ДатаПланПоступления
    КОНЕЦ КАК ДатаОтгрузки,
    ВЫБОР
        КОГДА ЗаявкаТовары.НаправлениеПоЗаявке = ЗНАЧЕНИЕ(Перечисление.НаправлениеПоЗаявке.СГП)
            ТОГДА ЗаявкаТовары.Количество
    КОНЕЦ КАК КоличествоСГП,
    ВЫБОР
        КОГДА ЗаявкаТовары.НаправлениеПоЗаявке = ЗНАЧЕНИЕ(Перечисление.НаправлениеПоЗаявке.СГП)
            ТОГДА ЗаявкаТовары.ДатаПланПоступления
    КОНЕЦ КАК ДатаСГП,
    ВЫБОР
        КОГДА ЗаявкаТовары.НаправлениеПоЗаявке = ЗНАЧЕНИЕ(Перечисление.НаправлениеПоЗаявке.НаСклад)
            ТОГДА ЗаявкаТовары.Количество
    КОНЕЦ КАК КоличествоНаСклад,
    ВЫБОР
        КОГДА ЗаявкаТовары.НаправлениеПоЗаявке = ЗНАЧЕНИЕ(Перечисление.НаправлениеПоЗаявке.НаСклад)
            ТОГДА ЗаявкаТовары.ДатаПланПоступления
    КОНЕЦ КАК ДатаНаСклад
ИЗ
    Документ.Заявка.Товары КАК ЗаявкаТовары
ГДЕ
    ЗаявкаТовары.Ссылка = &Ссылка

При таком подходе каждая строка исходной таблицы попадает в отдельную строку результата: в ней заполнены только те колонки, которые соответствуют значению поля НаправлениеПоЗаявке текущей строки, а остальные колонки содержат NULL.

Попытка применить простую группировку через СГРУППИРОВАТЬ ПО терпит неудачу: если включить в группировку даты, строки не схлопнутся, так как значения дат разные или равны NULL. Если же соединить таблицы просто по полям номенклатуры, возникнет декартово произведение, при котором единственная дата по «СГП» продублируется напротив всех дат «На склад».

Главная причина проблемы: в языке запросов платформы 1С отсутствуют аналитические (оконные) функции вроде ROW_NUMBER() OVER (...), которые позволяют явно сопоставить N-ю строку одной группы с N-й строкой другой группы. Чтобы сопоставить первую дату «СГП» с первой датой «На склад», а вторую со второй, необходимо искусственно сформировать порядковый номер строки внутри каждой группы.

Решение 1. Эмуляция порядкового номера строки в запросе

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

Помещаем исходные данные документа во временную таблицу, обязательно захватив исходное поле НомерСтроки табличной части.
Для каждого направления рассчитываем ранг (порядковый номер: 1, 2, 3...) путем соединения таблицы самой с собой по условию нестрогого неравенства Таблица1.НомерСтроки >= Таблица2.НомерСтроки и агрегации КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...).
Формируем реестр уникальных пар Номенклатура — НомерПоПорядку из всех направлений.
Соединяем общий реестр с данными каждого направления по номенклатуре и порядковому номеру.

Посмотрим на пример практической реализации такого запроса:


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

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

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

////////////////////////////////////////////////////////////////////////////////
// Формируем полный перечень строк для каждой номенклатуры
ВЫБРАТЬ
    ВТ_СГП.Изделие КАК Изделие,
    ВТ_СГП.Номенклатура КАК Номенклатура,
    ВТ_СГП.Материал КАК Материал,
    ВТ_СГП.НомерВГруппе КАК НомерВГруппе
ПОМЕСТИТЬ ВТ_Сетка
ИЗ
    ВТ_СГП КАК ВТ_СГП

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    ВТ_НаСклад.Изделие,
    ВТ_НаСклад.Номенклатура,
    ВТ_НаСклад.Материал,
    ВТ_НаСклад.НомерВГруппе
ИЗ
    ВТ_НаСклад КАК ВТ_НаСклад
;

////////////////////////////////////////////////////////////////////////////////
// Финальный результат: соединяем сетку с данными направлений
ВЫБРАТЬ
    Сетка.Изделие КАК Изделие,
    Сетка.Номенклатура КАК Номенклатура,
    Сетка.Материал КАК Материал,
    СГП.КоличествоСГП КАК КоличествоСГП,
    СГП.ДатаСГП КАК ДатаСГП,
    НаСклад.КоличествоНаСклад КАК КоличествоНаСклад,
    НаСклад.ДатаНаСклад КАК ДатаНаСклад
ИЗ
    ВТ_Сетка КАК Сетка
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СГП КАК СГП
        ПО Сетка.Номенклатура = СГП.Номенклатура
            И Сетка.Изделие = СГП.Изделие
            И Сетка.Материал = СГП.Материал
            И Сетка.НомерВГруппе = СГП.НомерВГруппе
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_НаСклад КАК НаСклад
        ПО Сетка.Номенклатура = НаСклад.Номенклатура
            И Сетка.Изделие = НаСклад.Изделие
            И Сетка.Материал = НаСклад.Материал
            И Сетка.НомерВГруппе = НаСклад.НомерВГруппе
УПОРЯДОЧИТЬ ПО
    Сетка.Изделие,
    Сетка.Номенклатура,
    Сетка.Материал,
    Сетка.НомерВГруппе

Важное замечание: нумерацию следует выполнять именно по полю НомерСтроки (или упорядоченной связке дат), так как соединение по дате при наличии повторяющихся дат в табличной части приведет к одинаковым рангам и некорректному результату.

Решение 2. Программная компоновка при выводе печатной формы

Запросы с искусственным вычислением порядковых номеров через самосоединение создают квадратичную сложность (O(N^2)). Если в документе сотни строк, такой запрос может работать медленно. Так как цель задачи — вывод печатной формы в ТабличныйДокумент, значительно проще и производительнее обработать данные на встроенном языке 1С.

Рассмотрим алгоритм программной обработки:

Получаем из документа линейную выборку строк, сгруппированную по изделию, номенклатуре и материалу.
Для каждой уникальной номенклатуры раскладываем строки по массивам в соответствии с направлением (массив для «СГП», массив для «НаСклад»).
Определяем максимальную длину среди получившихся массивов: это количество физических строк, которое займет номенклатура в печатном бланке.
В цикле обходим индексы от нуля до максимального и выводим строку: если для текущего индекса в массиве есть запись, выводим ее значения, иначе оставляем поля пустыми.

Посмотрим на пример кода формирования строк печатной формы:


// Предположим, что ВыборкаНоменклатуры сгруппирована по товару,
// а внутри нее подчиненная выборка детальных записей
Пока ВыборкаНоменклатуры.Следующий() Цикл
    
    МассивСГП = Новый Массив;
    МассивНаСклад = Новый Массив;
    
    ВыборкаДетали = ВыборкаНоменклатуры.Выбрать();
    Пока ВыборкаДетали.Следующий() Цикл
        
        ДанныеСтроки = Новый Структура("Количество, Дата", ВыборкаДетали.Количество, ВыборкаДетали.ДатаПланПоступления);
        
        Если ВыборкаДетали.НаправлениеПоЗаявке = Перечисления.НаправлениеПоЗаявке.СГП Тогда
            МассивСГП.Добавить(ДанныеСтроки);
        ИначеЕсли ВыборкаДетали.НаправлениеПоЗаявке = Перечисления.НаправлениеПоЗаявке.НаСклад Тогда
            МассивНаСклад.Добавить(ДанныеСтроки);
        КонецЕсли;
        
    КонецЦикла;
    
    // Определяем, сколько строк займет вывод номенклатуры
    МаксСтрок = Макс(МассивСГП.Количество(), МассивНаСклад.Количество());
    
    Для Индекс = 0 По МаксСтрок - 1 Цикл
        
        ОбластьСтрока.Параметры.Номенклатура = ВыборкаНоменклатуры.Номенклатура;
        
        // Заполняем данные СГП
        Если Индекс < МассивСГП.Количество() Тогда
            ОбластьСтрока.Параметры.КоличествоСГП = МассивСГП[Индекс].Количество;
            ОбластьСтрока.Параметры.ДатаСГП       = МассивСГП[Индекс].Дата;
        Иначе
            ОбластьСтрока.Параметры.КоличествоСГП = "";
            ОбластьСтрока.Параметры.ДатаСГП       = "";
        КонецЕсли;
        
        // Заполняем данные НаСклад
        Если Индекс < МассивНаСклад.Количество() Тогда
            ОбластьСтрока.Параметры.КоличествоНаСклад = МассивНаСклад[Индекс].Количество;
            ОбластьСтрока.Параметры.ДатаНаСклад       = МассивНаСклад[Индекс].Дата;
        Иначе
            ОбластьСтрока.Параметры.КоличествоНаСклад = "";
            ОбластьСтрока.Параметры.ДатаНаСклад       = "";
        КонецЕсли;
        
        ТабличныйДокумент.Вывести(ОбластьСтрока);
        
    КонецЦикла;
    
КонецЦикла;

Такой подход обеспечивает максимальное быстродействие, исключает перегрузку СУБД сложными соединениями и делает логику вывода прозрачной и удобной для доработки.

← На главную

Как объединить и сгруппировать строки по разным колонкам и датам в запросе 1С?

В чем заключается сложность классической группировки

Решение 1. Эмуляция порядкового номера строки в запросе

Решение 2. Программная компоновка при выводе печатной формы

Читайте также