Агрегатные функции СКД, о которых мало кто знает

Предыдущие мои статьи по СКД можно посмотреть по ссылкам ниже:

• Основные понятия и элементы схемы компоновки
• Настройки вариантов отчетов
• Работа с пользовательскими настройками
• Расширения языка запросов
• Наборы данных и связи между ними, иерархии
• Внутренние функции СКД, ВычислитьВыражение

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

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

Если посмотреть справку то можно увидеть гораздо больше агрегатных функций, о существовании некоторых из которых мало кто знает:

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

Но мы, программисты 1С довольно редко читаем справку. Обычно мы пытаемся понять новый материал интуитивно, максимум обращаемся к синтаксис-помощнику, и очень зря.  Ведь, использование этих функций позволяет сильно облегчить и, главное, ускорить создание большого количество отчетов.

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

Массив

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

Массив(Различные Номенклатура)
Массив(Количество)

Если массив формируется по числовому полю, то к его результату можно применять еще одну агрегатную функцию (Сумма, Количество, Максимум, Минимум, Среднее).

ТаблицаЗначений

Функция формирует таблицу значений. Количество колонок результирующей функции равно количеству переданных параметров (полей или выражений). Обрабатываются подчиненные записи текущей группировки. После ключевого слова «КАК» можно задать наименования колонок таблицы. Ключевое слово «Различные» перед первым параметром позволяет получить только различные записи. Проверка выполняется по всем колонкам таблицы значений.

ТаблицаЗначений(Различные Контрагент КАК Клиент,  Номенклатура КАК Товар)

Если в качестве параметров указаны поля-остатки и данные выбираются с периодичностью (Остатки и обороты), стоки таблицы формируются в разрезе периодов и уникальной комбинации измерений. То-есть, если значение остатка в разных периодах различное, в итоговой таблице значений будет сформировано несколько строк, со значениями из разных периодов.

Для наглядности приведу пример.

Создадим набор данных со следующим запросом:

ВЫБРАТЬ
ТоварныеЗапасыОстаткиИОбороты.КоличествоНачальныйОстаток,
ТоварныеЗапасыОстаткиИОбороты.КоличествоКонечныйОстаток,
ТоварныеЗапасыОстаткиИОбороты.Товар КАК Товар,
ТоварныеЗапасыОстаткиИОбороты.Склад КАК Склад
ИЗ
РегистрНакопления.ТоварныеЗапасы.ОстаткиИОбороты(, , месяц, , ) КАК ТоварныеЗапасыОстаткиИОбороты

Добавляем вычисляемое поле «ТаблицаЗначений» и в качестве выражения ресурса, укажем для него:

ТаблицаЗначений(ПериодМесяц КАК Период, Склад КАК Склад, КоличествоКонечныйОстаток КАК Остаток)

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

Важное дополнение. Если для колонок таблицы значений не заданы наименования после ключевого слова «КАК», во всех функциях, которые выполняют обработку этой таблицы (Свернуть, Упорядочить, ПолучитьЧасть) нельзя использовать наименования для указания требуемых колонок. Имена колонок будут иметь специальные наименования, назначенные системой. Для обращения к этим колонкам можно будет использовать только их порядковые номера. Так что если планируется обращение к колонкам таблицы значений по именам, обязательно нужно указывать эти имена после ключевого слова «КАК».

 Свернуть 

Функция позволяет удалить повторяющиеся строки из массива или таблицы значений. Если в качестве первого параметра используется Таблица значений, необходимо вторым параметром указать порядковые номера или имена колонок, по которым нужно свернуть данные:

Свернуть(Массив(Товар))
Свернуть(ТаблицаЗначений(Номенклатура КАК Товар, КоличествоОборот) ,"Товар")
Свернуть(ТаблицаЗначений(КоличествоОборот) ,"1")

Важный момент, данная функция не сворачивает данные с группировкой суммовых показателей. Происходит именно отсечение строк с повторяющимися значениями:

Для массива, функция аналогична применению ключевого слова «Различные» в функции «Массив». Для таблицы значений, функция позволяет проверить уникальность не по всем колонкам, в отличие от применения ключевого слова «Различные» в функции «ТаблицаЗначений».

ПолучитьЧасть

Функция позволяет получить часть таблицы значений. Оставить только необходимые колонки. Наименования или порядковые номера колонок нужно указать вторым параметром функции:

ПолучитьЧасть(ТаблицаЗначений(Номенклатура, Количество) ,"1")
ПолучитьЧасть(ТаблицаЗначений(Номенклатура КАК Товар, Количество) ,"Товар")

