При разработке аналитических отчетов в системе 1С:Предприятие (особенно систем расчета мотивации менеджеров, начисления бонусов или анализа скидок) часто возникает классическая задача: сопоставить полученный расчетный показатель с определенным интервалом или порогом из регистра сведений. Например, если менеджер продал товар со скидкой до 2%, ему полагается один процент вознаграждения, если скидка составила от 2% до 20% — другой процент, а все, что выше — третий.
Сложность заключается в том, что связь между фактическими продажами и таблицей порогов является нелинейной (условия вида «больше», «меньше или равно» вместо простого равенства). В этой статье мы подробно разберем, почему штатный механизм связей наборов данных в Системе компоновки данных (СКД) часто оказывается неэффективным, и рассмотрим три надежных, проверенных на практике способа решения этой задачи с помощью языка запросов 1С и программного кода.
Начинающие разработчики часто пытаются решить эту задачу «в лоб» через вкладку «Связи наборов данных» в конструкторе СКД. Они создают два набора данных (один для продаж, другой для регистра порогов) и связывают их. Однако этот подход таит в себе серьезные архитектурные проблемы:
LEFT JOIN. При этом платформа оптимизирует такие запросы, рассчитывая на строгое равенство связующих полей.Чтобы избежать этих проблем, все вычисления и сопоставления порогов необходимо переносить непосредственно в текст запроса (в рамках единого набора данных типа «Запрос») либо осуществлять предварительную подготовку данных программно. Рассмотрим эти методы подробнее.
Этот метод является стандартом разработки в 1С. Он работает максимально быстро на уровне СУБД и легко масштабируется. Суть метода заключается в поиске минимального верхнего порога, который больше или равен нашей фактической скидке.
Давайте разберем логику работы на примере. Пусть в регистре сведений заданы следующие пороги для Агента 1 по группе товаров «Химия»:
Если у агента фактическая скидка получилась равной 1.5%, нам нужно сопоставить ее с порогом 2% (так как 1.5% больше 0%, но меньше или равно 2%). С точки зрения SQL-запроса, нам нужно отобрать все пороги, которые больше или равны 1.5% (это будут пороги 2% и 20%), а затем выбрать из них МИНИМАЛЬНЫЙ. Минимальным среди них окажется именно порог 2%. Задача решена!
Разберем по шагам построение такого запроса:
// ШАГ 1: Рассчитываем фактические продажи и рассчитываем процент скидки по агентам во временную таблицу
ВЫБРАТЬ
ПродажиОбороты.Агент КАК Агент,
ПродажиОбороты.Номенклатура.Родитель КАК ТоварнаяГруппа,
ПродажиОбороты.СтоимостьБезСкидокОборот КАК СтоимостьБезСкидок,
ПродажиОбороты.СтоимостьОборот КАК Стоимость,
// Защита от деления на ноль: если продаж без скидки не было, скидка равна 0
ВЫБОР
КОГДА ПродажиОбороты.СтоимостьБезСкидокОборот = 0 ТОГДА 0
ИНАЧЕ (ПродажиОбороты.СтоимостьБезСкидокОборот - ПродажиОбороты.СтоимостьОборот) / ПродажиОбороты.СтоимостьБезСкидокОборот * 100
КОНЕЦ КАК СкидкаФакт
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Авто, ) КАК ПродажиОбороты
ИНДЕКСИРОВАТЬ ПО
Агент,
ТоварнаяГруппа
;
// ШАГ 2: Сопоставляем продажи с регистром порогов и находим минимально подходящий верхний порог
ВЫБРАТЬ
Продажи.Агент КАК Агент,
Продажи.ТоварнаяГруппа КАК ТоварнаяГруппа,
Продажи.СкидкаФакт КАК СкидкаФакт,
Продажи.Стоимость КАК Стоимость,
МИНИМУМ(ЕСТЬNULL(Пороги.Порог, 0)) КАК СопоставленныйПорог
ПОМЕСТИТЬ ВТ_СопоставленныеПороги
ИЗ
ВТ_Продажи КАК Продажи
// Соединяем по условию: ищем все пороги регистра, которые БОЛЬШЕ или РАВНЫ фактической скидке
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПорогиПродаж КАК Пороги
ПО Пороги.Агент = Продажи.Агент
И Пороги.Товар = Продажи.ТоварнаяГруппа
И Пороги.Порог >= Продажи.СкидкаФакт
СГРУППИРОВАТЬ ПО
Продажи.Агент,
Продажи.ТоварнаяГруппа,
Продажи.СкидкаФакт,
Продажи.Стоимость
ИНДЕКСИРОВАТЬ ПО
Агент,
ТоварнаяГруппа,
СопоставленныйПорог
;
// ШАГ 3: Получаем финальные данные с процентами ЗП из регистра по найденному порогу
ВЫБРАТЬ
Сопоставленные.Агент КАК Агент,
Сопоставленные.ТоварнаяГруппа КАК ТоварнаяГруппа,
Сопоставленные.СкидкаФакт КАК СкидкаФакт,
Сопоставленные.Стоимость КАК СтоимостьПродаж,
Сопоставленные.СопоставленныйПорог КАК Порог,
ЕСТЬNULL(Пороги.ПроцентЗП, 0) КАК ПроцентЗП,
Сопоставленные.Стоимость * ЕСТЬNULL(Пороги.ПроцентЗП, 0) / 100 КАК СуммаЗП
ИЗ
ВТ_СопоставленныеПороги КАК Сопоставленные
// Финальное точное соединение для получения ресурсов из регистра сведений
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПорогиПродаж КАК Пороги
ПО Пороги.Агент = Сопоставленные.Агент
И Пороги.Товар = Сопоставленные.ТоварнаяГруппа
И Пороги.Порог = Сопоставленные.СопоставленныйПорог
Важное примечание: Обратите внимание на индексацию временных таблиц по полям соединения (Агент, ТоварнаяГруппа). Это критически важно для СУБД (MS SQL, PostgreSQL) при работе с большими объемами данных. Без индексов производительность соединения таблиц будет падать в геометрической прогрессии.
Иногда пороги в регистре могут храниться хаотично, или бизнес-логика требует четкого разделения на интервалы вида [Минимум, Максимум). В таком случае мы можем "развернуть" плоскую таблицу порогов в интервальную структуру прямо внутри запроса.
Для этого мы свяжем таблицу порогов саму с собой, чтобы для каждого текущего порога определить следующий за ним (который станет верхней границей интервала). Проанализируем, как это реализовать:
// ШАГ 1: Превращаем плоские пороги в диапазоны с нижней и верхней границей
ВЫБРАТЬ
Пороги.Агент КАК Агент,
Пороги.Товар КАК Товар,
Пороги.Порог КАК ПорогМин,
// Находим минимальный из всех порогов, которые строго больше текущего — это и будет верхняя граница
ЕСТЬNULL(МИНИМУМ(ПорогиСлед.Порог), 999999) КАК ПорогМакс,
Пороги.ПроцентЗП КАК ПроцентЗП
ПОМЕСТИТЬ ВТ_ДиапазоныПорогов
ИЗ
РегистрСведений.ПорогиПродаж КАК Пороги
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПорогиПродаж КАК ПорогиСлед
ПО ПорогиСлед.Агент = Пороги.Агент
И ПорогиСлед.Товар = Пороги.Товар
И ПорогиСлед.Порог > Пороги.Порог
СГРУППИРОВАТЬ ПО
Пороги.Агент,
Пороги.Товар,
Пороги.Порог,
Пороги.ПроцентЗП
ИНДЕКСИРОВАТЬ ПО
Агент,
Товар
;
// ШАГ 2: Соединяем фактические продажи с полученными интервалами
ВЫБРАТЬ
Продажи.Агент КАК Агент,
Продажи.ТоварнаяГруппа КАК ТоварнаяГруппа,
Продажи.СкидкаФакт КАК СкидкаФакт,
Диапазоны.ПорогМин КАК ЛеваяГраница,
Диапазоны.ПорогМакс КАК ПраваяГраница,
ЕСТЬNULL(Диапазоны.ПроцентЗП, 0) КАК ПроцентЗП
ИЗ
ВТ_Продажи КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ДиапазоныПорогов КАК Диапазоны
ПО Диапазоны.Агент = Продажи.Агент
И Диапазоны.Товар = Продажи.ТоварнаяГруппа
// Вхождение в полуинтервал: фактическая скидка строго больше минимума и меньше либо равна максимуму
И Продажи.СкидкаФакт > Диапазоны.ПорогМин
И Продажи.СкидкаФакт <= Диапазоны.ПорогМакс
Этот способ более нагляден, если вам требуется выводить в отчет не просто конечный процент, но и сам диапазон скидок (например, колонки "Скидка от", "Скидка до"), в который попал менеджер по результатам работы.
Как отмечалось в обсуждении на форуме (сообщения 6 и 8), в реальном бизнесе часто возникают ситуации, когда прайсовые цены существенно меняются в течение отчетного периода. Если алгоритм расчета усложняется (например, нужно проверять историю изменения цен на каждую секунду транзакции или учитывать множество дополнительных условий), построить один красивый SQL-запрос становится чрезвычайно тяжело.
В таких случаях самым надежным решением является программная подготовка данных на встроенном языке 1С с последующей передачей готовой таблицы в СКД через Набор данных - Объект.
Разберем по шагам, как настроить этот механизм:
ТаблицаРезультатов.Агент, ТоварнаяГруппа, СкидкаФакт, ПроцентЗП, СуммаЗП.ПриКомпоновкеРезультата.Ниже представлен пример программной реализации этого алгоритма:
Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка)
СтандартнаяОбработка = Ложь; // Отключаем стандартный вывод отчета
// 1. Запросом получаем продажи за период отчета
ЗапросПродаж = Новый Запрос;
ЗапросПродаж.Текст =
"ВЫБРАТЬ
| Продажи.Агент КАК Агент,
| Продажи.Номенклатура.Родитель КАК ТоварнаяГруппа,
| СУММА(Продажи.СтоимостьБезСкидокОборот) КАК СтоимостьБезСкидок,
| СУММА(Продажи.СтоимостьОборот) КАК Стоимость
|ИЗ
| РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода) КАК Продажи
|ГРУППИРОВАТЬ ПО
| Продажи.Агент,
| Продажи.Номенклатура.Родитель";
ЗапросПродаж.УстановитьПараметр("НачалоПериода", КомпоновщикНастроек.Настройки.ПараметрыДанных.Элементы.Найти("НачалоПериода").Значение);
ЗапросПродаж.УстановитьПараметр("КонецПериода", КомпоновщикНастроек.Настройки.ПараметрыДанных.Элементы.Найти("КонецПериода").Значение);
ТЗ_Продажи = ЗапросПродаж.Выполнить().Выгрузить();
// 2. Запросом выгружаем всю таблицу порогов в память
ЗапросПорогов = Новый Запрос("ВЫБРАТЬ Агент, Товар, Порог, ПроцентЗП ИЗ РегистрСведений.ПорогиПродаж УПОРЯДОЧИТЬ ПО Агент, Товар, Порог Возр");
ТЗ_Пороги = ЗапросПорогов.Выполнить().Выгрузить();
// 3. Создаем результирующую таблицу для СКД
ТаблицаРезультатов = Новый ТаблицаЗначений;
ТаблицаРезультатов.Колонки.Добавить("Агент");
ТаблицаРезультатов.Колонки.Добавить("ТоварнаяГруппа");
ТаблицаРезультатов.Колонки.Добавить("СкидкаФакт", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)));
ТаблицаРезультатов.Колонки.Добавить("ПроцентЗП", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(5, 2)));
ТаблицаРезультатов.Колонки.Добавить("СуммаЗП", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)));
// 4. Обходим продажи в цикле и сопоставляем пороги программно
Для Каждого СтрокаПродаж Из ТЗ_Продажи Цикл
// Считаем фактическую скидку
СкидкаФакт = 0;
Если СтрокаПродаж.СтоимостьБезСкидок > 0 Тогда
СкидкаФакт = (СтрокаПродаж.СтоимостьБезСкидок - СтрокаПродаж.Стоимость) / СтрокаПродаж.СтоимостьБезСкидок * 100;
КонецЕсли;
// Ищем порог в ТЗ_Пороги методом ручного перебора (так как таблица отсортирована по возрастанию порога)
НайденныйПроцентЗП = 0;
ОтборПорогов = Новый Структура("Агент, Товар", СтрокаПродаж.Агент, СтрокаПродаж.ТоварнаяГруппа);
СтрокиПорогов = ТЗ_Пороги.НайтиСтроки(ОтборПорогов);
Для Каждого СтрокаПорога Из СтрокиПорогов Цикл
// Если фактическая скидка не превышает порог, то мы нашли нужную нам ступень
Если СкидкаФакт <= СтрокаПорога.Порог Тогда
НайденныйПроцентЗП = СтрокаПорога.ПроцентЗП;
Прервать; // Выходим из цикла поиска порогов
КонецЕсли;
КонецДля;
// Заполняем результирующую строку
НоваяСтрока = ТаблицаРезультатов.Добавить();
ЗаполнитьЗначенияСвойств(НоваяСтрока, СтрокаПродаж);
НоваяСтрока.СкидкаФакт = СкидкаФакт;
НоваяСтрока.ПроцентЗП = НайденныйПроцентЗП;
НоваяСтрока.СуммаЗП = СтрокаПродаж.Стоимость * НайденныйПроцентЗП / 100;
КонецДля;
// 5. Передаем готовую таблицу в СКД
ВнешниеНаборыДанных = Новый Структура;
ВнешниеНаборыДанных.Вставить("ТаблицаРезультатов", ТаблицаРезультатов);
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, КомпоновщикНастроек.ПолучитьНастройки(), ДанныеРасшифровки);
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, ВнешниеНаборыДанных, ДанныеРасшифровки, Истина);
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВТабличныйДокумент;
ПроцессорВывода.УстановитьДокумент(ДокументРезультат);
ПроцессорВывода.Вывести(ПроцессорКомпоновки);
КонецПроцедуры
Данный подход гарантирует 100% точность расчетов, легко поддается отладке с помощью стандартного конфигуратора 1С и позволяет внедрить абсолютно любую (даже самую экзотическую) логику сопоставления интервалов, которую невозможно выразить стандартным языком запросов.
Подводя итоги нашего анализа, дадим практические рекомендации по выбору архитектуры решения: