Рассмотрим часто встречающуюся задачу при разработке на платформе 1С:Предприятие. Допустим, мы создали в запросе вычисляемое поле с помощью конструкции ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ ... КОНЕЦ. В нашем случае это расчет количества дней просрочки с использованием функции РАЗНОСТЬДАТ. Результат вычислений может быть равен нулю, и мы хотим исключить такие строки из итоговой выборки. Однако, открыв вкладку «Условия» в конструкторе запросов, мы обнаруживаем, что созданного нами поля там нет.
Проанализируем ситуацию: платформа 1С не позволяет ссылаться на псевдоним (синоним) вычисляемого поля из секции ВЫБРАТЬ в секции ГДЕ того же самого запроса. Из-за этого разработчики часто прибегают к ошибочным или неоптимальным решениям. Давайте по шагам разберем возможные способы решения этой проблемы, выясним причины ошибок и подберем самый эффективный подход.
Первое, что приходит на ум — просто скопировать всю конструкцию ВЫБОР КОГДА в секцию ГДЕ и добавить условие проверки на неравенство нулю. Посмотрим на пример такого кода:
ГДЕ
ВЫБОР
КОГДА &ДатаОтчета > ВложенныйЗапрос.ДатаОплаты
ТОГДА РАЗНОСТЬДАТ(ВложенныйЗапрос.ДатаОплаты, &ДатаОтчета, ДЕНЬ)
ИНАЧЕ 0
КОНЕЦ <> 0
Технически этот код будет работать и отфильтрует ненужные записи. Однако, мы настоятельно рекомендуем избегать такого подхода. Выясним причину:
РАЗНОСТЬДАТ, ВЫБОР, ГОД и т.д.) в секции ГДЕ делает условие неоптимизируемым. СУБД не сможет использовать индексы по полю даты. Ей придется выполнить полное сканирование таблицы (Index Scan или Table Scan), вычислить разность дат для каждой строки, и только потом отсеять нули. Это приведет к критическому падению производительности на больших объемах данных.СРЕДНЕЕ().Давайте внимательнее проанализируем нашу формулу. Функция РАЗНОСТЬДАТ вернет значение больше нуля только в одном случае: если &ДатаОтчета больше, чем ВложенныйЗапрос.ДатаОплаты. Значит, нам не нужно вычислять саму разницу в днях на этапе фильтрации! Рассмотрим более элегантное и быстрое решение:
ГДЕ
&ДатаОтчета > ВложенныйЗапрос.ДатаОплаты
Заменив громоздкую конструкцию на простое математическое сравнение, мы позволяем СУБД использовать индексы. Запрос отработает моментально, так как база данных сразу отсечет все записи, которые не подходят под условие, еще до расчета дней просрочки.
Даже применив оптимизированное условие, мы можем столкнуться с тем, что в отчет все равно проникают строки с нулевой разницей дней. Выясним причину этого явления. В платформе 1С тип «Дата» почти всегда содержит в себе и время.
Посмотрим на пример: если &ДатаОтчета равна «01.01.2023 15:00:00», а ВложенныйЗапрос.ДатаОплаты равна «01.01.2023 10:00:00», условие &ДатаОтчета > ВложенныйЗапрос.ДатаОплаты будет выполнено (ИСТИНА). Но при этом функция РАЗНОСТЬДАТ(..., ДЕНЬ) посчитает разницу строго по границам дней. Так как обе даты находятся в рамках одних и тех же суток, разница в днях составит 0!
Чтобы избежать таких коллизий, приведем обе даты к началу дня с помощью функции НАЧАЛОПЕРИОДА. Это самый надежный паттерн в 1С для сравнения дат без учета времени:
ГДЕ
НАЧАЛОПЕРИОДА(&ДатаОтчета, ДЕНЬ) > НАЧАЛОПЕРИОДА(ВложенныйЗапрос.ДатаОплаты, ДЕНЬ)
Важно: Функция НАЧАЛОПЕРИОДА в 1С оптимизирована на уровне трансляции в SQL и работает корректнее, чем манипуляции с КОНЕЦПЕРИОДА или КОНЕЦДНЯ().
Если ваша формула гораздо сложнее, чем просто разность дат, и ее логику нельзя свести к простому сравнению, разберем архитектурно правильный подход — использование Временных Таблиц.
Поместим первоначальную выборку со всеми сложными вычислениями во временную таблицу. Назовем вычисляемое поле, например, ДнейПросрочки. Затем в следующем пакете запроса мы обратимся к этой временной таблице и наложим простое условие на уже готовое значение.
// Пакет 1
ВЫБРАТЬ
ВложенныйЗапрос.Ссылка КАК Ссылка,
ВЫБОР
КОГДА НАЧАЛОПЕРИОДА(&ДатаОтчета, ДЕНЬ) > НАЧАЛОПЕРИОДА(ВложенныйЗапрос.ДатаОплаты, ДЕНЬ)
ТОГДА РАЗНОСТЬДАТ(ВложенныйЗапрос.ДатаОплаты, &ДатаОтчета, ДЕНЬ)
ИНАЧЕ 0
КОНЕЦ КАК ДнейПросрочки
ПОМЕСТИТЬ ВТ_Данные
ИЗ
(Вложенный запрос) КАК ВложенныйЗапрос;
// Пакет 2
ВЫБРАТЬ
ВТ_Данные.Ссылка КАК Ссылка,
ВТ_Данные.ДнейПросрочки КАК ДнейПросрочки
ИЗ
ВТ_Данные КАК ВТ_Данные
ГДЕ
ВТ_Данные.ДнейПросрочки > 0
Такой подход делает код легко читаемым, решает проблему с невозможностью обращения к алиасу в секции ГДЕ и позволяет платформе без проблем применять агрегатные функции к полю ДнейПросрочки.
Если наш запрос разрабатывается для отчета на базе Системы Компоновки Данных (СКД), проанализируем еще одно эффективное решение. Не стоит жестко ограничивать данные на уровне запроса. Пусть запрос извлекает все строки и просто вычисляет поле ДнейПросрочки.
Отсев нулей мы перенесем в настройки самой СКД:
ДнейПросрочки и установим условие Больше 0.У этого метода есть масса преимуществ. Пользователь в режиме 1С:Предприятие сможет самостоятельно отключать или изменять этот отбор (например, вывести все документы, где просрочка больше 10 дней). Кроме того, СКД идеально вычисляет итоги (Сумма, Среднее, Максимум) для вычисляемых полей, если ограничение наложено именно в настройках компоновки, а не в жестких условиях пакета.
В завершение разберем еще один важный нюанс. Если данные берутся из вложенного запроса или левого соединения, существует вероятность, что ВложенныйЗапрос.ДатаОплаты окажется пустой (содержать NULL). Попытка выполнить РАЗНОСТЬДАТ или сравнение с NULL приведет к ошибкам или неожиданным результатам.
Обязательно оберните потенциально пустые поля в функцию ЕСТЬNULL перед любыми вычислениями:
ЕСТЬNULL(ВложенныйЗапрос.ДатаОплаты, &ДатаОтчета)
Применив описанные методы, вы не только избавите отчет от лишних нулевых строк, но и обеспечите высокую производительность и отказоустойчивость ваших запросов в 1С.