При разработке отчетов на базе Системы компоновки данных (СКД) программисты часто сталкиваются с задачей расчета средних показателей за период. Типичный пример: нужно получить среднее количество продаж товара в месяц. Однако стандартная агрегатная функция Среднее() в ресурсах СКД работает по принципу математического среднего только для существующих записей. Если в каком-то месяце продаж не было, записи в базе данных отсутствуют, и СКД просто исключает этот месяц из знаменателя. В итоге результат получается завышенным, так как общая сумма делится не на все месяцы выбранного периода, а только на "активные".
В этой статье мы разберем, почему так происходит, и изучим несколько способов решения проблемы: от настройки ролей полей до создания вспомогательных временных таблиц в запросе.
Проанализируем ситуацию: допустим, мы формируем отчет за год (12 месяцев). По определенной номенклатуре продажи были только в 3 месяцах. Суммарно продано 120 единиц. С точки зрения бизнеса, среднее в месяц должно быть 10 (120 / 12). Однако СКД, выполняя расчет Среднее(КоличествоОборот), увидит только 3 записи и выдаст результат 40 (120 / 3).
Это происходит потому, что в виртуальных таблицах оборотов (например, РегистрНакопления.Продажи.Обороты) физически нет строк для тех периодов, где не было движений. Чтобы получить корректное среднее, нам нужно принудительно "подставить" нули в те месяцы, где продаж не зафиксировано.
Самый простой путь, не требующий сложного переписывания запроса, — это использование настроек компоновки. СКД умеет самостоятельно генерировать недостающие строки периодов.
ПериодМесяц), в колонке Роль нажмите на кнопку редактирования.Месяц) и обязательно укажите Дополнительный период.После этих манипуляций СКД выведет все месяцы в интервале отчета. Однако будьте осторожны: расчет среднего в итогах всё равно может игнорировать эти дополненные строки, если формула ресурса не учитывает количество всех периодов в интервале.
Если нам нужно получить среднее значение в общих итогах (по всей номенклатуре), мы можем изменить саму формулу расчета ресурса. Вместо стандартного Среднее(Количество) мы разделим общую сумму на количество месяцев в выбранном пользователем периоде.
Пример формулы в поле Ресурс:
Сумма(КоличествоОборот) / (РазностьДат(&НачалоПериода, &КонецПериода, "МЕСЯЦ") + 1)
Этот метод надежен, так как знаменатель всегда постоянен и соответствует настройкам отчета, независимо от наличия данных в конкретных месяцах.
Рассмотрим более сложный, но максимально гибкий метод, который применил автор обсуждения. Суть заключается в создании временной таблицы, содержащей все возможные комбинации "Месяц + Номенклатура", и последующем левом соединении к ней реальных данных из регистра.
Проанализируем шаги построения такого запроса. Сначала мы генерируем последовательность дат (так называемая "числомешалка"). Это позволяет получить список всех месяцев в интервале без обращения к реальным таблицам БД.
ВЫБРАТЬ
НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&НачПериода, ДЕНЬ, aa.a * 1000 + bb.b * 100 + cc.c * 10 + dd.d), МЕСЯЦ) КАК ПериодМесяц
ПОМЕСТИТЬ ПродажиПериод
ИЗ
(ВЫБРАТЬ 0 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ... ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК aa
ПОЛНОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ 0 КАК b ... ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК bb ПО (ИСТИНА)
...
ГДЕ
ДОБАВИТЬКДАТЕ(&НачПериода, ДЕНЬ, aa.a * 1000 + bb.b * 100 + cc.c * 10 + dd.d) <= &КонПериода
СГРУППИРОВАТЬ ПО
НАЧАЛОПЕРИОДА(...)
Далее мы создаем таблицу уникальной номенклатуры, которая участвовала в продажах (или вообще всей имеющейся в справочнике, если это необходимо по условию задачи).
ВЫБРАТЬ
ПродажиОбороты.Номенклатура
ПОМЕСТИТЬ ТаблицаНоменклатура
ИЗ
РегистрНакопления.Продажи.Обороты(&НачПериода, &КонПериода, , ) КАК ПродажиОбороты
СГРУППИРОВАТЬ ПО
ПродажиОбороты.Номенклатура
Теперь самое важное: мы соединяем таблицу периодов с таблицей номенклатуры без условия (cross join). Это создает "сетку", где для каждого товара есть строка на каждый месяц периода отчета.
ВЫБРАТЬ
ПродажиПериод.ПериодМесяц,
ТаблицаНоменклатура.Номенклатура
ПОМЕСТИТЬ ТаблицаПериодИНоменклатура
ИЗ
ПродажиПериод КАК ПродажиПериод
ЛЕВОЕ СОЕДИНЕНИЕ ТаблицаНоменклатура КАК ТаблицаНоменклатура ПО (ИСТИНА)
На финальном этапе мы берем этот "каркас" и левым соединением подтягиваем реальные обороты. Крайне важно использовать функцию ЕСТЬNULL, чтобы превратить пустые значения в полноценные нули, которые СКД сможет корректно обработать при расчете среднего.
ВЫБРАТЬ
Сетка.ПериодМесяц,
Сетка.Номенклатура,
ЕСТЬNULL(РеальныеПродажи.КоличествоОборот, 0) КАК Количество
ИЗ
ТаблицаПериодИНоменклатура КАК Сетка
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты(&НачПериода, &КонПериода, Авто, ) КАК РеальныеПродажи
ПО Сетка.ПериодМесяц = РеальныеПродажи.ПериодМесяц
И Сетка.Номенклатура = РеальныеПродажи.Номенклатура
Выясним причину, по которой у автора темы в сообщении №6 "исчезали" строки при левом соединении. Это классическая особенность работы СКД. Если в запросе используется ЛЕВОЕ СОЕДИНЕНИЕ, но в настройках СКД (выбранных полях или отборах) используются поля из правой (присоединяемой) таблицы, система может оптимизировать запрос и превратить соединение во внутреннее (INNER JOIN).
Чтобы этого избежать, придерживайтесь следующих правил:
ЕСТЬNULL(..., 0) прямо в тексте запроса.Номенклатура.Артикул), замените их на обращение через левую (ведущую) таблицу.Рассмотренный метод с формированием временной таблицы-сетки является наиболее универсальным. Он позволяет не только правильно считать среднее, но и выводить в отчет товары, по которым вообще не было движений за выбранный период (например, для анализа неликвидов), что часто требуется в управленческом учете.