Почему не группируются строки табличной части в запросе 1С и как это исправить?

← На главную

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

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

Анализ исходной проблемы

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

Изначальный запрос, написанный разработчиком, выглядел следующим образом:


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

На первый взгляд, логика запроса кажется верной: мы выбираем номенклатуру, суммируем количество, накладываем фильтр по списку спецификаций и группируем по ссылке номенклатуры. Однако на выходе мы получаем строки, которые не сворачиваются. Например, если в двух разных спецификациях используется один и тот же материал "Леска пломбировочная 1100 мм" в количестве 4 штук, запрос вернет две отдельные строки по 4 штуки вместо одной строки с количеством 8 штук.

Выясним причину, почему группировка игнорируется платформой.

Архитектурная причина: Механизм вложенных таблиц

Главная ошибка кроется в выборе источника данных для запроса. В качестве источника указана основная таблица справочника — Справочник.РесурсныеСпецификации, а поля табличной части (МатериалыИУслуги) выбираются "через точку".

Когда мы обращаемся к табличной части через основную таблицу (шапку) объекта, платформа 1С задействует специальный механизм работы со вложенными таблицами. Для СУБД (будь то MS SQL Server, PostgreSQL или IBM DB2) это означает следующее:

Основным объектом выборки платформа считает сам элемент справочника (его шапку, представленную уникальным идентификатором ссылки _IDRRef).
Чтобы связать строки табличной части с конкретным элементом-владельцем, транслятор запросов 1С неявно (на уровне СУБД) добавляет в группировку и сортировку уникальный идентификатор родительского объекта — его ссылку.
Даже если в секции СГРУППИРОВАТЬ ПО мы указываем только номенклатуру, под капотом СУБД все равно выполняет группировку с учетом родительской ссылки. Это необходимо платформе для того, чтобы в результате запроса вернуть древовидную структуру, где у каждого элемента справочника есть своя вложенная таблица со строками.

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

Вторая ошибка: Несгруппированные поля в выборке

Обратим внимание на еще одну важную деталь исходного запроса — поле РесурсныеСпецификации.МатериалыИУслуги.Номенклатура.ЕдиницаИзмерения.Наименование.

По правилам языка запросов 1С (и стандартного SQL), все поля, указанные в секции ВЫБРАТЬ, которые не являются агрегатными функциями (такими как СУММА, КОЛИЧЕСТВО, МАКСИМУМ и т.д.), обязательно должны быть перечислены в секции СГРУППИРОВАТЬ ПО.

Если мы оставляем текстовое поле наименования единицы измерения в выборке, но не добавляем его в группировку, СУБД не сможет однозначно определить, какое именно значение выводить в строке при объединении данных. Чтобы исправить эту ошибку, у нас есть два пути:

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

Правильное решение: Обращение напрямую к табличной части

Чтобы обойти ограничение механизма вложенных таблиц и получить корректную группировку по всей базе данных (или по всему отобранному массиву спецификаций), необходимо изменить источник данных. Мы должны обратиться напрямую к плоской таблице табличной части справочника.

С технической точки зрения табличные части документов и справочников хранятся в базе данных SQL как **отдельные физические таблицы** (например, _Reference120_VT1305). В них связь с родительским документом осуществляется через обычное поле Ссылка.

Проанализируем правильный вариант запроса:


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

Разберем по шагам, почему этот запрос работает правильно и быстро:

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

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

Иногда при обращении напрямую к табличной части разработчику все же требуется получить какие-то реквизиты самого документа-родителя или спецификации-родителя (например, дату утверждения, статус или ответственного сотрудника).

Сделать это очень просто. Мы можем обращаться к реквизитам шапки "через точку" от поля Ссылка нашей табличной части. Например: МИУ.Ссылка.Статус или МИУ.Ссылка.ДатаУтверждения.

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

Вопросы производительности и оптимизации запросов

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

Исключение избыточного сканирования: При обращении через шапку СУБД вынуждена сначала сканировать таблицу шапки справочника (даже если данные из нее нам не нужны), а затем соединять её с табличной частью. Прямой запрос к ТЧ сразу начинает работу с целевыми строками.
Эффективное использование индексов: СУБД выполняет быструю фильтрацию по индексу, построенному по полю Ссылка табличной части (соответствующему физическому полю _ReferenceIDRRef), что минимизирует время выполнения операции ГДЕ Ссылка В (&СписокСсылок).
Упрощение плана запроса: Оптимизатор СУБД строит максимально простой и эффективный план выполнения, не перегруженный лишними вложенными подзапросами и соединениями, что экономит оперативную память сервера и процессорное время.

Резюме для разработчика

Для закрепления материала сформулируем золотое правило работы с запросами в 1С:

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

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