Как правильно составить запрос к табличной части документа с группировкой по реквизитам шапки и строк?

← На главную

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

Почему возникает ошибка группировки

Проанализируем ситуацию, приводящую к ошибке. Когда в секции ВЫБРАТЬ используется конструкция с круглыми скобками вида Документ.ИмяТабличнойЧасти.(Поле1, Поле2), платформа формирует выборку с так называемыми вложенными таблицами. Каждая строка результата запроса содержит ссылку на документ верхнего уровня, а в поле табличной части возвращается отдельная вложенная таблица со строками этого документа.

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

Решение: прямой запрос к реальной таблице табличной части

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

Рассмотрим по шагам, как построить такой запрос:

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

Посмотрим на пример корректного текста запроса:


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

Важные технические нюансы и оптимизация

Разберем особенности работы СУБД при таком подходе, которые важно учитывать при разработке высоконагруженных систем:

Разыменование через точку (неявные соединения): Обращение вида ТЧ.Ссылка.СтанцияНаблюдения приводит к тому, что платформа автоматически генерирует в SQL неявное левое соединение (LEFT JOIN) с таблицей шапки документа. Если в блоке ГДЕ используется много полей шапки, более производительным решением будет явное внутреннее соединение (ВНУТРЕННЕЕ СОЕДИНЕНИЕ) таблицы документа и таблицы его табличной части по полю Ссылка.
Фильтрация по признаку проведения и пометке удаления: В отличие от виртуальных таблиц регистров, обращение к табличной части документа выбирает абсолютно все записи базы данных, в том числе помеченные на удаление и непроведенные. Если требуются только актуальные данные, всегда явно добавляйте условия: ТЧ.Ссылка.Проведен = ИСТИНА и НЕ ТЧ.Ссылка.ПометкаУдаления.
Выборка через временные таблицы: При обработке больших массивов информации оптимально сначала выбрать ссылки на нужные документы с фильтрами по дате во временную таблицу, проиндексировать поле Ссылка, а затем соединить временную таблицу с табличной частью документа и выполнить агрегацию. Это предотвратит полное сканирование (Full Table Scan) строк табличной части.

Получение иерархии через предложение «ИТОГИ ПО»

Если данные необходимо вывести в виде дерева (где верхний уровень — станция, а подчиненные строки — вещества) без использования вложенных таблиц, применим предложение ИТОГИ:


ВЫБРАТЬ
    ТЧ.Ссылка.СтанцияНаблюдения КАК СтанцияНаблюдения,
    ТЧ.Вещество КАК Вещество,
    СУММА(ТЧ.Значение) КАК Значение
ИЗ
    Документ.Исследование.ДонныеОтложения КАК ТЧ
ГДЕ
    ТЧ.Ссылка.Дата МЕЖДУ &ДатаС И &ДатаПо
    И ТЧ.Ссылка.Проведен = ИСТИНА
СГРУППИРОВАТЬ ПО
    ТЧ.Ссылка.СтанцияНаблюдения,
    ТЧ.Вещество
ИТОГИ
    СУММА(Значение)
ПО
    СтанцияНаблюдения

В программном коде 1С такой результат обходится иерархически с помощью параметра ОбходРезультатаЗапроса.ПоГруппировкам, что дает готовое дерево без потери производительности:


ВыборкаСтанция = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);
Пока ВыборкаСтанция.Следующий() Цикл
    // Обработка группировки верхнего уровня (СтанцияНаблюдения)
    
    ВыборкаДетали = ВыборкаСтанция.Выбрать();
    Пока ВыборкаДетали.Следующий() Цикл
        // Обработка детальных записей (Вещество и агрегированное Значение)
    КонецЦикла;
КонецЦикла;

Реализация задачи в Системе компоновки данных (СКД)

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