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