Как убрать автоматическое добавление поля в группировку запроса 1С и правильно рассчитать остатки?

При работе с Конструктором запросов в 1С:Предприятии разработчики часто сталкиваются с ситуацией, когда платформа автоматически добавляет выбранные поля в секцию СГРУППИРОВАТЬ ПО. Попытка вручную удалить «лишнее» поле из группировки в текстовом редакторе приводит к синтаксической ошибке при сохранении или выполнении запроса: «Поле не входит в группу...».

В этой статье мы подробно разберем, почему СУБД и платформа 1С ведут себя именно так, проанализируем базовое правило агрегации данных и рассмотрим четыре практических способа решить проблему автора темы, когда поле Состояние мешает корректному суммированию остатков по оборудованию.

Выясняем причину: фундаментальное правило группировки

Прежде чем перейти к написанию кода, давайте разберем теорию. В языке запросов 1С (как и в стандартном SQL) действует строгое математическое правило:

Все поля, перечисленные в секции ВЫБРАТЬ, должны быть либо сгруппированы (указаны в секции СГРУППИРОВАТЬ ПО), либо обернуты в одну из агрегатных функций (СУММА, МИНИМУМ, МАКСИМУМ, СРЕДНЕЕ, КОЛИЧЕСТВО).

Представим, что у нас есть три записи в базе данных:

1. Оборудование: "Принтер HP", Состояние: "Новый", Количество: 5 шт.
2. Оборудование: "Принтер HP", Состояние: "Б/У", Количество: 3 шт.
3. Оборудование: "Принтер HP", Состояние: "В ремонте", Количество: 2 шт.

Если мы хотим получить сумму остатков (СУММА(Количество)), сгруппировав результат только по полю Оборудование, СУБД легко выполнит эту операцию: она сложит все количества и выдаст одну строку: "Принтер HP" — 10 шт.

Но что произойдет, если мы потребуем вывести в результат еще и поле Состояние, отказавшись при этом группировать по нему? Система окажется в тупике. Она рассчитала общую сумму (10 шт.) для одной строки "Принтер HP", но какое именно состояние ей вывести напротив этого значения? "Новый", "Б/У" или "В ремонте"? СУБД не может принять это решение самостоятельно. Именно поэтому Конструктор запросов 1С автоматически страхует разработчика от неопределенности и добавляет поле Состояние в группировку, что в итоге разделяет (детализирует) наши остатки обратно на 3 строки.

Давайте по шагам разберем, как обойти эту проблему и получить требуемый результат в зависимости от ваших задач.

Вариант 1. Полное исключение мешающего поля из выборки

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

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

ВЫБРАТЬ
    МестонахожденияОстатки.Оборудование КАК Оборудование,
    СУММА(МестонахожденияОстатки.КоличествоОстаток) КАК Остаток
ИЗ
    РегистрНакопления.Местонахождения.Остатки КАК МестонахожденияОстатки
СГРУППИРОВАТЬ ПО
    МестонахожденияОстатки.Оборудование

При таком подходе СУБД без проблем сложит все остатки по каждому уникальному оборудованию, проигнорировав различия в их состояниях. На выходе мы получим компактную и точную таблицу.

Вариант 2. Применение агрегатных функций к полю «Состояние»

Бывают задачи, когда поле Состояние обязательно должно присутствовать в результирующем наборе данных (например, этого требует структура принимающей таблицы или интерфейс), но при этом группировать (размножать строки) по нему по-прежнему нельзя.

В этом случае мы можем применить к текстовому или ссылочному полю Состояние агрегатные функции МИНИМУМ или МАКСИМУМ. Давайте посмотрим на пример такого запроса:

ВЫБРАТЬ
    МестонахожденияОстатки.Оборудование КАК Оборудование,
    МИНИМУМ(МестонахожденияОстатки.Состояние) КАК Состояние,
    СУММА(МестонахожденияОстатки.КоличествоОстаток) КАК Остаток
ИЗ
    РегистрНакопления.Местонахождения.Остатки КАК МестонахожденияОстатки
СГРУППИРОВАТЬ ПО
    МестонахожденияОстатки.Оборудование

Разберем, как отработает этот алгоритм. СУБД сгруппирует данные строго по оборудованию и посчитает общую сумму остатков. В колонку Состояние запишется ровно одно значение, которое окажется минимальным (например, по алфавитному порядку среди всех состояний данного оборудования). Строки двоиться и троиться не будут.

