Конструкция СГРУППИРОВАТЬ ПО в языке запросов 1С сворачивает детальные записи в сводные строки: одна строка результата на каждый уникальный набор значений полей, перечисленных после ключевого слова. Все остальные значения в такой строке вычисляют агрегатные функции: СУММА, КОЛИЧЕСТВО, СРЕДНЕЕ, МАКСИМУМ, МИНИМУМ.
Короткий ответ на вопрос «когда без неё не обойтись»: когда нужен итог, а не список операций. Запрос без группировки возвращает столько строк, сколько записей попало в выборку. Если в регистре за месяц лежит 12 000 движений, придут 12 000 строк, каждая про одну операцию. Тот же запрос с группировкой по контрагенту вернёт по одной строке на контрагента, и в каждой строке будет посчитанная сумма, количество или другой агрегат.
В синтаксисе 1С конструкция записывается как СГРУППИРОВАТЬ ПО. В обиходе её называют ГРУППИРОВАТЬ ПО или GROUP BY, по имени аналогичной конструкции SQL. Смысл один: разделить набор записей на группы с одинаковыми значениями полей и посчитать что-то внутри каждой группы.
Зачем нужна группировка в запросах 1С
Регистры 1С хранят движения. Каждая продажа, каждое поступление, каждое начисление лежит отдельной записью с датой, суммой, количеством и ссылками на объекты. Сводных строк там нет и быть не может: база не знает, что вам нужны суммы по контрагентам за квартал, пока вы об этом не попросите.
Сценарий типичный: в регистре продаж накопилось несколько сотен тысяч движений, а руководителю нужна таблица из нескольких сотен строк с итогами по контрагентам. Выгрузить всё и считать в Excel можно, но это лишний объём данных и лишняя ручная работа. Группировка заставляет базу посчитать итоги до возврата результата, и приложение получает уже готовую сводку.
Второй эффект важен для отчётности: группировка задаёт структуру результата. Компактный набор строк проще вывести в отчёт, проще отсортировать и проще сравнить между периодами.
Детальные строки и свёрнутые строки: в чём разница
Детальная строка описывает один факт: одну продажу, одно перемещение, одно начисление. Свёрнутая строка описывает группу фактов, объединённых одинаковыми значениями полей группировки.
Пусть в условном регистре продаж лежат четыре записи.
| Контрагент | Номенклатура | Сумма |
|---|---|---|
| Ромашка | Стол | 10 000 |
| Ромашка | Стул | 4 000 |
| Ромашка | Стол | 6 000 |
| Василёк | Стул | 3 000 |
Запрос без группировки выведет все четыре строки как есть:
ВЫБРАТЬ
Продажи.Контрагент КАК Контрагент,
Продажи.Номенклатура КАК Номенклатура,
Продажи.Сумма КАК Сумма
ИЗ
РегистрНакопления.Продажи КАК Продажи
Тот же набор данных, свёрнутый по контрагенту:
ВЫБРАТЬ
Продажи.Контрагент КАК Контрагент,
СУММА(Продажи.Сумма) КАК СуммаПродаж
ИЗ
РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Контрагент
Результат: две строки вместо четырёх.
| Контрагент | СуммаПродаж |
|---|---|
| Ромашка | 20 000 |
| Василёк | 3 000 |
Обратите внимание на два следствия. Поле Номенклатура в такой запрос добавить нельзя: в свёрнутой строке нет одной номенклатуры, там их несколько. Число 20 000 не лежит ни в одной исходной записи, его посчитала СУММА. Группировка убирает дубли как побочный эффект, а основная её работа - формировать новый уровень данных.
Аналогия с Excel поможет новичку: детальный запрос похож на обычную таблицу со всеми строками, запрос с группировкой - на сводную таблицу, где в строках лежит контрагент, а в значениях сумма. Разница в том, что сводную таблицу строит не пользователь мышкой, а текст запроса.
Синтаксис ГРУППИРОВАТЬ ПО: базовые примеры
Общий вид конструкции:
ВЫБРАТЬ
Поле1,
АгрегатнаяФункция(Поле2) КАК Результат
ИЗ
Источник КАК Источник
ГДЕ
УсловиеОтбора
СГРУППИРОВАТЬ ПО
Поле1
ИМЕЮЩИЕ
АгрегатнаяФункция(Поле2) > Значение
Ключевое правило: каждое поле в списке выборки либо перечислено после СГРУППИРОВАТЬ ПО, либо обёрнуто в агрегатную функцию. Третьего варианта нет. Именно на этом правиле спотыкаются почти все, кто пишет первый запрос со сводкой.
Раздел ИМЕЮЩИЕ фильтрует уже посчитанные группы. Он нужен, когда условие опирается на агрегат: например, отобрать контрагентов с суммой продаж выше порога. Отбор по обычным полям остаётся в ГДЕ и выполняется до группировки, поэтому он дешевле: в расчёт попадает меньше строк.
Группировка по одной колонке
Простейший случай: одна колонка в СГРУППИРОВАТЬ ПО и агрегаты в выборке.
ВЫБРАТЬ
ТоварыНаСкладах.Номенклатура КАК Номенклатура,
СУММА(ТоварыНаСкладах.Количество) КАК КоличествоТовара,
КОЛИЧЕСТВО(*) КАК КоличествоДвижений
ИЗ
РегистрНакопления.ТоварыНаСкладах КАК ТоварыНаСкладах
СГРУППИРОВАТЬ ПО
ТоварыНаСкладах.Номенклатура
Разберите разницу между двумя агрегатами. СУММА(Количество) складывает количество из всех попавших в выборку движений. КОЛИЧЕСТВО(*) считает сами строки. Если по товару прошло два движения, КОЛИЧЕСТВО(*) вернёт 2, а СУММА(Количество) - суммарное количество по этим двум записям. Для остатка такой суммы недостаточно: движения прихода и расхода хранятся с положительным количеством, а направление задаёт вид движения, поэтому остаток корректнее получать из виртуальной таблицы Остатки.
Результат группировки по одной колонке выглядит так: по одной строке на номенклатуру, без повторов и без деталей по документам.
| Номенклатура | КоличествоТовара | КоличествоДвижений |
|---|---|---|
| Стол | 16 | 2 |
| Стул | 9 | 3 |
Числа в таблице условные, важен принцип: количество строк результата равно количеству уникальных значений в поле группировки.
Группировка по нескольким колонкам
Вторая колонка в СГРУППИРОВАТЬ ПО меняет гранулярность результата: строка формируется на каждую уникальную комбинацию значений.
ВЫБРАТЬ
Продажи.Контрагент КАК Контрагент,
Продажи.Номенклатура КАК Номенклатура,
СУММА(Продажи.Сумма) КАК СуммаПродажи
ИЗ
РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Контрагент,
Продажи.Номенклатура
На тех же четырёх записях результат будет таким.
| Контрагент | Номенклатура | СуммаПродажи |
|---|---|---|
| Ромашка | Стол | 16 000 |
| Ромашка | Стул | 4 000 |
| Василёк | Стул | 3 000 |
Порядок колонок в СГРУППИРОВАТЬ ПО на результат не влияет: СГРУППИРОВАТЬ ПО Контрагент, Номенклатура и СГРУППИРОВАТЬ ПО Номенклатура, Контрагент дадут одинаковый набор строк. На читаемость влияет, поэтому перечисляйте поля в том же порядке, что и в списке выборки.
Ещё нюанс: если у контрагента в выборке нет продаж какой-то номенклатуры, строка с нулём не появится. Группировка строит строки только по существующим записям.
Агрегатные функции в запросах 1С
Агрегатная функция получает множество значений внутри группы и возвращает одно. Основные функции в 1С:
| Функция | Что считает | Типичный сценарий |
|---|---|---|
| СУММА | Сумму числовых значений группы | Выручка по контрагенту |
| КОЛИЧЕСТВО | Число строк или непустых значений | Количество продаж, число документов |
| СРЕДНЕЕ | Среднее арифметическое значений группы | Средний чек |
| МАКСИМУМ | Наибольшее значение группы | Крупнейшая продажа месяца |
| МИНИМУМ | Наименьшее значение группы | Дата первой покупки |
Все они считаются отдельно для каждой группы, которую сформировала группировка. В одном запросе можно вывести несколько агрегатов:
ВЫБРАТЬ
Продажи.Контрагент КАК Контрагент,
СУММА(Продажи.Сумма) КАК СуммаПродаж,
КОЛИЧЕСТВО(*) КАК КоличествоПродаж,
СРЕДНЕЕ(Продажи.Сумма) КАК СредняяПродажа,
МАКСИМУМ(Продажи.Сумма) КАК МаксимальнаяПродажа
ИЗ
РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Контрагент
Дальше момент, который часто упускают. Агрегат можно указывать в запросе без группировки, если в выборке нет обычных полей. Запрос ВЫБРАТЬ СУММА(Продажи.Сумма) КАК Итого ИЗ РегистрНакопления.Продажи КАК Продажи вернёт одну строку с общей суммой. Как только рядом появляется обычное поле, группировка по нему становится обязательной, иначе платформа вернёт ошибку.
СУММА, КОЛИЧЕСТВО, СРЕДНЕЕ: когда что применять
Выбор функции определяется вопросом, на который отвечает отчёт.
СУММА подходит для денег, количества и любых величин, которые имеет смысл складывать: рубли, штуки, часы. Складывать проценты, средние оценки или курсы валют нельзя, получится бессмыслица.
КОЛИЧЕСТВО имеет три варианта записи, и они дают разные числа:
- КОЛИЧЕСТВО(*) - число строк в группе, включая строки с пустыми значениями.
- КОЛИЧЕСТВО(Поле) - число строк, где это поле заполнено; пустые значения не учитываются.
- КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Поле) - число уникальных непустых значений поля в группе.
Разница видна на обычной задаче: сколько контрагентов купили товар. КОЛИЧЕСТВО(*) покажет число продаж, КОЛИЧЕСТВО(Контрагент) - число строк с заполненным контрагентом, а КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Контрагент) - сколько разных контрагентов было. Для ответа на исходный вопрос нужен третий вариант.
СРЕДНЕЕ считает среднее арифметическое и, как в SQL, пустые значения игнорирует. Если из десяти строк сумма заполнена в шести, среднее посчитается по шести. Для среднего чека это удобно, но при большом числе пустых полей результат смещается.
Агрегатные функции и вычисляемые поля
Агрегаты можно объединять в выражения. Выражение считается после того, как группы сформированы:
ВЫБРАТЬ
Продажи.Контрагент КАК Контрагент,
СУММА(Продажи.Сумма) / КОЛИЧЕСТВО(*) КАК СреднийЧек
ИЗ
РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Контрагент
Здесь важно следить за делением на ноль: если знаменатель окажется нулевым, запрос завершится ошибкой. Подстраховка - ВЫБОР КОГДА КОЛИЧЕСТВО(*) = 0 ТОГДА 0 ИНАЧЕ СУММА(Продажи.Сумма) / КОЛИЧЕСТВО(*) КОНЕЦ.
Вложенные агрегаты на одном уровне запроса запрещены: выражение вида СУММА(СРЕДНЕЕ(Поле)) платформа не примет. Среднее от сумм по контрагентам считается через подзапрос:
ВЫБРАТЬ
СРЕДНЕЕ(Сводка.СуммаПродаж) КАК СреднееПоКонтрагентам
ИЗ
(ВЫБРАТЬ
Продажи.Контрагент КАК Контрагент,
СУММА(Продажи.Сумма) КАК СуммаПродаж
ИЗ
РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Контрагент) КАК Сводка
Внутренний запрос сворачивает продажи по контрагентам, внешний считает среднее по полученным суммам. Так же решается задача отбора по агрегату, когда условие сложнее, чем позволяет ИМЕЮЩИЕ.
Отдельно про вычисляемые поля в самой группировке. Если группировать нужно по выражению, а не по полю, выражение повторяют в списке выборки и в СГРУППИРОВАТЬ ПО в одинаковом виде. Платформа сопоставляет их по тексту, поэтому разошедшаяся формулировка даст ошибку о поле, которое не входит в группировку.
Что происходит с данными без группировки
Без СГРУППИРОВАТЬ ПО запрос возвращает детальный набор: по одной строке на каждую запись, прошедшую отбор. Строки не объединяются, даже если значения полей полностью совпадают.
Для таблицы из тысячи записей результат - тысяча строк. Приложение получит их все, построит таблицу значений и передаст в отчёт. Работает это предсказуемо, но заканчивается тремя проблемами: время выполнения и объём передаваемых данных растут вместе с количеством записей; итоги приходится считать в коде или в отчёте; пользователь видит длинный список, в котором нет ответа на вопрос «сколько всего».
Как отсутствие группировки влияет на отчёты
Отчёт по продажам за месяц без группировки выглядит как выгрузка регистра: сотни или тысячи строк с датами, документами и суммами. Найти в нём общий итог по контрагенту не получится, если не включить группировку на уровне системы компоновки данных. Со свёрткой по контрагенту тот же отчёт превращается в короткую таблицу, где видна общая сумма.
Здесь есть практическая тонкость для типовых конфигураций. В отчётах на СКД группировки настраиваются в схеме компоновки: разработчик указывает поля группировки и ресурсы, а платформа сама формирует запрос и сворачивает строки. Поэтому добавлять СГРУППИРОВАТЬ ПО в собственный запрос, который служит источником данных для СКД, нужно осознанно: двойная агрегация даст либо неверные итоги, либо лишит отчёт детализации, по которой пользователь хотел развернуть строки.
Правило простое. Сворачивайте данные в запросе, когда отчёту нужны готовые итоги и детализация не требуется. Оставляйте детальные строки, когда СКД или другой механизм строит группировки сам, а также когда пользователю нужна расшифровка до документа.
Детальные строки нужны и в других задачах: показать историю операций по контрагенту, выгрузить данные для сверки, передать записи во внешнюю систему. Группировка тут только навредит, потому что потеряется связь с конкретным документом, датой и регистратором.
Типичные ошибки при использовании группировки
Ошибки новичков повторяются из запроса в запрос. Разберём четыре самые частые.
Обычное поле без группировки. Поле есть в выборке, но его нет ни в СГРУППИРОВАТЬ ПО, ни в агрегатной функции:
ВЫБРАТЬ
Продажи.Контрагент КАК Контрагент,
Продажи.Дата КАК Дата,
СУММА(Продажи.Сумма) КАК СуммаПродаж
ИЗ
РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Контрагент
Платформа вернёт ошибку и укажет на поле Дата. По смыслу она права: в одной свёрнутой строке по контрагенту лежит множество дат, и какую из них показать, непонятно. Вариантов два: добавить поле в группировку, если нужна разбивка по датам, либо заменить его агрегатом, например МАКСИМУМ(Продажи.Дата) как дату последней покупки.
Агрегат и обычное поле без группировки. Тот же конфликт, только группировки нет вовсе:
ВЫБРАТЬ
Продажи.Контрагент КАК Контрагент,
СУММА(Продажи.Сумма) КАК СуммаПродаж
ИЗ
РегистрНакопления.Продажи КАК Продажи
Запрос не выполнится: платформа не знает, к какой строке привязать сумму. Лечится добавлением СГРУППИРОВАТЬ ПО Продажи.Контрагент.
Выражение в группировке записано иначе, чем в выборке. Если группировка идёт по выражению, оно должно совпадать в обоих местах. Запись вида ВЫБРАТЬ ГОД(Продажи.Дата) КАК Год ... СГРУППИРОВАТЬ ПО Продажи.Дата приведёт к ошибке, потому что в выборке стоит функция, а в группировке - исходное поле. Правильно повторять выражение целиком: СГРУППИРОВАТЬ ПО ГОД(Продажи.Дата).
Попытка отфильтровать агрегат в ГДЕ. Условие СУММА(Продажи.Сумма) > 100000 в разделе ГДЕ не сработает: на момент отбора итоги ещё не посчитаны. Такой отбор ставится в ИМЕЮЩИЕ.
ВЫБРАТЬ
Продажи.Контрагент КАК Контрагент,
СУММА(Продажи.Сумма) КАК СуммаПродаж
ИЗ
РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Контрагент
ИМЕЮЩИЕ
СУММА(Продажи.Сумма) > 100000
Ещё один источник путаницы - пустые значения. Строки с незаполненным полем группировки платформа собирает в отдельную группу, и в отчёте появляется строка без названия контрагента. Если она не нужна, отсекайте такие записи в ГДЕ, до группировки.
Группировка и ИТОГИ: в чём разница
СГРУППИРОВАТЬ ПО меняет состав строк: детальные записи исчезают, остаются свёрнутые. ИТОГИ работает иначе: она добавляет к результату дополнительные итоговые строки, а исходные строки остаются на месте.
ВЫБРАТЬ
Продажи.Контрагент КАК Контрагент,
Продажи.Дата КАК Дата,
Продажи.Сумма КАК Сумма
ИЗ
РегистрНакопления.Продажи КАК Продажи
ИТОГИ
СУММА(Сумма)
ПО
Контрагент
Результат содержит все детальные строки продаж плюс итоговые строки, посчитанные по контрагенту. Ни одна продажа не пропала. Если нужны общие итоги по всей выборке, в ИТОГИ добавляют блок ПО ОБЩИЕ:
ИТОГИ
СУММА(Сумма)
ПО
ОБЩИЕ,
Контрагент
Для полей с иерархией, например групп номенклатуры, итоги можно получить по уровням: после имени поля добавляют слово ИЕРАРХИЯ, и результат дополнится строками по родительским группам. Группировка в языке запросов иерархию не строит вообще, это отдельный механизм.
Выбор между конструкциями диктует задача. Нужны только сводные строки - группировка. Нужны все детальные строки и подведение итогов по ним - ИТОГИ. Совместное использование тоже возможно: запрос с СГРУППИРОВАТЬ ПО по дням и ИТОГИ по контрагенту вернёт сводку по дням и итоговые строки по контрагентам. В отчётах на СКД оба механизма обычно настраивает схема компоновки, а не текст запроса.
Практические примеры для типовых конфигураций
Ниже три сценария из распространённых конфигураций. Имена регистров и полей в разных версиях и редакциях отличаются, поэтому перед использованием проверьте их в конфигураторе.
Бухгалтерия: обороты по счетам. Регистр бухгалтерии Хозрасчетный хранит проводки. Сводка по счетам получается группировкой по счёту:
ВЫБРАТЬ
Хозрасчетный.СчетКт КАК Счет,
СУММА(Хозрасчетный.Сумма) КАК ОборотКт
ИЗ
РегистрБухгалтерии.Хозрасчетный КАК Хозрасчетный
ГДЕ
Хозрасчетный.Период МЕЖДУ &НачалоПериода И &КонецПериода
СГРУППИРОВАТЬ ПО
Хозрасчетный.СчетКт
Без группировки получится список всех проводок периода, для анализа оборотов он бесполезен. С группировкой выходит по одной строке на счёт кредита. Для готовых оборотов удобнее виртуальная таблица Хозрасчетный.Обороты: она возвращает уже свёрнутые данные за период, и группировка в запросе к ней чаще всего не нужна, если только не требуется дополнительная свёртка по субконто.
Управление торговлей: движения товаров по складам. Регистр накопления ТоварыНаСкладах хранит приход и расход. Сводка по номенклатуре и складу строится так:
ВЫБРАТЬ
ТоварыНаСкладах.Номенклатура КАК Номенклатура,
ТоварыНаСкладах.Склад КАК Склад,
СУММА(ТоварыНаСкладах.Количество) КАК Количество
ИЗ
РегистрНакопления.ТоварыНаСкладах КАК ТоварыНаСкладах
СГРУППИРОВАТЬ ПО
ТоварыНаСкладах.Номенклатура,
ТоварыНаСкладах.Склад
Две колонки в группировке дают строку на каждую пару «номенклатура плюс склад». Без группировки запрос вернёт каждое движение отдельно, и по нему нельзя увидеть, сколько товара прошло через склад. Для остатков используйте виртуальную таблицу ТоварыНаСкладах.Остатки: она уже агрегирует движения с учётом вида движения и момента времени, поэтому дополнительная группировка в ней требуется только для нестандартных разрезов.
ЗУП: суммы к выплате по сотрудникам. Регистр накопления ЗарплатаКВыплате хранит суммы к выплате. Сводка по сотруднику:
ВЫБРАТЬ
ЗарплатаКВыплате.Сотрудник КАК Сотрудник,
СУММА(ЗарплатаКВыплате.Сумма) КАК СуммаКВыплате
ИЗ
РегистрНакопления.ЗарплатаКВыплате КАК ЗарплатаКВыплате
СГРУППИРОВАТЬ ПО
ЗарплатаКВыплате.Сотрудник
Одна строка на сотрудника вместо десятков записей по документам выплат. Если в конфигурации такого регистра нет или он называется иначе, найдите подходящий в дереве конфигурации: логика запроса останется той же, изменится только имя источника.
Общий признак всех трёх примеров: группировка нужна там, где отчёт отвечает на вопрос «сколько всего», а не «что именно произошло». Как только в требованиях появляется расшифровка до документа, суммы или даты, детальный набор строк снова становится нужен, а итоги к нему добавляют через ИТОГИ или настройки СКД.
Практический шаг для закрепления: возьмите любой регистр на своей базе, выполните к нему запрос без группировки и посмотрите на количество строк в результате. Затем добавьте СГРУППИРОВАТЬ ПО по одному полю и СУММА по числовому ресурсу, сравните число строк и суммы. Так ошибки в понимании гранулярности видно быстрее, чем в готовом отчёте.