Как выбрать последние записи из периодического регистра сведений для каждой строки документа при ЛЕВОМ СОЕДИНЕНИИ в 1С?

← На главную

При работе с данными в 1С часто возникает задача получить актуальные (последние на определенную дату) значения из периодических регистров сведений, например, цены номенклатуры на дату документа. Если просто соединить документ с регистром по номенклатуре и условию "период регистра <= дата документа", то мы столкнемся с проблемой избыточных данных: запрос вернет все записи регистра, удовлетворяющие условию, а не только самую последнюю.

В этой статье мы подробно разберем, как эффективно решить эту проблему, рассмотрим различные подходы, проанализируем их преимущества и недостатки, а также предложим оптимальное решение.

Исходная проблема: Избыточные данные при ЛЕВОМ СОЕДИНЕНИИ

Предположим, у нас есть документ "РеализацияТоваровУслуг" с табличной частью "Товары", и мы хотим получить для каждой строки документа приходную цену из регистра сведений "ЦеныНоменклатуры" на дату документа. Изначальный, но некорректный запрос может выглядеть так:


ВЫБРАТЬ
    ДокРеализацияТоваровУслуг.Номенклатура,
    ДокРеализацияТоваровУслуг.Цена,
    ЕСТЬNULL(РегЦеныНоменклатуры.Цена, 0) КАК ЦенаПриходная
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК ДокРеализацияТоваровУслуг
ЛЕВОЕ СОЕДИНЕНИЕ 
    РегистрСведений.ЦеныНоменклатуры КАК РегЦеныНоменклатуры
    ПО (РегЦеныНоменклатуры.Номенклатура = ДокРеализацияТоваровУслуг.Номенклатура) И 
       (РегЦеныНоменклатуры.Период <= ДокРеализацияТоваровУслуг.Ссылка.Дата) И
       (РегЦеныНоменклатуры.ТипЦен = &МакрЦенаПриходная)

В этом запросе мы соединяем документ со всеми записями регистра цен, у которых период меньше или равен дате документа. Если для одной номенклатуры к одной дате документа существует несколько записей в регистре (например, цена менялась несколько раз до этой даты), то в результат запроса попадут все эти записи, что приведет к дублированию строк документа. Нам же нужна только одна, самая последняя, актуальная запись.

Подход 1: Использование коррелирующего подзапроса в условии соединения

Одним из способов решения является использование подзапроса внутри условия соединения (оператора ПО), который для каждой строки документа будет находить максимальный период (самую последнюю дату) записи в регистре, соответствующую условиям. Затем мы соединяем регистр именно по этому максимальному периоду.

Рассмотрим пример реализации такого запроса, как это было предложено в одном из сообщений форума:


ВЫБРАТЬ
    ДокРеализацияТоваровУслуг.Номенклатура,
    ДокРеализацияТоваровУслуг.Цена,
    ЕСТЬNULL(РегЦеныНоменклатуры.Цена, 0) КАК ЦенаПриходная
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК ДокРеализацияТоваровУслуг
ЛЕВОЕ СОЕДИНЕНИЕ
    РегистрСведений.ЦеныНоменклатуры КАК РегЦеныНоменклатуры
    ПО (РегЦеныНоменклатуры.Номенклатура = ДокРеализацияТоваровУслуг.Номенклатура) И
       (РегЦеныНоменклатуры.ТипЦен = &МакрЦенаПриходная) И
       (РегЦеныНоменклатуры.Период В (
            ВЫБРАТЬ ПЕРВЫЕ 1
                Период
            ИЗ
                РегистрСведений.ЦеныНоменклатуры
            ГДЕ
                (Номенклатура = ДокРеализацияТоваровУслуг.Номенклатура) И
                (Период <= ДокРеализацияТоваровУслуг.Ссылка.Дата) И
                (ТипЦен = &МакрЦенаПриходная)
            УПОРЯДОЧИТЬ ПО
                Период УБЫВ
       ))

Как работает этот подход?

Для каждой строки табличной части ДокРеализацияТоваровУслуг выполняется подзапрос.
Этот подзапрос ищет в РегистрСведений.ЦеныНоменклатуры все записи, соответствующие текущей номенклатуре документа, типу цен и дате (период записи <= дата документа).
Затем результаты подзапроса сортируются по полю Период в убывающем порядке.
Оператор ВЫБРАТЬ ПЕРВЫЕ 1 возвращает только самую последнюю запись (то есть максимальный период).
Основной запрос соединяется с РегистрСведений.ЦеныНоменклатуры только по тем записям, чей Период совпадает с найденным максимальным периодом из подзапроса.

