При работе с запросами в платформе 1С:Предприятие разработчики часто сталкиваются с необходимостью получить данные в разрезе иерархии справочников (например, номенклатуры или контрагентов). Для этого используется конструкция ИТОГИ ПО ... ИЕРАРХИЯ или ИТОГИ ПО ... ТОЛЬКО ИЕРАРХИЯ. Однако при попытке выгрузить результат такого запроса в плоскую таблицу значений методом Выгрузить() возникает неприятный эффект: родительские группы (папки) начинают дублироваться. При этом в одной строке группы выводится полная сумма по всей ветке иерархии, а в другой — только сумма по элементам, которые лежат непосредственно в этой группе.
В этой статье мы подробно проанализируем, почему возникает такое поведение платформы, и рассмотрим четыре практических способа решения этой проблемы: от простых алгоритмов постобработки до продвинутых запросов с использованием временных таблиц и механизмов компоновки данных.
Давайте проанализируем ситуацию на примере структуры справочника Номенклатура, с которой столкнулся автор темы:
Когда мы выполняем запрос с конструкцией ИТОГИ ПО Номенклатура ТОЛЬКО ИЕРАРХИЯ, платформа 1С строит многоуровневый результат. Для группы «АВТОХИМИЯ» СУБД рассчитывает два разных типа итогов:
т1, т2, т3 и т4. Это полная и правильная сумма для этой группы.т3 и т4 (без учета группы «АРХИВ»).Когда мы вызываем метод РезультатЗапроса.Выгрузить(), платформа осуществляет прямой обход дерева и превращает его в плоскую таблицу значений. В эту таблицу последовательно попадают обе записи для группы «АВТОХИМИЯ». В результате в таблице значений появляются две строки с одинаковым представлением группы, но с разными суммами. Попытка свернуть такую таблицу приводит к завышению итоговых показателей, а использование ее «как есть» путает пользователей.
Если результирующая таблица значений имеет небольшой объем, самым быстрым в реализации решением является программная очистка дубликатов после выгрузки. Поскольку при прямом обходе первая строка группы всегда содержит полный итог по иерархии, а последующие строки — лишь частные итоги по группировкам, мы можем обойти таблицу значений, запомнить первое вхождение группы и удалить все последующие дубликаты.
Рассмотрим код функции постобработки, предложенный автором темы:
Функция УбратьЛишниеИтоги(ТЗ) Экспорт
// Создаем соответствие для фиксации уже обработанных групп
СоответствиеГрупп = Новый Соответствие();
МассивКУдалению = Новый Массив();
Для Каждого Строка Из ТЗ Цикл
// Проверяем, встречалась ли нам уже эта номенклатура (группа)
Если СоответствиеГрупп[Строка.Номенклатура] <> Неопределено Тогда
// Если да, значит это дублирующий итог по группировке, помечаем строку на удаление
МассивКУдалению.Добавить(Строка);
Иначе
// Если группа встретилась впервые, фиксируем ее в соответствии
СоответствиеГрупп.Вставить(Строка.Номенклатура, Истина);
КонецЕсли;
КонецЦикла;
// Удаляем помеченные строки из таблицы значений
Для Каждого Строка Из МассивКУдалению Цикл
ТЗ.Удалить(Строка);
КонецЦикла;
КонецФункции
Преимущества метода: простота реализации, не требует изменения исходного текста запроса.
Недостатки: низкая производительность на больших объемах данных. Платформа сначала тратит ресурсы на расчет лишних итогов на сервере СУБД, затем передает избыточные строки на клиент и выполняет циклы по их удалению.
Если вашей конечной целью является вывод иерархических данных в дерево значений на форме, выгружать запрос напрямую в плоскую таблицу не нужно. Вместо этого следует использовать обход выборки по группировкам с проверкой типов записей. Платформа 1С предоставляет для этого системное перечисление ТипЗаписиЗапроса.
Рассмотрим, как правильно обойти результат запроса программно и построить дерево значений без дублей:
// Выполняем запрос
Результат = Запрос.Выполнить();
// Получаем выборку по группировкам с иерархией
Выборка = Результат.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкамСИерархией);
// Создаем структуру дерева значений
ДеревоДанных = Новый ДеревоЗначений;
ДеревоДанных.Колонки.Добавить("Номенклатура", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
ДеревоДанных.Колонки.Добавить("Значение", Новый ОписаниеТипов("Число"));
// Запускаем рекурсивный или последовательный обход
ЗаполнитьВеткуДерева(Выборка, ДеревоДанных.Строки);
При обходе выборки мы можем анализировать свойство Выборка.ТипЗаписи(). Если тип записи равен ТипЗаписиЗапроса.ИтогПоИерархии, мы создаем строку в дереве значений. Если же мы встречаем ТипЗаписиЗапроса.ИтогПоГруппировке для той же группы, мы не создаем новую ветку, а лишь перенаправляем детальные записи внутрь уже созданной родительской ветки.
Если вы разрабатываете отчет, самым правильным решением будет отказаться от ручного построения запросов с итогами и перевести отчет на Систему компоновки данных (СКД). СКД умеет самостоятельно управлять иерархией без дублирования строк на уровне вывода.
Разберем по шагам, как настроить СКД для вывода чистой иерархии:
Номенклатура с типом группировки «Иерархия» (или «Только иерархия» в зависимости от задачи).РодительИерархии.
Выбор Когда Уровень() = 0 Тогда Неопределено Иначе ВычислитьВыражение("Номенклатура", , "Иерархия") Конец
Номенклатура Не Равно &РодительИерархии. В свойстве «Применение» этого отбора укажите значение «После группировки или для иерархии».Этот системный подход позволяет полностью исключить дублирование родительских папок при выводе отчета компоновщиком макета. Для настройки навигации по структуре справочника без дублей можно использовать отчет для построения дерева элементов иерархического справочника в СКД.
Если для бизнес-логики программы вам обязательно нужна плоская таблица значений без дубликатов, но при этом с рассчитанными суммами по группам, дубли можно убрать еще на уровне СУБД. Для этого мы откажемся от механизма ИТОГИ и рассчитаем связи «элемент-родитель» вручную с помощью временных таблиц.
Предположим, глубина вложенности справочника номенклатуры в нашей базе ограничена тремя уровнями. Мы можем составить запрос следующим образом:
// Шаг 1. Получаем детальные данные по продажам во временную таблицу
ВЫБРАТЬ
Т.Номенклатура КАК Номенклатура,
СУММА(Т.Стоимость) КАК Стоимость
ПОМЕСТИТЬ ВТ_ДетальныеДанные
ИЗ
РегистрНакопления.ПланыПродаж КАК Т
ГДЕ
Т.Сценарий = &Сценарий
СГРУППИРОВАТЬ ПО
Т.Номенклатура;
// Шаг 2. Строим связи товаров со всеми их родителями в иерархии
ВЫБРАТЬ
ВТ.Номенклатура КАК Номенклатура,
ВТ.Стоимость КАК Стоимость,
ВТ.Номенклатура.Родитель КАК Родитель1,
ВТ.Номенклатура.Родитель.Родитель КАК Родитель2
ПОМЕСТИТЬ ВТ_ИерархияСвязей
ИЗ
ВТ_ДетальныеДанные КАК ВТ;
// Шаг 3. Объединяем продажи самих элементов и их родителей в один поток
ВЫБРАТЬ
Связи.Номенклатура КАК Номенклатура,
Связи.Стоимость КАК Стоимость
ПОМЕСТИТЬ ВТ_ВсеИтоги
ИЗ
ВТ_ИерархияСвязей КАК Связи
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Связи.Родитель1 КАК Номенклатура,
Связи.Стоимость КАК Стоимость
ИЗ
ВТ_ИерархияСвязей КАК Связи
ГДЕ
Связи.Родитель1 <> ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Связи.Родитель2 КАК Номенклатура,
Связи.Стоимость КАК Стоимость
ИЗ
ВТ_ИерархияСвязей КАК Связи
ГДЕ
Связи.Родитель2 <> ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка);
// Шаг 4. Финальное суммирование данных по уникальным группам и элементам
ВЫБРАТЬ
Итоги.Номенклатура КАК Номенклатура,
СУММА(Итоги.Стоимость) КАК Значение
ИЗ
ВТ_ВсеИтоги КАК Итоги
СГРУППИРОВАТЬ ПО
Итоги.Номенклатура
Анализ решения: данный запрос полностью заменяет механизм итогов платформы. Мы искусственно «подняли» продажи каждого товара на все уровни его родителей с помощью конструкции ОБЪЕДИНИТЬ ВСЕ, а на финальном шаге сгруппировали результат по полю Номенклатура. На выходе получается абсолютно чистая плоская таблица значений, где каждая группа и каждый элемент встречаются ровно один раз и содержат честную, математически верную сумму по всей своей ветке. Для автоматической генерации подобных конструкций можно использовать инструмент для формирования текста запроса с родителями всех уровней.