В результате получится таблица значений с одной колонкой «Номенклатура».

Упорядочить

Функция позволяет упорядочить массив или таблицу значений. Если используется таблица значений, вторым параметром нужно указать номера колонок, по которым требуется выполнить упорядочивание. Можно указать направление упорядочивания (Возр / Убыв) и признак «Автоупорядочивание»:

Упорядочить (Массив(Номенклатура))
Упорядочить(ТаблицаЗначений(Номенклатура, Количество),"1 Возр Автоупорядочивание")
Упорядочить(ТаблицаЗначений(Номенклатура КАК Товар, Количество),"Товар Убыв")

СоединитьСтроки

Функция выполняет соединение срок, переданных в качестве массива или таблицы значений. Втором параметром можно указать символ соединения строк, массива или таблицы (по умолчанию используется символ перевода строки). Если в качестве первого параметра используется таблица значений, в третьем параметре можно указать символ соединения колонок таблицы (по умолчанию используется символ «;»). Результатом выполнения функции является строка.

Примеры использования функций:

СоединитьСтроки(Массив(Товар), ";")
СоединитьСтроки(ТаблицаЗначений(Товар, КоличествоОборот), ";", ",")

ГрупповаяОбработка

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

Синтаксис функции следующий:

ГрупповаяОбработка(Выражения, ВыраженияИерархии, ИмяГруппировки)

Выражения – поля или выражения, которые требуется обработать и поместить в результирующие данные.

ВыраженияИерархии – поля или выражения, которые требуется обработать для иерархических записей (если обработка выполняется для иерархии). Если параметр не указан, используются такие же поля, которые указаны в параметре «Выражения».

ИмяГруппировки – Имя группировки, по строкам которой необходимо выполнить расчет. Если параметр не указан, вычисление выполняется по строкам на уровне текущей группировки. Если указанная группировка не доступна на текущем уровне (группировка более низкого уровня), функция вернет значение NULL.

Результат групповой обработки возвращается в виде объекта с типом «ДанныеГрупповойОбработкиКомпоновкиДанных». Данные групповой обработки можно передать в качестве параметра в экспортную функцию общего модуля

Объект содержит следующие реквизиты:

• Данные – результирующая таблица значений. Колонками таблицы являются поля или результаты выражений, перечисленные в первом параметре функции «ГрупповаяОбработка».
• ТекущийЭлемент – строка таблицы значений соответствующая текущему элементу группировки.
• ВременныеДанныеОбработки – структура в которую можно поместить произвольные параметры в процессе работы с результатом групповой обработки в функции общего модуля. Эти параметры будут доступны при следующей обработке групповых данных в рамках этой же группировки.

Пример использования функции

Создадим набор данных с простым запросом:

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

Создадим вычисляемое поле «ГрупповаяОбработка», определим его как ресурс и укажем следующее выражение:

СКДСервер.ФункцияОбщегоМодуля(ГрупповаяОбработка ("СУММА(КоличествоОборот) КАК Количество"))

В конфигурации, в общем серверном модуле «СКДСервер» разместим экспортную процедуру:

Функция ФункцияОбщегоМодуля(Данные) Экспорт

ВременныеДанные = Данные.ВременныеДанныеОбработки;
Если ВременныеДанные.Свойство("СреднееКоличество") Тогда
СреднееКоличество = ВременныеДанные.СреднееКоличество;
Иначе
ОбщееКоличество = 0;
Для каждого СтрокаДанных Из Данные.Данные Цикл
ОбщееКоличество = ОбщееКоличество + СтрокаДанных.Количество;
КонецЦикла;
СреднееКоличество = Окр(ОбщееКоличество / Данные.Данные.Количество());
ВременныеДанные.Вставить("СреднееКоличество", СреднееКоличество);
КонецЕсли;

Если Данные.ТекущийЭлемент <> Неопределено Тогда
ОтклонениеОтСреднего = СреднееКоличество - Данные.ТекущийЭлемент.Количество;
Иначе
ОтклонениеОтСреднего = 0;
КонецЕсли;

Возврат ОтклонениеОтСреднего;

КонецФункции

Функция рассчитывает отклонение значения в текущей группировке от среднего значения по всем группировкам.

Проверяется наличие параметра «СреднееКоличество» в структуре «ВременныеДанныеОбработки». Если параметр существует, используется он, так как среднее количество в рамках одной и той же группировки будет одинаковым. Если параметра не существует (выводится первая строка группировки), выполняется его расчет по таблицы значений «Данные». Полученное значение добавляется в структуру «ВременныеДанныеОбработки».

