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