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

← На главную

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

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

Ограничения языка запросов 1С при работе с иерархией

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

Отсутствие метода в SQL: Метод Уровень() реализован исключительно в объектной модели встроенного языка 1С (для ссылок, выборок запроса, строк дерева значений). Платформа 1С не транслирует его в функции SQL-запроса к СУБД.
Отсутствие рекурсивных CTE: В языке запросов 1С не поддерживается рекурсия (аналог WITH RECURSIVE). Соответственно, обойти дерево неизвестной глубины исключительно одним универсальным запросом без ограничения уровней невозможно.
Неявные соединения: Каждое обращение «через точку» вида Родитель.Родитель разворачивается на уровне СУБД в неявное левое соединение (LEFT JOIN) к таблице справочника. При глубокой иерархии злоупотребление такими конструкциями создает избыточную нагрузку на базу данных.

Способ 1. Прямое вычисление целевого предка через конструкцию ВЫБОР КОГДА

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

Разберем логику: если элемент находится на 2-м уровне, то его дедушка (Родитель.Родитель) равен пустой ссылке. Если элемент находится на 3-м уровне, то пустой ссылке равен его прадедушка (Родитель.Родитель.Родитель), а предком 2-го уровня для него является непосредственный Родитель. Посмотрим на пример формирования такого поля в запросе:


ВЫБРАТЬ
    Подразделения.Ссылка КАК Подразделение,
    ВЫБОР
        // Элемент находится на 2-м уровне: берем его самого
        КОГДА Подразделения.Родитель.Родитель = ЗНАЧЕНИЕ(Справочник.Подразделения.ПустаяСсылка)
            ТОГДА Подразделения.Ссылка

        // Элемент находится на 3-м уровне: берем его родителя (уровень 2)
        КОГДА Подразделения.Родитель.Родитель.Родитель = ЗНАЧЕНИЕ(Справочник.Подразделения.ПустаяСсылка)
            ТОГДА Подразделения.Родитель

        // Элемент находится на 4-м уровне: берем дедушку (уровень 2)
        КОГДА Подразделения.Родитель.Родитель.Родитель.Родитель = ЗНАЧЕНИЕ(Справочник.Подразделения.ПустаяСсылка)
            ТОГДА Подразделения.Родитель.Родитель

        ИНАЧЕ Подразделения.Ссылка
    КОНЕЦ КАК ПодразделениеУровня2
ИЗ
    Справочник.Подразделения КАК Подразделения

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

Способ 2. Определение числового уровня через объединение таблиц

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

Рассмотрим вариант с использованием операции ОБЪЕДИНИТЬ ВСЕ:


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

ОБЪЕДИНИТЬ ВСЕ

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

ОБЪЕДИНИТЬ ВСЕ

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Контрагенты.Ссылка КАК Контрагент,
    ЕСТЬNULL(ВтУровни.Уровень, 1) КАК НомерУровня
ИЗ
    Справочник.Контрагенты КАК Контрагенты
    ЛЕВОЕ СОЕДИНЕНИЕ ВтУровни КАК ВтУровни
        ПО Контрагенты.Ссылка = ВтУровни.Ссылка

Такой подход позволяет наглядно присвоить группам и элементам конкретный номер уровня при заранее известной максимальной глубине иерархии в информационной базе.

Способ 3. Использование итогов по иерархии и постобработки

Если логика формирования данных допускает программный обход выборки на встроенном языке 1С, наиболее производительным и лаконичным решением будет использование предложения ИТОГИ ПО ... ИЕРАРХИЯ.

Разберем по шагам, как это работает:

В тексте запроса строим группировку по полю с ключевым словом ИЕРАРХИЯ.
В коде на встроенном языке обходим результат с типом ОбходРезультатаЗапроса.ПоГруппировкамСИерархией.
В цикле обхода выборки вызываем встроенный метод Выборка.Уровень() и анализируем Выборка.ТипЗаписи.

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


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	Продажи.Подразделение КАК Подразделение,
    |	СУММА(Продажи.СуммаОборот) КАК Сумма
    |ИЗ
    |	РегистрНакопления.Продажи.Обороты(&НачПериода, &КонПериода, , ) КАК Продажи
    |СГРУППИРОВАТЬ ПО
    |	Продажи.Подразделение
    |ИТОГИ
    |	СУММА(Сумма)
    |ПО
    |	Подразделение ИЕРАРХИЯ";

Запрос.УстановитьПараметр("НачПериода", НачалоДня(ТекущаяДата()));
Запрос.УстановитьПараметр("КонПериода", КонецДня(ТекущаяДата()));

Результат = Запрос.Выполнить();
Выборка = Результат.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкамСИерархией);

Пока Выборка.Следующий() Цикл
    // Метод Уровень() возвращает 0 для корня, 1 для первого уровня и т.д.
    Если Выборка.Уровень() = 2 Тогда
        Сообщить(СтрШаблон("Узел 2-го уровня: %1, Сумма: %2", Выборка.Подразделение, Выборка.Сумма));
    КонецЕсли;
КонецЦикла;

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

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

Если отчет строится с использованием СКД, задача решается штатными механизмами без усложнения исходного текста запроса:

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

Способ 5. Денормализация данных для высоконагруженных систем

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

Добавить в справочник реквизит (например, УровеньИерархии или ПодразделениеУровня2) и включить для него индексирование.
В модуле объекта в процедуре ПередЗаписью вычислять значение предка через стандартный метод ссылки Уровень() или обход родителей.
В аналитических запросах выполнять группировку и накладывать отборы напрямую по добавленному реквизиту. Это обеспечит максимальную скорость выполнения запроса без соединений и условий.