Преимущества и недостатки коррелирующих подзапросов

Преимущества:

Компактность и относительная читаемость кода (на первый взгляд).
Решает поставленную задачу – получаем только одну, актуальную запись.

Недостатки:

Производительность: Это главное узкое место. Коррелирующий подзапрос, особенно в условиях соединения (ПО), выполняется для каждой строки основной таблицы. На больших объемах данных (тысячи и более строк в документе или регистре) такой подход может привести к катастрофическому падению производительности, замедляя выполнение запроса в десятки и сотни раз. Оптимизатор СУБД не всегда может эффективно справиться с такими конструкциями.
Сложность для оптимизатора: Сложно предсказать план выполнения запроса, и СУБД может выбрать неоптимальный путь.

В некоторых случаях, когда объем данных невелик, и запрос выполняется редко, такой подход может быть приемлемым. Однако для большинства боевых систем, особенно в документах с большим количеством строк, мы настоятельно рекомендуем избегать коррелирующих подзапросов в условиях соединения.

Оптимизация коррелирующего подзапроса (не рекомендуется как основной метод)

Даже если вы вынуждены использовать коррелирующий подзапрос, есть небольшие улучшения:

Вместо ВЫБРАТЬ ПЕРВЫЕ 1 Период ... УПОРЯДОЧИТЬ ПО Период УБЫВ можно использовать ВЫБРАТЬ МАКСИМУМ(Период). Это может быть немного быстрее, так как не требует полной сортировки, если СУБД хорошо оптимизирует агрегатные функции.
Переносите все возможные фильтры (например, Номенклатура = ДокРеализацияТоваровУслуг.Номенклатура и ТипЦен = &МакрЦенаПриходная) внутрь подзапроса. Это уменьшит объем данных, с которым работает подзапрос.


// Пример оптимизированного, но все еще потенциально медленного коррелирующего подзапроса
// НЕ рекомендуется для больших объемов данных
ВЫБРАТЬ
    ДокРеализацияТоваровУслуг.Номенклатура,
    ДокРеализацияТоваровУслуг.Цена,
    ЕСТЬNULL(РегЦеныНоменклатуры.Цена, 0) КАК ЦенаПриходная
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК ДокРеализацияТоваровУслуг
ЛЕВОЕ СОЕДИНЕНИЕ
    РегистрСведений.ЦеныНоменклатуры КАК РегЦеныНоменклатуры
    ПО (РегЦеныНоменклатуры.Номенклатура = ДокРеализацияТоваровУслуг.Номенклатура) И
       (РегЦеныНоменклатуры.ТипЦен = &МакрЦенаПриходная) И
       (РегЦеныНоменклатуры.Период = (
            ВЫБРАТЬ МАКСИМУМ(ПодзапросЦены.Период)
            ИЗ
                РегистрСведений.ЦеныНоменклатуры КАК ПодзапросЦены
            ГДЕ
                ПодзапросЦены.Номенклатура = ДокРеализацияТоваровУслуг.Номенклатура И
                ПодзапросЦены.Период <= ДокРеализацияТоваровУслуг.Ссылка.Дата И
                ПодзапросЦены.ТипЦен = &МакрЦенаПриходная
       ))

Подход 2: Срез последних на каждую дату с использованием временных таблиц (рекомендуемый метод)

Наиболее производительным и идиоматичным в 1С для решения задачи "среза последних на каждую дату" является многошаговый запрос с использованием временных таблиц. Этот подход позволяет СУБД более эффективно построить план выполнения запроса, так как каждый шаг обрабатывается отдельно, а результаты сохраняются во временных таблицах.

Мы разобьем задачу на несколько этапов:

Подготовим список уникальных номенклатур и дат документов, для которых нам нужны цены.
Для каждой из этих пар "номенклатура-дата" найдем максимальный период в регистре цен, который не превышает дату документа.
Используя найденные максимальные периоды, получим сами значения цен из регистра.
Финально соединим полученные цены с исходными данными документа.

Давайте разберем каждый шаг подробно с примером кода.

Шаг 1: Подготовка данных документа во временную таблицу (`ВТ_НоменклатураИДаты`)

Сначала мы собираем все уникальные сочетания Номенклатура и ДатаДокумента из табличной части документа, а также передаем нужный ТипЦен. Это уменьшает количество записей, которые придется обрабатывать на следующих шагах, и избегает лишних расчетов.


