При разработке отчетов и обработок в среде 1С:Предприятие часто возникает задача: для каждого клиента (контрагента, договора, номенклатуры) необходимо выбрать ровно одну запись — например, торговую точку с максимальным оборотом, самый первый документ реализации или последнее актуальное движение. В стандартном SQL для этого применяются аналитические оконные функции (например, ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)), однако в языке запросов платформы 1С оконные функции отсутствуют, а конструкция ПЕРВЫЕ N ограничивает результирующий набор строк целиком, а не внутри каждой отдельной группы.
Разберем подробнее, почему возникает эта сложность и какими практическими способами можно гарантированно получить первую или максимальную строку по каждой группе данных.
Проанализируем ключевые ограничения платформы, с которыми сталкивается разработчик:
ВЫБРАТЬ ПЕРВЫЕ 1 вернет всего одну строку из всей выборки базы данных, а не по одной строке на каждого контрагента.МАКСИМУМ(СтоимостьОборот) или МИНИМУМ(Период) и соединяем результат с исходными данными, то при наличии одинаковых максимальных сумм у нескольких точек контрагента запрос вернет две и более строки по одному клиенту.В (ВЫБРАТЬ ...) без индексации могут существенно замедлять выполнение выборки на больших объемах данных.Рассмотрим наиболее производительный и универсальный способ решения задачи на чистом языке запросов. Он заключается в последовательном получении исходных движений, расчете экстремума (минимальной даты или максимального оборота) и последующем левом соединении с фильтрацией дублей.
Посмотрим на пример запроса, в котором для каждого контрагента выбирается запись с минимальным периодом движения:
ВЫБРАТЬ
ПродажиОбороты.СтоимостьОборот КАК СтоимостьОборот,
ПродажиОбороты.Номенклатура КАК Номенклатура,
ПродажиОбороты.Контрагент КАК Контрагент,
ПродажиОбороты.Период КАК Период
ПОМЕСТИТЬ ВТ_ВсеДвижения
ИЗ
РегистрНакопления.Продажи.Обороты(, , Запись, ) КАК ПродажиОбороты
ИНДЕКСИРОВАТЬ ПО
Период,
Контрагент,
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
МИНИМУМ(ВТ_ВсеДвижения.Период) КАК Период,
ВТ_ВсеДвижения.Контрагент КАК Контрагент,
ВТ_ВсеДвижения.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_МинПериод
ИЗ
ВТ_ВсеДвижения КАК ВТ_ВсеДвижения
СГРУППИРОВАТЬ ПО
ВТ_ВсеДвижения.Контрагент,
ВТ_ВсеДвижения.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Период,
Контрагент,
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_МинПериод.Период КАК Период,
ВТ_МинПериод.Контрагент КАК Контрагент,
ВТ_МинПериод.Номенклатура КАК Номенклатура,
ВТ_ВсеДвижения.СтоимостьОборот КАК СтоимостьОборот
ИЗ
ВТ_МинПериод КАК ВТ_МинПериод
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ВсеДвижения КАК ВТ_ВсеДвижения
ПО ВТ_МинПериод.Период = ВТ_ВсеДвижения.Период
И ВТ_МинПериод.Контрагент = ВТ_ВсеДвижения.Контрагент
И ВТ_МинПериод.Номенклатура = ВТ_ВсеДвижения.Номенклатура
Разберем по шагам, почему в данном решении обязательно используется индексация:
ВТ_ВсеДвижения мы собираем плоские данные и индексируем поля связи.ВТ_МинПериод выполняем группировку и вычисляем минимальное значение даты.Если задача состоит в получении точки с максимальным оборотом, при равенстве сумм оборота стандартное соединение вернет дубли. Чтобы исключить неоднозначность и гарантировать получение ровно одной строки на контрагента, применим дополнительную свертку по ссылочному идентификатору (например, МИНИМУМ(ТорговаяТочка)):
ВЫБРАТЬ
ПродажиОбороты.Контрагент КАК Контрагент,
ПродажиОбороты.ТорговаяТочка КАК ТорговаяТочка,
СУММА(ПродажиОбороты.СтоимостьОборот) КАК СуммаОборот
ПОМЕСТИТЬ ВТ_ОборотыПоТочкам
ИЗ
РегистрНакопления.Продажи.Обороты(&ДатаНач, &ДатаКон, Авто, ) КАК ПродажиОбороты
СГРУППИРОВАТЬ ПО
ПродажиОбороты.Контрагент,
ПродажиОбороты.ТорговаяТочка
ИНДЕКСИРОВАТЬ ПО
Контрагент,
СуммаОборот
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ.Контрагент КАК Контрагент,
МАКСИМУМ(ВТ.СуммаОборот) КАК МаксСумма
ПОМЕСТИТЬ ВТ_МаксСуммы
ИЗ
ВТ_ОборотыПоТочкам КАК ВТ
СГРУППИРОВАТЬ ПО
ВТ.Контрагент
ИНДЕКСИРОВАТЬ ПО
Контрагент,
МаксСумма
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Обороты.Контрагент КАК Контрагент,
МИНИМУМ(ВТ_Обороты.ТорговаяТочка) КАК ТорговаяТочка,
ВТ_Обороты.СуммаОборот КАК СуммаОборот
ПОМЕСТИТЬ ВТ_ИтоговыеТочки
ИЗ
ВТ_ОборотыПоТочкам КАК ВТ_Обороты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_МаксСуммы КАК ВТ_Макс
ПО ВТ_Обороты.Контрагент = ВТ_Макс.Контрагент
И ВТ_Обороты.СуммаОборот = ВТ_Макс.МаксСумма
СГРУППИРОВАТЬ ПО
ВТ_Обороты.Контрагент,
ВТ_Обороты.СуммаОборот
;
Такой подход обеспечивает математическую строгость: даже если у клиента есть пять торговых точек с одинаковым максимальным объемом продаж, во временную таблицу ВТ_ИтоговыеТочки попадет строго одна запись.
Когда данные необходимо быстро обработать во встроенном языке 1С без написания громоздких самосоединений, удобно использовать секцию ИТОГИ ПО с последующим обходом группировок. При обходе по группировке верхнего уровня мы просто считываем первую детальную запись и переходим к следующему контрагенту.
Посмотрим на пример кода модуля:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ПродажиОбороты.Контрагент КАК Контрагент,
| ПродажиОбороты.ТорговаяТочка КАК ТорговаяТочка,
| СУММА(ПродажиОбороты.СтоимостьОборот) КАК СтоимостьОборот
|ИЗ
| РегистрНакопления.Продажи.Обороты(&Дата1, &Дата2, Авто, ) КАК ПродажиОбороты
|СГРУППИРОВАТЬ ПО
| ПродажиОбороты.Контрагент,
| ПродажиОбороты.ТорговаяТочка
|УПОРЯДОЧИТЬ ПО
| Контрагент,
| СтоимостьОборот УБЫВ
|ИТОГИ ПО
| Контрагент";
Запрос.УстановитьПараметр("Дата1", НачалоГода(ТекущаяДата()));
Запрос.УстановитьПараметр("Дата2", КонецГода(ТекущаяДата()));
РезультатЗапроса = Запрос.Выполнить();
ТаблицаИтогов = Новый ТаблицаЗначений;
ТаблицаИтогов.Колонки.Добавить("Контрагент");
ТаблицаИтогов.Колонки.Добавить("ТорговаяТочка");
ТаблицаИтогов.Колонки.Добавить("СтоимостьОборот");
ВыборкаКонтрагенты = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);
Пока ВыборкаКонтрагенты.Следующий() Цикл
ВыборкаДетали = ВыборкаКонтрагенты.Выбрать();
// Считываем только самую первую строку детальных записей для каждого контрагента
Если ВыборкаДетали.Следующий() Тогда
НоваяСтрока = ТаблицаИтогов.Добавить();
ЗаполнитьЗначенияСвойств(НоваяСтрока, ВыборкаДетали);
КонецЕсли;
КонецЦикла;
В этом примере за счет конструкции УПОРЯДОЧИТЬ ПО СтоимостьОборот УБЫВ первая выбранная детальная запись всегда будет содержать максимальную сумму оборота. Полученную таблицу ТаблицаИтогов можно передать в качестве параметра в следующий запрос через &ТаблицаИтогов или поместить в менеджер временных таблиц.
Если задача решается в рамках формирования отчета, от сложных манипуляций в тексте запроса можно отказаться с помощью стандартного функционала СКД:
Контрагент.ТорговаяТочка).СтоимостьОборот по убыванию.1.В результате компоновщик данных автоматически ограничит вывод, оставив ровно одну строку с максимальным значением для каждого контрагента.
При построении запросов к регистрам накопления и документов обратите внимание на следующие правила:
Регистратор.Сделка.ТорговаяТочка вызывают неявные левые соединения таблиц документов, что приводит к деградации производительности. Нужные аналитические разрезы следует выносить в измерения регистра.Запись в параметрах виртуальной таблицы оборотов, а для расчета сумм по точкам — периодичность Авто.Контрагент и Период) их индексация через ИНДЕКСИРОВАТЬ ПО является критически важной для скорости выполнения запроса СУБД.