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