При разработке отчетов на базе системы компоновки данных (СКД) одной из классических задач является вывод различных показателей в одной таблице. Очень часто пользователям требуется видеть в одном отчете не только объемы продаж и себестоимость, но и сопутствующие цены — например, актуальную закупочную и розничную цену номенклатуры на момент анализа. При реализации этой задачи начинающие разработчики часто сталкиваются с техническими сложностями: от падения производительности СУБД из-за неоптимальных соединений до банальных синтаксических ошибок в запросах. В этой статье мы подробно разберем, как правильно реализовать вывод нескольких типов цен, оптимизировать этот процесс и корректно рассчитать процент рентабельности в СКД.
Рассмотрим базовый сценарий, с которого начал автор темы на форуме. Имеется запрос к оборотам продаж, к которому необходимо присоединить цены из регистра сведений ЦеныНоменклатуры. Первое интуитивное решение, которое приходит в голову — написать левое соединение к виртуальной таблице СрезПоследних для каждого типа цен отдельно.
Давайте посмотрим, как выглядит структура такого запроса примитивным способом:
ВЫБРАТЬ
ПродажиОбороты.Номенклатура КАК Номенклатура,
ПродажиОбороты.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
ПродажиОбороты.ЗаказПокупателя КАК ЗаказПокупателя,
ПродажиОбороты.КоличествоОборот КАК Количество,
ПродажиОбороты.СтоимостьОборот КАК ПродажнаяСтоимость,
ЦеныЗакупка.Цена КАК ЦенаЗакупка,
ЦеныРозница.Цена КАК ЦенаРозница
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода) КАК ПродажиОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&КонецПериода, ТипЦен = &ТипЦенЗакупка) КАК ЦеныЗакупка
ПО (ЦеныЗакупка.Номенклатура = ПродажиОбороты.Номенклатура)
И (ЦеныЗакупка.ХарактеристикаНоменклатуры = ПродажиОбороты.ХарактеристикаНоменклатуры)
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&КонецПериода, ТипЦен = &ТипЦенРозница) КАК ЦеныРозница
ПО (ЦеныРозница.Номенклатура = ПродажиОбороты.Номенклатура)
И (ЦеныРозница.ХарактеристикаНоменклатуры = ПродажиОбороты.ХарактеристикаНоменклатуры)
Почему это решение неоптимально? Дело в том, что виртуальная таблица СрезПоследних является "тяжелой" для базы данных. Платформа 1С:Предприятие транслирует каждое такое обращение в сложный подзапрос к физической таблице регистра с использованием максимальных дат. Когда мы делаем несколько левых соединений к одной и той же виртуальной таблице с разными параметрами отбора, СУБД вынуждена выполнять эти ресурсоемкие операции многократно. На базах с большим объемом номенклатуры и частым изменением цен такой отчет будет работать крайне медленно или вызовет перегрузку сервера.
Для того чтобы избежать многократных обращений к виртуальной таблице СрезПоследних, мы можем применить архитектурный прием — получить все необходимые цены за один проход, поместить их во временную таблицу, развернув строки в колонки (транспонировать), а затем выполнить всего одно легкое соединение с таблицей продаж.
Проанализируем этот алгоритм по шагам:
В (&ТипЦенЗакупка, &ТипЦенРозница).МАКСИМУМ и оператора ВЫБОР, чтобы распределить значения цен по соответствующим колонкам.ВТ_ЦеныНоменклатуры.Давайте посмотрим на пример реализации этого эффективного запроса:
ВЫБРАТЬ
ЦеныСрез.Номенклатура КАК Номенклатура,
ЦеныСрез.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
МАКСИМУМ(ВЫБОР
КОГДА ЦеныСрез.ТипЦен = &ТипЦенЗакупка
ТОГДА ЦеныСрез.Цена
ИНАЧЕ 0
КОНЕЦ) КАК ЦенаЗакупка,
МАКСИМУМ(ВЫБОР
КОГДА ЦеныСрез.ТипЦен = &ТипЦенРозница
ТОГДА ЦеныСрез.Цена
ИНАЧЕ 0
КОНЕЦ) КАК ЦенаРозница
ПОМЕСТИТЬ ВТ_ЦеныНоменклатуры
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&КонецПериода, ТипЦен В (&ТипЦенЗакупка, &ТипЦенРозница)) КАК ЦеныСрез
СГРУППИРОВАТЬ ПО
ЦеныСрез.Номенклатура,
ЦеныСрез.ХарактеристикаНоменклатуры
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ПродажиОбороты.Номенклатура КАК Номенклатура,
ПродажиОбороты.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
ПродажиОбороты.ЗаказПокупателя КАК ЗаказПокупателя,
ПродажиОбороты.КоличествоОборот КАК Количество,
ПродажиОбороты.СтоимостьОборот КАК ПродажнаяСтоимость,
ЕСТЬNULL(ВТ_ЦеныНоменклатуры.ЦенаЗакупка, 0) КАК ЦенаЗакупка,
ЕСТЬNULL(ВТ_ЦеныНоменклатуры.ЦенаРозница, 0) КАК ЦенаРозница,
ЕСТЬNULL(ПродажиСебестоимость.Стоимость, 0) КАК Себестоимость
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода) КАК ПродажиОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ПродажиСебестоимость КАК ПродажиСебестоимость
ПО (ПродажиСебестоимость.Номенклатура = ПродажиОбороты.Номенклатура)
И (ПродажиСебестоимость.ХарактеристикаНоменклатуры = ПродажиОбороты.ХарактеристикаНоменклатуры)
И (ПродажиСебестоимость.ЗаказПокупателя = ПродажиОбороты.ЗаказПокупателя)
И (ПродажиСебестоимость.Период МЕЖДУ &НачалоПериода И &КонецПериода)
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ЦеныНоменклатуры КАК ВТ_ЦеныНоменклатуры
ПО (ВТ_ЦеныНоменклатуры.Номенклатура = ПродажиОбороты.Номенклатура)
И (ВТ_ЦеныНоменклатуры.ХарактеристикаНоменклатуры = ПродажиОбороты.ХарактеристикаНоменклатуры)
Такой подход минимизирует количество тяжелых дисковых операций СУБД и значительно ускоряет формирование отчета, особенно на больших информационных базах.
Раз уж мы работаем в рамках СКД, платформа предлагает нам еще один профессиональный инструмент — Связи наборов данных. Мы можем полностью отказаться от сложных и тяжелых соединений внутри одного текста запроса и переложить эту работу на саму систему компоновки данных.
Давайте разберем по шагам, как это настроить в интерфейсе схемы компоновки данных:
НаборДанныхПродажи. В его тексте мы выбираем только данные о продажах и себестоимости (без каких-либо левых соединений с ценами).НаборДанныхЦены. Текст этого запроса будет максимально простым:
ВЫБРАТЬ
Цены.Номенклатура КАК Номенклатура,
Цены.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
Цены.Цена КАК Цена,
Цены.ТипЦен КАК ТипЦен
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ПериодЦен, Номенклатура = &Номенклатура И ХарактеристикаНоменклатуры = &Характеристика) КАК Цены
НаборДанныхПродажи, а Приемником — НаборДанныхЦены.Номенклатура и ХарактеристикаНоменклатуры.&Номенклатура передадим поле Номенклатура из источника, в &Характеристика — поле ХарактеристикаНоменклатуры.&ПериодЦен мы можем передавать дату конкретного документа или дату продажи. Это позволит получить не статичную цену на конец всего периода отчета, а исторически точную цену, актуальную непосредственно в момент совершения конкретной продажи!В настройках структуры отчета СКД мы можем вынести поле ТипЦен в колонки таблицы, а поле Цена поместить в выбранные поля. В результате система автоматически сформирует динамическую таблицу, где под каждый выбранный пользователем тип цен будет создана своя отдельная колонка без необходимости жестко прописывать их в кодовой базе.
В сообщении №8 автор темы столкнулся с синтаксической ошибкой при попытке написать условное выражение для расчета рентабельности. Код автора выглядел следующим образом:
КОГДА СУММА(ПродажиОбороты.СтоимостьОборот) <> 0
ТОГДА (СУММА(ПродажиОбороты.СтоимостьОборот) - СУММА(ВложенПродажиСебестоимостьныйЗапрос.Стоимость)) / СУММА(ПродажиОбороты.СтоимостьОборот)
ИНАЧЕ 0
КОНЕЦ КАК Рентабельность
Выясним причину этой синтаксической ошибки. В языке запросов 1С не существует изолированного оператора КОГДА. Любое ветвление и проверка условий должны быть обязательно обернуты в конструкцию ВЫБОР ... КОНЕЦ. Также в коде автора была допущена грубая опечатка в имени таблицы: ВложенПродажиСебестоимостьныйЗапрос, которая возникла из-за некорректного автозаполнения или слияния слов "ВложенныйЗапрос" и "ПродажиСебестоимость".
Правильный синтаксис условного оператора в запросе должен выглядеть так:
ВЫБОР
КОГДА СУММА(ПродажиОбороты.СтоимостьОборот) <> 0
ТОГДА (СУММА(ПродажиОбороты.СтоимостьОборот) - СУММА(ПродажиСебестоимость.Стоимость)) / СУММА(ПродажиОбороты.СтоимостьОборот)
ИНАЧЕ 0
КОНЕЦ КАК Рентабельность
Однако, несмотря на то что этот запрос успешно пройдет синтаксический контроль и выполнится без ошибок, писать расчет рентабельности непосредственно в тексте запроса СКД категорически запрещено.
Рассмотрим, к каким негативным последствиям приводит расчет расчетных показателей (таких как рентабельность, наценка, доли или проценты) непосредственно внутри текста запроса.
Когда СКД получает результат выполнения запроса, она начинает строить группировки и рассчитывать итоги (общие итоги по отчету, итоги по папкам номенклатуры, складам или контрагентам). Для расчета итогов по суммовым и количественным полям СКД выполняет простое суммирование. Но если поле рентабельности рассчитано в запросе, СКД применит к нему то же самое суммирование (или усреднение) на уровне группировок.
Представим простую ситуацию в отчете:
Если рентабельность считается в запросе, СКД на уровне группировки "Итого" либо сложит эти проценты и получит абсурдные 70%, либо посчитает среднее значение (20% + 50%) / 2 = 35%. Однако математически верная рентабельность по этим двум сделкам должна рассчитываться из общей суммы продаж (1100 руб.) и общей себестоимости (580 руб.): (1100 - 580) / 1100 = 47.27%. Как мы видим, погрешность колоссальная.
Чтобы избежать подобных математических искажений в отчете, расчет всех процентных показателей необходимо полностью переносить на вкладку Ресурсы в схеме компоновки данных.
Разберем детальный алгоритм правильной реализации:
СтоимостьОборот КАК ПродажнаяСтоимость и Стоимость КАК Себестоимость. Никаких формул деления в запросе писать не нужно.ПродажнаяСтоимость с выражением расчета Сумма(ПродажнаяСтоимость).Себестоимость с выражением расчета Сумма(Себестоимость).Рентабельность в список ресурсов. В качестве выражения расчета итогов для него пишем следующую формулу:
Выбор
Когда Сумма(ПродажнаяСтоимость) <> 0
Тогда (Сумма(ПродажнаяСтоимость) - Сумма(Себестоимость)) / Сумма(ПродажнаяСтоимость) * 100
Иначе 0
Конец
При такой настройке СКД сначала корректно просуммирует показатели продаж и себестоимости для каждой конкретной группировки (будь то детальная строка, группа номенклатуры или общий итог всего отчета), а затем применит к полученным суммам формулу расчета рентабельности. На всех уровнях итогов пользователь увидит абсолютно точные, математически выверенные проценты.
Используя приведенные в статье методы оптимизации запросов через временные таблицы и правильный расчет ресурсов в СКД, вы сможете создавать быстрые, надежные и математически корректные аналитические отчеты любой сложности.