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

← На главную

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

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

Вариант 1: Каноничное решение через временные таблицы (Оптимальное)

Этот способ считается самым правильным и производительным при написании запросов 1С. Разберем по шагам алгоритм решения:

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

Рассмотрим пример реализации подобного алгоритма в коде:


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

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

ВЫБРАТЬ
    ВТ_МаксПериоды.Дата КАК Дата,
    ВТ_МаксПериоды.Валюта КАК Валюта,
    ЕСТЬNULL(КурсыВалют.Курс, 1) КАК Курс
ИЗ
    ВТ_МаксПериоды КАК ВТ_МаксПериоды
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КурсыВалют КАК КурсыВалют
        ПО ВТ_МаксПериоды.Валюта = КурсыВалют.Валюта
            И ВТ_МаксПериоды.МаксПериод = КурсыВалют.Период

Вариант 2: Использование вложенного запроса (Менее производительное)

Рассмотрим еще один подход, который часто пытаются использовать разработчики. Он заключается в левом соединении с применением конструкции В (ВЫБРАТЬ МАКСИМУМ...). Посмотрим на пример кода:


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

Почему этот вариант считается антипаттерном? При выполнении такого запроса СУБД часто выбирает план выполнения с использованием вложенных циклов (Nested Loops). Это означает, что для каждой строки из левой таблицы (документов или ТЗ) база данных будет заново вычислять подзапрос поиска максимальной даты. Если исходная таблица содержит десятки тысяч строк, производительность многократно упадет, и запрос "зависнет". Этот метод подходит только для очень маленьких объемов данных.

Альтернативный подход: Использование СКД (Система компоновки данных)

Если конечная цель нашей задачи — не программная обработка данных, а вывод информации пользователю в виде отчета, то вместо написания громоздкого многоэтапного текста запроса мы можем элегантно использовать возможности СКД.

Разберем по шагам настройку в СКД:

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

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

Почему в 1С приходится прибегать к таким сложным конструкциям?

Как часто отмечают на профессиональных форумах, язык запросов 1С имеет ограничения по сравнению с чистым SQL (например, T-SQL или PostgreSQL). В "взрослых" СУБД подобная задача решается за один проход с использованием оконных функций. Выражение вида ROW_NUMBER() OVER(PARTITION BY Валюта ORDER BY Период DESC) позволяет сразу пронумеровать записи по убыванию даты в разрезе каждой валюты и отобрать записи с номером 1.

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