Первые N записей в каждой группе одним запросом: топ товаров клиента и топ покупателей в УТ 11.5.27.70, КА 2.5.27.93 и БП 3.0.203.18

В языке запросов 1С нет оконных функций, а задача "три главных товара у каждого клиента" встречается постоянно. Место в группе считается соединением временной таблицы с самой собой, без цикла в коде. Запрос сверен с процедурным расчетом мест на трех реальных базах.

Задача

Нужно показать не просто самые продаваемые товары, а первые три товара у каждого клиента. Или пять крупнейших покупателей в каждой организации. В SQL это решается функцией ROW_NUMBER с разбиением по группе, но в языке запросов 1С оконных функций нет, а ПЕРВЫЕ N ограничивает весь результат, а не каждую группу.

Решение

Суммы по группе и элементу кладутся во временную таблицу. Затем таблица соединяется сама с собой левым соединением по условию: та же группа и сумма больше. Число таких строк плюс один и есть место элемента в группе. Остается отобрать строки, у которых место не больше N, и добавить итог группы, чтобы показать долю.

В Управлении торговлей и Комплексной автоматизации группа это клиент (партнер), элемент это номенклатура, сумма берется из оборотов регистра ВыручкаИСебестоимостьПродаж. В Бухгалтерии регистра продаж по товарам нет, поэтому пример другой: группа это организация, элемент это покупатель, сумма это дебетовый оборот счета 62 в корреспонденции со счетом 90.

Места из запроса сверены с процедурным расчетом по тем же оборотам на трех базах: расхождений нет, итоги групп совпадают.

// Первые N товаров по выручке у каждого клиента за период.
// В языке запросов 1С нет оконных функций, поэтому место товара считается
// соединением таблицы с самой собой: место = число товаров того же клиента
// с большей выручкой плюс один.

// 1. Выручка за период в разрезе клиента (партнера) и товара.
ВЫБРАТЬ
	Продажи.АналитикаУчетаПоПартнерам.Партнер КАК Клиент,
	Продажи.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура,
	СУММА(Продажи.СуммаВыручкиОборот) КАК Выручка
ПОМЕСТИТЬ втВыручка
ИЗ
	РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&Начало, &Конец, , ) КАК Продажи
СГРУППИРОВАТЬ ПО
	Продажи.АналитикаУчетаПоПартнерам.Партнер,
	Продажи.АналитикаУчетаНоменклатуры.Номенклатура
ИМЕЮЩИЕ
	СУММА(Продажи.СуммаВыручкиОборот) > 0
ИНДЕКСИРОВАТЬ ПО
	Клиент
;

// 2. Место товара у клиента: сколько его товаров продано на большую сумму, плюс один.
//    Товары с одинаковой выручкой получают одно место.
ВЫБРАТЬ
	Текущий.Клиент КАК Клиент,
	Текущий.Номенклатура КАК Номенклатура,
	Текущий.Выручка КАК Выручка,
	КОЛИЧЕСТВО(Выше.Номенклатура) + 1 КАК Место
ПОМЕСТИТЬ втМеста
ИЗ
	втВыручка КАК Текущий
		ЛЕВОЕ СОЕДИНЕНИЕ втВыручка КАК Выше
		ПО (Выше.Клиент = Текущий.Клиент)
			И (Выше.Выручка > Текущий.Выручка)
СГРУППИРОВАТЬ ПО
	Текущий.Клиент,
	Текущий.Номенклатура,
	Текущий.Выручка
;

// 3. Выручка клиента целиком, чтобы показать долю товара.
ВЫБРАТЬ
	Строки.Клиент КАК Клиент,
	СУММА(Строки.Выручка) КАК ВыручкаКлиента
ПОМЕСТИТЬ втКлиенты
ИЗ
	втВыручка КАК Строки
СГРУППИРОВАТЬ ПО
	Строки.Клиент
ИНДЕКСИРОВАТЬ ПО
	Клиент
;