На заметку. Имейте в виду про следующую особенность. Функция общего модуля, указанная для ресурса, вызывается дважды для одной и той же строки. Это связано с типовой обработкой расшифровки для поля – ресурса. Если для данного поля создать собственный макет, с указанием одного и того же выражения для основного параметра и для параметра расшифровки, такой особенности не будет. Функция будет вызвана один раз для каждой строки.

Каждый

Функция вычисляет выражения для всех строк и возвращает значение «Истина», если выражения по всем строкам вернули значение «Истина». В противном случае, будет возвращено значение «Ложь».

Каждый(КоличествоОборот > 10)

Любой

Функция вычисляет выражения для всех строк и возвращает значение «Истина», если хотя бы по одной строке результат будет «Истина». В противном случае, будет возвращено значение «Ложь».

Любой(КоличествоОборот > 10)

МестоВПорядке

Функция определяет позицию данной строки, если расположить все строки группировки в порядке, указанном в выражении первого параметра. Также как и для функции «ГрупповаяОработка», используются строки, которые находятся на одном уровне с обрабатываемой или на одном уровне иерархии (не вложенные строки).

Строки с одинаковым значением порядка будут иметь одно и то же место.

Синтаксис функции следующий:

МестоВПорядке(Порядок, ПорядокИерархии, ИмяГруппировки)

Порядок – выражение порядка, по которому происходит сортировка строк. Можно указать направление упорядочивания (Возр / Убыв) и признак «Автоупорядочивание».

ПорядокИерархия – выражение упорядочивания для иерархических записей. Если не указано, действует выражение для основных записей.

ИмяГруппировки – группировка, в контексте которой необходимо выполнить упорядочивание. Если параметр не указан, порядок вычисляется в контексте текущей группировки.

Примеры использования:

МестоВПорядке("СУММА(КоличествоОборот)")
МестоВПорядке("Номенклатура Возр Автоупорядочивание")
МестоВПорядке("СУММА(КоличествоОборот) ", СУММА("СуммаОборот"))

Для обычных строк группировки действует сортировка по полю «КоличествоОборот», для иерархических – «СуммаОборот»:

МестоВПорядке("Сумма(КоличествоОборот)",, "Покупатель")

Для всех вложенных группировок место в порядке будет рассчитано по группировкам верхнего уровня «Покупатель»:

КлассификацияABC

Функция вычисляет значение классификации «ABC» по переданному выражению и пороговым значениям.

Синтаксис функции следующий:

КлассификацияABC(Значение, КоличествоГрупп, ПроцентыДляГрупп, ИмяГруппировки)

Значение – поле или выражение, для которого требуется рассчитать классификацию ABC.

КоличествоГрупп – количество групп классификации. Можно указать количество больше трех, тогда уже будет классификация «ABCD», «ABCDE» и т.д.

ПроцентыДляГрупп – пороги для попадания в группы через запятую в виде строки. Причем для последней группы порог не указывается. Диапазон последней группы считается от последнего порога до 100%. Пример указания порогов для трех групп: «50, 80».

ИмяГруппировки — группировка, в контексте которой необходимо выполнить расчет. Если параметр не указан, вычисление происходит в контексте текущей группировки.

Примеры использования:

КлассификацияABC("Сумма(КоличествоОборот)", 4, "40, 70, 90")

КлассификацияABC("Сумма(КоличествоОборот)", 3, "50, 80", "Покупатель")

Классификация вложенных строк выполняется по строкам группировки верхнего уровня «Покупатель»:

В СКД существует еще целый набор математических агрегатных функций:

• СтандартноеОтклонениеГенеральнойСовокупности
• СтандартноеОтклонениеВыборки
• ДисперсияВыборки
• ДисперсияГенеральнойСовокупности
• КовариацияГенеральнойСовокупности
• КовариацияВыборки
• Корреляция
• РегрессияНаклон
• РегрессияОтрезок
• РегрессияКоличество
• РегрессияR2
• РегрессияСреднееX
• РегрессияСреднееY
• РегрессияSXX
• РегрессияSYY
• РегрессияSXY

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

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

Для ознакомления с предыдущими статьями можно перейти по ссылкам ниже:

• Основные понятия и элементы схемы компоновки
• Настройки компоновки данных
• Пользовательские настройки
• Расширения языка запросов для СКД
• Наборы данных и связи между ними, собственная иерархия
• Внутренние функции СКД, примеры использования