При работе с регистрами сведений в платформе 1С:Предприятие разработчики часто сталкиваются с задачей получения актуальных срезов данных. Стандартная виртуальная таблица СрезПоследних отлично справляется, когда нам нужно получить последние записи по конкретным измерениям. Однако ситуация усложняется, когда мы пытаемся соединить исходную таблицу (например, табличную часть документа или временную таблицу) с регистром сведений, в которой для части строк измерение заполнено, а для другой части — пусто (или содержит дефолтное пустое значение). В этом случае стандартное левое соединение со срезом последних начинает "размножать" строки, возвращая избыточные и некорректные данные.
В этой статье мы подробно разберем природу данной проблемы, выясним, почему "очевидные" решения не работают или приводят к ошибкам компиляции СУБД, и рассмотрим три надежных и оптимизированных метода решения этой задачи на встроенном языке запросов 1С.
Прежде чем перейти к решениям, проанализируем поведение системы. Виртуальная таблица СрезПоследних всегда рассчитывается в разрезе всех без исключения измерений, определенных в метаданных регистра сведений. Предположим, у нас есть регистр сведений ЦеныНоменклатуры с измерениями Товар, Партия и ресурсом Цена. В регистре содержатся следующие записи:
Мы хотим сопоставить этот регистр со следующей входящей таблицей (например, нашей ТЗ):
Для первой строки ожидаемый результат очевиден — 100 рублей. Для второй строки, где партия не указана, наша бизнес-логика требует получить последнюю актуальную цену товара в целом, независимо от партии. То есть мы хотим увидеть цену из самой свежей по периоду записи по данному товару (в нашем примере это Партия3 с ценой 120 руб. от 01.11.2023).
Если мы выполним простое левое соединение нашей таблицы со срезом последних по условию:
ПО ТЗ.Товар = Срез.Товар
И (ТЗ.Партия = "" ИЛИ ТЗ.Партия = Срез.Партия)
Мы получим некорректный результат. Так как виртуальная таблица СрезПоследних вернет все три строки (ведь у них разные партии), условие ТЗ.Партия = "" ИЛИ ТЗ.Партия = Срез.Партия для второй строки ТЗ вернет истину для всех трех записей регистра. В итоге строка с пустой партией продублируется трижды, выдав цены 100, 150 и 120 рублей одновременно. Это классическая ошибка дублирования строк при левом соединении.
Иногда у начинающих разработчиков возникает соблазн написать динамический выбор первой строки прямо внутри секции соединения ПО, используя конструкцию вида:
ВЫБРАТЬ
Док.Товар,
Рег.Цена
ИЗ
Документ.МойДокумент.Товары КАК Док
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК Рег
ПО Док.Товар = Рег.Товар
И Рег.Партия В
(ВЫБРАТЬ ПЕРВЫЕ 1
Подрег.Партия
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК Подрег
ГДЕ
Подрег.Товар = Док.Товар
УПОРЯДОЧИТЬ ПО
Подрег.Период УБЫВ)
Этот запрос является некорректным и вызовет ошибку на этапе выполнения по двум критическим причинам:
ПО. Вложенный запрос во фрагменте выше обращается к полю таблицы верхнего уровня Док.Товар, что вызовет синтаксическую ошибку компиляции запроса.Выяснив, как делать нельзя, перейдем к рассмотрению трех архитектурно правильных и производительных способов решения этой задачи.
Поскольку стандартная виртуальная таблица нам не подходит из-за жесткой привязки ко всем измерениям, мы можем самостоятельно эмулировать получение среза последних. Для этого мы сначала найдем максимальные даты (периоды) актуальных записей для каждой строки нашей исходной таблицы, а затем получим ресурсы по этим датам.
Рассмотрим этот алгоритм по шагам.
Шаг 1. Помещаем исходную таблицу во временную таблицу
Для обеспечения быстродействия и возможности повторного использования данных сначала поместим нашу исходную таблицу (например, табличную часть документа или переданную ТЗ) во временную таблицу ВТ_ИсходныеДанные.
ВЫБРАТЬ
Исходные.Товар КАК Товар,
Исходные.Партия КАК Партия
ПОМЕСТИТЬ ВТ_ИсходныеДанные
ИЗ
&ТаблицаТоваров КАК Исходные
;
Шаг 2. Вычисление максимальных дат (периодов)
Теперь мы связываем нашу временную таблицу с физической (не виртуальной!) таблицей регистра сведений. Главная хитрость заключается в группировке по полям исходной таблицы и получении агрегатной функции МАКСИМУМ(Период) с учетом того, заполнена ли партия:
ВЫБРАТЬ
Исходные.Товар КАК Товар,
Исходные.Партия КАК Партия,
МАКСИМУМ(Рег.Период) КАК Период
ПОМЕСТИТЬ ВТ_МаксимальныеДаты
ИЗ
ВТ_ИсходныеДанные КАК Исходные
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК Рег
ПО Рег.Товар = Исходные.Товар
И (Исходные.Партия = "" ИЛИ Рег.Партия = Исходные.Партия)
И Рег.Период <= &ПериодГраница
СГРУППИРОВАТЬ ПО
Исходные.Товар,
Исходные.Партия
;
Благодаря условию (Исходные.Партия = "" ИЛИ Рег.Партия = Исходные.Партия) мы получаем:
Шаг 3. Получение значений ресурсов из регистра
На финальном шаге мы соединяем полученную таблицу ВТ_МаксимальныеДаты обратно с физической таблицей регистра сведений по полям Товар, Период и по нашему условию соответствия партии, чтобы вытащить ресурс Цена:
ВЫБРАТЬ
МаксДаты.Товар КАК Товар,
МаксДаты.Партия КАК Партия,
Рег.Цена КАК Цена
ИЗ
ВТ_МаксимальныеДаты КАК МаксДаты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК Рег
ПО Рег.Товар = МаксДаты.Товар
И Рег.Период = МаксДаты.Период
И (МаксДаты.Партия = "" ИЛИ Рег.Партия = МаксДаты.Партия)
Данный метод абсолютно надежен, полностью решает проблему дублирования и возвращает ровно одну строку на каждую строку исходной таблицы.
Хотя первый вариант универсален, у него есть один недостаток с точки зрения оптимизации СУБД. Конструкция ИЛИ в условиях соединения (Исходные.Партия = "" ИЛИ Рег.Партия = Исходные.Партия) мешает оптимизатору запросов SQL правильно использовать индексы таблиц. На больших объемах данных (сотни тысяч и миллионы строк) это может приводить к сканированию индекса (Index Scan) вместо быстрого поиска (Index Seek).
Чтобы добиться максимальной производительности, мы можем применить паттерн разделения потоков данных на "заполненные" и "незаполненные", обработать их оптимальным для каждого случая путем, а затем объединить результаты с помощью ОБЪЕДИНИТЬ ВСЕ.
Проанализируем этот изящный алгоритм:
// Шаг 1. Отбираем строки, где измерение Партия заполнено.
// Для них мы можем сделать прямое и очень быстрое соединение с виртуальной таблицей!
ВЫБРАТЬ
Исходные.Товар КАК Товар,
Исходные.Партия КАК Партия
ПОМЕСТИТЬ ВТ_СтрокиСПартией
ИЗ
ВТ_ИсходныеДанные КАК Исходные
ГДЕ
Исходные.Партия <> ""
;
// Шаг 2. Отбираем строки, где Партия пустая.
ВЫБРАТЬ
Исходные.Товар КАК Товар,
"" КАК Партия
ПОМЕСТИТЬ ВТ_СтрокиБезПартии
ИЗ
ВТ_ИсходныеДанные КАК Исходные
ГДЕ
Исходные.Партия = ""
;
// Шаг 3. Получаем срез последних для строк с заполненными партиями (работает мгновенно по индексу)
ВЫБРАТЬ
СтрокиПартия.Товар КАК Товар,
СтрокиПартия.Партия КАК Партия,
Срез.Цена КАК Цена
ПОМЕСТИТЬ ВТ_РезультатСПартией
ИЗ
ВТ_СтрокиСПартией КАК СтрокиПартия
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ПериодГраница, ) КАК Срез
ПО Срез.Товар = СтрокиПартия.Товар
И Срез.Партия = СтрокиПартия.Партия
;
// Шаг 4. Находим максимальные периоды только по Товару для строк без партии
ВЫБРАТЬ
СтрокиБезПартии.Товар КАК Товар,
МАКСИМУМ(Рег.Период) КАК Период
ПОМЕСТИТЬ ВТ_МаксДатыБезПартии
ИЗ
ВТ_СтрокиБезПартии КАК СтрокиБезПартии
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК Рег
ПО Рег.Товар = СтрокиБезПартии.Товар
И Рег.Период <= &ПериодГраница
СГРУППИРОВАТЬ ПО
СтрокиБезПартии.Товар
;
// Шаг 5. Получаем цену по найденным датам для пустых партий
ВЫБРАТЬ
МаксДаты.Товар КАК Товар,
"" КАК Партия,
Рег.Цена КАК Цена
ПОМЕСТИТЬ ВТ_РезультатБезПартии
ИЗ
ВТ_МаксДатыБезПартии КАК МаксДаты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК Рег
ПО Рег.Товар = МаксДаты.Товар
И Рег.Период = МаксДаты.Период
;
// Шаг 6. Объединяем оба потока данных в один результирующий набор
ВЫБРАТЬ Товар, Партия, Цена ИЗ ВТ_РезультатСПартией
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ Товар, Партия, Цена ИЗ ВТ_РезультатБезПартии
Несмотря на то, что код пакета запросов стал длиннее, с точки зрения СУБД этот запрос выполнится в разы (а иногда и в десятки раз) быстрее на крупных базах данных. Мы полностью исключили условие ИЛИ из связей и позволили планировщику SQL применить оптимальные индексы по полям Товар + Партия + Период.
Если ваша задача решается в рамках построения отчета или динамического списка с использованием Системы компоновки данных (СКД), то писать сложные конструкции во временных таблицах вообще не требуется. СКД предоставляет встроенный интеллектуальный механизм связи наборов данных, который берет на себя всю рутину по оптимизации и исключению дублирования.
Выясним, как настроить такую схему связи:
Товар и Партия.
ВЫБРАТЬ
Срез.Товар КАК Товар,
Срез.Партия КАК Партия,
Срез.Цена КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, Товар = &ПараметрТовар И Партия = &ПараметрПартия) КАК Срез
&ПараметрТовар и &ПараметрПартия прямо внутри параметров виртуальной таблицы.НаборДанных1НаборДанных2ТоварТоварТовар укажите имя параметра ПараметрТовар.НаборДанных1НаборДанных2ПартияПартияПараметрПартия.При выполнении отчета СКД самостоятельно проанализирует каждую строку первого набора данных и выполнит запросы к базе данных оптимальными пачками (передавая параметры списком), гарантируя получение ровно одной актуальной записи из среза последних для каждой строки отчета.
Выбор конкретного метода зависит от архитектурного контекста вашей задачи: