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