ВЫБРАТЬ РАЗЛИЧНЫЕ
    ДокРеализацияТоваровУслуг.Ссылка.Дата КАК ДатаДокумента,
    ДокРеализацияТоваровУслуг.Номенклатура КАК Номенклатура,
    &МакрЦенаПриходная КАК ТипЦен
ПОМЕСТИТЬ ВТ_НоменклатураИДаты
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК ДокРеализацияТоваровУслуг
;

Оператор РАЗЛИЧНЫЕ важен здесь, если одна и та же номенклатура встречается в документе несколько раз с одной и той же датой.

Шаг 2: Определение максимального периода цен (`ВТ_МаксимальныеПериодыЦен`)

На этом шаге мы берем каждую запись из ВТ_НоменклатураИДаты и соединяем ее с РегистрСведений.ЦеныНоменклатуры. Условие РегЦеныНоменклатуры.Период <= ВТ_НоменклатураИДаты.ДатаДокумента гарантирует, что мы рассматриваем только те цены, которые действовали на или до даты документа. Затем мы группируем результаты по Номенклатура и ДатаДокумента и находим МАКСИМУМ(РегЦеныНоменклатуры.Период). Это дает нам точный период, на который нам нужна цена.


ВЫБРАТЬ
    ВТ_НоменклатураИДаты.Номенклатура,
    ВТ_НоменклатураИДаты.ДатаДокумента,
    МАКСИМУМ(РегЦеныНоменклатуры.Период) КАК Период
ПОМЕСТИТЬ ВТ_МаксимальныеПериодыЦен
ИЗ
    ВТ_НоменклатураИДаты КАК ВТ_НоменклатураИДаты
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК РегЦеныНоменклатуры
        ПО ВТ_НоменклатураИДаты.Номенклатура = РегЦеныНоменклатуры.Номенклатура
        И ВТ_НоменклатураИДаты.ТипЦен = РегЦеныНоменклатуры.ТипЦен
        И РегЦеныНоменклатуры.Период <= ВТ_НоменклатураИДаты.ДатаДокумента
СГРУППИРОВАТЬ ПО
    ВТ_НоменклатураИДаты.Номенклатура,
    ВТ_НоменклатураИДаты.ДатаДокумента
;

Обратите внимание на использование ЛЕВОЕ СОЕДИНЕНИЕ, чтобы гарантировать, что мы сохраняем все строки из ВТ_НоменклатураИДаты, даже если для какой-то номенклатуры цена не найдена.

Шаг 3: Получение самих цен (`ВТ_ЦеныНаДатыДокументов`)

Теперь, когда у нас есть точные максимальные периоды для каждой номенклатуры и даты документа, мы можем легко получить саму цену. Мы соединяем ВТ_МаксимальныеПериодыЦен обратно с РегистрСведений.ЦеныНоменклатуры, используя точное равенство по Номенклатура, ТипЦен и Период. Это гарантирует, что мы берем именно ту запись, которая соответствует найденному максимальному периоду.


ВЫБРАТЬ
    ВТ_МаксимальныеПериодыЦен.Номенклатура,
    ВТ_МаксимальныеПериодыЦен.ДатаДокумента,
    РегЦеныНоменклатуры.Цена КАК ЦенаПриходная
ПОМЕСТИТЬ ВТ_ЦеныНаДатыДокументов
ИЗ
    ВТ_МаксимальныеПериодыЦен КАК ВТ_МаксимальныеПериодыЦен
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК РегЦеныНоменклатуры
        ПО ВТ_МаксимальныеПериодыЦен.Номенклатура = РегЦеныНоменклатуры.Номенклатура
        И ВТ_МаксимальныеПериодыЦен.Период = РегЦеныНоменклатуры.Период
        И &МакрЦенаПриходная = РегЦеныНоменклатуры.ТипЦен // Важно: тип цен также должен быть в условиях
ГДЕ
    ВТ_МаксимальныеПериодыЦен.Период ЕСТЬ НЕ NULL // Исключаем строки, для которых цены не были найдены
;

Условие ГДЕ ВТ_МаксимальныеПериодыЦен.Период ЕСТЬ НЕ NULL отфильтровывает те строки, для которых на предыдущем шаге не было найдено ни одной подходящей записи цены (т.е. МАКСИМУМ(Период) вернул NULL).

Шаг 4: Финальное соединение с исходным документом

