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