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