Группировка данных в запросе (СГРУППИРОВАТЬ ПО [ГРУППИРУЮЩИМ НАБОРАМ], ИТОГИ ПО [ОБЩИЕ])

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

Контрагент

Товар

Проект

Страна

Количество

Сумма

 

 

КОНСТРУКЦИЯ 1: СГРУППИРОВАТЬ ПО (GROUP BY)

Аналог в языке SQL – GROUP BY, именно в эту конструкцию и транслируется СГРУППИРОВАТЬ ПО.

Секция позволяет получить итоги по комбинации выбранных реквизитов без промежуточных итогов (говоря языком методов таблицы значений, свернуть):

ПРИМЕР 1

ЗАПРОС:

ВЫБРАТЬ						АГРЕГАТНЫЕ ФУНКЦИИ
СГРУППИРОВАТЬ ПО

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

РЕЗУЛЬТАТ:

 

 

Если нужно получить итоги по разным комбинациям группируемых реквизитов в одном запросе, то понадобится конструкция ОБЪЕДИНИТЬ [ВСЕ] (UNION [ALL]) (аналог в языке SQL – UNION [ALL]):

ПРИМЕР 2

ЗАПРОС :

ВЫБРАТЬ		NULL		NULL	АГРЕГАТНЫЕ ФУНКЦИИ
СГРУППИРОВАТЬ ПО
ОБЪЕДИНИТЬ
ВЫБРАТЬ	NULL		NULL	NULL	АГРЕГАТНЫЕ ФУНКЦИИ
СГРУППИРОВАТЬ ПО
ОБЪЕДИНИТЬ
ВЫБРАТЬ	NULL		NULL		АГРЕГАТНЫЕ ФУНКЦИИ
СГРУППИРОВАТЬ ПО

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

РЕЗУЛЬТАТ:

 

Можно использовать разные агрегатные функции в подзапросах, хотя в данном конкретном примере это явно неуместно, тем не менее, задачи бывают разные.

КОНСТРУКЦИЯ 2: ИТОГИ ПО [ОБЩИЕ] (TOTALS BY [OVERALL])

Аналог в языке SQL – WITH ROLLUP, но конструкция ИТОГИ ПО не транслируется в SQL-запрос, а обрабатывается самой платформой.

Конструкция позволяет применить к исходной выборке дополнительные группировки и присоединить к выборке полученные итоги. Итоги собираются по автоматически формирующимся комбинациям реквизитов секции ПО по следующему правилу: комбинация из первого реквизита, комбинация из первых двух реквизитов, комбинация из первых трех реквизитов и т.д. до последнего реквизита. Результат вполне себе можно назвать деревом с итогами по узлам, что, в общем-то, закрывает большинство задач по группировке данных одинэсника.

ПРИМЕР 3

ЗАПРОС:

ВЫБРАТЬ
ИТОГИ					АГРЕГАТНЫЕ ФУНКЦИИ
ПО

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

РЕЗУЛЬТАТ:

 

Отобрано по значению «Контрагент 4» для наглядности:

Если используется ключевое слово ОБЩИЕ, то дополнительно добавляется итоговая строка самого верхнего уровня.

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

ПРИМЕР 4

ЗАПРОС:

ВЫБРАТЬ					АГРЕГАТНЫЕ ФУНКЦИИ
СГРУППИРОВАТЬ ПО
ИТОГИ					АГРЕГАТНЫЕ ФУНКЦИИ
ПО

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

РЕЗУЛЬТАТ:

 

 

КОНСТРУКЦИЯ 3: СГРУППИРОВАТЬ ПО ГРУППИРУЮЩИМ НАБОРАМ  (GROUP BY GROUPING SETS)

Аналог в языке SQL — GROUP BY GROUPING SETS, именно в эту конструкцию и транслируется СГРУППИРОВАТЬ ПО ГРУППИРУЮЩИМ НАБОРАМ.

Начиная с версии платформы 1С 8.3.16 в языке запросов появилось расширение ГРУППИРУЮЩИМ НАБОРАМ секции СГРУППИРОВАТЬ ПО. В конструкторе запросов это выглядит следующим образом:

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

ПРИМЕР 5

ЗАПРОС:

ВЫБРАТЬ					АГРЕГАТНЫЕ ФУНКЦИИ
СГРУППИРОВАТЬ ПО ГРУППИРУЮЩИМ НАБОРАМ

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

РЕЗУЛЬТАТ:

 

 

Отмечу, что если указать единственный набор из всех реквизитов группировки, то результат запроса будет идентичен запросу без расширения ПО ГРУППИРУЮЩИМ НАБОРАМ, т.е. обычная группировка по всем реквизитам, как в запросе из примера 1.

Если область применения предыдущих конструкций вопросов не вызывает, то ценность конструкции СГРУППИРОВАТЬ ПО ГРУППИРУЮЩИМ НАБОРАМ не сразу очевидна рядовому одинэснику. Пример из Зазеркалья https://wonderland.v8.1c.ru/blog/podderzhka-grouping-sets-v-yazyke-zaprosov/ при всем моем безмерном уважении к ресурсу и его авторам, по моему скромному мнению, не самый лучший. Хороший пример придумать трудно еще вот по какой причине: большинство наборов данных в типовых конфигурациях можно успешно описать деревом, за счет чего минимальный функционал СКД закрывает большую часть задач. Кроме того, такие типичные отчеты, как дебиторская/кредиторская задолженность, продажи, как правило, содержат связанные или даже подчиненные друг другу аналитики, например, договор подчинен контрагенту, документ содержит ссылку на договор и т.д., вследствие чего получение итогов по нескольким отдельным аналитикам требуется крайне редко. Но есть сфера, в которой подобные разреженные матрицы используются повсеместно – это OLAP. Потенциально конструкция может принести в этой сфере, если запросы к OLAP-источникам пишутся на языке 1С.

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

Стоит добавить, что в языке SQL у секции GROUP BY есть расширение WITH CUBE, выводящее итоги по всем возможным комбинациям реквизитов группировки. Его аналога в языке запросов 1С нет, но с помощью конструкций, использованных в примерах 2 и 5 можно добиться того же результата, хотят и с бОльшими трудозатратами.

П.С. Ради эксперимента решил скрестить конструкцию СГРУППИРОВАТЬ ПО ГРУППИРУЮЩИМ НАБОРАМ с конструкцией ИТОГИ ПО (использовал агрегатную функцию СУММА). Общие итоги где-то задвоились, где-то затроились в зависимости от содержания группирующих наборов (комбинаций реквизитов) и их количества в запросе. Я полагаю, это вовсе не ошибка, а просто так делать было нельзя, но пока возможность не была закрыта ). В SQL такой финт не прокатил:

Возможно, это следствие того, что в SQL конструкция WITH ROLLUP – это расширение секции GROUP BY, а в 1C ИТОГИ ПО – это отдельная независимая конструкция (благодаря чему, кстати, мы можем использовать разные агрегатные функции в разных секциях (пример 4)). Но, учитывая, что на момент написания статьи и платформа 8.3.16, и конструкция находятся в стадии тестирования, до выхода релиза  в продакшн коллеги в желтых майках обязательно закроют возможность выполнения запросов с запрещенными комбинациями синтаксических конструкций.  Всем добра!