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