В практической работе разработчика 1С часто возникает классическая задача: из регистра сведений ЦеныНоменклатуры необходимо выбрать для каждого товара минимальную цену среди всех доступных видов цен (например, оптовой, розничной, дилерской) и при этом вывести не только саму цену, но и её вид (измерение ВидЦен). С аналогичной проблемой сталкиваются и при получении последних статусов документов, актуальных договоров или любых других срезов данных с дополнительной аналитикой.
В этой статье мы подробно разберем, почему простой запрос с группировкой не решает эту задачу, как правильно построить запрос с использованием временных таблиц, разберем критические ошибки производительности и дублирования строк, а также рассмотрим изящный альтернативный вариант решения средствами Системы компоновки данных (СКД).
Посмотрим на первую попытку решения, которую часто предпринимают начинающие разработчики:
ВЫБРАТЬ
Номен.Ссылка КАК Ссылка,
МИНИМУМ(Цены.Цена) КАК Цена,
Цены.ВидЦен КАК ВидЦен
ИЗ
Справочник.Номенклатура КАК Номен
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ПО (Цены.Номенклатура = Номен.Ссылка)
ГДЕ
Номен.ЭтоГруппа = ЛОЖЬ
И Цены.Цена <> 0
СГРУППИРОВАТЬ ПО
Номен.Ссылка,
Цены.ВидЦен
Проанализируем ситуацию: почему этот запрос возвращает все виды цен для каждого товара, а не только минимальную? Дело в логике работы конструкции СГРУППИРОВАТЬ ПО. СУБД группирует данные по уникальным комбинациям всех полей, указанных в этой секции. Поскольку мы указали в группировке поле Цены.ВидЦен, система сгруппирует записи отдельно по каждой паре «Номенклатура — Вид цены» и выведет минимальную цену внутри каждого вида цены. То есть, если для товара установлены три вида цен, мы получим три строки, что противоречит нашей задаче.
Если же мы уберем Цены.ВидЦен из группировки, то СУБД закономерно выдаст синтаксическую ошибку, так как любое поле в секции ВЫБРАТЬ, не входящее в группировку, должно быть обязательно обернуто в агрегатную функцию (такую как МИНИМУМ, МАКСИМУМ, СУММА и т.д.). Но если мы применим к полю ВидЦен функцию МИНИМУМ в одном запросе без связки с минимальной ценой, система вернет минимальный вид цены по алфавиту, который может абсолютно не соответствовать минимальной цене.
Вывод очевиден: решить задачу в один простой запрос с группировкой без потери связи между ценой и её видом невозможно. Требуется использовать многоэтапный подход.
Для решения этой задачи мы применим проверенную двухшаговую схему:
Рассмотрим код, который решает эту задачу:
ВЫБРАТЬ
ЦеныСрезПоследних.Номенклатура КАК Номенклатура,
МИНИМУМ(ЦеныСрезПоследних.Цена) КАК Цена
ПОМЕСТИТЬ табМинЦен
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК ЦеныСрезПоследних
СГРУППИРОВАТЬ ПО
ЦеныСрезПоследних.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
табМинЦен.Номенклатура КАК Номенклатура,
табМинЦен.Цена КАК Цена,
ЦеныСрезПоследних.ВидЦен КАК ВидЦен
ИЗ
табМинЦен КАК табМинЦен
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК ЦеныСрезПоследних
ПО (ЦеныСрезПоследних.Номенклатура = табМинЦен.Номенклатура
И ЦеныСрезПоследних.Price = табМинЦен.Цена)
Этот подход логичен и понятен, однако в нем кроются две серьезные проблемы: логическая ошибка (дублирование строк) и проблема с производительностью. Давайте разберем их подробно.
Представим жизненную ситуацию в базе данных: для товара «Смартфон» оптовая цена составляет 50 000 рублей, а дилерская цена — тоже 50 000 рублей. Обе эти цены являются минимальными для данного товара.
В первом запросе пакета во временную таблицу табМинЦен запишется одна строка: «Смартфон» — 50 000 рублей. Однако на втором шаге, при выполнении левого соединения по условию равенства номенклатуры и цены, система найдет в исходной таблице цен две подходящие записи (и оптовую, и дилерскую). В результате в финальную выборку попадут две строки для одной номенклатурной позиции, что приведет к задвоению данных в отчете.
Чтобы гарантировать уникальность номенклатуры в результате, мы обязаны выполнить группировку и в финальном запросе, выбрав один из видов цен с помощью агрегатной функции (например, МИНИМУМ). Изменим финальный запрос следующим образом:
ВЫБРАТЬ
табМинЦен.Номенклатура КАК Номенклатура,
табМинЦен.Цена КАК Цена,
МИНИМУМ(ЦеныСрезПоследних.ВидЦен) КАК ВидЦен
ИЗ
табМинЦен КАК табМинЦен
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК ЦеныСрезПоследних
ПО (ЦеныСрезПоследних.Номенклатура = табМинЦен.Номенклатура
И ЦеныСрезПоследних.Цена = табМинЦен.Цена)
СГРУППИРОВАТЬ ПО
табМинЦен.Номенклатура,
табМинЦен.Цена
Теперь, даже если у товара будет несколько разных видов цен с абсолютно одинаковой минимальной стоимостью, запрос отработает корректно и вернет ровно одну строку для каждой номенклатуры.
Обратите внимание: в предыдущем пакете запросов мы дважды обращаемся к виртуальной таблице РегистрСведений.ЦеныНоменклатуры.СрезПоследних. На уровне СУБД обращение к виртуальной таблице среза последних — это тяжелая операция, так как под капотом платформа строит сложный подзапрос для расчета актуальных данных на указанную дату.
Выполнение двух срезов последних на больших объемах данных (десятки и сотни тысяч позиций номенклатуры) неизбежно приведет к существенному замедлению работы системы.
Для оптимизации мы обратимся к виртуальной таблице СрезПоследних **всего один раз**, выгрузим все актуальные цены во временную таблицу, проиндексируем её для ускорения последующих соединений, а затем будем работать исключительно с этой легкой временной таблицей. Посмотрим на оптимальный вариант реализации:
// Шаг 1: Выгружаем все актуальные цены в быструю временную таблицу и индексируем ключевые поля
ВЫБРАТЬ
Цены.Номенклатура КАК Номенклатура,
Цены.Цена КАК Цена,
Цены.ВидЦен КАК ВидЦен
ПОМЕСТИТЬ ВТ_ВсеАктуальныеЦены
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ГДЕ
Цены.Цена <> 0
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Цена
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Находим минимальную цену для каждого товара на основе временной таблицы
ВЫБРАТЬ
ВсеЦены.Номенклатура КАК Номенклатура,
МИНИМУМ(ВсеЦены.Цена) КАК Цена
ПОМЕСТИТЬ ВТ_МинимальныеЦены
ИЗ
ВТ_ВсеАктуальныеЦены КАК ВсеЦены
СГРУППИРОВАТЬ ПО
ВсеЦены.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Цена
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Соединяем две временные таблицы и группируем для исключения дубликатов вида цен
ВЫБРАТЬ
МинЦены.Номенклатура КАК Номенклатура,
МинЦены.Цена КАК Цена,
МИНИМУМ(ВсеЦены.ВидЦен) КАК ВидЦен
ИЗ
ВТ_МинимальныеЦены КАК МинЦены
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ВсеАктуальныеЦены КАК ВсеЦены
ПО (ВсеЦены.Номенклатура = МинЦены.Номенклатура
И ВсеЦены.Цена = МинЦены.Цена)
СГРУППИРОВАТЬ ПО
МинЦены.Номенклатура,
МинЦены.Цена
Преимущества данного подхода:
ИНДЕКСИРОВАТЬ ПО для связки полей Номенклатура и Цена. СУБД выполнит соединение таблиц мгновенно по готовым индексам.ВНУТРЕННЕЕ СОЕДИНЕНИЕ).Если ваша задача состоит в выводе этих данных в отчет, то писать сложные пакетные запросы с временными таблицами вовсе не обязательно. Платформа «1С:Предприятие» предоставляет мощный функционал СКД, который позволяет решить эту проблему на уровне настроек компоновки данных с использованием максимально простого базового запроса.
Выясним, как настроить этот механизм по шагам:
ВЫБРАТЬ
Цены.Номенклатура КАК Номенклатура,
Цены.Цена КАК Цена,
Цены.ВидЦен КАК ВидЦен
ИЗ
РегистрСведений.ЦеныНоменклатуры.SrezLast КАК Цены
Номенклатура.Детальные записи.Детальные записи и перейдите на вкладку Другие настройки в нижней части экрана:
1.Нет для корректного отображения полей.Цена в направлении По возрастанию.Номенклатура (для родительской группировки), а также Цена и ВидЦен (для детальных записей).При формировании отчета СКД автоматически выполнит группировку по номенклатуре, внутри нее отсортирует все доступные цены по возрастанию (то есть минимальная цена окажется первой в списке) и выведет ровно одну строку (благодаря ограничению количества записей) для каждого товара. Этот способ работает невероятно быстро и не требует написания громоздкого программного кода.
В промышленных СУБД (таких как MS SQL, PostgreSQL или Oracle) подобные задачи с легкостью решаются в один простой запрос с помощью аналитических (оконных) функций. Например, конструкция ROW_NUMBER() OVER (PARTITION BY Номенклатура ORDER BY Цена ASC) пронумеровала бы цены по возрастанию для каждого товара, после чего нам оставалось бы лишь выбрать записи с номером 1.
Однако важно помнить, что нативная поддержка оконных функций в стандартном языке запросов 1С на данный момент отсутствует. Платформа транслирует запросы в СУБД по своим внутренним правилам для обеспечения кроссплатформенности. Поэтому рассмотренные нами подходы (использование оптимальных временных таблиц для кода программных модулей и гибкие настройки СКД для построения отчетов) остаются единственными правильными, надежными и рекомендуемыми стандартами разработки в экосистеме «1С:Предприятие».