Каждый разработчик 1С рано или поздно сталкивается с необходимостью построения сложных аналитических отчетов, где данные нужно свернуть в разных разрезах одновременно. До появления платформы 1С:Предприятие 8.3.16.1030 для решения этой задачи приходилось писать громоздкие запросы с объединением нескольких таблиц через оператор ОБЪЕДИНИТЬ ВСЕ (UNION ALL) или выполнять постобработку данных в кодовой части. С введением поддержки группирующих наборов (GROUPING SETS) ситуация кардинально изменилась.
В этой статье мы подробно разберем, что представляют собой группирующие наборы в языке запросов 1С, как они помогают сократить количество обращений к СУБД, в чем их преимущество перед классическими объединениями, а также рассмотрим практические примеры применения этой технологии в реальных задачах.
Представим стандартную задачу: нам необходимо получить остатки или продажи товаров одновременно в двух разрезах для построения дашборда. Нам нужны итоги по паре Контрагент + Номенклатура и параллельно общие итоги только по Контрагенту.
Раньше для этого мы строили запрос с объединением двух выборок. Давайте проанализируем типичный пример такого запроса:
ВЫБРАТЬ
NULL КАК Номенклатура,
Остатки.Контрагент КАК Контрагент,
СУММА(Остатки.Сумма) КАК Сумма
ИЗ
РегистрНакопления.Взаиморасчеты.Остатки КАК Остатки
СГРУППИРОВАТЬ ПО
Остатки.Контрагент
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Остатки.Номенклатура,
Остатки.Контрагент,
СУММА(Остатки.Сумма)
ИЗ
РегистрНакопления.Взаиморасчеты.Остатки КАК Остатки
СГРУППИРОВАТЬ ПО
Остатки.Контрагент,
Остатки.Номенклатура
Давайте выясним причину, почему такой подход неэффективен на больших базах данных. При выполнении этого запроса СУБД вынуждена сделать два независимых прохода по таблице остатков (или дважды просканировать соответствующие индексы). Сначала СУБД сгруппирует данные по контрагентам, затем выполнит второе сканирование той же таблицы для группировки по контрагентам и номенклатуре, после чего объединит эти результаты во временных таблицах. Если исходная таблица содержит миллионы строк, это приведет к серьезной потере производительности.
Использование группирующих наборов позволяет СУБД выполнить агрегацию данных за один проход по исходной таблице. В языке запросов 1С для этого применяется конструкция СГРУППИРОВАТЬ ПО ГРУППИРУЮЩИМ НАБОРАМ (в СУБД это транслируется в стандартный GROUP BY GROUPING SETS).
Давайте перепишем наш предыдущий пример, используя новые возможности платформы:
ВЫБРАТЬ
Остатки.Номенклатура КАК Номенклатура,
Остатки.Контрагент КАК Контрагент,
СУММА(Остатки.Сумма) КАК Сумма
ИЗ
РегистрНакопления.Взаиморасчеты.Остатки КАК Остатки
СГРУППИРОВАТЬ ПО ГРУППИРУЮЩИМ НАБОРАМ (
(Остатки.Контрагент),
(Остатки.Контрагент, Остатки.Номенклатура)
)
Разберем по шагам, что здесь происходит. Вместо дублирования разделов ВЫБРАТЬ и ИЗ мы один раз описываем поля выборки и источник данных. В секции группировки мы указываем круглые скобки, внутри которых перечисляем необходимые нам комбинации полей (наборы группировок).
При выполнении этого запроса СУБД прочитает данные из таблицы остатков единожды. Затем во внутренней памяти (или TempDB) будут построены сразу две группировки. В результирующей выборке для строк, соответствующих первому набору (Остатки.Контрагент), колонка Номенклатура автоматически примет значение NULL, так как это поле не участвовало в расчете данной строки.
Некоторые разработчики ошибочно полагают, что использование группирующих наборов — это лишь синтаксический сахар, который под капотом разворачивается платформой в тот же самый UNION ALL. Это в корне неверно.
Конструкция группирующих наборов напрямую поддерживается всеми современными СУБД, с которыми работает 1С:Предприятие:
СУБД используют оптимизированные алгоритмы (такие как многопоточный Hash Aggregate или оптимизированный Sort Aggregate), чтобы параллельно рассчитывать агрегаты по разным осям в рамках одной операции чтения данных. На практике прирост скорости на больших массивах информации может составлять от 2 до 5 раз по сравнению с запросами на UNION ALL. Для оценки нагрузки и анализа реального выполнения запросов на стороне СУБД подойдёт обработка для анализа SQL-сервера.
Рассмотрим реальную задачу, которая часто возникает в торговых компаниях: получение отчета по ABC-классификации товаров. Аналитик хочет видеть детальные записи по товарам с их классами (А, В или С), но в то же время ему необходимо сразу выводить свернутые группы по этим классам (итоговые суммы по классу А, по классу B и по классу C) в один проход без перезапроса базы данных.
Обычно для решения такой задачи в компоновке данных (СКД) разработчики прибегали к двухпроходной схеме:
С использованием GROUPING SETS мы можем получить детальный и свернутый результат за один легкий запрос. Давайте посмотрим на пример реализации такого запроса:
ВЫБРАТЬ
Продажи.Товар КАК Товар,
Продажи.КлассABC КАК КлассABC,
СУММА(Продажи.Выручка) КАК Выручка
ИЗ
РегистрНакопления.ПродажиОбороты КАК Продажи
СГРУППИРОВАТЬ ПО ГРУППИРУЮЩИМ НАБОРАМ (
(Продажи.КлассABC, Продажи.Товар), // Детальные записи в разрезе товаров и классов
(Продажи.КлассABC), // Итоги по самим классам ABC
() // Общий итог по всему предприятию (пустые скобки)
)
Обратите внимание на конструкцию () в самом конце списка наборов. Пустые круглые скобки указывают системе на необходимость расчета общего итога по всей таблице (гранд-тотал). Это избавляет нас от необходимости делать расчет общих итогов на стороне приложения.
При использовании группирующих наборов возникает логическая сложность. Если в результирующей строке колонка Товар содержит значение NULL, как нам понять: это строка итогов по классу ABC (где товар был исключен из группировки), или же в базе данных действительно лежит запись с незаполненным товаром (пустая ссылка / NULL)?
Для решения этой проблемы в платформу была добавлена специальная функция языка запросов СгруппированоПо() (в англоязычном синтаксисе — GroupedBy()).
Эта функция принимает в качестве параметра имя поля и возвращает булево значение (Истина или Ложь):
NULL).Рассмотрим пример использования этой функции для формирования красивого и понятного отчета:
ВЫБРАТЬ
ВЫБОР
КОГДА СГРУППИРОВАНОПО(Продажи.Товар) ТОГДА Продажи.Товар.Наименование
ИНАЧЕ "--- ИТОГО ПО КЛАССУ ---"
КОНЕЦ КАК ТоварНаименование,
Продажи.КлассABC КАК КлассABC,
СУММА(Продажи.Выручка) КАК Выручка
ИЗ
РегистрНакопления.ПродажиОбороты КАК Продажи
СГРУППИРОВАТЬ ПО ГРУППИРУЮЩИМ НАБОРАМ (
(Продажи.КлассABC, Продажи.Товар),
(Продажи.КлассABC)
)
Благодаря функции СгруппированоПо() мы можем четко разграничить вывод информации и программно подставлять текстовые заглушки для итоговых строк, что делает результат абсолютно понятным для конечного пользователя.
Несмотря на колоссальное удобство, использование группирующих наборов в 1С накладывает ряд жестких технологических ограничений, о которых необходимо помнить при проектировании запросов:
СГРУППИРОВАТЬ ПО ГРУППИРУЮЩИМ НАБОРАМ допускается исключительно в запросах верхнего уровня. Это означает, что вы не можете использовать эту конструкцию внутри вложенных запросов (подзапросов). Если вам все же необходимо обработать сгруппированные данные далее, используйте временные таблицы (запись результата во временную таблицу через ключевое слово ПОМЕСТИТЬ полностью поддерживается).ВЫБРАТЬ и к которым не применены агрегатные функции (такие как СУММА, КОЛИЧЕСТВО, МИНИМУМ), обязаны входить хотя бы в один из группирующих наборов.ВЫБРАТЬ является единым для всех наборов группировок. Вы не можете вычислять сумму для одного набора и среднее значение для другого в рамках одного запроса.Разработчикам не обязательно писать весь этот синтаксис вручную. Платформа предоставляет удобный визуальный интерфейс для создания таких запросов.
Если мы откроем Конструктор запросов и перейдем на вкладку «Группировка», мы увидим специальный флаг «Использовать несколько группировок». При установке этого флага интерфейс преобразуется: в правой части окна появится возможность интерактивно создавать независимые наборы полей, перетаскивая их из левого списка. Конструктор автоматически сгенерирует корректный синтаксис со всеми скобками и ключевыми словами. Для составления и отладки подобных запросов в тонком клиенте можно использовать инженерную консоль запросов.
Мы проанализировали мощный инструмент оптимизации запросов — группирующие наборы. Использование GROUPING SETS позволяет:
ОБЪЕДИНИТЬ ВСЕ.СгруппированоПо().Рекомендуем активно внедрять данную технологию в свои проекты, особенно при работе с высоконагруженными базами данных и сложными аналитическими отчетами.