При работе с запросами в платформе 1С:Предприятие разработчики часто сталкиваются с необходимостью обработать внешние данные. Для этого таблицы значений передаются в запрос в качестве параметров, помещаются во временные таблицы и связываются между собой. Однако на этапе соединения данных может возникнуть неприятный сюрприз: количество строк в итоговой выборке оказывается значительно больше, чем в исходной левой таблице. Например, мы ожидаем получить ровно 22 строки (по количеству записей в левой таблице), но запрос возвращает 27 или даже 44 строки.
В этой статье мы подробно разберем, почему происходит «размножение» строк при использовании связи ЛЕВОЕ СОЕДИНЕНИЕ, как работает SQL-механика сопоставления данных «один ко многим» и какими способами можно предотвратить появление дубликатов в результирующем пакете данных.
Рассмотрим сценарий, с которым столкнулся автор темы. В коде инициализируются две таблицы значений со строгой типизацией колонок:
// Первая таблица (Налив) — содержит 22 строки
Таблица = Новый ТаблицаЗначений;
Таблица.Колонки.Добавить("Паспорт", Новый ОписаниеТипов("Число",,, Новый КвалификаторыЧисла(10,0)));
Таблица.Колонки.Добавить("Резервуар", Новый ОписаниеТипов("Число",,, Новый КвалификаторыЧисла(10,0)));
Таблица.Колонки.Добавить("Товар", Новый ОписаниеТипов("Строка",,,, Новый КвалификаторыСтроки(200)));
// Вторая таблица (Накладные) — содержит 5 строк
Таблица1 = Новый ТаблицаЗначений;
Таблица1.Колонки.Добавить("Грузополучатель", Новый ОписаниеТипов("Строка",,,, Новый КвалификаторыСтроки(200)));
Таблица1.Колонки.Добавить("Груз", Новый ОписаниеТипов("Строка",,,, Новый КвалификаторыСтроки(200)));
Обратите внимание, что автор абсолютно верно указал типы колонок при создании таблиц значений. Если передать в запрос таблицу с колонками неопределенного типа, система выдаст ошибку о невозможности выбора неопределенного типа в запросе.
Далее эти таблицы передаются в качестве параметров в запрос, где они помещаются во временные таблицы:
Запрос = Новый Запрос;
МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
Запрос.МенеджерВременныхТаблиц = МенеджерВременныхТаблиц;
Запрос.УстановитьПараметр("Налив", Таблица );
Запрос.УстановитьПараметр("Накладные", Таблица1);
Запрос.Текст =
"ВЫБРАТЬ
| Налив.Паспорт,
| Налив.Резервуар,
| Налив.Товар
|ПОМЕСТИТЬ ВремТаб
|ИЗ
| &Налив КАК Налив
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Накладные.Груз,
| Накладные.Грузополучатель
|ПОМЕСТИТЬ ВремТаб2
|ИЗ
| &Накладные КАК Накладные
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВремТаб.Паспорт,
| ВремТаб.Резервуар,
| ВремТаб.Товар КАК Товар,
| ВремТаб2.Груз,
| ВремТаб2.Грузополучатель
|ИЗ
| ВремТаб КАК ВремТаб
| ЛЕВОЕ СОЕДИНЕНИЕ ВремТаб2 КАК ВремТаб2
| ПО ВремТаб.Товар = ВремТаб2.Груз";
При выполнении этого запроса вместо ожидаемых 22 строк на выходе получается 27 строк. Выясним, почему это происходит.
Многие начинающие разработчики полагают, что ЛЕВОЕ СОЕДИНЕНИЕ работает следующим образом: берется строка из левой таблицы, для нее ищется соответствие в правой таблице, и если соответствие найдено — подставляются значения, а если нет — подставляется значение NULL. При этом общее число строк якобы должно остаться неизменным и равняться числу строк в левой таблице.
Это убеждение ошибочно. На самом деле СУБД выполняет сопоставление по условию связи абсолютно для каждой комбинации строк. Если одному и тому же значению ключа связи в левой таблице соответствует несколько записей в правой таблице, левая строка будет скопирована (продублирована) столько раз, сколько соответствий нашлось в правой таблице.
Проанализируем простую абстрактную ситуацию:
Левая временная таблица (ВремТаб) содержит записи о наливе бензина:
Правая временная таблица (ВремТаб2) содержит информацию о грузополучателях:
Мы связываем их по условию ВремТаб.Товар = ВремТаб2.Груз. СУБД начинает обработку:
Итого вместо 2 исходных строк мы получили 4 строки в результате левого соединения. Именно это и произошло у автора вопроса: в его правой таблице накладных для одного и того же наименования груза содержалось несколько разных грузополучателей, что привело к увеличению общего объема выборки с 22 до 27 строк (а при тесте с единым наименованием товара выборка разрослась до 44 строк).
В зависимости от бизнес-логики вашей задачи, проблему дублирования при соединении таблиц можно решить несколькими способами. Рассмотрим их подробнее.
Если для левой таблицы нам нужно получить лишь уникальное значение из правой таблицы (например, нам не важно, сколько там получателей, или мы хотим взять первого попавшегося / минимального), правую временную таблицу необходимо предварительно сгруппировать по полю связи.
Внесем изменения в текст запроса, сгруппировав таблицу ВремТаб2 по полю Груз и применив агрегатную функцию к полю Грузополучатель:
Запрос.Текст =
"ВЫБРАТЬ
| Налив.Паспорт,
| Налив.Резервуар,
| Налив.Товар
|ПОМЕСТИТЬ ВремТаб
|ИЗ
| &Налив КАК Налив
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Накладные.Груз КАК Груз,
| МИНИМУМ(Накладные.Грузополучатель) КАК Грузополучатель
|ПОМЕСТИТЬ ВремТаб2
|ИЗ
| &Накладные КАК Накладные
|ГРУППИРОВАТЬ ПО
| Накладные.Груз
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВремТаб.Паспорт,
| ВремТаб.Резервуар,
| ВремТаб.Товар КАК Товар,
| ВремТаб2.Грузополучатель КАК Грузополучатель
|ИЗ
| ВремТаб КАК ВремТаб
| ЛЕВОЕ СОЕДИНЕНИЕ ВремТаб2 КАК ВремТаб2
| ПО ВремТаб.Товар = ВремТаб2.Груз";
В данном случае использование функции МИНИМУМ гарантирует, что для каждого наименования груза во временной таблице ВремТаб2 останется ровно одна уникальная строка. Соответственно, при левом соединении строки основной таблицы ВремТаб дублироваться не будут, и общее количество строк строго совпадет с исходным (22 строки).
При левом соединении для строк левой таблицы, которым не нашлось соответствия в правой, все поля правой таблицы примут значение NULL. Чтобы избежать дальнейших ошибок при обработке результата на стороне сервера 1С, рекомендуется использовать функцию ЕСТЬNULL. Она позволяет заменить системный NULL на пустое значение соответствующего типа (например, пустую строку или число 0):
|ВЫБРАТЬ
| ВремТаб.Паспорт,
| ВремТаб.Резервуар,
| ВремТаб.Товар КАК Товар,
| ЕСТЬNULL(ВремТаб2.Грузополучатель, """") КАК Грузополучатель
|ИЗ
| ВремТаб КАК ВремТаб
| ЛЕВОЕ СОЕДИНЕНИЕ ВремТаб2 КАК ВремТаб2
| ПО ВремТаб.Товар = ВремТаб2.Груз
Иногда соединение не срабатывает из-за того, что строковые ключи внешне выглядят одинаково, но содержат невидимые пробелы в начале или конце строки. Прежде чем передавать таблицы в запрос, рекомендуется выполнить очистку данных с помощью встроенных функций платформы:
Для Каждого Строка Из Таблица Цикл
Строка.Товар = СократитьЛП(Строка.Товар);
КонецЦикла;
Для Каждого Строка1 Из Таблица1 Цикл
Строка1.Груз = СократитьЛП(Строка1.Груз);
КонецЦикла;
Это простое действие гарантирует точное символьное совпадение строк при проверке равенства СУБД.
Если ваши таблицы значений содержат значительный объем информации (от 1000 строк и более), левое соединение без предварительной оптимизации может сильно снизить производительность СУБД. Проанализируем правила хорошего тона при работе с временными таблицами.
Для ускорения поиска соответствий СУБД должна использовать индексы. При создании временной таблицы в запросе 1С мы можем явно указать, по какому полю необходимо построить индекс. Сделать это можно с помощью инструкции ИНДЕКСИРОВАТЬ ПО:
|ВЫБРАТЬ
| Налив.Паспорт,
| Налив.Резервуар,
| Налив.Товар КАК Товар
|ПОМЕСТИТЬ ВремТаб
|ИЗ
| &Налив КАК Налив
|ИНДЕКСИРОВАТЬ ПО
| Товар
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Накладные.Груз КАК Груз,
| Накладные.Грузополучатель КАК Грузополучатель
|ПОМЕСТИТЬ ВремТаб2
|ИЗ
| &Накладные КАК Накладные
|ИНДЕКСИРОВАТЬ ПО
| Груз
Важно: Индексация полей, по которым в дальнейшем будет происходить связь таблиц (в секции ПО), переводит операцию сканирования всей таблицы (Table Scan) в операцию быстрого поиска по индексу (Index Seek). Это критически важно для высоконагруженных систем.
Начиная с версии платформы 1С:Предприятие 8.3.25, разработчикам стала доступна множественная индексация одной временной таблицы по нескольким независимым полям, а также возможность дополнения существующих временных таблиц с помощью ключевого слова ДОБАВИТЬ. Это открывает новые горизонты для гибкой и быстрой обработки больших объемов данных во временных таблицах.
Мы детально разобрали особенности поведения левого соединения в запросах 1С. Подведем итоги: