Как предотвратить автоматическое добавление условий ГДЕ и ИМЕЮЩИЕ системой компоновки данных

← На главную

При работе с Системой компоновки данных (СКД) разработчики часто сталкиваются с ситуацией, когда итоговый запрос, выполняемый платформой, существенно отличается от того, который был написан в конструкторе. СКД обладает интеллектуальным механизмом «оптимизации», который пытается «протолкнуть» пользовательские отборы как можно глубже в структуру запроса — во временные таблицы, вложенные запросы и части объединений (UNION ALL). Однако иногда эта «интеллектуальность» приводит к логическим ошибкам, особенно при расчете средних величин или использовании агрегатных функций. Рассмотрим, как управлять этим процессом и «отучить» СКД добавлять лишние условия.

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

Проанализируем ситуацию на примере. Допустим, мы рассчитываем среднее количество ставок в штатном расписании. В запросе используется объединение нескольких частей, где в первой части мы группируем данные по рабочим дням, а в последующих — выбираем данные на границы периода. Исходный фрагмент запроса выглядит так:


ВЫБРАТЬ
    ВТРабочиеДни.ПозицияШтатногоРасписания КАК ПозицияШтатногоРасписания,
    СУММА(ВТРабочиеДни.СтавокОткрыто) / КОЛИЧЕСТВО(ВТРабочиеДни.Дата) КАК СтавокОткрыто,
    СУММА(ВТРабочиеДни.СтавокЗанято) / КОЛИЧЕСТВО(ВТРабочиеДни.Дата) КАК СтавокЗанято
ПОМЕСТИТЬ ВТВсеДанные
ИЗ
    ВТРабочиеДни КАК ВТРабочиеДни
СГРУППИРОВАТЬ ПО
    ВТРабочиеДни.ПозицияШтатногоРасписания

После компоновки СКД может добавить секцию ИМЕЮЩИЕ (HAVING), если пользователь установил отбор или если система решила отсечь нулевые значения. В результате запрос превращается в конструкцию с дополнительными фильтрами:


СГРУППИРОВАТЬ ПО
    ВТРабочиеДни.ПозицияШтатногоРасписания
ИМЕЮЩИЕ
    (СУММА(ВТРабочиеДни.СтавокОткрыто) / КОЛИЧЕСТВО(ВТРабочиеДни.Дата) <> &П3 
    ИЛИ СУММА(ВТРабочиеДни.СтавокЗанято) / КОЛИЧЕСТВО(ВТРабочиеДни.Дата) <> &П3)

Где &П3 — это параметр, равный нулю. Это происходит из-за того, что СКД пытается минимизировать объем данных, передаваемых между временными таблицами. Рассмотрим основные способы борьбы с этим поведением.

Способ 1. Управление параметром «Автозаполнение»

Самый радикальный и действенный метод — это отключение флага Автозаполнение на закладке «Наборы данных». Когда этот флаг включен, СКД автоматически анализирует текст запроса и делает все доступные поля доступными для отбора, группировки и порядка. При этом она сама решает, куда добавлять условия ГДЕ.

Разберем алгоритм действий при отключении автозаполнения:

Снимем галочку Автозаполнение в нижней части окна редактирования набора данных.
Перейдем на закладку Компоновка данных в конструкторе запроса.
Для каждого поля, которое должно быть доступно пользователю для отбора или вывода, вручную укажем его настройки.
В секции Поля выберем те поля, которые СКД должна «видеть».
В секции Условия укажем те поля, по которым разрешены отборы.

Важно помнить: если мы отключаем автозаполнение, нам придется описывать все поля вручную. Поле, не указанное на закладке компоновки, станет недоступным для настроек пользователя.

Способ 2. Использование расширения языка запросов (фигурные скобки)

Если мы не хотим полностью отказываться от удобства автозаполнения, воспользуемся специальным синтаксисом СКД — фигурными скобками {}. Это позволяет явно указать системе, в каком именно месте запроса должны применяться отборы.

Проанализируем ситуацию: если мы хотим, чтобы отбор по позиции штатного расписания применялся только к итоговому результату, а не к промежуточным временным таблицам, мы можем написать в конечном запросе пакета:


ВЫБРАТЬ
    ВТВсеДанные.ПозицияШтатногоРасписания,
    ВТВсеДанные.СтавокОткрыто
ИЗ
    ВТВсеДанные КАК ВТВсеДанные
{ГДЕ
    ВТВсеДанные.ПозицияШтатногоРасписания.*}

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

Способ 3. Тонкая настройка в конструкторе запроса

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

Откроем конструктор запроса и перейдем на закладку Компоновка данных.
В верхней части выберем нужную таблицу (например, нашу временную таблицу ВТРабочиеДни).
Перейдем на вложенную закладку Условия.
Добавим поле ПозицияШтатногоРасписания.
Обратим внимание на колонку Использовать во всех частях. Если снять эту галочку, СКД перестанет тиражировать условие по всем объединениям и временным таблицам пакета.

Способ 4. Использование алиасов (псевдонимов) для «маскировки»

Механизм автозаполнения ориентируется на имена полей. Если в промежуточной таблице поле называется Организация_Техническая, а в результирующей — Организация, то при установке пользователем отбора по полю Организация, СКД не сможет автоматически сопоставить его с полем Организация_Техническая во временной таблице.

Посмотрим на пример логики:

Во временной таблице пишем: ВЫБРАТЬ Ссылка КАК Позиция_Скрытая.
В итоговом запросе пишем: ВЫБРАТЬ Позиция_Скрытая КАК ПозицияШтатногоРасписания.
СКД увидит поле ПозицияШтатногоРасписания в итоговой выборке. Если пользователь наложит отбор, фильтр будет добавлен только в финальную секцию ГДЕ, так как во временной таблице поля с таким именем «не существует» для компоновщика.

Способ 5. Перенос логики агрегации в ресурсы СКД

Часто проблема с лишними ИМЕЮЩИЕ возникает из-за того, что разработчик пытается вычислить среднее значение или сложные коэффициенты прямо в тексте запроса. С точки зрения СКД, правильнее возвращать «сырые» данные, а расчеты производить на закладке Ресурсы.

Рассмотрим, как это упрощает работу:

В запросе просто выбираем количество ставок и даты.
На закладке Ресурсы для поля СтавокОткрыто пишем выражение: Сумма(СтавокОткрыто) / Количество(Различные Дата).
В этом случае в самом запросе не будет ни группировок, ни агрегатных функций, а значит, СКД не будет повода добавлять секцию ИМЕЮЩИЕ. Отборы будут накладываться на детальные записи, что работает гораздо предсказуемее.

Резюме

Мы проанализировали основные методы управления запросом в СКД. Подведем итог: если система мешает корректному расчету данных, добавляя свои условия, наиболее «чистым» решением является использование закладки Компоновка данных в конструкторе запроса для управления использованием полей в частях запроса. В особо сложных случаях следует отключать Автозаполнение или использовать фигурные скобки для явного указания мест фильтрации.