Почему СКД некорректно группирует данные по вычисляемому числовому полю цены и как это исправить?

← На главную

При разработке отчетов на схеме компоновки данных (СКД) разработчики часто сталкиваются с ситуацией, когда запрос, идеально работающий в консоли запросов, при переносе в СКД выдает совершенно другой результат. Например, требуется сгруппировать закупки по номенклатуре, договору и цене документа, однако СКД вместо вывода строк с разными ценами рассчитывает единую общую цену или суммирует ее как ресурс.

Разберем детально, почему так происходит, как работает оптимизатор запросов СКД и как правильно построить структуру отчета для корректной группировки по расчетным показателям.

Анализ проблемы: почему поведение в СКД отличается от консоли запросов

Проанализируем типичную ситуацию. В исходном запросе разработчик создает временную таблицу с группировкой по документам закупки и рассчитывает цену делением стоимости на количество:


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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_ДанныеЗакупок.ДоговорКонтрагента КАК ДоговорКонтрагента,
    ВТ_ДанныеЗакупок.Номенклатура КАК Номенклатура,
    ВЫБОР
        КОГДА ВТ_ДанныеЗакупок.КоличествоОборот = 0 ТОГДА 0
        ИНАЧЕ ВЫРАЗИТЬ(ВТ_ДанныеЗакупок.СтоимостьОборот / ВТ_ДанныеЗакупок.КоличествоОборот КАК ЧИСЛО(15, 2))
    КОНЕЦ КАК Цена,
    СУММА(ВТ_ДанныеЗакупок.КоличествоОборот) КАК КоличествоОборот,
    СУММА(ВТ_ДанныеЗакупок.СтоимостьОборот) КАК СтоимостьОборот
ИЗ
    ВТ_ДанныеЗакупок КАК ВТ_ДанныеЗакупок
СГРУППИРОВАТЬ ПО
    ВТ_ДанныеЗакупок.ДоговорКонтрагента,
    ВТ_ДанныеЗакупок.Номенклатура,
    ВЫБОР
        КОГДА ВТ_ДанныеЗакупок.КоличествоОборот = 0 ТОГДА 0
        ИНАЧЕ ВЫРАЗИТЬ(ВТ_ДанныеЗакупок.СтоимостьОборот / ВТ_ДанныеЗакупок.КоличествоОборот КАК ЧИСЛО(15, 2))
    КОНЕЦ

Выясним причину, почему этот код работает в консоли запросов, но ломается в СКД:

Оптимизация запроса механизмом СКД: схема компоновки данных анализирует только те поля, которые выбраны в настройках варианта отчета (в группировках, выбранных полях, отборах или сортировках). Поле ДокументЗакупки присутствует только в промежуточной таблице ВТ_ДанныеЗакупок, но не выводится в финальном наборе данных.
Исключение неиспользуемых полей: генератор макета СКД перестраивает исходный SQL-запрос. Видя, что поле ДокументЗакупки нигде в финальной выборке не задействовано, СКД автоматически вырезает его из первой выборки. В итоге таблица ВТ_ДанныеЗакупок сразу сворачивается до уровня ДоговорКонтрагента и Номенклатура, а расчетная цена становится средней ценой по договору, теряя дискретность по конкретным документам.
Недоступность флага «Обязательное»: на вкладке «Наборы данных» отображаются только поля финального пакета запроса. Поставить флаг «Обязательное» на промежуточное поле во временной таблице невозможно.

Решение 1. Оптимальная схема: расчет цены на детальном уровне и группировка средствами СКД

Рассмотрим правильный подход к построению отчетов на СКД. Вместо попыток выполнить всю агрегацию и группировку внутри текста запроса перенесем постобработку данных на уровень настроек компоновщика.

Разберем по шагам реализацию данного варианта:

Упростим текст запроса до одного пакета, оставив детальные записи по регистраторам/документам и рассчитав дискретную цену на уровне строки документа:


ВЫБРАТЬ
    ЗакупкиОбороты.ДоговорКонтрагента КАК ДоговорКонтрагента,
    ЗакупкиОбороты.Номенклатура КАК Номенклатура,
    ЗакупкиОбороты.ДокументЗакупки КАК ДокументЗакупки,
    СУММА(ЗакупкиОбороты.КоличествоОборот) КАК КоличествоОборот,
    СУММА(ЗакупкиОбороты.СтоимостьОборот) КАК СтоимостьОборот,
    МАКСИМУМ(ВЫБОР
        КОГДА ЗакупкиОбороты.КоличествоОборот = 0 ТОГДА 0
        ИНАЧЕ ВЫРАЗИТЬ(ЗакупкиОбороты.СтоимостьОборот / ЗакупкиОбороты.КоличествоОборот КАК ЧИСЛО(15, 2))
    КОНЕЦ) КАК Цена
ИЗ
    РегистрНакопления.Закупки.Обороты(, , Регистратор, Организация = &Организация И ДоговорКонтрагента = &Договор) КАК ЗакупкиОбороты
