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