Вариант 3. Использование возможностей Системы компоновки данных (СКД)

Если вы разрабатываете отчет на базе СКД, то жестко прописывать секцию СГРУППИРОВАТЬ ПО в самом SQL-запросе очень часто является грубой методологической ошибкой. СКД — это мощный интеллектуальный процессор, который умеет трансформировать запросы на лету.

Давайте выполним задачу по правилам разработки в СКД:

1. В схеме компоновки данных напишем простой "плоский" запрос без использования группировок:
   Скопировать код

   
   ВЫБРАТЬ
       МестонахожденияОстатки.Оборудование КАК Оборудование,
       МестонахожденияОстатки.Состояние КАК Состояние,
       МестонахожденияОстатки.КоличествоОстаток КАК КоличествоОстаток
   ИЗ
       РегистрНакопления.Местонахождения.Остатки КАК МестонахожденияОстатки
   

2. Перейдем на вкладку «Ресурсы» в конструкторе СКД.
3. Перенесем поле КоличествоОстаток в правое окно ресурсов, определив для него выражение СУММА(КоличествоОстаток).
4. Перейдем на вкладку «Настройки». В структуре отчета добавим группировку только по полю Оборудование.

Проанализируем результат: компоновщик данных автоматически поймет структуру настроек и сам сформирует оптимальный SQL-запрос к СУБД. На экране пользователь увидит свернутые остатки по оборудованию без учета состояний. Если же завтра пользователю понадобится увидеть детализацию по состояниям, ему достаточно будет просто добавить подчиненную группировку в настройках отчета, не меняя при этом код запроса!

Вариант 4. Использование расчета итогов (Конструкция ИТОГИ ПО)

Еще один классический способ решения подобных аналитических задач в 1С — это использование конструкции ИТОГИ ПО вместо жесткой группировки. Итоги позволяют получить древовидную, иерархическую структуру данных прямо из базы.

Давайте посмотрим на практический пример реализации:

ВЫБРАТЬ
    МестонахожденияОстатки.Оборудование КАК Оборудование,
    МестонахожденияОстатки.Состояние КАК Состояние,
    МестонахожденияОстатки.КоличествоОстаток КАК КоличествоОстаток
ИЗ
    РегистрНакопления.Местонахождения.Остатки КАК МестонахожденияОстатки
ИТОГИ
    СУММА(КоличествоОстаток)
ПО
    Оборудование

В результате выполнения такого запроса мы получим двухуровневую выборку:

• Итоговые строки (первый уровень): будут содержать название оборудования и общую сумму остатков по нему (все состояния будут просуммированы вместе).
• Детальные строки (второй уровень): будут вложены в итоговые строки и покажут расшифровку по каждому конкретному состоянию.

Полезные фичи работы с группировками в 1С

Для повышения профессионального уровня работы с запросами в 1С, рассмотрим две полезные особенности платформы:

1. Автоматическая группировка реквизитов «через точку»

В платформе 1С реализован эффективный механизм оптимизации SQL-кода. Если вы группируете данные по ссылочному полю (например, Оборудование), то любые его реквизиты, выбранные через точку в секции ВЫБРАТЬ, указывать в секции СГРУППИРОВАТЬ ПО не требуется!

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

ВЫБРАТЬ
    МестонахожденияОстатки.Оборудование КАК Оборудование,
    МестонахожденияОстатки.Оборудование.Код КАК КодОборудования,
    МестонахожденияОстатки.Оборудование.СтатьяНалоговогоУчета КАК СтатьяУчета,
    СУММА(МестонахожденияОстатки.КоличествоОстаток) КАК Остаток
ИЗ
    РегистрНакопления.Местонахождения.Остатки КАК МестонахожденияОстатки
СГРУППИРОВАТЬ ПО
    МестонахожденияОстатки.Оборудование

Платформа самостоятельно сгруппирует реквизиты Код и СтатьяНалоговогоУчета, что значительно упрощает чтение и поддержку кода разработчиками.

2. Использование конструкции ГРУППИРУЮЩИМ НАБОРАМ (с версии 8.3.16)

Если вам необходимо в рамках одного запроса быстро рассчитать разные варианты и комбинации группировок (например, отдельно получить итоги по оборудованию, отдельно — по состояниям, и дополнительно совместный вариант), используйте конструкцию ГРУППИРУЮЩИМ НАБОРАМ. Это работает намного быстрее, чем соединение нескольких тяжелых запросов через оператор ОБЪЕДИНИТЬ ВСЕ.