СГРУППИРОВАТЬ ПО
    ЗакупкиОбороты.ДоговорКонтрагента,
    ЗакупкиОбороты.Номенклатура,
    ЗакупкиОбороты.ДокументЗакупки

Перейдем на вкладку «Наборы данных». Для поля ДокументЗакупки установим флаг «Обязательное». Это гарантирует, что СКД не исключит документ из выборки и цена всегда рассчитается строго в разрезе каждого документа закупки.
Перейдем на вкладку «Ресурсы». Добавим поля КоличествоОборот и СтоимостьОборот с функцией Сумма(...). Поле Цена в ресурсы не добавляем.
Перейдем на вкладку «Настройки»:
- Создадим единую группировку строк, в которую добавим сразу три поля: ДоговорКонтрагента, Номенклатура, Цена.
- В состав «Выбранные поля» группировки добавим: ДоговорКонтрагента, Номенклатура, Цена, КоличествоОборот, СтоимостьОборот.
- Поле ДокументЗакупки в выводимые поля добавлять не нужно — СКД автоматически объединит строки с одинаковой ценой, суммируя ресурсы.

Решение 2. Фиксация выборки через язык расширения СКД

Если по архитектурным соображениям требуется сохранить структуру с временными таблицами, применим конструкции языка расширения компоновки данных (фигурные скобки {}). Они запрещают генератору макета удалять необходимые поля из промежуточных запросов.

Посмотрим на пример запроса с языком расширения:


ВЫБРАТЬ
    ЗакупкиОбороты.ДоговорКонтрагента КАК ДоговорКонтрагента,
    ЗакупкиОбороты.Номенклатура КАК Номенклатура,
    ЗакупкиОбороты.ДокументЗакупки КАК ДокументЗакупки,
    СУММА(ЗакупкиОбороты.КоличествоОборот) КАК КоличествоОборот,
    СУММА(ЗакупкиОбороты.СтоимостьОборот) КАК СтоимостьОборот,
    МАКСИМУМ(ВЫБОР
        КОГДА ЗакупкиОбороты.КоличествоОборот = 0 ТОГДА 0
        ИНАЧЕ ВЫРАЗИТЬ(ЗакупкиОбороты.СтоимостьОборот / ЗакупкиОбороты.КоличествоОборот КАК ЧИСЛО(15, 2))
    КОНЕЦ) КАК Цена
{ВЫБРАТЬ
    ДоговорКонтрагента.*,
    Номенклатура.*,
    ДокументЗакупки.*,
    КоличествоОборот,
    СтоимостьОборот,
    Цена}
ПОМЕСТИТЬ ВТ_ДанныеЗакупок
ИЗ
    РегистрНакопления.Закупки.Обороты(, , Регистратор, Организация = &Организация И ДоговорКонтрагента = &Договор) КАК ЗакупкиОбороты
СГРУППИРОВАТЬ ПО
    ЗакупкиОбороты.ДоговорКонтрагента,
    ЗакупкиОбороты.Номенклатура,
    ЗакупкиОбороты.ДокументЗакупки
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_ДанныеЗакупок.ДоговорКонтрагента КАК ДоговорКонтрагента,
    ВТ_ДанныеЗакупок.Номенклатура КАК Номенклатура,
    ВТ_ДанныеЗакупок.Цена КАК Цена,
    СУММА(ВТ_ДанныеЗакупок.КоличествоОборот) КАК КоличествоОборот,
    СУММА(ВТ_ДанныеЗакупок.СтоимостьОборот) КАК СтоимостьОборот
ИЗ
    ВТ_ДанныеЗакупок КАК ВТ_ДанныеЗакупок
СГРУППИРОВАТЬ ПО
    ВТ_ДанныеЗакупок.ДоговорКонтрагента,
    ВТ_ДанныеЗакупок.Номенклатура,
    ВТ_ДанныеЗакупок.Цена

Важные нюансы при работе с числовыми группировками

При настройке группировок по расчетным числовым полям важно помнить о следующих правилах:

Точность деления: всегда используйте явное округление через конструкцию ВЫРАЗИТЬ(... КАК ЧИСЛО(15, 2)) при расчете цены в запросе. Из-за погрешностей вычислений с плавающей точкой в СУБД одинаковые цены с микроотклонениями в дальних знаках могут разделиться на разные строки отчета.
Контроль автополей: если в настройках группировки активно системное поле Авто (Автополе), а поле цены случайно объявлено ресурсом, СКД заменит группировочное значение итоговой суммой. Для вычисляемых цен, участвующих в группировке строк, объявление ресурса не требуется.
Отладка результирующего SQL-запроса: для проверки того, какой реальный текст запроса генерирует СКД с учетом настроек пользователя, используйте специализированную консоль СКД или просматривайте свойство ТекстВыполняемогоЗапроса в объекте МакетКомпоновкиДанных в режиме отладки.