// 4. Первые N мест у каждого клиента.
ВЫБРАТЬ
	Места.Клиент КАК Клиент,
	Места.Место КАК Место,
	Места.Номенклатура КАК Номенклатура,
	Места.Выручка КАК Выручка,
	ВЫРАЗИТЬ(Места.Выручка * 100 / Клиенты.ВыручкаКлиента КАК ЧИСЛО(5, 1)) КАК ДоляПроцент,
	Клиенты.ВыручкаКлиента КАК ВыручкаКлиента
ИЗ
	втМеста КАК Места
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ втКлиенты КАК Клиенты
		ПО (Клиенты.Клиент = Места.Клиент)
ГДЕ
	Места.Место <= &КоличествоМест
УПОРЯДОЧИТЬ ПО
	Клиенты.ВыручкаКлиента УБЫВ,
	Места.Клиент,
	Места.Место

// Первые N товаров по выручке у каждого клиента за период.
// В языке запросов 1С нет оконных функций, поэтому место товара считается
// соединением таблицы с самой собой: место = число товаров того же клиента
// с большей выручкой плюс один.

// 1. Выручка за период в разрезе клиента (партнера) и товара.
ВЫБРАТЬ
	Продажи.АналитикаУчетаПоПартнерам.Партнер КАК Клиент,
	Продажи.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура,
	СУММА(Продажи.СуммаВыручкиОборот) КАК Выручка
ПОМЕСТИТЬ втВыручка
ИЗ
	РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&Начало, &Конец, , ) КАК Продажи
СГРУППИРОВАТЬ ПО
	Продажи.АналитикаУчетаПоПартнерам.Партнер,
	Продажи.АналитикаУчетаНоменклатуры.Номенклатура
ИМЕЮЩИЕ
	СУММА(Продажи.СуммаВыручкиОборот) > 0
ИНДЕКСИРОВАТЬ ПО
	Клиент
;

// 2. Место товара у клиента: сколько его товаров продано на большую сумму, плюс один.
//    Товары с одинаковой выручкой получают одно место.
ВЫБРАТЬ
	Текущий.Клиент КАК Клиент,
	Текущий.Номенклатура КАК Номенклатура,
	Текущий.Выручка КАК Выручка,
	КОЛИЧЕСТВО(Выше.Номенклатура) + 1 КАК Место
ПОМЕСТИТЬ втМеста
ИЗ
	втВыручка КАК Текущий
		ЛЕВОЕ СОЕДИНЕНИЕ втВыручка КАК Выше
		ПО (Выше.Клиент = Текущий.Клиент)
			И (Выше.Выручка > Текущий.Выручка)
СГРУППИРОВАТЬ ПО
	Текущий.Клиент,
	Текущий.Номенклатура,
	Текущий.Выручка
;

// 3. Выручка клиента целиком, чтобы показать долю товара.
ВЫБРАТЬ
	Строки.Клиент КАК Клиент,
	СУММА(Строки.Выручка) КАК ВыручкаКлиента
ПОМЕСТИТЬ втКлиенты
ИЗ
	втВыручка КАК Строки
СГРУППИРОВАТЬ ПО
	Строки.Клиент
ИНДЕКСИРОВАТЬ ПО
	Клиент
;

// 4. Первые N мест у каждого клиента.
ВЫБРАТЬ
	Места.Клиент КАК Клиент,
	Места.Место КАК Место,
	Места.Номенклатура КАК Номенклатура,
	Места.Выручка КАК Выручка,
	ВЫРАЗИТЬ(Места.Выручка * 100 / Клиенты.ВыручкаКлиента КАК ЧИСЛО(5, 1)) КАК ДоляПроцент,
	Клиенты.ВыручкаКлиента КАК ВыручкаКлиента
ИЗ
	втМеста КАК Места
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ втКлиенты КАК Клиенты
		ПО (Клиенты.Клиент = Места.Клиент)
ГДЕ
	Места.Место <= &КоличествоМест
УПОРЯДОЧИТЬ ПО
	Клиенты.ВыручкаКлиента УБЫВ,
	Места.Клиент,
	Места.Место

