В процессе разработки отчетов и обработок в среде 1С:Предприятие 8.3 (особенно в таких конфигурациях, как «1С:Розница», «1С:Управление торговлей» или «1С:ERP») программисты часто сталкиваются с задачей сопоставления данных из разных документов. Классический пример — необходимость сопоставить табличные части (ТЧ) документа ПоступлениеТоваровУслуг (или ПриобретениеТоваровУслуг) и документа ПеремещениеТоваров, чтобы увидеть, какое количество товара пришло на основной склад и сколько из этого объема было впоследствии перемещено на розничные точки.
На первый взгляд задача кажется простой, однако прямолинейное соединение табличных частей «в лоб» скрывает в себе серьезные архитектурные ловушки: критическое падение производительности СУБД, блокировки таблиц и логическое дублирование (размножение) строк при связях типа «многие-ко-многим». В этой статье мы подробно разберем несколько профессиональных способов решения этой задачи, начиная от базовых запросов и заканчивая оптимизированными алгоритмами с использованием временных таблиц и регистров накопления.
Прежде чем перейти к написанию кода, проанализируем, как СУБД (MS SQL Server, PostgreSQL и др.) хранит и обрабатывает данные табличных частей документов 1С. Физически в базе данных под каждую табличную часть создается отдельная таблица. Индексы в этих таблицах строятся платформой автоматически по полям Ссылка (указывает на заголовок документа) и КлючСтроки (или комбинация Ссылка + НомерСтроки).
Важный нюанс: по умолчанию в табличных частях документов нет индекса по полю Номенклатура. Если мы попытаемся выполнить прямое левое соединение табличных частей по условию:
ПО ДокПоступление.Номенклатура = ДокПеремещение.Номенклатура
СУБД будет вынуждена выполнять полное сканирование таблиц (Table Scan или Clustered Index Scan) для поиска соответствующих записей. На реальной базе данных с тысячами документов и миллионами строк в табличных частях такой запрос приведет к катастрофическому зависанию системы и наложению избыточных блокировок. Рассмотрим правильные подходы к реализации этой задачи.
Рассмотрим ситуацию, когда документ ПеремещениеТоваров создается строго на основании конкретного документа ПоступлениеТоваровУслуг. В этом случае у перемещения есть реквизит (например, ДокументОснование или ссылка на поступление в реквизите ДокументПоступления), который связывает заголовки этих документов. Это значительно упрощает задачу и делает запрос более точным.
Разберем пример запроса, в котором мы связываем поступление и перемещение по номенклатуре, но строго в рамках связанных документов-оснований, попутно ограничивая выборку по дате:
ВЫБРАТЬ
ДокПоступление.Номенклатура КАК Номенклатура,
ДокПоступление.Количество КАК КоличествоПоступления,
ДокПоступление.Цена КАК ЦенаПоступления,
ЕСТЬNULL(ДокПеремещение.Количество, 0) КАК КоличествоПеремещения
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК ДокПоступление
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПеремещениеТоваров.Товары КАК ДокПеремещение
ПО ДокПоступление.Ссылка = ДокПеремещение.Ссылка.ДокументОснование
И ДокПоступление.Номенклатура = ДокПеремещение.Номенклатура
ГДЕ
ДокПоступление.Ссылка.Дата >= &НачалоПериода
И ДокПоступление.Ссылка.Дата <= &КонецПериода
Обратите внимание на детали:
ЕСТЬNULL(..., 0) обязательно для полей соединяемой таблицы, так как при левом соединении для некоторых товаров из поступления может не оказаться записей о перемещении (в таком случае СУБД вернет значение NULL, которое мы заменяем на ноль).ПО ДокПоступление.Ссылка = ДокПеремещение.Ссылка.ДокументОснование позволяет СУБД использовать кластерный индекс по полю Ссылка, что кардинально ускоряет выполнение запроса.&НачалоПериода и &КонецПериода предотвращают сканирование всей истории базы данных.Если нам необходимо сопоставить данные не по конкретным парам связанных документов, а в целом собрать общую картину по номенклатуре за период (например, сколько пришло на склад и сколько ушло по перемещениям), то прямое соединение физических таблиц документов использовать нельзя. Правильный подход — предварительная выборка данных во временные таблицы с обязательным созданием индексов по связующим полям.
Рассмотрим по шагам этот алгоритм:
Шаг 1. Выбираем и группируем данные по поступлениям во временную таблицу:
ВЫБРАТЬ
Поступления.Номенклатура КАК Номенклатура,
СУММА(Поступления.Количество) КАК КоличествоПоступления,
СРЕДНЕЕ(Поступления.Цена) КАК ЦенаПоступления
ПОМЕСТИТЬ ВТ_Поступления
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК Поступления
ГДЕ
Поступления.Ссылка.Проведен = ИСТИНА
И Поступления.Ссылка.Дата МЕЖДУ &НачалоПериода И &КонецПериода
ГРУППИРОВАТЬ ПО
Поступления.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
Шаг 2. Выбираем и группируем данные по перемещениям во временную таблицу:
ВЫБРАТЬ
Перемещения.Номенклатура КАК Номенклатура,
СУММА(Перемещения.Количество) КАК КоличествоПеремещения
ПОМЕСТИТЬ ВТ_Перемещения
ИЗ
Документ.ПеремещениеТоваров.Товары КАК Перемещения
ГДЕ
Перемещения.Ссылка.Проведен = ИСТИНА
И Перемещения.Ссылка.Дата МЕЖДУ &НачалоПериода И &КонецПериода
ГРУППИРОВАТЬ ПО
Перемещения.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
Шаг 3. Соединяем подготовленные и проиндексированные временные таблицы:
ВЫБРАТЬ
ЕСТЬNULL(ВТ_Поступления.Номенклатура, ВТ_Перемещения.Номенклатура) КАК Номенклатура,
ЕСТЬNULL(ВТ_Поступления.КоличествоПоступления, 0) КАК КоличествоПоступления,
ЕСТЬNULL(ВТ_Поступления.ЦенаПоступления, 0) КАК ЦенаПоступления,
ЕСТЬNULL(ВТ_Перемещения.КоличествоПеремещения, 0) КАК КоличествоПеремещения
ИЗ
ВТ_Поступления КАК ВТ_Поступления
ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Перемещения КАК ВТ_Перемещения
ПО ВТ_Поступления.Номенклатура = ВТ_Перемещения.Номенклатура
Проанализируем преимущества этого подхода. Во-первых, мы использовали конструкцию ИНДЕКСИРОВАТЬ ПО Номенклатура при создании временных таблиц. Теперь СУБД выполнит слияние (Merge Join) или хэш-сопоставление по индексам в оперативной памяти за доли секунды. Во-вторых, мы применили ПОЛНОЕ СОЕДИНЕНИЕ, что гарантирует вывод номенклатуры, даже если она только поступила, но не перемещалась, или наоборот — если перемещались остатки прошлых периодов (когда поступлений в текущем периоде не было).
В практике разработки на платформе 1С существует еще один высокопроизводительный прием сопоставления разнородных данных. Он позволяет полностью исключить ресурсоемкую операцию соединения таблиц (JOIN) на уровне SQL. Этот метод заключается в вертикальном объединении выборок через оператор ОБЪЕДИНИТЬ ВСЕ с последующей финальной группировкой.
Рассмотрим структуру такого запроса:
ВЫБРАТЬ
ОбъединенныеДанные.Номенклатура КАК Номенклатура,
СУММА(ОбъединенныеДанные.КоличествоПоступления) КАК КоличествоПоступления,
МАКСИМУМ(ОбъединенныеДанные.Цена) КАК Цена,
СУММА(ОбъединенныеДанные.КоличествоПеремещения) КАК КоличествоПеремещения
ИЗ
(
// Первая ветка: только поступления
ВЫБРАТЬ
ТЧ_Пост.Номенклатура КАК Номенклатура,
ТЧ_Пост.Количество КАК КоличествоПоступления,
ТЧ_Пост.Цена КАК Цена,
0 КАК КоличествоПеремещения
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК ТЧ_Пост
ГДЕ
ТЧ_Пост.Ссылка.Проведен = ИСТИНА
И ТЧ_Пост.Ссылка.Дата МЕЖДУ &НачалоПериода И &КонецПериода
ОБЪЕДИНИТЬ ВСЕ
// Вторая ветка: только перемещения
ВЫБРАТЬ
ТЧ_Перем.Номенклатура,
0, // КоличествоПоступления зануляем
0, // Цену зануляем
ТЧ_Перем.Количество
ИЗ
Документ.ПеремещениеТоваров.Товары КАК ТЧ_Перем
ГДЕ
ТЧ_Перем.Ссылка.Проведен = ИСТИНА
И ТЧ_Перем.Ссылка.Дата МЕЖДУ &НачалоПериода И &КонецПериода
) КАК ОбъединенныеДанные
ГРУППИРОВАТЬ ПО
ОбъединенныеДанные.Номенклатура
Важное правило оптимизации: всегда используйте именно ОБЪЕДИНИТЬ ВСЕ, а не просто ОБЪЕДИНИТЬ. Оператор без приставки ВСЕ заставляет СУБД выполнять неявную и очень тяжелую операцию сравнения всех строк для удаления дубликатов. В нашем случае дубликатов строк быть не может, так как мы изначально разносим показатели поступления и перемещения по разным колонкам, заполняя противоположные поля нулями.
Если мы разрабатываем отчет или алгоритм для тиражной конфигурации (например, «1С:Розница»), обращение напрямую к табличным частям документов для получения итогов за период считается серьезным нарушением стандартов разработки фирмы «1С».
Документы при проведении записывают свои данные в специализированные таблицы — **Регистры накопления**. Именно регистры оптимизированы для быстрого получения остатков и оборотов, содержат все необходимые индексы и поддерживают механизм разделения итогов. Для решения нашей задачи идеально подходят виртуальные таблицы оборотов регистров.
Рассмотрим, как красиво и правильно решить задачу с помощью запроса к регистрам накопления (на примере регистров Закупки и ТоварыНаСкладах):
ВЫБРАТЬ
ЕСТЬNULL(ЗакупкиОбороты.Номенклатура, ТоварыНаСкладахОбороты.Номенклатура) КАК Номенклатура,
ЕСТЬNULL(ЗакупкиОбороты.КоличествоОборот, 0) КАК КоличествоПоступления,
ВЫБОР
КОГДА ЕСТЬNULL(ЗакупкиОбороты.КоличествоОборот, 0) > 0
ТОГДА ЕСТЬNULL(ЗакупкиОбороты.СтоимостьОборот, 0) / ЗакупкиОбороты.КоличествоОборот
ИНАЧЕ 0
КОНЕЦ КАК ЦенаПоступления,
ЕСТЬNULL(ТоварыНаСкладахОбороты.КоличествоРасход, 0) КАК КоличествоПеремещения
ИЗ
РегистрНакопления.Закупки.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ЗакупкиОбороты
ПОЛНОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Обороты(&НачалоПериода, &КонецПериода, , Склад = &ОсновнойСклад) КАК ТоварыНаСкладахОбороты
ПО ЗакупкиОбороты.Номенклатура = ТоварыНаСкладахОбороты.Номенклатура
Почему этот вариант самый лучший:
Мы детально изучили проблему соединения ТЧ документов в 1С и нашли несколько путей ее решения. Подведем итог, какой метод выбрать в зависимости от ситуации:
ОБЪЕДИНИТЬ ВСЕ).