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