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