Как запросе рассчитать итоги по определенной группировке. Примеры применения функции скд - вычислитьвыражение. Агрегатная функция среднее

В данной небольшой заметке хочу показать, как можно произвести суммирование значений на различных уровнях группировки, в отчете с использованием системы компоновки данными.
Как показано на изображении, только на уровне группировки «Группы номенклатуры», рассчитывается ресурс «Заказать», в нем отображается, сколько нужно заказать по текущей группе номенклатуры исходя из определенных условий:

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

Соответственно, теперь необходимо рассчитать итоги, по выше находящимся группировкам («Склады», «ТипыСкладов») и общий итог.
Для этого используется функция ВычислитьВыражениеСГруппировкойМассив:
ВЫЧИСЛИТЬВЫРАЖЕНИЕСГРУППИРОВКОЙМАССИВ (EVALEXPRESSIONWITHGROUPARRAY)
Синтаксис:
ВычислитьВыражениеСГруппировкойМассив(,)
Описание:
Функция возвращает массив, каждый элемент которого содержит результат вычисления выражения для группировки по указанному полю.
Компоновщик макета при генерации макета преобразовывает параметры функции в термины полей макета компоновки данных. Например, поле Контрагент будет преобразовано в НаборДанных.Контрагент.
Компоновщик макета при генерации выражений для вывода пользовательского поля, в выражении которого присутствует только функцияВычислитьМассивСГруппировкойМассив(), генерирует выводимое выражение таким образом, чтобы выводимая информация была упорядочена. Например, для пользовательского поля с выражением:

ВычислитьВыражениеСГруппировкойМассив("Сумма(СуммаОборот)", "Контрагент")
Компоновщик макета сгенерирует для вывода следующее выражение:

СоединитьСтроки(Массив(Упорядочить(ВычислитьВыражениеСГруппировкойТаблицаЗначений("Представление(Сумма(НаборДанных.СуммаОборот)),Сумма(НаборДанных.СуммаОборот)","НаборДанных.Контрагент"),"2")))

Параметры:

Тип: Строка. Выражение, которое нужно вычислить. Строка, например, Сумма(СуммаОборот).

Тип: Строка. Выражения полей группировки – выражения полей группировки, перечисленные через запятую. Например, Контрагент, Партия.

Тип: Строка. Выражение, описывающее отбор, применяемый к детальным записям. В выражении не поддерживается использование агрегатных функций. Например,ПометкаУдаления = Ложь.

Тип: Строка. Выражение, описывающее отбор, применяемый к групповым записям. Например, Сумма(СуммаОборот) > &Параметр1.
Пример:

Максимум(ВычислитьВыражениеСГруппировкойМассив ("Сумма(СуммаОборот)", "Контрагент"));

Подробное описание синтаксиса функции можно найти по адресу http://its.1c.ru/db/v837doc#bookmark:dev:TI000000582
Теперь для расчета, продублируем поле «Заказать», с различными значениями «Рассчитывать по…», используя следующие выражения, обратите внимание, что в каждом выше стоящем уровне используются значения уровней ниже стоящих группировок.

В итоге получаем следующую конструкцию:

Вроде и нет ничего проще,а с четким определением проблема.
Итак.

Сгруппировать(группировка) - свернуть записи в группу по групповым полям (полям с одинаковым значением).
Пример . Нам необходимо узнать общее количество товаров на определенном складе (...всего 999 тонн..). В регистр записывается каждое движение и даже элементарный запрос из двух полей (Склад и Количество) сформирует выборку из множества записей с одинаковым значением поля Склад и разными значениями поля Количество.
Свернуть (сгруппировать) остатки по полю Склад- это получить общее количество товара на этом складе.
Отсюда вытекает необходимость (для нашего примера, но вообщем не обязательно) вычисления агрегатных функций (производят одиночное значение для определенной группы полей таблицы) по групповому полю (в терминах конструктора запросов- Суммируемое поле ).
В нашем примере - групповое поле Склад, а агрегатную функцию вычислим по полю Количество- получим ОБЩЕЕ КОЛИЧЕСТВО всех товаров на складе.
Т.к. настолько простые запросы очень редки, то во всех иных случаях при применении Группировки, группироваться будут все поля запроса (за исключением Суммируемых) и выборка будет сокращаться (сворачиваться) только на те записи , у которых все групповые поля одинаковы (одинаковый склад, одинаковый товар, одинаковый размер товара- эти записи свернуться, если хоть что-то разное- нет).

