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