При работе с запросами в системе «1С:Предприятие» разработчики часто сталкиваются с ситуацией, когда агрегатная функция СРЕДНЕЕ в иерархических итогах или общих итогах выдает результат, который не совпадает с ожиданиями пользователя. Особенно остро эта проблема проявляется, когда отчет строится без использования Системы компоновки данных (СКД), где механизмы расчета итогов более гибкие. В этой статье мы подробно разберем, почему так происходит, и рассмотрим проверенные способы получения корректных данных.
Для начала проанализируем ситуацию. Основная ошибка заключается в ожидании того, что система на уровне итогов будет вычислять «среднее от средних». Однако язык запросов 1С работает иначе. При использовании конструкции ИТОГИ СРЕДНЕЕ(Поле) система рассчитывает среднее значение по всем детальным записям, которые входят в эту группировку, а не на основании уже рассчитанных средних значений в подгруппах.
Рассмотрим конкретный пример для наглядности:
(10 + 20) / 2 = 15).(15 + 60) / 2 = 37,5.(10 + 20 + 60) / 3 = 30.С точки зрения математики и выборки данных из базы, запрос 1С абсолютно прав — он берет среднее по всей совокупности строк. Но с точки зрения бизнес-логики отчета пользователю часто требуется именно усреднение показателей по категориям. Давайте разберем, как реализовать правильный расчет.
Если нам нужно заставить систему считать «среднее от средних», мы должны разбить расчет на два этапа. Сначала мы подготовим промежуточные усредненные данные во временной таблице, а затем вторым запросом рассчитаем итог по ним.
Проанализируем пример кода для такого решения:
// Этап 1: Получаем средние значения по каждой группе и сохраняем во временную таблицу
ВЫБРАТЬ
Продажи.Номенклатура.Родитель КАК Категория,
СРЕДНЕЕ(Продажи.Цена) КАК СредняяЦенаПоКатегории
ПОМЕСТИТЬ ВТ_СредниеПоКатегориям
ИЗ
РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Номенклатура.Родитель;
// Этап 2: Считаем среднее значение уже на основании подготовленных данных
ВЫБРАТЬ
СРЕДНЕЕ(ВТ_СредниеПоКатегориям.СредняяЦенаПоКатегории) КАК ИтоговоеСреднееОтСредних
ИЗ
ВТ_СредниеПоКатегориям КАК ВТ_СредниеПоКатегориям
Таким образом, на втором этапе система не видит детальных записей (те самые 10, 20 и 60 из примера выше). Она видит только значения 15 и 60, и их среднее значение будет рассчитано как 37,5. Это самый надежный способ получить нужный результат средствами чистого SQL-запроса в 1С.
Иногда использование функции СРЕДНЕЕ в самом запросе вообще нецелесообразно. Гораздо больше контроля дает расчет среднего значения «вручную». Для этого в запросе мы получаем общую сумму и общее количество записей, а деление производим уже при выводе результата.
Рассмотрим, как это выглядит в структуре запроса:
ВЫБРАТЬ
Продажи.Подразделение КАК Подразделение,
СУММА(Продажи.Сумма) КАК СуммаОборот,
КОЛИЧЕСТВО(Продажи.Ссылка) КАК КоличествоЗаписей
ИЗ
РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Подразделение
ИТОГИ
СУММА(СуммаОборот),
СУММА(КоличествоЗаписей)
ПО
ОБЩИЕ,
Подразделение
После выполнения такого запроса мы обходим выборку в коде 1С. Посмотрим на алгоритм вывода:
Выборка = РезультатЗапроса.Выбрать();
Пока Выборка.Следующий() Цикл
// Проверяем, что количество не равно нулю во избежание ошибки
СреднееЗначение = 0;
Если Выборка.КоличествоЗаписей <> 0 Тогда
СреднееЗначение = Выборка.СуммаОборот / Выборка.КоличествоЗаписей;
КонецЕсли;
// Выводим данные в отчет или таблицу
Сообщить("Подразделение: " + Выборка.Подразделение + " Среднее: " + СреднееЗначение);
КонецЦикла;
Этот метод хорош тем, что вы можете программно управлять логикой усреднения на каждом уровне группировки.
Если в запросе используются сложные итоги (ИТОГИ ... ПО ...), вы можете перехватывать момент вывода итоговой строки и подменять в ней значение среднего. Для этого используется проверка типа записи.
Разберем пример реализации:
ВыборкаПоГруппам = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);
Пока ВыборкаПоГруппам.Следующий() Цикл
// Если это итоговая строка по группировке или общий итог
Если ВыборкаПоГруппам.ТипЗаписи() = ТипЗаписиЗапроса.ИтогПоГруппировке
ИЛИ ВыборкаПоГруппам.ТипЗаписи() = ТипЗаписиЗапроса.ОбщийИтог Тогда
// Здесь мы можем реализовать любую сложную логику пересчета
// Например, получить данные из вложенной выборки и усреднить их
ВыборкаДетальные = ВыборкаПоГруппам.Выбрать();
СуммаСредних = 0;
Счетчик = 0;
Пока ВыборкаДетальные.Следующий() Цикл
СуммаСредних = СуммаСредних + ВыборкаДетальные.СреднееЗначение;
Счетчик = Счетчик + 1;
КонецЦикла;
ИтоговоеПоказатель = ?(Счетчик = 0, 0, СуммаСредних / Счетчик);
Иначе
ИтоговоеПоказатель = ВыборкаПоГруппам.СреднееЗначение;
КонецЕсли;
КонецЦикла;
Часто под «неправильным средним» пользователи подразумевают отсутствие учета веса записей. Например, средняя цена товара должна считаться не просто как (Цена1 + Цена2) / 2, а как (Цена1 * Колво1 + Цена2 * Колво2) / (Колво1 + Колво2).
Запомним правило: Стандартная функция СРЕДНЕЕ в языке запросов никогда не считает средневзвешенное значение автоматически. Если вам нужно средневзвешенное (например, средняя цена закупки или средняя маржа), всегда используйте схему:
Сумма * Количество КАК Произведение.ИТОГИ СУММА(Произведение), СУММА(Количество).Мы выяснили, что «неправильное» среднее в запросе 1С — это не ошибка платформы, а ее архитектурная особенность. Чтобы получить ожидаемый результат в отчетах без СКД, необходимо либо предварительно группировать данные во временных таблицах, либо выполнять финальный расчет среднего значения программно при обходе результата запроса.
Использование метода с временными таблицами является наиболее предпочтительным с точки зрения производительности и чистоты кода, так как позволяет получить готовый результат одним запросом без лишних циклов в коде 1С.