Итоги (т.н. общие )- дополнительные строки в результате запроса, содержащие итоги по заданным полям и группировкам (т.н. промежуточные итоги).
Общие итоги- строка в результате запроса со значением итогов по всем записям.

Итоги по группировкам - значения агрегатных функций по групповым полям (полям с одинаковым значением).

Отличия запроса с группировками (СГРУППИРОВАТЬ ПО) от запроса с итогами по группировкам .

В первом случае в результат запроса попадут только итоговые строки. В примере групповое поле- номенклатура:

Во втором- итоги и исходные записи. Причина- в определении (итоги не "сворачивают" данные, а добавляют строки)!В примере групповое поле- номенклатура:

Важно! В запросе нет необходимости пользоваться и группировкой и итогами! Все зависит от того, что нам нужно...

Порядок группировок в запросе с итогами по группировкам имеет важное значение, т.к. итоги рассчитываются по порядку.

Итоги по иерархии. Если групповое (группировочное) поле является ссылкой на справочник, то для расчета итогов по группам справочника (или родительским элементам, если справочник без групп) необходимо указать ключевое слово ИЕРАРХИЯ. В этом случае в результат будут добавлены записи с итогами для уровней иерархии справочника.

Важно! В языке запросов допускается совместное использование различных итогов в одном запросе (общих, по группировкам и иерархических).

Синтаксис работы с итогами в выборке из результата запроса

Метод ТипЗаписи()
Пример:
Если Выборка . ТипЗаписи () = ТипЗаписиЗапроса . ИтогПоГруппировке Тогда

Продолжить;
КонецЕсли;

В компоновке данных настройка итогов выглядит несколько иначе, чем в запросах. Определим в системе компоновки данных набор данных «Запрос».

В самом запросе мы не настраиваем итоги, а переходим на закладку «Ресурсы» компоновки данных. На уровне схемы компоновки данных мы определяем ресурсы . Это те поля, которые нужно посчитать на уровне группировок. Нажмём на кнопку «>>» и система сама перенесёт все числовые поля и определит их в качестве ресурсов.

В ресурсах можно указывать и нечисловые поля. Например, если выбрать реквизит «Ссылка», то система подсчитает количество документов в наших группировках. Эта информация тоже может быть полезной. Итак, в схеме компоновки мы определяем только ресурсы , а сами группировки настраиваются на уровне варианта отчёта. Так же пользователь сам может создать группировки, которые хочет видеть в своей настройке варианта отчёта.

Создадим стандартную настройку компоновки данных.
Нажмём на кнопку «Открыть конструктор настроек».

Выберем тип отчета — список. Нажмём кнопку «Далее».

Выберем все поля и переместим поле контрагент на самую верхнюю позицию. Нажмём кнопку «Далее».

Выберем все поля и переместим поле контрагент на самую верхнюю позицию. Нажмём кнопку «OK».

Получилась следующая настройка:

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

Если открыть наш отчёт в режиме 1С:Предприятие и сформировать его, то мы увидим, что формируются итоговые данные на уровне группировок. Итоги по номенклатуре и по контрагентам.

Настройка ресурсов в схеме компоновки данных 1С

Теперь обратим внимание на настройки, которые существуют у ресурсов . В поле «Выражение» мы можем указать агрегатную функцию, по которой можно получить значение ресурса. В выпадающем списке можно увидеть ряд стандартных функций, но не все. Например, нет функций .

Здесь в поле «Выражение» мы можем написать и своё собственное выражение.

В поле «Выражение» мы можем обращаться и к функциям общих модулей.

Кроме того, можно указать в поле «Расчитывать по…» по каким группировкам необходимо рассчитывать ресурс. Если поле «Расчитывать по…» не заполнено, то итоговое значение ресурса будет считаться на всех уровнях группировки, которые определены в настройках варианта отчёта . В нашем случае нужно заполнить поле «Расчитывать по…» ресурса «Количество», так как мы можем продавать товар с разными единицами измерения. Например: масло в литрах и колёса в штуках. Не правда ли, нелогично будет складывать количество этих товаров? Поэтому нам нужно оставить суммирование количества на уровне номенклатуры, а на уровне контрагентов
суммирование уберём.

Если сформируем отчёт, то увидим, что итоги по количеству считаются только по номенклатуре, а по контрагентам итоги количества пустые.

Возможности при описании ресурсов в схеме компоновки данных 1С

Давайте рассмотрим ряд неочевидных особенностей связанных с описанием ресурсов .

