В процессе разработки в среде 1С:Предприятие 8 часто возникает задача контроля полноты данных. Одной из самых распространенных является поиск позиций Номенклатуры, для которых по какой-то причине не была установлена цена (например, забыли заполнить документ «Установка цен номенклатуры»). Сложность заключается в том, что обычный запрос к регистру сведений ЦеныНоменклатуры покажет только те товары, по которым записи есть, а нам нужно найти те, по которым записей нет.
В этой статье мы подробно разберем, как правильно составить запрос, чтобы выявить «пробелы» в ценообразовании, рассмотрим типичные ошибки начинающих разработчиков и оптимизируем решение для работы на больших базах данных.
Разберем ситуацию, с которой столкнулся автор темы. Если мы делаем выборку напрямую из виртуальной таблицы СрезПоследних регистра сведений ЦеныНоменклатуры, система возвращает нам только существующие записи. Если для товара «Ботинок 42 размера» цена типа «Оптовая» никогда не назначалась, то в регистре просто не будет такой строки. Следовательно, запрос ее не «увидит».
Рассмотрим попытку автора: использование ЛЕВОЕ СОЕДИНЕНИЕ справочника типов цен с временной таблицей цен. Проблема здесь в том, что такое соединение покажет все типы цен, но не привяжет их к каждой конкретной номенклатуре. Нам же нужно получить матрицу «Товар — Тип цены» и проверить наличие данных в каждой ячейке этой матрицы.
Если задача стоит максимально просто — найти товары, у которых вообще нет цены определенного вида (без привязки к сложным таблицам), можно использовать конструкцию с вложенным запросом. Проанализируем пример:
ВЫБРАТЬ
Номенклатура.Ссылка КАК Ссылка
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
НЕ Номенклатура.ЭтоГруппа
И НЕ Номенклатура.ПометкаУдаления
И НЕ Номенклатура.Ссылка В
(ВЫБРАТЬ
ЦеныНоменклатурыСрезПоследних.Номенклатура
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, ВидЦены = &ВидЦены) КАК ЦеныНоменклатурыСрезПоследних)
В данном примере мы выбираем все элементы справочника Номенклатура и исключаем из них те, которые «засветились» в регистре сведений на указанную дату по конкретному виду цены. Это быстрое решение, но оно не позволяет вывести список всех отсутствующих цен, если их должно быть несколько для каждого товара.
Для полноценного анализа, когда нам нужно увидеть отсутствие цен в разрезе каждого типа цен для каждой позиции номенклатуры, необходимо использовать концепцию декартова произведения. Поскольку в языке запросов 1С нет явного оператора CROSS JOIN, мы реализуем его через соединение таблиц без условия.
Разберем этот процесс по шагам:
Номенклатура и соединяем его со справочником ТипыЦенНоменклатуры. В результате получаем таблицу, где для каждого товара созданы строки со всеми существующими типами цен.Номенклатура и ТипЦен.NULL. Это и будут наши искомые позиции.Проанализируем пример кода для этого решения:
// Шаг 1: Формируем все возможные пары Товар-ТипЦены
ВЫБРАТЬ
СпрНоменклатура.Ссылка КАК Номенклатура,
СпрТипыЦен.Ссылка КАК ТипЦены
ПОМЕСТИТЬ ВТ_Сетка
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ТипыЦенНоменклатуры КАК СпрТипыЦен
ПО (ИСТИНА) // Декартово произведение
ГДЕ
НЕ СпрНоменклатура.ЭтоГруппа
И НЕ СпрНоменклатура.ПометкаУдаления;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Ищем отсутствие данных в регистре
ВЫБРАТЬ
ВТ_Сетка.Номенклатура,
ВТ_Сетка.ТипЦены
ИЗ
ВТ_Сетка КАК ВТ_Сетка
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, ) КАК ЦеныСрез
ПО ВТ_Сетка.Номенклатура = ЦеныСрез.Номенклатура
И ВТ_Сетка.ТипЦены = ЦеныСрез.ТипЦен
ГДЕ
ЦеныСрез.Цена ЕСТЬ NULL
При работе с подобными запросами важно учитывать несколько критических моментов, чтобы не перегрузить сервер базы данных и получить корректный результат:
1. Исключение групп и помеченных на удаление позиций. В справочнике Номенклатура всегда есть папки (группы), для которых цены не устанавливаются. Если их не исключить условием ГДЕ Номенклатура.ЭтоГруппа = ЛОЖЬ, ваш отчет будет забит бесполезной информацией. Также важно проверять ПометкаУдаления, чтобы не анализировать неактуальные товары.
2. Обработка нулевых цен. Ситуация, когда запись в регистре существует, но цена в ней равна 0, технически считается «установленной ценой», но по факту это ошибка данных. Чтобы найти такие случаи, дополним условие фильтрации:
ГДЕ (ЦеныСрез.Цена ЕСТЬ NULL ИЛИ ЦеныСрез.Цена = 0)
3. Производительность декартова произведения. Выясним причину, по которой CROSS JOIN может «повесить» систему. Если у вас 50 000 товаров и 10 типов цен, временная таблица будет содержать 500 000 записей. Это приемлемо. Однако если товаров 500 000, а типов цен 100, таблица в 50 миллионов строк может создать проблемы. В таких случаях рекомендуется ограничивать выборку номенклатуры (например, только по конкретной папке или только по товарам, которые есть в наличии на складах).
4. Использование виртуальной таблицы СрезПоследних. Рассмотрим подробнее параметры этой таблицы. Если мы не укажем дату в параметре &Период, 1С возьмет срез на текущий момент. Однако при левом соединении очень важно, чтобы фильтрация по виду цены (если она нужна) происходила внутри параметров виртуальной таблицы, а не в секции ГДЕ основного запроса. Это значительно ускоряет выполнение, так как СУБД обрабатывает меньший объем данных.
Проанализируем ситуацию с точки зрения производительности SQL-сервера. Платформа 1С эффективнее обрабатывает запросы, в которых данные предварительно помещаются во Временные таблицы. Это позволяет оптимизатору построить точный план выполнения. Использование сложных вложенных запросов внутри секции ЛЕВОЕ СОЕДИНЕНИЕ часто приводит к тому, что индекс не используется, и система переходит к полному сканированию таблицы (Table Scan), что крайне медленно.
Поэтому правильным подходом будет:
Выполнение этих шагов гарантирует стабильную работу даже в высоконагруженных системах с миллионами записей в регистрах сведений.