Как получить срез последних на каждую дату в запросе 1С?

В процессе разработки отчетов и алгоритмов проведения документов нередко возникает задача соединения данных регистра накопления или документов с периодическим регистром сведений. Типичный пример: необходимо получить список начислений или движений по периодам и сопоставить каждой строке актуальную цену или тариф на соответствующую дату.

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

Способ 1. Классический пакетный запрос с неэквисоединением и группировкой

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

1. Подготовка таблицы периодов. Собираем уникальные даты (и необходимые измерения), для которых требуется получить срез.
2. Подготовка таблицы значений регистра. Если данные регистра сведений требуется предварительно транспонировать или развернуть по колонкам, формируем отдельную временную таблицу.
3. Поиск актуальной даты среза. Выполняем левое неэквивалентное соединение таблицы дат с таблицей цен по условию Периоды.Период >= Цены.Период и находим МАКСИМУМ(Цены.Период) с группировкой по исходному периоду.
4. Получение итоговых значений. Соединяем полученную таблицу максимальных дат с таблицей регистра сведений по равенству периодов (и измерений).

Посмотрим на пример практической реализации данного подхода в пакетном запросе:

// 1. Разворачиваем регистр цен по типам услуг во временную таблицу
ВЫБРАТЬ
    Запрос.Период КАК Период,
    СУММА(Запрос.Цена_1) КАК Цена_1,
    СУММА(Запрос.Цена_2) КАК Цена_2
ПОМЕСТИТЬ Цены
ИЗ
    (ВЫБРАТЬ
        Цены.Период КАК Период,
        ВЫБОР
            КОГДА Цены.ТипУслуги = ЗНАЧЕНИЕ(Перечисление.ТипУслуги.Услуга_1)
                ТОГДА Цены.Цена
            ИНАЧЕ 0
        КОНЕЦ КАК Цена_1,
        ВЫБОР
            КОГДА Цены.ТипУслуги = ЗНАЧЕНИЕ(Перечисление.ТипУслуги.Услуга_2)
                ТОГДА Цены.Цена
            ИНАЧЕ 0
        КОНЕЦ КАК Цена_2
    ИЗ
        РегистрСведений.Цены КАК Цены) КАК Запрос
СГРУППИРОВАТЬ ПО
    Запрос.Период
ИНДЕКСИРОВАТЬ ПО
    Период
;

////////////////////////////////////////////////////////////////////////////////
// 2. Собираем уникальные периоды начислений
ВЫБРАТЬ РАЗЛИЧНЫЕ
    Начисления.Период КАК Период
ПОМЕСТИТЬ Периоды
ИЗ
    РегистрНакопления.Начисления КАК Начисления
ИНДЕКСИРОВАТЬ ПО
    Период
;

////////////////////////////////////////////////////////////////////////////////
// 3. Вычисляем максимальную дату цены, предшествующую дате начисления
ВЫБРАТЬ
    Периоды.Период КАК Период,
    МАКСИМУМ(Цены.Период) КАК ПериодЦены
ПОМЕСТИТЬ ППЦ
ИЗ
    Периоды КАК Периоды
        ЛЕВОЕ СОЕДИНЕНИЕ Цены КАК Цены
        ПО Периоды.Период >= Цены.Период
СГРУППИРОВАТЬ ПО
    Периоды.Период
ИНДЕКСИРОВАТЬ ПО
    Период,
    ПериодЦены
;

////////////////////////////////////////////////////////////////////////////////
// 4. Итоговая выборка: сопоставляем цены по найденным датам среза
ВЫБРАТЬ
    ППЦ.Период КАК Период,
    Цены.Цена_1 КАК Цена_1,
    Цены.Цена_2 КАК Цена_2
ИЗ
    ППЦ КАК ППЦ
        ЛЕВОЕ СОЕДИНЕНИЕ Цены КАК Цены
        ПО ППЦ.ПериодЦены = Цены.Период

Важные нюансы оптимизации:

1. Индексация. Неэквисоединение по условию >= может приводить к высокой нагрузке на СУБД при больших объемах данных. Обязательно указывайте секцию ИНДЕКСИРОВАТЬ ПО для временных таблиц по полям связи и измерений.
2. Учет измерений. Если регистр цен содержит измерения (например, Номенклатура или ВидЦены), группировка и соединение должны выполняться в разрезе этих измерений.
3. Регистры, подчиненные регистратору. Если регистр сведений непериодический или подчинен регистратору, одной даты для однозначного определения среза недостаточно — необходимо учитывать поля Регистратор и НомерСтроки (момент времени).

Способ 2. Использование оконных функций (платформа 8.3.20 и выше)

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

Идея заключается в нумерации строк с помощью функции ROW_NUMBER() с сортировкой по убыванию даты:

ВЫБРАТЬ
    Периоды.Период КАК Период,
    Цены.Цена КАК Цена,
    ROW_NUMBER() OVER (
        PARTITION BY Периоды.Период 
        ORDER BY Цены.Период УБЫВ
    ) КАК НомерСтрокиЦены
ПОМЕСТИТЬ ЦеныСНомерами
ИЗ
    Периоды КАК Периоды
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Цены КАК Цены
        ПО Периоды.Период >= Цены.Период
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ЦеныСНомерами.Период,
    ЦеныСНомерами.Цена
ИЗ
    ЦеныСНомерами КАК ЦеныСНомерами
ГДЕ
    ЦеныСНомерами.НомерСтрокиЦены = 1

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

Способ 3. Связи наборов данных в СКД

Если задача решается в рамках Системы компоновки данных (СКД), срез на каждую дату реализуется штатными механизмами без сложных конструкций в тексте запроса:

1. Создаем Набор данных 1 с движениями или документами, содержащий поле даты (например, Период).
2. Создаем Набор данных 2, где источником выступает виртуальная таблица РегистрСведений.Цены.СрезПоследних(&ПериодСреза, ).
3. На вкладке «Связи наборов данных» настраиваем соединение: источником связи указываем Набор данных 1, приемником — Набор данных 2. В колонке Параметры передаем поле Период в параметр ПериодСреза.

Платформа 1С в СКД автоматически оптимизирует выполнение запросов пакетами и подставляет актуальную дату для каждой строки отчета.