• Каждый ресурс можно определить несколько раз . Но это имеет смысл лишь в том случае, если
  ресурс будет рассчитываться по разным уровням группировок. Например, если количество, в одном случае
  суммируется для номенклатуры, а по контрагентам получаем минимальное значение.

Если сформируем отчёт, то увидим, что по контрагенту «Дерия» минимальная закупка составляет пять единиц номенклатуры «Бумага самоклеющаяся».

• В поле «Выражение», кроме написания формулы, можно использовать специальную функцию компоновки данных которая называется «Вычислить» . Эта функция позволяет вычислить какое-то итоговое значение по определённой формуле. Например, по каждому контрагенту необходимо знать процент закупки в натуральных единицах относительно общего объёма. Но как получить общий объём закупок по количеству? Для этого воспользуемся функцией «Вычислить» и запишем следующее выражение в поле «Выражение»:

Сумма(Количество)/Вычислить("Сумма(Количество)", "ОбщийИтог")*100

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

Запустим отчёт и увидим результат вычисления процента закупки по контрагенту «АУПП КОС ТОО» относительно
общего объёма:

В конце статьи хочу посоветовать вам бесплатный от Сотникова Анатолия. Это курс от опытного программиста. Он на отдельной базе покажет вам, как строить отчеты в СКД. Вам только нужно внимательно слушать и запоминать! Вы получите ответы на такие вопросы:

• Как создать простой отчет в виде списка?
• Для чего нужны колонки Поле, Путь и Заголовок на закладке «Поля»?
• Какие существуют ограничения для полей компоновки?
• Как правильно настраивать роли?
• Какие существуют роли для полей компоновки?
• Где найти закладку компоновка данных в запросе?
• Как настраивать параметры в СКД?
• Дальше еще интереснее...

Наверное, не стоит самому стараться бороздить интернет в поисках нужной информации? Тем более все готово для применения. Только начните! Все подробности о том, что есть в бесплатных видеоуроках

Вот один из уроков о закладке компоновки данных в запросе:

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

1. нарастающий итог в группировке;
2. нарастающий итог в кросс-таблице;
3. получение предыдущего значения;
4. вывод ТЧ в одной строке.

1. Получение показателя нарастающим итогом

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

Если количество товара нарастающим итогом требуется получать на уровне детальных записей, то функцию ВычислитьВыражение задаем для вычисляемого поля на вкладке "Вычисляемые поля" (см. рисунок 3).
В зависимости от уровня получения нарастающего итога, создаем группировку (см. рисунок 4): на уровне ресурсов - группировку по товарам, на уровне ДЗ - группировку детальных записей.

Рисунок 4. Группировки отчета с нарастающим итогом

2. Получение значения показателя из предыдущей строки

Получим курс валюты на дату и предыдущую дату. Для этого создаем вычисляемое поле и в поле выражение пишем следующее выражение (см. рисунок 5):
ВычислитьВыражение("Курс", "Предыдущая", "Предыдущая")
которое будет брать предыдущее значение курса валюты для текущей строки, последний параметр функции ограничивает получение данных.
Так как работаем на уровне детальных записей, сразу переходим к вкладке "Настройки" и создаем группировку - детальные записи.

3. Получение показателя нарастающим итогом в кросс-таблице

Получим количество товара нарастающим итогом в разрезе периода. Для этого создаем вычисляемое поле (см. рисунок 1). На вкладке "Ресурсы" для вычисляемого поля укажем следующее выражение (см. рисунок 6):
ВычислитьВыражение("Сумма(КоличествоОборот)", "Период", "Первая", "Текущая")
которое на уровне группировки будет вычислять количество товара в промежутке от первой строки до текущей в разрезе периода по каждой номенклатуре.
На вкладке "Настройки" создаем таблицу с группировкой по номенклатуре в строке и группировкой по периоду в колонке (см. рисунок 7).

4. Вывод данных табличной части в одну строку

Способы вывода данных табличной части в одну строку, включая способ с использованием функции ВычислитьВыражение, рассмотрен в статье

Они имеют ту же задачу — произвести математические или статистические операции над данными выборки. Но имеются и существенные различия:

1. Итоги добавляют строки к данным выборки, в то время как группировки сворачивают выборку и строк становится меньше.
2. Итоги можно рассчитывать по всем данным выборки или по отдельным полям, при этом, в отличие от группировок, могут оставаться поля, которые не являются ни итоговыми, ни группировочными.
3. Итоги могут учитывать иерархию.

Для начала немного теории. Секция итогов в запросах 1С состоит из двух разделов.

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

