При разработке отчетов в Системе компоновки данных (СКД) программисты часто сталкиваются с ситуацией, когда формулы в вычисляемых полях начинают возвращать некорректные результаты или игнорировать условия. Особенно ярко эта проблема проявляется в запросах, использующих объединение таблиц (ОБЪЕДИНИТЬ ВСЕ или UNION ALL). Рассмотрим детально, почему возникает эта ошибка, проанализируем физический смысл расчетов СКД и разберем, как решить проблему правильно и профессионально.
Рассмотрим классический пример построения отчета. Нам необходимо получить данные по остаткам, поступлениям и продажам товаров в одном отчете, а также рассчитать относительный показатель — процент продаж относительно поступлений. Для этого пишется запрос, объединяющий несколько таблиц: обороты по поступлениям, обороты по продажам, списания и остатки. Поля, которые отсутствуют в конкретной объединяемой таблице, заполняются значениями NULL или 0.
Пример упрощенного исходного запроса:
ВЫБРАТЬ
ТоварыНаСкладахОбороты.Склад,
ТоварыНаСкладахОбороты.Номенклатура,
ТоварыНаСкладахОбороты.ХарактеристикаНоменклатуры,
ЕСТЬNULL(ТоварыНаСкладахОбороты.КоличествоПриход, 0) КАК Поступление,
0 КАК Продажи
ИЗ
РегистрНакопления.ТоварыНаСкладах.Обороты(&НачалоПериода, &КонецПериода, Авто, ) КАК ТоварыНаСкладахОбороты
ГДЕ
ТИПЗНАЧЕНИЯ(ТоварыНаСкладахОбороты.Регистратор) = ТИП(Документ.ПоступлениеТоваров)
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ПродажиОбороты.Склад,
ПродажиОбороты.Номенклатура,
ПродажиОбороты.ХарактеристикаНоменклатуры,
0,
ПродажиОбороты.КоличествоОборот
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Период, ) КАК ПродажиОбороты
Для расчета процента продаж разработчик создает вычисляемое поле на вкладке «Вычисляемые поля» со следующим выражением:
ВЫБОР КОГДА Поступление <> 0 ТОГДА Продажи / Поступление * 100 ИНАЧЕ Продажи * 100 КОНЕЦ
Проблема: даже когда в итоговом отчете есть заполненные значения поступления, система все равно рассчитывает значение по ветке ИНАЧЕ (то есть просто умножает продажи на 100, выдавая гигантские бессмысленные цифры вместо реального процента).
Для того чтобы понять, почему вычисляемое поле работает неверно, проанализируем, как именно СКД обрабатывает детальные записи запроса. Операция ОБЪЕДИНИТЬ ВСЕ не связывает строки по горизонтали, она «дописывает» их друг под друга. Это означает, что в результирующей выборке детальных записей у нас никогда в одной строке не будут одновременно заполнены поля Продажи и Поступление.
Давайте посмотрим на условную детальную таблицу, которую получает СКД до выполнения группировки:
Вычисляемое поле в СКД по умолчанию рассчитывается построчно для каждой детальной записи еще до того, как сработают группировки отчета. Проследим за логикой расчета формулы для каждой строки:
Поступление равно 10 (не равно 0). Условие выполняется. Происходит расчет: Продажи / Поступление * 100. Так как продажи в этой строке равны 0, результат равен: 0 / 10 * 100 = 0.Поступление равно 0. Условие не выполняется, вычисление уходит в ветку ИНАЧЕ: Продажи * 100. Результат равен: 2 * 100 = 200.После этого СКД переходит к выводу группировки по номенклатуре "Товар А" и просто складывает (суммирует) результаты расчетов детальных строк: 0 + 200 = 200. Математически верный результат должен быть: 2 / 10 * 100 = 20%. Мы же получили абсолютно некорректные 200%.
Именно из-за построчного вычисления формулы на уровне детальных записей и последующего суммирования результатов группировкой расчет относительных показателей в вычисляемых полях напрямую не работает.
Чтобы решить эту проблему штатным и наиболее производительным способом, необходимо перенести расчет формулы с детального уровня на уровень группировок (ресурсов). Сделать это очень просто. Разберем процесс по шагам.
Шаг 1. Очистка вычисляемого поля.
Перейдите на вкладку «Вычисляемые поля». Найдите ваше поле (например, ПроцентПродаж). Полностью удалите формулу из колонки «Выражение», оставив ее пустой. Само поле удалять не нужно — оно по-прежнему должно быть объявлено в списке.
Шаг 2. Настройка ресурса.
Перейдите на вкладку «Ресурсы». Добавьте поле ПроцентПродаж в список ресурсов. В колонке «Выражение» напишите формулу с использованием агрегатных функций:
ВЫБОР
КОГДА СУММА(Поступление) <> 0
ТОГДА СУММА(Продажи) / СУММА(Поступление) * 100
ИНАЧЕ СУММА(Продажи) * 100
КОНЕЦ
Почему это работает?
Когда мы указываем агрегатные функции (такие как СУММА()) в выражении ресурса, мы приказываем СКД изменить порядок расчетов. Теперь система сначала сгруппирует детальные записи (сложит все продажи и все поступления по каждой номенклатуре), получив по "Товару А" общие итоги: СУММА(Поступление) = 10 и СУММА(Продажи) = 2. Только после этого для сгруппированных данных применится формула: 2 / 10 * 100 = 20%. Результат будет абсолютно верным на любом уровне группировки и в общих итогах.
Часто разработчики, не разобравшись в тонкостях работы ресурсов СКД, пытаются решить проблему "в лоб" на уровне SQL-запроса. Например, связывая виртуальные таблицы оборотов через левое соединение внутри общей структуры объединения. Рассмотрим пример такого неоптимального запроса:
// ... фрагмент объединения ...
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ПродажиОбороты.Склад,
ПродажиОбороты.Номенклатура,
ПродажиОбороты.ХарактеристикаНоменклатуры,
0,
0,
СУММА(ПродажиОбороты.КоличествоОборот),
ТоварыНаСкладахОбороты.КоличествоПриход,
ВЫБОР
КОГДА ТоварыНаСкладахОбороты.КоличествоПриход ЕСТЬ NULL
ТОГДА ПродажиОбороты.КоличествоОборот * 100
ИНАЧЕ ПродажиОбороты.КоличествоОборот / ТоварыНаСкладахОбороты.КоличествоПриход * 100
КОНЕЦ
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Авто, ) КАК ПродажиОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Обороты КАК ТоварыНаСкладахОбороты
ПО ПродажиОбороты.Склад = ТоварыНаСкладахОбороты.Склад
И ПродажиОбороты.Номенклатура = ТоварыНаСкладахОбороты.Номенклатура
Несмотря на то, что такое решение может вернуть визуально правильные данные в отчете, оно имеет критические недостатки и считается **плохой практикой разработки (антипаттерном)** по следующим причинам:
РегистрНакопления...Обороты) без жестких фильтров по индексам заставляет СУБД выполнять тяжелые операции вложенных циклов или хэш-соединений. На реальной базе данных с сотнями тысяч записей этот запрос будет вызывать длительные зависания отчета, транзакционные блокировки и может закончиться аварийной остановкой процесса из-за нехватки памяти (TempDB).Если вы хотите спроектировать этот отчет профессионально и с максимальным быстродействием, воспользуйтесь следующими рекомендациями:
1. Используйте виртуальную таблицу «ОстаткиИОбороты»
Вместо того чтобы вручную объединять через ОБЪЕДИНИТЬ ВСЕ таблицы оборотов и остатков по регистру накопления «Товары на складах», используйте одну виртуальную таблицу РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты. Она за один проход СУБД возвратит начальный остаток, приход (поступление), расход (списание) и конечный остаток по каждой аналитике. Это позволит вам выкинуть из запроса 4 тяжелых подзапроса из 5.
2. Используйте связи наборов данных в СКД
Если вам действительно необходимо объединить информацию из разных физических регистров (например, регистр остатков товаров и регистр продаж), создайте в СКД два отдельных набора данных (запроса): один выбирает данные по товарам, второй — по продажам. Свяжите их на вкладке «Связи наборов данных» по полям Склад, Номенклатура и ХарактеристикаНоменклатуры. Платформа 1С выполнит это соединение на уровне собственного процессора компоновки данных максимально оптимально, не перегружая сервер SQL.
3. Не забывайте про роли полей остатков
При ручном объединении остатков (как в исходном запросе автора) СКД не понимает, что эти поля являются остаточными. В итоге на уровне группировок по времени остатки будут просто суммироваться (складываться за каждый день), что методически неверно. Чтобы остатки рассчитывались правильно, обязательно перейдите на вкладку «Набор данных» и для полей начального и конечного остатка настройте колонку «Роль», указав тип остатка и связав его с соответствующим полем периода.