Как сделать группировку в запросе 1С

Группировка в запросе 1С нужна, когда требуется свернуть детальные строки таблицы до сводных значений: посчитать сумму продаж по каждому контрагенту, количество документов по месяцам, остатки по складам. Без группировки запрос вернёт каждую строку отдельно, и агрегировать данные придётся в коде — это медленнее и сложнее.

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

Ниже разобран синтаксис, рабочие примеры для типовых задач, отличие группировки от итогов, а также ошибки, которые чаще всего приводят к неверным суммам или падению запроса.

Синтаксис СГРУППИРОВАТЬ ПО

Конструкция размещается после секции выборки и условий ГДЕ, но до упорядочивания. Минимальный шаблон выглядит так:

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

Правило простое: каждое поле в списке ВЫБРАТЬ должно либо входить в перечень группировки, либо быть обёрнуто в агрегатную функцию. Если добавить в выборку поле Продажи.Номенклатура, не включив его в СГРУППИРОВАТЬ ПО и не применив агрегат, платформа вернёт ошибку вида «Поле не входит в группу».

Группировать можно по нескольким полям — они перечисляются через запятую. Порядок полей определяет уникальность комбинации: группировка по «Склад, Номенклатура» даст строку на каждую пару значений, а не отдельные итоги по складу и отдельно по номенклатуре.

Агрегатные функции и их особенности

Каждая функция ведёт себя по-разному, и выбор влияет на результат сильнее, чем кажется:

• СУММА(Поле) — складывает числовые значения. Строки со значением NULL игнорируются;
• КОЛИЧЕСТВО(Поле) — считает строки, где поле не NULL;
• КОЛИЧЕСТВО(*) — считает все строки группы, включая строки с NULL;
• КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Поле) — считает уникальные значения, например число разных покупателей;
• МАКСИМУМ и МИНИМУМ — работают с числами, датами и строками;
• СРЕДНЕЕ — среднее по непустым значениям.

Тонкое место — КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...). Если нужно узнать, сколько уникальных товаров продавалось, пишите КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Продажи.Номенклатура). Обычное КОЛИЧЕСТВО вернёт число строк, что при нескольких продажах одного товара даст завышенное значение.

Ещё один нюанс: агрегатные функции нельзя вкладывать друг в друга и нельзя использовать в условии ГДЕ — отбор по результатам агрегации делается отдельной секцией ИМЕЮЩИЕ (HAVING), которая идёт после группировки.

ВЫБРАТЬ
Продажи.Контрагент КАК Контрагент,
СУММА(Продажи.СуммаОборот) КАК СуммаПродаж
ИЗ
РегистрНакопления.Продажи.Обороты КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Контрагент
ИМЕЮЩИЕ
СУММА(Продажи.СуммаОборот) > 100000

Группировка по периодам и выражениям

Частая задача — свернуть данные по месяцам, неделям или дням. Для этого применяются функции периодичности: МЕСЯЦ(Дата), ГОД(Дата), НАЧАЛОПЕРИОДА(Дата, Месяц). Первые две возвращают число, третья — дату начала периода, что удобнее для сортировки и вывода в отчёт.

ВЫБРАТЬ
НАЧАЛОПЕРИОДА(Продажи.Период, МЕСЯЦ) КАК Месяц,
СУММА(Продажи.СуммаОборот) КАК СуммаПродаж
ИЗ
РегистрНакопления.Продажи.Обороты КАК Продажи
СГРУППИРОВАТЬ ПО
НАЧАЛОПЕРИОДА(Продажи.Период, МЕСЯЦ)
УПОРЯДОЧИТЬ ПО
Месяц

Группировать можно по любому выражению: по результату функции, по ВЫБОР КОГДА ... КОНЕЦ, по подстроке. Главное — то же самое выражение должно стоять и в списке выборки, и в секции группировки. Расхождение даже в пробеле или регистре функции может привести к ошибке конструктора.

Для виртуальных таблиц оборотных регистров (Обороты) часто выгоднее задать периодичность параметром самой таблицы, а не группировкой: РегистрНакопления.Продажи.Обороты(&Начало, &Конец, Месяц, ). Тогда платформа сделает агрегацию на уровне СУБД эффективнее, и дополнительная группировка по периоду не понадобится.

Группировка или ИТОГИ: что выбрать

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

Сравнение вариантов

СГРУППИРОВАТЬ ПО

Подходит, когда детальные строки не нужны: сводный отчёт, суммы по контрагентам, количество документов. Результат — плоская таблица.

ИТОГИ ПО

Подходит, когда нужны и детали, и подытоги: отчёт «продажи по менеджерам с расшифровкой до документов». Результат — дерево с группировками.

Виртуальная таблица Обороты

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

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