Существует 6 видов агрегатных функций, применяемых при группировках:

1. СУММА — суммирует значения группируемого столбца, применяется только для числовых значений.
2. СРЕДНЕЕ — вычисляет среднее арифметическое из значений группируемого столбца, применяется только для числовых значений.
3. МАКСИМУМ — может применяться для любых типов значений группируемого столбца, при этом возвращается максимальное значение из всех группируемых. Если значения не числовые, то возвращается последнее при сортировке по возрастанию.
4. МИНИМУМ — может применяться для любых типов значений группируемого столбца, при этом возвращается минимальное значение из всех группируемых. Если значения не числовые, то возвращается первое при сортировке по возрастанию.
5. КОЛИЧЕСТВО — возвращает количество значений в группируемом столбце, может использоваться для любых типов значений. В расчет не берутся значения типа NULL.
6. КОЛИЧЕСТВО РАЗЛИЧНЫЕ — возвращает количество различных значений в группируемом столбце, может использоваться для любых типов значений. В расчет не берутся значения типа NULL.

Второй раздел начинается с ключевого слова ПО и содержит группировочные поля в разрезе которых будет подсчитываться результат по итоговым полям. И/или слово ОБЩИЕ , если нужно подсчитать итоги по всей выборке. Здесь важен порядок, в котором будут располагаться поля, это этого зависит и порядок подсчета итогов в результате запроса.

Если в группировочном поле находятся данные, имеющие иерархическую структуру (иерархический справочник), то можно подсчитать итоги по всей иерархической цепочке. Для этого используется ключевое слово ИЕРАРХИЯ после имени группировочного поля. Если же итоги нужно подсчитывать по всей иерархической цепочке, кроме самого группировочного поля, то используется ключевое слово ТОЛЬКО ИЕРАРХИЯ .

Рассмотрим в качестве примера таблицу поставок товаров.

Задача 1. Подсчет итогов по нескольким полям.

Необходимо узнать, сколько всего товаров поставлено и сколько столов и стульев.

Запрос. Текст= "ВЫБРАТЬ
Поставки.Товар КАК Товар,


ИЗ
Поставки КАК Поставки
ИТОГИ
СУММА(Количество)
ПО
ОБЩИЕ,
Товар" ;

В результате получим следующую таблицу с итогами (итоговые строки выделены желтым).

Задача 2. Группировка записей по полю.

Необходимо сгруппировать записи таблицы по виду товара.

Запрос. Текст= "ВЫБРАТЬ
Поставки.Товар КАК Товар,
Поставки.Поставщик КАК Поставщик,
Поставки.Количество КАК Количество,
ИЗ
Поставки КАК Поставки
ИТОГИ

ПО
Товар" ;

Задача 3. Подсчет итогов по полю с учетом иерархии.

Запрос. Текст= "ВЫБРАТЬ
Поставки.Товар КАК Товар,
Поставки.Поставщик КАК Поставщик,
Поставки.Количество КАК Количество,
ИЗ
Поставки КАК Поставки
ИТОГИ
СУММА(Количество)
ПО
Поставщик ИЕРАРХИЯ" ;

В результате получим следующую таблицу.

Задача 4. Подсчет итогов только по иерархии.

Запрос. Текст= "ВЫБРАТЬ
Поставки.Товар КАК Товар,
Поставки.Поставщик КАК Поставщик,
Поставки.Количество КАК Количество,
ИЗ
Поставки КАК Поставки
ИТОГИ
СУММА(Количество)
ПО
Поставщик ТОЛЬКО ИЕРАРХИЯ" ;

В результате получим следующую таблицу.

Как обойти результат запроса с итогами

Результат запроса с итогами можно обойти несколькими способами:

1. Как обычный запрос. В этом случае будут последовательно выведены группировочные и детальные записи. Пример такого обхода приведен в статье .
2. Отдельно обойти группировки и детальные записи.
   В качестве примера возьмем запрос из задачи 1. Но не будем учитывать общие итоги.

   ГруппировкиТовар = Запрос. Выполнить() . Выбрать(ОбходРезультатаЗапроса. ПоГруппировкам, "Товар" ) ;
   Пока ГруппировкиТовар. Следующий() Цикл
   //здесь обрабатываем группировочные строки результата запроса
   ДетальныеЗаписи = ГруппировкиТовар. Выбрать() ;
   Пока ДетальныеЗаписи. Следующий() Цикл
   //здесь обрабатываем строки детальных записей, принадлежащих группировкам
   КонецЦикла ;
   КонецЦикла ;