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

Публикация № 1124762

Разработка - Практика программирования

СГРУППИРОВАТЬ ПО ГРУППИРУЮЩИМ НАБОРАМ ИТОГИ GROUP BY GROUPING SETS TOTALS CUBE ROLLUP

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

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

Контрагент

Товар

Проект

Страна

Количество

Сумма

 

 

КОНСТРУКЦИЯ 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 можно добиться того же результата, хотят и с бОльшими трудозатратами.

Всем добра!

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо развёрнутое
Свернуть все
1. VmvLer 19.09.19 15:57 Сейчас в теме
запросы понятны, спасибо.
цветные квадратики бесят, возможно это потому, что я не курю.

Применимо к динамическим ABC, например.
Я, чтобы сгруппировать группировки в СКД делал выполнение два раза:
1-й раз получал через СКД ABC набор по определенным показателям.
2-й раз группировал его в классическое ABC-дерево.

похоже, теперь можно делать такие отчеты группировка по группировкам в один проход.
nikivr; paybaseme; Kontakt; +3 Ответить
2. sertak 235 19.09.19 16:03 Сейчас в теме
(1) Я не знаю как правильно, попробую в следующий раз монохром. Идей много, короче.
3. CSiER 29 21.09.19 06:59 Сейчас в теме
Спасибо за статью, визуализация отличная.
Novinsky; conductor; hame1e00n; user774630; sertak; +5 Ответить
4. sertak 235 21.09.19 09:40 Сейчас в теме
5. ILM 238 23.09.19 05:26 Сейчас в теме
Квадратики цветные это для младшей ясельной группы. Для средней подготовительной уже нужны карточки с рисунками: домики, грибочки, яблочки и т.д.
hame1e00n; +1 Ответить
6. sertak 235 23.09.19 06:20 Сейчас в теме
(5) Отпишитесь, когда перейдете в среднюю, я переделаю статью.
russb; Krasnyj; user1290312; Novinsky; hame1e00n; IgorS; +6 Ответить
7. ILM 238 23.09.19 16:50 Сейчас в теме
(6) Эх, опоздали! У нас весной выпускной был! Теперь в первом классе)))
8. Diks_Soft 27.01.20 09:13 Сейчас в теме
Спасибо! И с цветными квадратиками все норм)
9. Ibrogim 1149 28.02.20 11:20 Сейчас в теме
Спасибо, однозначно +, узнал про "СГРУППИРОВАТЬ ПО ГРУППИРУЮЩИМ НАБОРАМ" )
Может я не визуал, но мне вспомнить какой цветной квадрат что означает почти нереально

Прям бы в квадрате мелким шрифтом "Пр-т" Тов." "Кол" и т.п.
Оставьте свое сообщение

См. также

Универсальная выгрузка/загрузка данных для отличающихся конфигураций (JSON, Такси+ОФ) Промо

Перенос данных из 1C8 в 1C8 Универсальные обработки Распределенная БД (УРИБ, УРБД) v8 1cv8.cf Абонемент ($m)

Простой перенос через JSON данных между двумя базами 1С (документов, справочников, ПВХ, ПВР, счетов). Аналогична произвольной выгрузке в типовой "Выгрузка/загрузка XML", но может использоваться для отличающихся конфигураций. Подходит для любых пар баз с любым интерфейсом (управляемый + обычный). Без настроек. Не требует идентичности конфигураций и платформ. При переносе типы данных сопоставляются по наименованиям метаданных, объекты и ссылки по UID.

1 стартмани

22.10.2014    202145    3077    ekaruk    178    

Загрузка данных из 1С МиниДеньги в 1С:Деньги 8, редакция 2.0

Обмен через XML Перенос данных из 1C8 в 1C8 v8 Домашние учет и финансы УУ Абонемент ($m)

Обработка для первоначальной загрузки данных (справочников и операций) в настольную программу 1С:Деньги 8, редакция 2.0 из файлы резервной копии мобильной программы 1С Миниденьги.

2 стартмани

01.10.2015    14834    14    Claus32    5    

Редактор чеков ККМ Розница 2.0 - 2.3

Обработка документов Кассовые операции Розничная торговля Кассовые операции Розничная торговля v8 Розница Розничная и сетевая торговля (FMCG) Рестораны, кафе и фаст-фуд БУ НУ Абонемент ($m)

Обработка чеков ККМ списком (редактирование, удаление, ...). Переформирование ОРП, связь чеков с ОРП. Редактирование времени КС. Редактирование суммы и времени выемки.

1 стартмани

06.08.2015    47896    450    Dima_    83    

Загрузка из Excel в 1С:Розница

Обработка документов Обработка справочников Загрузка и выгрузка в Excel Учет ТМЦ Учет ТМЦ v8 Розница Россия УУ Абонемент ($m)

Универсальная загрузка Excel файлов в 1С:Розница 2.0

1 стартмани

27.05.2015    14617    90    KarinaSV    7    

Автоматическая установка себестоимости номенклатуры для конфигурации Розница 2.0 (2.1) +Бонус, исправляем ошибки при настройке расписания запуска для внешних обработок в Рознице 2.0

Ценообразование, прайсы Обработка документов Розничная торговля Ценообразование, анализ цен Розничная торговля Ценообразование, анализ цен v8 Розница Розничная и сетевая торговля (FMCG) Россия УУ Абонемент ($m)

Обработка для автоматического создания документов Установка себестоимости, в случае если себестоимость не установлена на момент продажи. Помогает упростить установку себестоимости номенклатуры, если у вас в базе ведется учет по нескольким магазинам. Тестировалась на релизе 2.0.8.12 и 2.1.7.14. Подойдет для тех систем, где себестоимость номенклатуры одинакова для всех магазинов.

1 стартмани

31.03.2014    26204    38    sanches    15    

Выемка денежных средств из КассыККМ. Розница 2.0

Кассовые операции ККМ Кассовые операции v8 Розница УУ Абонемент ($m)

Уставшим от отсутствия ВыемкиДС после закрытия кассовой смены посвящается. Подключаемая обработка. Управляемый интерфейс.

1 стартмани

12.12.2012    74974    287    StepByStep    40