Как в запросе 1С получить данные из регистра сведений на каждую дату из таблицы?

Разбор проблемы и постановка задачи

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

Стандартная виртуальная таблица СрезПоследних с фиксированным параметром даты не позволяет получить разные срезы для разных строк за один простой запрос. Рассмотрим основные методы решения этой задачи и разберем примеры кода.

Способ 1. Соединение временных таблиц с поиском максимального периода

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

Рассмотрим пошагово выполнение такого запроса:

1. Формируем временную таблицу Т_Продажи с исходными данными (номенклатура и дата события). Индексируем поля связи.
2. Формируем или выбираем данные из регистра сведений во временную таблицу Т_Цены.
3. Связываем исходные данные с ценами и находим максимальную дату записи цены, которая не превышает дату продажи. Помещаем результат во временную таблицу ВТ_МаксДаты.
4. Соединяем ВТ_МаксДаты с таблицей цен повторно — по номенклатуре и найденной максимальной дате, чтобы получить итоговое значение цены.

Проанализируем пример запроса:

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_МаксДаты.Период КАК Период,
    ВТ_МаксДаты.Номенклатура КАК Номенклатура,
    Т_Цены.Цена КАК Цена
ИЗ
    ВТ_МаксДаты КАК ВТ_МаксДаты
    ЛЕВОЕ СОЕДИНЕНИЕ Т_Цены КАК Т_Цены
        ПО ВТ_МаксДаты.Номенклатура = Т_Цены.Номенклатура
        И ВТ_МаксДаты.ПериодЦены = Т_Цены.Период;

Способ 2. Использование неконстантного выражения в параметре «СрезПоследних»

Начиная с платформы 1С:Предприятие 8.3, язык запросов поддерживает передачу полей других таблиц пакета в параметр Период виртуальной таблицы СрезПоследних.

Посмотрим, как выглядит такой запрос:

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

Важные особенности и ограничения данного метода:

1. Поле даты, передаваемое в параметр виртуальной таблицы, не должно иметь составной тип данных.
2. СУБД при таком подходе выполняет коррелированный подзапрос для каждой строки. На больших объемах данных (особенно в PostgreSQL) это может привести к снижению производительности.

Способ 3. Интервальный метод (метод интервалов действия)

Если регистр сведений содержит сотни тысяч или миллионы записей, соединение «все со всеми» по условию Период >= Период вызывает декартово произведение и перегружает СУБД. В этом случае оптимально использовать метод интервалов.

Рассмотрим алгоритм интервального метода:

1. Из регистра сведений формируем таблицу интервалов, где для каждой записи рассчитывается дата начала (ПериодНачала) и дата окончания действия (ПериодОкончания — дата следующей записи по данному измерению).
2. Выполняем простое левое соединение исходной таблицы с таблицей интервалов по условию вхождения даты события в интервал: ДатаСобытия >= Интервал.ПериодНачала И (ДатаСобытия < Интервал.ПериодОкончания ИЛИ Интервал.ПериодОкончания ЕСТЬ NULL).

Рекомендации по повышению производительности

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

1. Индексация: всегда добавляем ключевое слово ИНДЕКСИРОВАТЬ ПО для временных таблиц по полям, участвующим в связях (например, Номенклатура, Период).
2. Предварительный отбор: перед соединением с регистром обязательно отбираем из регистра только те измерения, которые присутствуют в нашей исходной таблице, чтобы не обрабатывать лишнюю историю.