Почему неправильно считаются вычисляемые поля в СКД и как победить магию NULL

← На главную

Каждый разработчик 1С рано или поздно сталкивается с "мистическим" поведением Системы компоновки данных (СКД). Вроде бы пишем простую и логичную формулу, но в результате получаем пустые строки, задвоенные итоги или странные расчеты в группировках. Особенно ярко эти проблемы проявляются при работе с вычисляемыми полями, левыми соединениями и условными операторами.

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

---

Ситуация №1: Куда исчезают строки при вычитании? (Эффект поглощения NULL)

Рассмотрим первый сценарий, с которым столкнулся автор темы. В вычисляемом поле была прописана следующая формула:

ОстатокНаСкладе - (НормаРасхода - КоличествоОборот)

В чем проблема: там, где поле КоличествоОборот заполнено (больше 0), расчет идет правильно. Но в строках, где оборотов не было, все вычисляемое поле становится абсолютно пустым. Почему так происходит?

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

Давайте проанализируем по шагам, что происходит в строке без оборотов:

Вычисляется разность в скобках: НормаРасхода - NULL. Результат операции равен NULL.
Вычисляется итоговая разность: ОстатокНаСкладе - NULL. Результат снова равен NULL.

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

---

Ситуация №2: Коварство функции ЕСТЬNULL и задвоение остатков

Пытаясь исправить ситуацию с пустыми значениями, разработчик логично решает заменить потенциальный NULL на ноль. Он переписывает формулу так:

ОстатокНаСкладе - (НормаРасхода - ЕСТЬNULL(КоличествоОборот, 0))

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

Давайте разберем по шагам эту неочевидную ловушку СКД. Проблема кроется в детализации записей и задвоении остатков при левом соединении:

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

В первом варианте (без ЕСТЬNULL) строки без оборотов превращались в NULL, и СКД при суммировании их просто игнорировала. Из-за этого результат казался "правильным" (на самом деле это было случайное совпадение условий). Как только мы заменили NULL на 0, эти строки стали полноценно участвовать в сложении ресурсов, что привело к колоссальному искажению итогов.

---

Ситуация №3: Почему ветка ИНАЧЕ влияет на результат при истинном условии?

Автор темы зафиксировал еще один странный "прикол" СКД. Два условных выражения выдают разный результат при гарантированно истинном условии КоличествоОборот > 0:

Вариант выражения №1:


ВЫБОР
    КОГДА КоличествоОборот > 0
        ТОГДА ОстатокНаСкладе - (НормаРасхода - КоличествоОборот)
    ИНАЧЕ ОстатокНаСкладе - (НормаРасхода - ЕСТЬNULL(КоличествоОборот, 0))
КОНЕЦ

Вариант выражения №2:


ВЫБОР
    КОГДА КоличествоОборот > 0
        ТОГДА ОстатокНаСкладе - (НормаРасхода - КоличествоОборот)
    ИНАЧЕ 1
КОНЕЦ

Почему при КоличествоОборот > 0 первый вариант считает неверно, а второй — верно? Казалось бы, ветка ИНАЧЕ вообще не должна выполняться!

Здесь срабатывают сразу две особенности СКД:

1. Отсутствие "ленивых вычислений" (Short-circuit evaluation)

В отличие от классических языков программирования, вычислитель выражений СКД (особенно при подготовке промежуточного кэша или анализе типов данных на сервере приложений) часто вычисляет обе ветки оператора ВЫБОР. Если в ветке ИНАЧЕ содержатся сложные математические выражения с полями, которые могут содержать NULL, или происходит неявное приведение типов, результат всей функции может исказиться еще до финального применения логического условия КОГДА.

2. Расчет на уровне группировок

Если вычисляемое поле выводится в строках группировок (а не только в детальных записях), но при этом для него не задана явная формула расчета на вкладке "Ресурсы", СКД пытается вычислить выражение ВЫБОР непосредственно над уже сгруппированными данными:

На уровне группировки простое (не агрегированное) поле КоличествоОборот превращается в NULL (так как СКД не знает, какую именно детальную запись брать для расчета).
Из-за этого условие КоличествоОборот > 0 на уровне группировки выдает Ложь.
СКД проваливается в ветку ИНАЧЕ.
Если в ветке ИНАЧЕ во втором варианте стоит жесткая единица 1, группировка выводит её. А если в первом варианте там стоит сложная формула с неагрегированными полями, СКД получает кашу из NULL-значений и выдает неверный итог по группировке.

---

Как правильно решать такие задачи в СКД: три золотых правила

Чтобы избежать подобных проблем, спроектируем расчет показателей по правильной методологии СКД. Разберем по шагам, как нужно действовать.

Шаг 1: Разделите вычисляемые поля и ресурсы

Никогда не производите арифметические действия над полями разных типов (особенно если замешаны остатки и обороты) на уровне детальных записей в "Вычисляемых полях". В "Вычисляемых полях" мы должны декларировать только базовые значения.

Шаг 2: Перенесите всю логику расчетов на вкладку "Ресурсы"

Все формулы, разности, условия ВЫБОР и обработки ЕСТЬNULL должны жить на вкладке Ресурсы. Именно там СКД производит расчет на уровне группировок и общих итогов, зная правила агрегации каждого отдельного поля.

Шаг 3: Оберните каждое поле в агрегатную функцию

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

Рассмотрим пример правильной настройки ресурса для нашего вычисляемого поля (назовем его ПлановыйОстаток):

На вкладке Ресурсы для поля ПлановыйОстаток в колонке "Выражение" напишем:


ЕСТЬNULL(Максимум(ОстатокНаСкладе), 0) - (ЕСТЬNULL(Сумма(НормаРасхода), 0) - ЕСТЬNULL(Сумма(КоличествоОборот), 0))

Почему это будет работать правильно:

Максимум(ОстатокНаСкладе) — так как остаток при левом соединении продублировался, функция Максимум (или Среднее) вернет нам истинное значение остатка для этой номенклатуры, не суммируя дубли. Если у вас настроена роль поля "Остатки", СКД сама корректно рассчитает остаток по периодам.
Сумма(КоличествоОборот) — корректно соберет все детальные движения по оборотам.
ЕСТЬNULL(..., 0) — применяется индивидуально к результату агрегатных функций. Сначала СУБД безопасно сгруппирует данные, а уже потом, если итог по группировке окажется пустым, заменит его на 0. Это полностью исключает поглощение данных значением NULL.

Соблюдение этих правил гарантирует, что ваши отчеты в СКД всегда будут выдавать точные, предсказуемые и математически верные результаты при любых комбинациях исходных данных!