Как правильно свернуть таблицу в запросе СКД и избежать искажения данных оптимизатором

← На главную

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

Рассмотрим типовой сценарий. Имеется пакет запросов, где в первой временной таблице собираются детальные записи с промежуточной группировкой, вычисляется признак выполнения, а во втором запросе данные агрегируются по более крупному разрезу:


ВЫБРАТЬ
    СводнаяПланЛечения.Прием КАК Прием,
    СводнаяПланЛечения.ВрачНазначил КАК ВрачНазначил,
    СводнаяПланЛечения.МедсестраНазначила КАК МедсестраНазначила,
    СводнаяПланЛечения.Клиент КАК Клиент,
    СводнаяПланЛечения.Заявка КАК Заявка,
    СводнаяПланЛечения.ДатаВыполнения КАК ДатаВыполнения,
    ВЫБОР
        КОГДА СводнаяПланЛечения.ДатаВыполнения <> ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
            ТОГДА 1
        ИНАЧЕ 0
    КОНЕЦ КАК Выполнение
ПОМЕСТИТЬ СводнаяЗаявки
ИЗ
    СводнаяПланЛечения КАК СводнаяПланЛечения
СГРУППИРОВАТЬ ПО
    СводнаяПланЛечения.Прием,
    СводнаяПланЛечения.ВрачНазначил,
    СводнаяПланЛечения.МедсестраНазначила,
    СводнаяПланЛечения.Клиент,
    СводнаяПланЛечения.Заявка,
    СводнаяПланЛечения.ДатаВыполнения
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    СУММА(СводнаяЗаявки.Выполнение) КАК ВыполненныхЗаявок,
    СводнаяЗаявки.МедсестраНазначила КАК МедсестраНазначила
ИЗ
    СводнаяЗаявки КАК СводнаяЗаявки
ГДЕ
    СводнаяЗаявки.ДатаВыполнения МЕЖДУ &ДатаНачала И &ДатаКонца
СГРУППИРОВАТЬ ПО
    СводнаяЗаявки.МедсестраНазначила

Вне СКД данный запрос выдает точное число уникальных выполненных заявок по каждой медсестре. Однако внутри отчета СКД результат оказывается неверным. Выясним причину такого поведения платформы.

Почему СКД искажает расчеты в пакете запросов

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

В момент формирования результирующего запроса оптимизатор удаляет эти неиспользуемые поля не только из секции ВЫБРАТЬ, но и из секции СГРУППИРОВАТЬ ПО первой временной таблицы. В итоге состав ключа группировки меняется, таблица схлопывается на более высоком уровне раньше времени, и логика подсчета вычисляемого поля Выполнение полностью нарушается.

Решение 1. Настройка роли «Обязательное» и отключение автозаполнения

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

Открываем схему компоновки данных и переходим на вкладку Наборы данных.
В тексте запроса находим инструкцию автозаполнения в конце запроса и отключаем её, удалив директиву {// ИНСТРУКЦИИ АВТОЗАПОЛНЕНИЯ}, либо снимаем флажок Автозаполнение в нижней части окна конструктора.
Вручную добавляем поля набора данных в список полей СКД.
В колонке Роль для полей, формирующих уникальность промежуточных записей (например, Заявка, Прием, Клиент), вызываем окно редактирования роли и устанавливаем флажок Обязательное.

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

Решение 2. Индексирование полей временной таблицы

Рассмотрим еще один надежный способ зафиксировать структуру временной таблицы без отключения автозаполнения. Оптимизатор компоновки не может удалить поля, если они входят в индекс временной таблицы. Добавим в первый запрос конструкцию ИНДЕКСИРОВАТЬ ПО:


ВЫБРАТЬ
    СводнаяПланЛечения.Прием КАК Прием,
    СводнаяПланЛечения.ВрачНазначил КАК ВрачНазначил,
    СводнаяПланЛечения.МедсестраНазначила КАК МедсестраНазначила,
    СводнаяПланЛечения.Клиент КАК Клиент,
    СводнаяПланЛечения.Заявка КАК Заявка,
    СводнаяПланЛечения.ДатаВыполнения КАК ДатаВыполнения,
    ВЫБОР
        КОГДА СводнаяПланЛечения.ДатаВыполнения <> ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
            ТОГДА 1
        ИНАЧЕ 0
    КОНЕЦ КАК Выполнение
ПОМЕСТИТЬ СводнаяЗаявки
ИЗ
    СводнаяПланЛечения КАК СводнаяПланЛечения
СГРУППИРОВАТЬ ПО
    СводнаяПланЛечения.Прием,
    СводнаяПланЛечения.ВрачНазначил,
    СводнаяПланЛечения.МедсестраНазначила,
    СводнаяПланЛечения.Клиент,
    СводнаяПланЛечения.Заявка,
    СводнаяПланЛечения.ДатаВыполнения
ИНДЕКСИРОВАТЬ ПО
    Заявка,
    Прием
;

Так как поля Заявка и Прием объявлены частью индекса временной таблицы СводнаяЗаявки, платформа обязана сохранить их в выборке, что предотвращает нежелательное схлопывание строк.

Решение 3. Перенос агрегации в ресурсы СКД

Наиболее идеологически правильный подход при работе с СКД — отказаться от искусственных счетчиков вида ВЫБОР КОГДА ... ТОГДА 1 ИНАЧЕ 0 в тексте запроса и доверить расчет уникальных значений встроенному языку выражений компоновки данных.

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


ВЫБРАТЬ
    СводнаяПланЛечения.МедсестраНазначила КАК МедсестраНазначила,
    СводнаяПланЛечения.Заявка КАК Заявка,
    СводнаяПланЛечения.ДатаВыполнения КАК ДатаВыполнения
ИЗ
    СводнаяПланЛечения КАК СводнаяПланЛечения
ГДЕ
    СводнаяПланЛечения.ДатаВыполнения МЕЖДУ &ДатаНачала И &ДатаКонца

Перейдем на вкладку Ресурсы схемы компоновки данных. Добавим поле ВыполненныхЗаявок и в колонке Выражение укажем формулу:


Количество(Различные ВЫБОР КОГДА ДатаВыполнения <> ДАТАВРЕМЯ(1, 1, 1) ТОГДА Заявка ИНАЧЕ NULL КОНЕЦ)

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

Решение 4. Использование набора данных «Объект»

Если запрос содержит сложную многоуровневую логику временных таблиц с промежуточными соединениями, и поведение СКД сложно контролировать настройками полей, можно передать данные через внешний набор. Выполним пакет запросов в кодовой части через стандартный объект Запрос, получим выгруженную таблицу значений и передадим ее в компоновку:


Запрос = Новый Запрос;
Запрос.Текст = ТекстПакетногоЗапроса;
Запрос.УстановитьПараметр("ДатаНачала", Период.ДатаНачала);
Запрос.УстановитьПараметр("ДатаКонца", Период.ДатаОкончания);

ТаблицаРезультат = Запрос.Выполнить().Выгрузить();

ВнешниеНаборы = Новый Структура;
ВнешниеНаборы.Вставить("ТаблицаДанных", ТаблицаРезультат);

ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, ВнешниеНаборы, ДанныеРасшифровки);

В этом случае платформа отправляет запрос в СУБД в исходном виде без модификации текста оптимизатором СКД.