При проектировании сложных отчетов или выгрузок данных разработчики часто сталкиваются со следующей задачей: у одного главного объекта (например, контрагента) есть несколько независимых подчиненных сущностей. Это могут быть контактные лица, автомобили, банковские счета или договоры. Если мы попытаемся получить эти данные классическим левым соединением в одном запросе, то неизбежно столкнемся с проблемой декартова произведения (размножения строк). Количество записей в результирующей таблице будет равно произведению количества строк во всех подчиненных таблицах.
Например, если у контрагента зарегистрировано 10 контактных лиц и 20 автомобилей, то при обычном левом соединении СУБД вернет нам 200 строк (10 × 20). Наша цель — получить ровно 20 строк (максимальное количество среди подчиненных таблиц), где в первых 10 строках будут заполнены и контакты, и автомобили, а в оставшихся 10 строках поля контактов будут содержать значение Null. Рассмотрим, как решить эту задачу в рамках языка запросов 1С.
В этой статье мы подробно разберем несколько вариантов решения этой задачи, проанализируем их плюсы и минусы, подводные камни производительности, а также альтернативные варианты обхода этой проблемы средствами платформы 1С.
Давайте посмотрим на пример простого запроса, который программисты часто пишут по ошибке:
ВЫБРАТЬ
Контрагенты.Ссылка КАК Контрагент,
Контакты.Ссылка КАК КонтактноеЛицо,
Автомобили.Автомобиль КАК Автомобиль
ИЗ
Справочник.Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.КонтактныеЛица КАК Контакты
ПО Контрагенты.Ссылка = Контакты.Владелец
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ВладельцыАвтомобилей КАК Автомобили
ПО Контрагенты.Ссылка = Автомобили.Владелец
Для СУБД данное условие означает следующее: для каждой строки из таблицы контактов, соответствующей контрагенту, необходимо сопоставить все строки из таблицы автомобилей, также соответствующие этому контрагенту. В итоге данные начинают дублироваться: каждый контакт связывается с каждой машиной, искажая итоговую аналитику и критически увеличивая объем выборки.
Чтобы избежать этого эффекта, нам нужно сопоставить строки «горизонтально». Иными словами, связать первую строку контактов с первой строкой автомобилей, вторую — со второй, третью — с третьей и так далее. Для этого нам необходимо ввести дополнительный ключ связи — порядковый номер строки внутри группы каждого контрагента.
Начиная с версии платформы 8.3.13 в языке запросов 1С появился механизм автоматической нумерации строк во временных таблицах — функция АВТОНОМЕРЗАПИСИ(). Мы можем применить ее для генерации уникальных индексов строк, чтобы затем связать их с помощью полного соединения.
Рассмотрим пример запроса, работающего с отбором по конкретному контрагенту:
// Шаг 1: Нумеруем автомобили конкретного контрагента во временной таблице
ВЫБРАТЬ
АВТОНОМЕРЗАПИСИ() КАК НомерСтроки,
ВладельцыАвтомобилей.Автомобиль КАК Автомобиль,
ВладельцыАвтомобилей.Владелец КАК Владелец
ПОМЕСТИТЬ ВТ_Автомобили
ИЗ
РегистрСведений.ВладельцыАвтомобилей КАК ВладельцыАвтомобилей
ГДЕ
ВладельцыАвтомобилей.Владелец = &Контрагент;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Нумеруем контактные лица во временной таблице
ВЫБРАТЬ
АВТОНОМЕРЗАПИСИ() КАК НомерСтроки,
КонтактныеЛица.Ссылка КАК КонтактноеЛицо,
КонтактныеЛица.Владелец КАК Владелец
ПОМЕСТИТЬ ВТ_Контакты
ИЗ
Справочник.КонтактныеЛица КАК КонтактныеЛица
ГДЕ
КонтактныеЛица.Владелец = &Контрагент;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Соединяем временные таблицы «бок о бок» через ПОЛНОЕ СОЕДИНЕНИЕ
ВЫБРАТЬ
Контрагенты.Ссылка КАК Контрагент,
СопоставленныеДанные.Автомобиль КАК Автомобиль,
СопоставленныеДанные.КонтактноеЛицо КАК КонтактноеЛицо
ИЗ
Справочник.Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ
ЕСТЬNULL(ВТ_Автомобили.Владелец, ВТ_Контакты.Владелец) КАК Владелец,
ВТ_Автомобили.Автомобиль КАК Автомобиль,
ВТ_Контакты.КонтактноеЛицо КАК КонтактноеЛицо
ИЗ
ВТ_Автомобили КАК ВТ_Автомобили
ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Контакты КАК ВТ_Контакты
ПО ВТ_Автомобили.НомерСтроки = ВТ_Контакты.НомерСтроки
И ВТ_Автомобили.Владелец = ВТ_Контакты.Владелец
) КАК СопоставленныеДанные
ПО Контрагенты.Ссылка = СопоставленныеДанные.Владелец
ГДЕ
Контрагенты.Ссылка = &Контрагент
Критически важный нюанс: Обратите внимание на использование конструкции ПОЛНОЕ СОЕДИНЕНИЕ во вложенном запросе. Если мы используем левое соединение, мы рискуем потерять часть данных, если в правой таблице строк окажется больше, чем в левой. Полное соединение гарантирует, что мы получим все строки из обеих таблиц, сопоставив их по номеру.
Хотя первый метод выглядит элегантно, он имеет несколько серьезных технологических ограничений:
АВТОНОМЕРЗАПИСИ() генерирует сквозную нумерацию по всей таблице. Она не умеет сбрасывать счетчик при изменении контрагента (в отличие от SQL-функции ROW_NUMBER() OVER (PARTITION BY...), аналогов которой в 1С пока нет). Это означает, что данный метод корректно работает только при жестком отборе по одному контрагенту. Если мы уберем отбор и попытаемся выбрать всех контрагентов сразу, нумерация пойдет дальше по всей таблице, и сопоставление сломается.Если нам необходимо построить отчет по нескольким контрагентам (или по всей базе сразу), нам потребуется классический «рабоче-крестьянский» метод нумерации строк в разрезе группировки. Он основан на левом соединении таблицы с самой собой по неравенству уникальных идентификаторов.
Проанализируем этот запрос подробнее:
// Шаг 1: Нумеруем контакты в разрезе каждого владельца
ВЫБРАТЬ
Контакты1.Владелец КАК Владелец,
Контакты1.Ссылка КАК КонтактноеЛицо,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Контакты2.Ссылка) КАК НомерСтроки
ПОМЕСТИТЬ ВТ_ПронумерованныеКонтакты
ИЗ
Справочник.КонтактныеЛица КАК Контакты1
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.КонтактныеЛица КАК Контакты2
ПО Контакты1.Владелец = Контакты2.Владелец
И Контакты1.Ссылка >= Контакты2.Ссылка
СГРУППИРОВАТЬ ПО
Контакты1.Владелец,
Контакты1.Ссылка;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Нумеруем автомобили в разрезе каждого владельца
ВЫБРАТЬ
Авто1.Владелец КАК Владелец,
Авто1.Автомобиль КАК Автомобиль,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Авто2.Автомобиль) КАК НомерСтроки
ПОМЕСТИТЬ ВТ_ПронумерованныеАвто
ИЗ
РегистрСведений.ВладельцыАвтомобилей КАК Авто1
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ВладельцыАвтомобилей КАК Авто2
ПО Авто1.Владелец = Авто2.Владелец
И Авто1.Автомобиль >= Авто2.Автомобиль
СГРУППИРОВАТЬ ПО
Авто1.Владелец,
Авто1.Автомобиль;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Объединяем результаты в один отчет по всем контрагентам
ВЫБРАТЬ
Контрагенты.Ссылка КАК Контрагент,
СопоставленныеДанные.КонтактноеЛицо КАК КонтактноеЛицо,
СопоставленныеДанные.Автомобиль КАК Автомобиль
ИЗ
Справочник.Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ
ЕСТЬNULL(ВТ_ПронумерованныеКонтакты.Владелец, ВТ_ПронумерованныеАвто.Владелец) КАК Владелец,
ВТ_ПронумерованныеКонтакты.КонтактноеЛицо КАК КонтактноеЛицо,
ВТ_ПронумерованныеАвто.Автомобиль КАК Автомобиль
ИЗ
ВТ_ПронумерованныеКонтакты КАК ВТ_ПронумерованныеКонтакты
ПОЛНОЕ СОЕДИНЕНИЕ ВТ_ПронумерованныеАвто КАК ВТ_ПронумерованныеАвто
ПО ВТ_ПронумерованныеКонтакты.Владелец = ВТ_ПронумерованныеАвто.Владелец
И ВТ_ПронумерованныеКонтакты.НомерСтроки = ВТ_ПронумерованныеАвто.НомерСтроки
) КАК СопоставленныеДанные
ПО Контрагенты.Ссылка = СопоставленныеДанные.Владелец
Как работает этот механизм? Мы соединяем таблицу саму с собой по условию равенства владельца и неравенства ссылок (Контакты1.Ссылка >= Контакты2.Ссылка). При группировке по конкретному элементу функция КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Контакты2.Ссылка) посчитает, сколько элементов имеют ссылку «меньше или равную» текущей. Это дает нам идеальную последовательную нумерацию (1, 2, 3...) внутри каждого отдельного контрагента.
Важное предупреждение о производительности: Данный метод выполняет квадратичное количество сравнений строк СУБД (сложность алгоритма $O(N^2)$). Если у одного контрагента содержится несколько сотен или тысяч записей в подчиненных таблицах, такой запрос может перегрузить временную базу данных tempdb и привести к зависанию сервера 1С. Используйте этот метод только тогда, когда вы точно уверены, что количество подчиненных записей у одного владельца измеряется десятками, но не тысячами.
Если вашей конечной целью является вывод этих данных в отчет для пользователей, то пытаться решить эту задачу «в лоб» через сложные и тяжелые SQL-запросы считается плохой практикой разработки (антипаттерном). В платформе 1С реализован мощный декларативный механизм СКД, который умеет делать это автоматически без декартова произведения.
Для реализации этого в СКД мы можем настроить параллельный вывод таблиц через Связи наборов данных:
НаборДанныхКонтрагенты, НаборДанныхКонтакты и НаборДанныхАвтомобили. Каждый набор выбирает данные из своей таблицы без каких-либо соединений.НаборДанныхКонтрагенты с НаборДанныхКонтакты по полю Ссылка = Владелец.НаборДанныхКонтрагенты с НаборДанныхАвтомобили по полю Ссылка = Владелец.СКД самостоятельно обработает выборку данных оптимальными пакетами запросов и сопоставит строки на уровне процессора компоновки данных, не нагружая СУБД тяжелыми неоптимальными соединениями.
Если данные нужны не для вывода в отчет, а для выполнения бизнес-логики (например, заполнения табличных частей документов, выгрузки во внешние файлы JSON/XML или отправки по API), оптимальным решением по скорости работы будет отказ от сложных запросов в пользу программной обработки таблиц в оперативной памяти сервера.
Рассмотрим алгоритм сопоставления таблиц на встроенном языке 1С:
// Получаем данные двумя простыми и быстрыми запросами
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| КонтактныеЛица.Владелец КАК Контрагент,
| КонтактныеЛица.Ссылка КАК КонтактноеЛицо
|ИЗ
| Справочник.КонтактныеЛица КАК КонтактныеЛица
|ГДЕ
| КонтактныеЛица.Владелец В (&МассивКонтрагентов);
|
|ВЫБРАТЬ
| ВладельцыАвтомобилей.Владелец КАК Контрагент,
| ВладельцыАвтомобилей.Автомобиль КАК Автомобиль
|ИЗ
| РегистрСведений.ВладельцыАвтомобилей КАК ВладельцыАвтомобилей
|ГДЕ
| ВладельцыАвтомобилей.Владелец В (&МассивКонтрагентов)";
Запрос.УстановитьПараметр("МассивКонтрагентов", МассивКонтрагентов);
РезультатыЗапроса = Запрос.ВыполнитьПакет();
ТаблицаКонтактов = РезультатыЗапроса[0].Выгрузить();
ТаблицаАвтомобилей = РезультатыЗапроса[1].Выгрузить();
// Создаем результирующую таблицу
ТаблицаРезультат = Новый ТаблицаЗначений;
ТаблицаРезультат.Колонки.Добавить("Контрагент");
ТаблицаРезультат.Колонки.Добавить("КонтактноеЛицо");
ТаблицаРезультат.Колонки.Добавить("Автомобиль");
// Обходим данные по каждому контрагенту
Для Каждого ТекущийКонтрагент Из МассивКонтрагентов Цикл
Отбор = Новый Структура("Контрагент", ТекущийКонтрагент);
СтрокиКонтактов = ТаблицаКонтактов.НайтиСтроки(Отбор);
СтрокиАвто = ТаблицаАвтомобилей.НайтиСтроки(Отбор);
МаксСтрок = Макс(СтрокиКонтактов.Количество(), СтрокиАвто.Количество());
Для Индекс = 0 По МаксСтрок - 1 Цикл
НоваяСтрока = ТаблицаРезультат.Добавить();
НоваяСтрока.Контрагент = ТекущийКонтрагент;
Если Индекс < СтрокиКонтактов.Количество() Тогда
НоваяСтрока.КонтактноеЛицо = СтрокиКонтактов[Индекс].КонтактноеЛицо;
КонецЕсли;
Если Индекс < СтрокиАвто.Количество() Тогда
НоваяСтрока.Автомобиль = СтрокиАвто[Индекс].Автомобиль;
КонецЕсли;
КонецЦикла;
КонецЦикла;
Почему этот способ предпочтительнее для больших баз данных? В данном случае мы полностью исключаем из работы СУБД ресурсоемкие операции по поиску комбинаций строк. СУБД выполняет два простейших индексных сканирования по таблицам контактов и автомобилей. Сопоставление строк в памяти сервера приложений 1С происходит за линейное время $O(N)$ и выполняется практически мгновенно даже на сотнях тысяч записей.
Подведем итоги анализа и сформулируем правила выбора оптимального архитектурного решения:
АВТОНОМЕРЗАПИСИ() во временных таблицах (Решение №1).