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