В процессе разработки отчетов в системе «1С:Предприятие» программисты часто сталкиваются с задачей сопоставления данных из разных документов. Классический пример — вывод информации о связанных документах «бок о бок». Например, когда у нас есть акционные накладные (полученные по промо-акциям) и основные накладные (которые послужили основанием для начисления этих акций), и нам требуется вывести их в одну строку табличного документа: слева — данные акционного документа (номенклатура, количество), справа — данные основного документа.
Сложность заключается в том, что связь между документами может быть типа «один ко многим» или «многие ко многим», а количество строк в табличных частях сопоставляемых документов почти никогда не совпадает. В этой статье мы подробно разберем, почему простые соединения в запросах приводят к дублированию информации, и рассмотрим три надежных способа решения этой задачи.
Давайте проанализируем стандартную ошибку, которую часто совершают начинающие разработчики. Попытка связать две таблицы по условию Акция.НомерСтроки = Основной.НомерСтроки через ПОЛНОЕ СОЕДИНЕНИЕ приводит к двум серьезным проблемам:
Ниже мы рассмотрим три профессиональных метода, которые позволяют обойти эти ограничения и построить идеальный отчет.
---
Этот способ основан на идее, предложенной в сообщении 16 обсуждения. Вместо того чтобы пытаться связать несовпадающие строки на уровне СУБД с помощью тяжелых соединений, мы переносим логику сопоставления на сторону встроенного языка 1С. Этот подход работает быстрее всего, поскольку СУБД выполняет простую выборку без ресурсоемких операций сопоставления.
Разберем алгоритм по шагам:
ОБЪЕДИНИТЬ ВСЕ.ТипДокумента (значение "АКЦИЯ" или "ОСНОВНОЙ") для разделения строк при обработке.Вывести() для левой части и Присоединить() для правой.Рассмотрим подробнее пример запроса для этого метода:
ВЫБРАТЬ
РегистрСвязи.АкционныйДокумент КАК КлючСвязи,
"АКЦИЯ" КАК ТипДокумента,
АкционныеТовары.НомерСтроки КАК НомерСтроки,
АкционныеТовары.Номенклатура КАК Номенклатура,
АкционныеТовары.Количество КАК Количество
ИЗ
РегистрСведений.впПривязкаАкционныхДокументов КАК РегистрСвязи
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг.Товары КАК АкционныеТовары
ПО РегистрСвязи.АкционныйДокумент = АкционныеТовары.Ссылка
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
РегистрСвязи.АкционныйДокумент КАК КлючСвязи,
"ОСНОВНОЙ" КАК ТипДокумента,
ОсновныеТовары.НомерСтроки КАК НомерСтроки,
ОсновныеТовары.Номенклатура КАК Номенклатура,
ОсновныеТовары.Количество КАК Количество
ИЗ
РегистрСведений.впПривязкаАкционныхДокументов КАК РегистрСвязи
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг.Товары КАК ОсновныеТовары
ПО РегистрСвязи.ОсновнойДокумент = ОсновныеТовары.Ссылка
УПОРЯДОЧИТЬ ПО
КлючСвязи,
ТипДокумента,
НомерСтроки
Теперь проанализируем код на встроенном языке, который будет обрабатывать этот запрос и выводить данные в отчет. Нам понадобятся области макета: область шапки строки акции (левая часть) и область шапки строки основного документа (правая часть).
// Выполняем запрос
РезультатЗапроса = Запрос.Выполнить();
Выборка = РезультатЗапроса.Выбрать();
ТабДок = Новый ТабличныйДокумент;
Макет = ПолучитьМакет("МакетОтчета");
ОбластьСтрокиАкция = Макет.ПолучитьОбласть("СтрокаАкция");
ОбластьСтрокиОсновной = Макет.ПолучитьОбласть("СтрокаОсновной");
ОбластьПустаяСтрокаАкция = Макет.ПолучитьОбласть("ПустаяСтрокаАкция");
ОбластьПустаяСтрокаОсновной = Макет.ПолучитьОбласть("ПустаяСтрокаОсновной");
// Структуры для накопления данных по текущему блоку документов
ТекущийКлюч = Неопределено;
МассивАкции = Новый Массив;
МассивОсновных = Новый Массив;
Пока Выборка.Следующий() Цикл
Если Выборка.КлючСвязи <> ТекущийКлюч Тогда
// Перед обработкой нового документа выводим накопленные данные по предыдущему
Если ТекущийКлюч <> Неопределено Тогда
ВывестиПараллельныеСтроки(ТабДок, МассивАкции, МассивОсновных, ОбластьСтрокиАкция, ОбластьСтрокиОсновной, ОбластьПустаяСтрокаАкция, ОбластьПустаяСтрокаОсновной);
КонецЕсли;
// Очищаем структуры для нового блока
ТекущийКлюч = Выборка.КлючСвязи;
МассивАкции.Очистить();
МассивОсновных.Очистить();
КонецЕсли;
// Накапливаем данные в зависимости от типа строки
СтрокаДанных = Новый Структура("Номенклатура, Количество", Выборка.Номенклатура, Выборка.Количество);
Если Выборка.ТипДокумента = "АКЦИЯ" Тогда
МассивАкции.Добавить(СтрокаДанных);
Иначе
МассивОсновных.Добавить(СтрокаДанных);
КонецЕсли;
КонецЦикла;
// Не забываем вывести последний блок данных
Если ТекущийКлюч <> Неопределено Тогда
ВывестиПараллельныеСтроки(ТабДок, МассивАкции, МассивОсновных, ОбластьСтрокиАкция, ОбластьСтрокиОсновной, ОбластьПустаяСтрокаАкция, ОбластьПустаяСтрокаОсновной);
EndIf;
Для вывода строк воспользуемся вспомогательной процедурой, которая сопоставляет индексы массивов и выравнивает строки по высоте:
Процедура ВывестиПараллельныеСтроки(ТабДок, МассивАкции, МассивОсновных, ОбластьАкция, ОбластьОсновной, ПустаяАкция, ПустаяОсновной)
КоличествоАкции = МассивАкции.Количество();
КоличествоОсновных = МассивОсновных.Количество();
МаксСтрок = Макс(КоличествоАкции, КоличествоОсновных);
Для Индекс = 0 По МаксСтрок - 1 Цикл
// 1. Выводим левую часть (Акция)
Если Индекс < КоличествоАкции Тогда
ТекущиеДанные = МассивАкции[Индекс];
ОбластьАкция.Параметры.НоменклатураАкция = ТекущиеДанные.Номенклатура;
ОбластьАкция.Параметры.КоличествоАкция = ТекущиеДанные.Количество;
ТабДок.Вывести(ОбластьАкция);
Иначе
// Если акционных строк меньше, выводим пустую заглушку для сохранения структуры таблицы
ТабДок.Вывести(ПустаяАкция);
КонецЕсли;
// 2. Присоединяем правую часть (Основной документ)
Если Индекс < КоличествоОсновных Тогда
ТекущиеДанные = МассивОсновных[Индекс];
ОбластьОсновной.Параметры.НоменклатураОсновной = ТекущиеДанные.Номенклатура;
ОбластьОсновной.Параметры.КоличествоОсновной = ТекущиеДанные.Количество;
ТабДок.Присоединить(ОбластьОсновной);
Иначе
// Пустая заглушка для правой части
ТабДок.Присоединить(ПустаяОсновной);
КонецЕсли;
КонецЦикла;
КонецПроцедуры
Этот метод гарантирует абсолютную точность вывода информации. Строки никогда не затворятся, а отчет будет выглядеть аккуратно, даже если в левой части 2 строки, а в правой — 10.
---
Если вам обязательно нужно получить готовый результат непосредственно из SQL-запроса (например, для передачи данных во внешние системы или для вывода через стандартный универсальный отчет), программная сборка не подойдет. В этом случае нам необходимо решить задачу внутригрупповой нумерации строк средствами СУБД.
В классическом SQL для этого используется оконная функция ROW_NUMBER() OVER (PARTITION BY Document ORDER BY LineNumber). В платформе 1С стандартных оконных функций нет (функция АВТОНОМЕРЗАПИСИ() нумерует строки глобально, без сброса счетчика для каждого документа). Поэтому мы применим классический алгоритм самосоединения временных таблиц.
Разберем этот метод подробно по шагам:
ВТ_Акция.ВТ_Основной.ПОЛНОЕ СОЕДИНЕНИЕ по полям КлючСвязи и новому СинтетическийНомер.Рассмотрим пример реализации такого пакета запросов:
// ШАГ 1. Подготовка и индексация данных по акциям
ВЫБРАТЬ
РегистрСвязи.АкционныйДокумент КАК КлючСвязи,
АкционныеТовары.Номенклатура КАК Номенклатура,
АкционныеТовары.Количество КАК Количество,
АкционныеТовары.НомерСтроки КАК НомерСтрокиФизический
ПОМЕСТИТЬ ВТ_Акция_Исходная
ИЗ
РегистрСведений.впПривязкаАкционныхДокументов КАК РегистрСвязи
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг.Товары КАК АкционныеТовары
ПО РегистрСвязи.АкционныйДокумент = АкционныеТовары.Ссылка;
ИКС ИНДЕКСИРОВАТЬ ВТ_Акция_Исходная ПО КлючСвязи, НомерСтрокиФизический;
////////////////////////////////////////////////////////////////////////////////
// ШАГ 2. Генерация синтетического номера строки для акций
ВЫБРАТЬ
Т1.КлючСвязи КАК КлючСвязи,
Т1.Номенклатура КАК Номенклатура,
Т1.Количество КАК Количество,
КОЛИЧЕСТВО(Т2.НомерСтрокиФизический) КАК СинтетическийНомер
ПОМЕСТИТЬ ВТ_Акция_Пронумерованная
ИЗ
ВТ_Акция_Исходная КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Акция_Исходная КАК Т2
ПО Т1.КлючСвязи = Т2.КлючСвязи
И Т1.НомерСтрокиФизический >= Т2.НомерСтрокиФизический
СГРУППИРОВАТЬ ПО
Т1.КлючСвязи,
Т1.Номенклатура,
Т1.Количество;
ИКС ИНДЕКСИРОВАТЬ ВТ_Акция_Пронумерованная ПО КлючСвязи, СинтетическийНомер;
////////////////////////////////////////////////////////////////////////////////
// ШАГ 3. Подготовка и индексация данных по основным документам
ВЫБРАТЬ
РегистрСвязи.АкционныйДокумент КАК КлючСвязи,
ОсновныеТовары.Номенклатура КАК Номенклатура,
ОсновныеТовары.Количество КАК Количество,
ОсновныеТовары.НомерСтроки КАК НомерСтрокиФизический
ПОМЕСТИТЬ ВТ_Основной_Исходная
ИЗ
РегистрСведений.впПривязкаАкционныхДокументов КАК РегистрСвязи
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг.Товары КАК ОсновныеТовары
ПО РегистрСвязи.ОсновнойДокумент = ОсновныеТовары.Ссылка;
ИКС ИНДЕКСИРОВАТЬ ВТ_Основной_Исходная ПО КлючСвязи, НомерСтрокиФизический;
////////////////////////////////////////////////////////////////////////////////
// ШАГ 4. Генерация синтетического номера строки для основных документов
ВЫБРАТЬ
Т1.КлючСвязи КАК КлючСвязи,
Т1.Номенклатура КАК Номенклатура,
Т1.Количество КАК Количество,
КОЛИЧЕСТВО(Т2.НомерСтрокиФизический) КАК СинтетическийНомер
ПОМЕСТИТЬ ВТ_Основной_Пронумерованная
ИЗ
ВТ_Основной_Исходная КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Основной_Исходная КАК Т2
ПО Т1.КлючСвязи = Т2.КлючСвязи
И Т1.НомерСтрокиФизический >= Т2.НомерСтрокиФизический
СГРУППИРОВАТЬ ПО
Т1.КлючСвязи,
Т1.Номенклатура,
Т1.Количество;
ИКС ИНДЕКСИРОВАТЬ ВТ_Основной_Пронумерованная ПО КлючСвязи, СинтетическийНомер;
////////////////////////////////////////////////////////////////////////////////
// ШАГ 5. Итоговое полное соединение двух пронумерованных таблиц
ВЫБРАТЬ
ЕСТЬNULL(ТаблицаАкции.КлючСвязи, ТаблицаОсновные.КлючСвязи) КАК ДокументСвязи,
ЕСТЬNULL(ТаблицаАкции.СинтетическийНомер, ТаблицаОсновные.СинтетическийНомер) КАК СтрокаПоПорядку,
ТаблицаАкции.Номенклатура КАК АкцияНоменклатура,
ТаблицаАкции.Количество КАК АкцияКоличество,
ТаблицаОсновные.Номенклатура КАК ОсновнойНоменклатура,
ТаблицаОсновные.Количество КАК ОсновнойКоличество
ИЗ
ВТ_Акция_Пронумерованная КАК ТаблицаАкции
ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Основной_Пронумерованная КАК ТаблицаОсновные
ПО ТаблицаАкции.КлючСвязи = ТаблицаОсновные.КлючСвязи
И ТаблицаАкции.СинтетическийНомер = ТаблицаОсновные.СинтетическийНомер
УПОРЯДОЧИТЬ ПО
ДокументСвязи,
СтрокаПоПорядку
Важное примечание по оптимизации: Обратите внимание на использование оператора ИКС ИНДЕКСИРОВАТЬ в запросе. Применение самосоединений для генерации номеров строк создает серьезную нагрузку на СУБД (сложность алгоритма O(N^2) в рамках одного документа). Без создания индексов по полям связи и физическим номерам строк на больших базах данных этот запрос может выполняться крайне медленно. Индексация позволяет СУБД использовать быстрые алгоритмы поиска и значительно снижает нагрузку на дисковую подсистему сервера.
---
Система компоновки данных (СКД) предоставляет нам встроенные механизмы сопоставления данных, которые работают очень эффективно. Мы можем полностью отказаться от сложных запросов со слияниями и переложить задачу сопоставления на саму СКД.
Для реализации этого метода выполним следующие шаги в конструкторе схемы компоновки данных:
АкционныйДокумент.НаборДанныхАкция.НаборДанныхОсновной.АкционныйДокумент = АкционныйДокумент.АкционныйДокумент.ТипИнформации (которое в схеме принимает значения "Акция" или "Основной").Номенклатура и Количество.Платформа автоматически сгенерирует оптимальные временные таблицы на уровне СУБД, свяжет их по указанным ключам и выведет информацию параллельно в колонках отчета, избежав при этом дублирования и зависания системы.
---
Мы подробно проанализировали три кардинально отличающихся подхода к решению задачи параллельного вывода связанных данных в 1С. Чтобы вам было проще сделать выбор для вашего проекта, сопоставим их особенности: