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