Разработка отчетов на Системе компоновки данных (СКД) часто преподносит сюрпризы: один и тот же текст запроса в консоли запросов выдает верные цифры, а в СКД — неполные или искаженные данные. Рассмотрим подробнее, почему так происходит, проанализируем внутренние механизмы СКД и разберем по шагам, как добиться идентичности результатов.
Основная причина расхождений заключается в том, что СКД не выполняет запрос «как есть». Перед отправкой на сервер СУБД компоновщик макета анализирует настройки отчета (выбранные поля, группировки, отборы) и перестраивает запрос. Он может:
JOIN) «лишние» таблицы, если поля из них не участвуют в текущем варианте отчета.СГРУППИРОВАТЬ ПО, если они не являются измерениями в терминах СКД.Чтобы понять, во что превратился ваш запрос, нельзя ограничиваться обычной консолью запросов. Проанализируем ситуацию с помощью специализированного инструмента — Консоли компоновки данных. Рассмотрим алгоритм действий:
WHERE или JOIN исчезли.Разберем типичную ситуацию: в запросе есть временная таблица, из которой выбираются Сумма и Дата. Если в настройках отчета вы не выводите детальные записи со ссылкой на документ, СКД может посчитать, что ссылка не нужна, и сгруппировать данные только по дате, отсекая дубли сумм. Посмотрим на пример некорректной логики:
Если компоновщик видит, что поле Ссылка не используется в итоговом результате, он исключает его из выборки. Чтобы этого избежать, используем следующие методы:
Проанализируем пример, когда неправильная группировка ресурсов во временных таблицах (ВТ) внутри пакета запросов приводит к потере данных. Рассмотрим код, который часто вызывает ошибки в СКД:
// Ошибочный вариант: транзитные поля в группировке ВТ
ВЫБРАТЬ
Продажи.Номенклатура,
Продажи.Количество КАК ПродажиКоличество,
Продажи.Сумма КАК ПродажиСумма,
МИНИМУМ(Цены.Период) КАК ПериодЦены
ПОМЕСТИТЬ ВТ_Цены
ИЗ
ВТ_Продажи КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Цены КАК Цены ПО ...
СГРУППИРОВАТЬ ПО
Продажи.Номенклатура,
Продажи.Количество,
Продажи.Сумма;
В данном случае поля Количество и Сумма попали в СГРУППИРОВАТЬ ПО. Консоль запросов вернет всё верно, но СКД при оптимизации может решить, что эти поля являются измерениями, и результат «схлопнется». Разберем правильный подход: во временных таблицах нужно оставлять только те поля, которые участвуют в соединениях или агрегатных функциях, а ресурсы (суммы) присоединять в самом конце.
Посмотрим на исправленный вариант:
// Правильный вариант: ресурсы не участвуют в промежуточных группировках
ВЫБРАТЬ
Продажи.Номенклатура,
МИНИМУМ(Цены.Период) КАК ПериодЦены
ПОМЕСТИТЬ ВТ_Цены
ИЗ
ВТ_Продажи КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Цены КАК Цены ПО ...
СГРУППИРОВАТЬ ПО
Продажи.Номенклатура;
//////////////////////////////////////////////////////////
ВЫБРАТЬ
Продажи.Номенклатура,
СУММА(Продажи.Количество) КАК Количество,
СУММА(Продажи.Сумма) КАК Сумма
ИЗ
ВТ_Продажи КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Цены КАК Цены ПО ...
СГРУППИРОВАТЬ ПО
Продажи.Номенклатура;
Если логика вашего запроса настолько сложна, что оптимизатор СКД постоянно искажает её, применим метод «консервации» логики. Выясним, как это работает: СКД гораздо менее агрессивно оптимизирует вложенные запросы по сравнению с пакетами временных таблиц. Помещение проблемного участка запроса во вложенный подзапрос часто заставляет компоновщик оставить структуру SELECT и JOIN неизменной.
Посмотрим на пример, где СКД выдает неверный итог из-за особенностей расчета ресурсов. Допустим, во временной таблице мы получаем МАКСИМУМ(Пробег) по модели автомобиля, а в итоговом запросе хотим получить СУММА этих пробегов по всей группировке.
ВЫБРАТЬ
Модель,
МАКСИМУМ(Пробег) КАК МаксПробег
ПОМЕСТИТЬ ВТ_Данные
ИЗ Регистр КАК Рег
СГРУППИРОВАТЬ ПО Модель;
//////////////////////////////////////////////////////////
ВЫБРАТЬ
СУММА(МаксПробег) КАК ОбщийПробег
ИЗ ВТ_Данные;
В СКД этот механизм может дать неверный результат (например, взять максимум по всем моделям сразу), так как она пытается «протащить» агрегатную функцию МАКСИМУМ до самого верхнего уровня. Чтобы это исправить, убедитесь, что на вкладке «Ресурсы» в СКД выражение для расчета итога соответствует вашей бизнес-логике, а не просто копирует поле запроса.
{&НачалоПериода}. Это подскажет СКД, что управлять параметром нужно именно здесь.Таким образом, мы выяснили, что расхождение данных — это не ошибка платформы, а следствие работы оптимизатора. Используя Консоль СКД для контроля финального текста запроса и грамотно распределяя роли полей, вы сможете создавать надежные отчеты с предсказуемым результатом.