Пошаговый пример: свод продаж по контрагентам

Разберём полный цикл — от постановки задачи до проверки. Задача: получить по каждому контрагенту сумму продаж и число документов за выбранный период, отсортировав по убыванию суммы.

1. 1
   Откройте консоль запросов или конструктор запроса в конфигураторе.
2. 2
   Выберите виртуальную таблицу РегистрНакопления.Продажи.Обороты и задайте параметры периода.
3. 3
   Добавьте в выборку поле Контрагент и агрегаты: СУММА(СуммаОборот), КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Регистратор).
4. 4
   Добавьте секцию СГРУППИРОВАТЬ ПО Продажи.Контрагент.
5. 5
   Добавьте УПОРЯДОЧИТЬ ПО СуммаПродаж УБЫВ.
6. 6
   Выполните запрос и сверьте итоговую сумму с отчётом по продажам за тот же период.

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

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

Проверка результата — обязательный этап. Сумма по колонке СуммаПродаж должна совпасть с итогом стандартного отчёта по продажам за тот же период и с теми же отборами. Расхождение обычно означает разные границы периода (граница включает/не включает конечную дату) или разный набор отборов.

Контрольные точки

Как проверить результат

Итоговая сумма по всем строкам

Норма

Совпадает с типовым отчётом за тот же период

Тревожный признак

Расходится — проверьте границы периода и отборы

Число строк результата

Норма

Равно числу уникальных значений поля группировки

Тревожный признак

Строк больше — в группировку попало лишнее поле

Значения NULL в агрегатах

Норма

Отсутствуют или осознанно допустимы

Тревожный признак

NULL там, где ожидался 0 — оберните в ЕСТЬNULL

Типичные ошибки и ограничения

Большинство проблем с группировкой укладывается в короткий список повторяющихся сценариев. Знание их экономит время отладки.

Отдельно о производительности. Группировка по полю, не входящему в индекс таблицы, на больших объёмах может выполняться минутами. Для регистров накопления по возможности используйте виртуальные таблицы Обороты и Остатки — они физически хранят агрегированные данные и не требуют тяжёлой группировки по таблице движений.

Ещё одно ограничение: нельзя группировать по полям типа ХранилищеЗначения и по полям неограниченной длины — платформа откажется выполнять такой запрос. Если группировка по такому полю действительно нужна, сначала преобразуйте значение в промежуточной выборке.

Проверка и отладка запроса

Удобнее всего отлаживать группировку в консоли запросов: она показывает результат сразу, без написания кода. Начинайте с минимального набора полей — одно поле группировки и один агрегат, — затем добавляйте остальные по одному. Так проще локализовать, какое именно поле ломает запрос.

Полезный приём — временно убрать группировку и посмотреть детальные строки, которые попадают в проблемную группу. Если сумма по контрагенту «не сходится», детальная выборка по этому контрагенту покажет, какие документы учтены, а какие потерялись из-за отбора или соединения таблиц.

При соединении нескольких таблиц помните: группировка применяется уже к результату соединения. Левое соединение может размножить строки, и СУММА завысится. Проверяйте кратность соединения до агрегации — типичный признак проблемы: суммы кратно больше ожидаемых.

Частые вопросы

Чем СГРУППИРОВАТЬ ПО отличается от РАЗЛИЧНЫЕ?

РАЗЛИЧНЫЕ просто убирает дубли строк из результата, но не считает никаких агрегатов. СГРУППИРОВАТЬ ПО объединяет строки в группы и позволяет вычислять по ним суммы, количества и другие показатели. Если нужна только уникальность — достаточно РАЗЛИЧНЫЕ, оно обычно быстрее.

Как сгруппировать и получить общий итог одновременно?

Добавьте после выборки секцию ИТОГИ СУММА(СуммаПродаж) ПО ОБЩИЕ — к результату добавится итоговая запись. Альтернатива — выполнить отдельный запрос без группировки с одной агрегатной функцией, но это два обращения к базе вместо одного.

Почему запрос с группировкой работает медленно?

Чаще всего потому, что агрегируется физическая таблица движений регистра вместо виртуальной таблицы Обороты, либо группировка идёт по неиндексированному полю. Также проверьте, не размножает ли строки соединение с другой таблицей до группировки.

Можно ли группировать по полю из соединённой таблицы?

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

Как отобрать группы с суммой больше заданной?

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

Что делать, если в агрегате получается NULL вместо нуля?

NULL появляется, когда в группе нет ни одного непустого значения — типично при левых соединениях. Оберните агрегат в ЕСТЬNULL: ЕСТЬNULL(СУММА(Остатки.КоличествоОстаток), 0) — тогда вместо NULL вернётся 0, и последующие вычисления в коде не сломаются.