В практике разработки на платформе «1С:Предприятие 8» часто возникает задача консолидации данных из нескольких разнородных источников. Например, когда необходимо в одном отчете свести показатели продаж, закупок и складских остатков по одним и тем же номенклатурным позициям. Начинающие разработчики часто пытаются решить эту задачу «в лоб» — с помощью полного соединения таблиц. Однако при работе с тремя и более источниками такой подход неизбежно приводит к неприятному сюрпризу: данные в результирующей таблице начинают загадочным образом задваиваться или вовсе утрачиваться.
В этой статье мы подробно проанализируем, почему классическое ПОЛНОЕ СОЕДИНЕНИЕ (FULL OUTER JOIN) трех таблиц приводит к логическим ошибкам на уровне СУБД, и разберем эталонное, максимально быстрое решение этой задачи с использованием конструкции ОБЪЕДИНИТЬ ВСЕ и последующей группировки.
Рассмотрим ситуацию, которую описывает автор темы. У нас есть три таблицы: ВТ1, ВТ2 и ВТ3. В каждой из них содержатся уникальные строки по полю Имя и числовой ресурс Количество. Задача — получить единую таблицу ВТ4, где для каждого имени будут выведены три колонки количества из всех трех таблиц.
Автор пытается написать запрос следующим образом:
ВЫБРАТЬ
ВТ1.Имя КАК Имя,
ВТ1.Количество КАК Кол1,
ВТ2.Количество КАК Кол2,
ВТ3.Количество КАК Кол3
ИЗ
ВТ1 КАК ВТ1
ПОЛНОЕ СОЕДИНЕНИЕ ВТ2 КАК ВТ2 ПО ВТ1.Имя = ВТ2.Имя
ПОЛНОЕ СОЕДИНЕНИЕ ВТ3 КАК ВТ3 ПО ВТ1.Имя = ВТ3.Имя
На первый взгляд логика кажется верной. Но давайте посмотрим, что происходит внутри СУБД (MS SQL Server, PostgreSQL и др.) на физическом уровне во время выполнения этого запроса. Представим, что у нас есть имя «Иван», которое присутствует в таблицах ВТ2 и ВТ3, но отсутствует в таблице ВТ1.
ВТ1 «Ивана» нет, СУБД берет его из таблицы ВТ2. В промежуточном наборе данных образуется строка, в которой:
ВТ1.Имя принимает значение NULL (пустая ссылка/значение);ВТ2.Имя принимает значение "Иван".ПО ВТ1.Имя = ВТ3.Имя. Но постойте! На первом шаге поле ВТ1.Имя для нашего «Ивана» уже превратилось в NULL. СУБД пытается сравнить: NULL = "Иван". С точки зрения трехзначной логики SQL это сравнение возвращает ложь (или UNKNOWN).ВТ2 останется с NULL в колонках от таблицы ВТ3.ВТ3, где «Иван» получит NULL в колонках от таблиц ВТ1 и ВТ2.В итоге в финальной выборке для одного и того же «Ивана» мы получаем две разные строки! Последующая группировка по полю ВТ1.Имя ничем не поможет, так как в обеих строках это поле равно NULL. Именно из-за этого физического механизма СУБД данные начинают двоиться и троиться.
Чтобы полное соединение трех таблиц отработало корректно, условие связи каждой последующей таблицы должно учитывать, что ключевое поле могло прийти из любой ранее соединенной таблицы. Для этого нам придется использовать тяжелую конструкцию ЕСТЬNULL в условиях связи:
ВЫБРАТЬ
ЕСТЬNULL(ВТ1.Имя, ЕСТЬNULL(ВТ2.Имя, ВТ3.Имя)) КАК Имя,
ВТ1.Количество КАК Кол1,
ВТ2.Количество КАК Кол2,
ВТ3.Количество КАК Кол3
ИЗ
ВТ1 КАК ВТ1
ПОЛНОЕ СОЕДИНЕНИЕ ВТ2 КАК ВТ2 ПО ВТ1.Имя = ВТ2.Имя
ПОЛНОЕ СОЕДИНЕНИЕ ВТ3 КАК ВТ3 ПО ЕСТЬNULL(ВТ1.Имя, ВТ2.Имя) = ВТ3.Имя
Проанализируем этот код. Если таблиц станет не 3, а 5 или 10, этот запрос превратится в громоздкую, нечитаемую «лесенку» из вложенных функций ЕСТЬNULL. Малейшая ошибка в этой структуре приведет к неверным данным. Более того, такие соединения крайне медленно обрабатываются оптимизатором СУБД.
Опытные разработчики 1С знают: если задачу сопоставления данных можно решить через вертикальное объединение таблиц вместо горизонтального соединения, нужно всегда выбирать объединение. Этот подход регламентирован стандартами разработки фирмы «1С» для оптимизации запросов.
Идея метода заключается в следующем: мы складываем все три таблицы «в стопку» друг под друга с помощью оператора ОБЪЕДИНИТЬ ВСЕ, предварительно выровняв их структуру. Для каждой таблицы мы создаем пустые колонки-заглушки со значением 0 для «чужих» ресурсов. Затем полученный плоский набор данных мы просто сворачиваем с помощью конструкции СГРУППИРОВАТЬ ПО.
Давайте посмотрим, как выглядит этот элегантный и быстродействующий шаблон запроса:
ВЫБРАТЬ
ОбъединенныеДанные.Имя КАК Имя,
СУММА(ОбъединенныеДанные.Кол1) КАК Кол1,
СУММА(ОбъединенныеДанные.Кол2) КАК Кол2,
СУММА(ОбъединенныеДанные.Кол3) КАК Кол3
ПОМЕСТИТЬ ВТ_ИтоговыйРезультат
ИЗ
(ВЫБРАТЬ
ВТ1.Имя КАК Имя,
ВТ1.Количество КАК Кол1,
0 КАК Кол2,
0 КАК Кол3
ИЗ
ВТ1 КАК ВТ1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВТ2.Имя,
0,
ВТ2.Количество,
0
ИЗ
ВТ2 КАК ВТ2
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВТ3.Имя,
0,
0,
ВТ3.Количество
ИЗ
ВТ3 КАК ВТ3) КАК ОбъединенныеДанные
СГРУППИРОВАТЬ ПО
ОбъединенныеДанные.Имя
Выясним причину, по которой этот метод превосходит по производительности любые варианты с JOIN:
ОБЪЕДИНИТЬ ВСЕ не анализирует данные на уникальность и не пытается сопоставлять строки. СУБД просто считывает блоки данных из трех таблиц и последовательно записывает их в один общий поток. Это линейная операция с минимальными затратами процессора.ОБЪЕДИНИТЬ ВСЕ и добавить одну колонку. Запрос останется простым, понятным и быстрым.В исходном сообщении автора темы упоминается, что таблицы ВТ1, ВТ2, ВТ3 были получены как вложенные запросы в одном пакете. С точки зрения стандартов оптимизации 1С, использование вложенных запросов внутри соединений или объединений — это серьезная архитектурная ошибка.
Оптимизатор СУБД не может адекватно оценить количество строк во вложенных запросах до начала их выполнения, из-за чего строит неоптимальные планы запросов. Чтобы этого избежать, всегда предварительно выгружайте результаты промежуточных вычислений во временные таблицы с помощью инструкции ПОМЕСТИТЬ:
// Шаг 1: Получаем данные первого источника во временную таблицу
ВЫБРАТЬ
ТаблицаПродаж.Номенклатура КАК Имя,
СУММА(ТаблицаПродаж.Количество) КАК Количество
ПОМЕСТИТЬ ВТ1
ИЗ
РегистрНакопления.Продажи КАК ТаблицаПродаж
СГРУППИРОВАТЬ ПО
ТаблицаПродаж.Номенклатура
;
// Шаг 2: Получаем данные второго источника
ВЫБРАТЬ
ТаблицаЗакупок.Номенклатура КАК Имя,
СУММА(ТаблицаЗакупок.Количество) КАК Количество
ПОМЕСТИТЬ ВТ2
ИЗ
РегистрНакопления.Закупки КАК ТаблицаЗакупок
СГРУППИРОВАТЬ ПО
ТаблицаЗакупок.Номенклатура
;
// Шаг 3: Получаем данные третьего источника
ВЫБРАТЬ
ТаблицаОстатков.Номенклатура КАК Имя,
СУММА(ТаблицаОстатков.КоличествоОстаток) КАК Количество
ПОМЕСТИТЬ ВТ3
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки КАК ТаблицаОстатков
СГРУППИРОВАТЬ ПО
ТаблицаОстатков.Номенклатура
;
// Шаг 4: Финальное объединение и группировка
ВЫБРАТЬ
ОбъединенныеДанные.Имя КАК Имя,
СУММА(ОбъединенныеДанные.Кол1) КАК Кол1,
СУММА(ОбъединенныеДанные.Кол2) КАК Кол2,
СУММА(ОбъединенныеДанные.Кол3) КАК Кол3
ИЗ
(ВЫБРАТЬ ВТ1.Имя, ВТ1.Количество КАК Кол1, 0 КАК Кол2, 0 КАК Кол3 ИЗ ВТ1 КАК ВТ1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ВТ2.Имя, 0, ВТ2.Количество, 0 ИЗ ВТ2 КАК ВТ2
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ВТ3.Имя, 0, 0, ВТ3.Количество ИЗ ВТ3 КАК ВТ3) КАК ОбъединенныеДанные
СГРУППИРОВАТЬ ПО
ОбъединенныеДанные.Имя
Если во временных таблицах ожидается большой объем данных (более 1000 строк), рекомендуется дополнительно проиндексировать их по полю Имя, добавив в конец каждого промежуточного запроса инструкцию ИНДЕКСИРОВАТЬ ПО Имя.
Применяя этот подход в своей повседневной практике, вы навсегда избавитесь от дублирования строк при сборке многомерных отчетов и обеспечите максимальную скорость работы вашей конфигурации.