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