// Первые N покупателей по отгрузке в каждой организации за период.
// В языке запросов 1С нет оконных функций, поэтому место покупателя считается
// соединением таблицы с самой собой: место = число покупателей той же организации
// с большей отгрузкой плюс один.

// 1. Отгрузка за период: дебетовые обороты счета 62 в корреспонденции со счетом 90
//    в разрезе организации и контрагента.
ВЫБРАТЬ
	Обороты.Организация КАК Организация,
	Обороты.Субконто1 КАК Покупатель,
	СУММА(Обороты.СуммаОборотДт) КАК Отгрузка
ПОМЕСТИТЬ втОтгрузка
ИЗ
	РегистрБухгалтерии.Хозрасчетный.Обороты(
			&Начало,
			&Конец,
			,
			Счет В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.РасчетыСПокупателямиИЗаказчиками)),
			ЗНАЧЕНИЕ(ПланВидовХарактеристик.ВидыСубконтоХозрасчетные.Контрагенты),
			,
			КорСчет В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.Продажи)),
			) КАК Обороты
СГРУППИРОВАТЬ ПО
	Обороты.Организация,
	Обороты.Субконто1
ИМЕЮЩИЕ
	СУММА(Обороты.СуммаОборотДт) > 0
ИНДЕКСИРОВАТЬ ПО
	Организация
;

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

// 3. Отгрузка организации целиком, чтобы показать долю покупателя.
ВЫБРАТЬ
	Строки.Организация КАК Организация,
	СУММА(Строки.Отгрузка) КАК ОтгрузкаОрганизации
ПОМЕСТИТЬ втОрганизации
ИЗ
	втОтгрузка КАК Строки
СГРУППИРОВАТЬ ПО
	Строки.Организация
ИНДЕКСИРОВАТЬ ПО
	Организация
;

// 4. Первые N мест в каждой организации.
ВЫБРАТЬ
	Места.Организация КАК Организация,
	Места.Место КАК Место,
	Места.Покупатель КАК Покупатель,
	Места.Отгрузка КАК Отгрузка,
	ВЫРАЗИТЬ(Места.Отгрузка * 100 / Организации.ОтгрузкаОрганизации КАК ЧИСЛО(5, 1)) КАК ДоляПроцент,
	Организации.ОтгрузкаОрганизации КАК ОтгрузкаОрганизации
ИЗ
	втМеста КАК Места
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ втОрганизации КАК Организации
		ПО (Организации.Организация = Места.Организация)
ГДЕ
	Места.Место <= &КоличествоМест
УПОРЯДОЧИТЬ ПО
	Организации.ОтгрузкаОрганизации УБЫВ,
	Места.Организация,
	Места.Место

Что учесть

• Элементы с одинаковой суммой получают одно место, поэтому в группе может оказаться больше N строк. Если нужно строго N, добавьте второе условие упорядочивания, например по коду элемента, в условие соединения.
• Соединение таблицы с собой растет как квадрат числа элементов в группе. Для клиентов с тысячами товаров сузьте период или отберите товары с заметной выручкой до соединения.
• Псевдоним таблицы в запросе не должен совпадать с именем поля: запись вида втВыручка КАК Выручка при поле Выручка дает ошибку "Неоднозначное поле".
• В Бухгалтерии в выборку попадает отгрузка с НДС, как в проводке.

Как воспользоваться

1. Выберите вкладку своей конфигурации над кодом: под каждой лежит запрос, выполненный именно в этой конфигурации.
2. Скачайте обработку под ней и откройте в 1С через "Файл, Открыть". Форма покажет поля отбора, результат появится в той же форме таблицей.
3. Нужно иначе: скопируйте запрос кнопкой над кодом и подставьте в консоль запросов или в свою обработку. Все параметры перечислены в тексте, значения по умолчанию видны в обработке.
4. Хотите то же самое, но под свою базу и свои поля: подключите базу к мосту и попросите ИИ доработать этот запрос. Он увидит структуру вашей конфигурации и проверит результат на ваших данных.