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