При разработке и модификации аналитических отчетов в конфигурациях на обычных формах (например, в «1С:Управление торговлей 10.3», «1С:Управление производственным предприятием 1.3» или «1С:Комплексная автоматизация 1.1») программисты часто используют механизм Построителя отчета. Одной из типичных задач является объединение данных по продажам за период и текущим остаткам товаров на складах в рамках одного отчета.
Однако при соединении виртуальных таблиц РегистрНакопления.Продажи.Обороты и РегистрНакопления.ТоварыНаСкладах.Остатки разработчики нередко сталкиваются с двумя неприятными проблемами: из отчета полностью исчезают проданные позиции, которых уже нет на складе (нулевой остаток), а сам отчет начинает формироваться крайне медленно. Разберем детально, почему так происходит и как правильно составить текст запроса с фигурными скобками для Построителя.
Проанализируем фрагмент запроса, который описывает связь таблиц и блок условий построителя:
ИЗ
РегистрНакопления.Продажи.Обороты(&ДатаНач, &ДатаКон, ,) КАК ПродажиОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&ДатаКон, ) КАК ТоварыНаСкладахОстатки
ПО ПродажиОбороты.Номенклатура = ТоварыНаСкладахОстатки.Номенклатура
ГДЕ
ПродажиОбороты.КоличествоОборот <> 0
{ГДЕ
ТоварыНаСкладахОстатки.Номенклатура.*,
ТоварыНаСкладахОстатки.КоличествоОстаток,
ПродажиОбороты.Номенклатура.*}
Посмотрим на поведение СУБД при формировании итогового SQL-запроса:
ЛЕВОЕ СОЕДИНЕНИЕ, где главной (левой) таблицей выступают продажи, а подчиненной (правой) — остатки. Если товар был продан, но на складе его остаток равен 0, в полях таблицы ТоварыНаСкладахОстатки будет возвращено значение NULL.ТоварыНаСкладахОстатки.Номенклатура (из правой таблицы), Построитель автоматически добавляет в секцию ГДЕ условие вида: ГДЕ ТоварыНаСкладахОстатки.Номенклатура В (&СписокНоменклатуры).NULL с конкретным значением возвращает логический результат UNKNOWN (Ложь). В итоге строка с проданным товаром отбрасывается.ЛЕВОЕ СОЕДИНЕНИЕ фактически превращается во ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN).Решение проблемы: все глобальные отборы отчета (номенклатура, группы, свойства) должны быть привязаны исключительно к ведущей таблице — ПродажиОбороты.Номенклатура.*. Поле ТоварыНаСкладахОстатки.Номенклатура.* из секции {ГДЕ ...} необходимо убрать.
Выясним причину долгого выполнения отчета. Если условия в фигурных скобках указаны только в общей секции {ГДЕ}, платформа 1С выполняет следующие операции:
ГДЕ, отсекая 99% рассчитанных записей.Чтобы ускорить отчет, отбор пользователя должен быть передан напрямую в параметры виртуальных таблиц еще до момента их расчета. Для Построителя отчета это делается с помощью одинаковых псевдонимов полей в фигурных скобках параметров таблиц:
ИЗ
РегистрНакопления.Продажи.Обороты(&ДатаНач, &ДатаКон, , {Номенклатура.* КАК НоменклатураОтбор}) КАК ПродажиОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&ДатаКон, {Номенклатура.* КАК НоменклатураОтбор}) КАК ТоварыНаСкладахОстатки
ПО ПродажиОбороты.Номенклатура = ТоварыНаСкладахОстатки.Номенклатура
Когда пользователь установит отбор по полю с псевдонимом НоменклатураОтбор, платформа автоматически подставит это условие в обе виртуальные таблицы на уровне СУБД, что сократит время выполнения запроса с нескольких минут до нескольких секунд.
Разберем две скрытые архитектурные ошибки, присутствующие в исходном запросе, которые могут исказить финансовые показатели отчета:
Если один и тот же товар был продан 15 раз разными документами покупателям за выбранный период, в результате левого соединения остаток этого товара на складе повторится в 15 строках. Если в секции итогов указано СУММА(Остаток), в общих итогах отчета и группировках по номенклатуре остаток на складе умножится на 15. Для корректного отображения остатков в иерархических отчетах данные продаж и остатков следует объединять через ОБЪЕДИНИТЬ ВСЕ во временных таблицах с предварительной группировкой.
Конструкция СУММА(Цена) в итогах математически неверна. Нельзя суммировать цены позиций. Для вычисления корректной средневзвешенной цены в группировках и итогах необходимо использовать расчет от суммы и количества:
ИТОГИ
ВЫБОР
КОГДА СУММА(КоличествоОборот) = 0 ТОГДА 0
ИНАЧЕ СУММА(СтоимостьОборот) / СУММА(КоличествоОборот)
КОНЕЦ КАК Цена
ПО
ОБЩИЕ
Соберем все исправления воедино. Ниже представлен корректный текст запроса, в котором сохранены нулевые остатки, ускорена выборка данных и исправлены условия отборов:
ТекстЗапроса = "ВЫБРАТЬ
| ЕСТЬNULL(ТоварыНаСкладахОстатки.КоличествоОстаток, 0) КАК Остаток,
| ЕСТЬNULL(ТоварыВРезервеНаСкладахОстатки.КоличествоОстаток, 0) КАК ОстатокРезерв,
| ПродажиОбороты.ДокументПродажи КАК ДокументПродажи,
| ПродажиОбороты.Контрагент КАК Контрагент,
| ПродажиОбороты.КоличествоОборот КАК КоличествоОборот,
| ПродажиОбороты.СтоимостьОборот КАК СтоимостьОборот,
| ПродажиОбороты.СтоимостьБезСкидокОборот КАК СтоимостьБезСкидокОборот,
| ПродажиОбороты.НДСОборот КАК НДСОборот,
| ПродажиОбороты.Номенклатура КАК Номенклатура,
| ВЫБОР
| КОГДА ПродажиОбороты.КоличествоОборот = 0 ТОГДА 0
| ИНАЧЕ ПродажиОбороты.СтоимостьОборот / ПродажиОбороты.КоличествоОборот
| КОНЕЦ КАК Цена
|{ВЫБРАТЬ
| Остаток,
| ОстатокРезерв,
| ДокументПродажи.*,
| Контрагент.*,
| Номенклатура.*,
| КоличествоОборот,
| СтоимостьОборот,
| Цена}
|ИЗ
| РегистрНакопления.Продажи.Обороты(&ДатаНач, &ДатаКон, , {Номенклатура.* КАК Номенклатура}) КАК ПродажиОбороты
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&ДатаКон, {Номенклатура.* КАК Номенклатура}) КАК ТоварыНаСкладахОстатки
| ПО ПродажиОбороты.Номенклатура = ТоварыНаСкладахОстатки.Номенклатура
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыВРезервеНаСкладах.Остатки(&ДатаКон, {Номенклатура.* КАК Номенклатура}) КАК ТоварыВРезервеНаСкладахОстатки
| ПО ПродажиОбороты.Номенклатура = ТоварыВРезервеНаСкладахОстатки.Номенклатура
|ГДЕ
| ПродажиОбороты.КоличествоОборот <> 0
|{ГДЕ
| ПродажиОбороты.ДокументПродажи.*,
| ПродажиОбороты.Контрагент.*,
| ПродажиОбороты.Номенклатура.*,
| ПродажиОбороты.Организация.*}
|ИТОГИ
| МАКСИМУМ(Остаток),
| МАКСИМУМ(ОстатокРезерв),
| СУММА(КоличествоОборот),
| СУММА(СтоимостьОборот),
| ВЫБОР
| КОГДА СУММА(КоличествоОборот) = 0 ТОГДА 0
| ИНАЧЕ СУММА(СтоимостьОборот) / СУММА(КоличествоОборот)
| КОНЕЦ КАК Цена
|ПО
| ОБЩИЕ
|{ИТОГИ ПО
| Номенклатура.*,
| Контрагент.*,
| ДокументПродажи.*}";
Рассмотренная задача наглядно демонстрирует ограничения механизма ПостроительОтчета. При разработке новых сложных отчетов со смешанными источниками данных (продажи, остатки, резервы, взаиморасчеты) рекомендуется переводить отчеты на Систему компоновки данных (СКД). В СКД механизм расчета ресурсов позволяет гибко настраивать итоги по разным уровням группировок, а оптимизатор запросов автоматически исключает неиспользуемые соединения и передает отборы в виртуальные таблицы без ручного прописывания параметров.