Последний шаг – это присоединение полученных актуальных цен к исходным данным документа Документ.РеализацияТоваровУслуг.Товары. Мы используем ЛЕВОЕ СОЕДИНЕНИЕ, чтобы убедиться, что все строки документа будут в результате, даже если для какой-то номенклатуры цена не была найдена.


ВЫБРАТЬ
    ДокРеализацияТоваровУслуг.Номенклатура,
    ДокРеализацияТоваровУслуг.Цена,
    ЕСТЬNULL(ВТ_ЦеныНаДатыДокументов.ЦенаПриходная, 0) КАК ЦенаПриходная
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК ДокРеализацияТоваровУслуг
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ЦеныНаДатыДокументов КАК ВТ_ЦеныНаДатыДокументов
        ПО ДокРеализацияТоваровУслуг.Номенклатура = ВТ_ЦеныНаДатыДокументов.Номенклатура
        И ДокРеализацияТоваровУслуг.Ссылка.Дата = ВТ_ЦеныНаДатыДокументов.ДатаДокумента

Преимущества многошагового запроса с временными таблицами

Высокая производительность: Это наиболее эффективный подход для получения среза последних на каждую дату, особенно на больших объемах данных. Каждый шаг запроса оптимизируется СУБД отдельно, что приводит к лучшему плану выполнения.
Масштабируемость: Хорошо работает с возрастающим объемом данных.
Понятность: Запрос становится более структурированным и легче читаемым, если понимать логику каждого шага.
Стандартная практика: Широко используется в типовых конфигурациях и рекомендован 1С для сложных запросов.

Альтернативные методы для отчетов (СКД)

В системе компоновки данных (СКД) для решения подобных задач существуют свои специфические механизмы, которые позволяют достичь аналогичного результата более декларативно. Например, можно использовать два набора данных: один для документов, другой для цен. В набор данных с ценами можно передать в параметр виртуальной таблицы СрезПоследних поле с датой из первого набора данных. СКД самостоятельно построит оптимальный запрос, обычно используя механизмы, схожие с временными таблицами.

Однако, если мы формируем "тупой" запрос без СКД (например, в модуле объекта или обработке), то подход с временными таблицами остается наиболее универсальным и эффективным.

Общие рекомендации по оптимизации запросов в 1С

Независимо от выбранного подхода, мы всегда должны помнить о некоторых общих принципах оптимизации запросов в 1С:

Параметры виртуальных таблиц: Всегда используем параметры виртуальных таблиц (например, СрезПоследних(&ДатаСреза, Отбор)) вместо отборов через ГДЕ, когда это возможно. Это позволяет виртуальной таблице выполнить предварительную фильтрацию, работая с меньшим объемом данных.
Избегайте разыменования полей составного типа: Если поле имеет составной тип (например, Номенклатура может быть Справочник.Номенклатура или Документ.Оприходование), то при обращении к его реквизитам через точку (например, Номенклатура.ВидНоменклатуры) 1С может выполнять дополнительные запросы. Используйте оператор ВЫРАЗИТЬ, если тип заранее известен, или избегайте разыменования, если это не критично для логики.
Индексирование: Убедитесь, что поля, используемые в условиях ГДЕ и ПО, а также в УПОРЯДОЧИТЬ ПО и СГРУППИРОВАТЬ ПО, проиндексированы. В 1С платформе большинство стандартных полей регистров и табличных частей документов уже имеют индексы, но для пользовательских реквизитов или в специфических случаях их может не хватать.
Анализ плана запроса: Используйте инструменты отладки или консоли запросов для просмотра плана выполнения запроса. Это поможет вам понять, какие операции занимают больше всего времени и где находятся узкие места.
Использование временных таблиц: Не бойтесь использовать временные таблицы для разбиения сложных запросов на более мелкие, управляемые части. Это часто приводит к значительному приросту производительности.

Заключение

Выбор правильного метода для получения последних записей из периодического регистра сведений при соединении критически важен для производительности систем на 1С. Хотя коррелирующий подзапрос может показаться интуитивно понятным, его использование в условиях соединения часто приводит к серьезным проблемам. Мы настоятельно рекомендуем использовать многошаговый запрос с временными таблицами, который имитирует логику "среза последних на каждую дату". Этот метод является более надежным, производительным и масштабируемым для большинства реальных сценариев.

Изучая и применяя эти методы, мы сможем создавать более эффективные и быстрые запросы в наших 1С-системах.