При разработке отчетов на базе Системы компоновки данных (СКД) программисты часто сталкиваются с ситуацией, когда один и тот же запрос выдает разные результаты в консоли запросов и в самом отчете. Типичный пример: использование ВНУТРЕННЕЕ СОЕДИНЕНИЕ, которое в консоли исправно отсекает лишние записи, а в СКД почему-то выводит строки, не имеющие данных в присоединенной таблице. Рассмотрим подробнее, почему это происходит и как заставить СКД соблюдать логику жесткой фильтрации.
Представим ситуацию, когда нам нужно вывести список заказов, по которым были оплаты. Мы создаем запрос, объединяющий таблицу заказов и обороты регистра расчетов. Логика проста: если оплаты нет, внутреннее соединение должно исключить такой заказ из выборки. Проанализируем исходный код запроса:
ВЫБРАТЬ
Заказы.Ссылка КАК Ссылка,
СУММА(РасчетыСКлиентамиОбороты.СуммаРасход) КАК СуммаОплаты
ИЗ
Документ.ЗаказКлиента КАК Заказы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.РасчетыСКлиентами.Обороты КАК РасчетыСКлиентамиОбороты
ПО Заказы.Ссылка = РасчетыСКлиентамиОбороты.ЗаказКлиента
В консоли запросов этот код работает безупречно: в результат попадают только те заказы, по которым в регистре РасчетыСКлиентами есть движения. Однако в СКД пользователь может увидеть заказы с нулевой или пустой суммой оплаты. Это происходит из-за того, что СКД — это не просто инструмент выполнения SQL-кода, а сложный генератор запросов, который оптимизирует текст перед отправкой в СУБД.
Выясним причину такого поведения. СКД анализирует настройки отчета (выбранные поля, группировки, отборы) и пытается упростить запрос для повышения производительности. Если в настройках отчета (на закладке "Выбранные поля") не указаны поля из правой таблицы (в нашем примере — из таблицы оборотов) или эти поля не используются в вычисляемых ресурсах, СКД может посчитать ВНУТРЕННЕЕ СОЕДИНЕНИЕ избыточным.
Важный нюанс: Система "решает", что если данные из второй таблицы не выводятся пользователю, то и само соединение не нужно. В итоге она может просто выбросить правую таблицу из итогового SQL-запроса, превратив его в обычную выборку из справочника или документов. При этом фильтрующее свойство ВНУТРЕННЕЕ СОЕДИНЕНИЕ теряется.
Разберем ситуацию, когда СКД принудительно заменяет тип соединения. Это часто случается при включенном флаге "Автозаполнение" или использовании расширения языка запросов (фигурные скобки {}). Если система считает, что отсечение данных может привести к потере итогов по основной таблице, она может трансформировать ВНУТРЕННЕЕ СОЕДИНЕНИЕ в ЛЕВОЕ СОЕДИНЕНИЕ. Это делается для того, чтобы пользовательские отборы работали гибко, но в результате в отчет попадают записи с NULL или нулями вместо сумм.
Как показала практика, наиболее надежным способом заставить СКД фильтровать записи является явное указание условия на агрегатные функции. Добавление условия ИМЕЮЩИЕ заставляет систему выполнить группировку и применить фильтр уже после сбора данных. Посмотрим на пример доработанного кода:
ВЫБРАТЬ
Заказы.Ссылка КАК Ссылка,
СУММА(РасчетыСКлиентамиОбороты.СуммаРасход) КАК СуммаОплаты
ИЗ
Документ.ЗаказКлиента КАК Заказы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.РасчетыСКлиентами.Обороты КАК РасчетыСКлиентамиОбороты
ПО Заказы.Ссылка = РасчетыСКлиентамиОбороты.ЗаказКлиента
СГРУППИРОВАТЬ ПО
Заказы.Ссылка
ИМЕЮЩИЕ
СУММА(РасчетыСКлиентамиОбороты.СуммаРасход) > 0
Такой подход гарантирует, что даже если СКД попытается оптимизировать запрос, итоговый фильтр по сумме отсечет "пустые" заказы. Это принудительно включает механизм расчета итогов на уровне СУБД.
Если вы используете несколько наборов данных или настраиваете связи в схеме компоновки, обратите внимание на свойство Обязательное для связей. Чтобы соединение никогда не выбрасывалось оптимизатором СКД, выполните следующие шаги:
Это даст системе четкое указание: данная связь должна присутствовать в итоговом запросе независимо от того, выбраны ли поля из присоединенной таблицы или нет. Это сохранит логику ВНУТРЕННЕЕ СОЕДИНЕНИЕ в неизменном виде.
Проанализируем работу с виртуальными таблицами, такими как Обороты или ОстаткиИОбороты. СКД очень специфично работает с их параметрами. Если условие фильтрации можно наложить непосредственно на виртуальную таблицу, это всегда предпочтительнее, чем использование ГДЕ или соединений.
Вместо того чтобы надеяться на фильтрацию через соединение, можно использовать параметры виртуальной таблицы для отсечения пустых записей на самом раннем этапе:
РегистрНакопления.РасчетыСКлиентами.Обороты(&НачалоПериода, &КонецПериода, , ЗаказКлиента <> ЗНАЧЕНИЕ(Документ.ЗаказКлиента.ПустаяСсылка))
Хотя в данном случае это не всегда решает проблему отсутствия записей, использование условий внутри () виртуальных таблиц помогает СКД более эффективно строить план запроса.
Посмотрим, как роли полей влияют на результат. В СКД каждое поле имеет роль (Без роли, Период, Измерение, Остаток и т.д.). Если поле СуммаРасход помечено как ресурс, СКД рассчитывает его итоги отдельно. Если в регистре есть записи, которые в сумме дают ноль (например, приход и сторно), то для консоли запросов это будут существующие записи (строка с нулем), а для СКД это может стать поводом исключить строку из визуального представления, если настроен соответствующий отбор.
Рекомендация: Всегда проверяйте, не установлены ли в настройках отчета автоматические отборы на "ненулевые значения", и контролируйте флаг "Игнорировать значения NULL" в настройках ресурсов.
Чтобы избежать "магии" СКД и получить предсказуемый результат при использовании соединений, придерживайтесь следующих правил:
ИМЕЮЩИЕ для жесткой фильтрации по агрегатным суммам.{...} вручную, чтобы контролировать каждый этап формирования запроса.ВНУТРЕННЕЕ СОЕДИНЕНИЕ в СКД — это лишь "рекомендация" для системы, которую она может игнорировать ради минимизации SQL-трафика, если не видит явной необходимости в данных правой таблицы.