Как в СКД получить срез последних регистра сведений на вычисленную в запросе дату

← На главную

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

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

Почему возникают проблемы с производительностью и потерей строк

Проанализируем две основные проблемы, с которыми сталкиваются разработчики при реализации динамического среза последних:

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

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

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

Разберем алгоритм по шагам:

Подготавливаем исходную временную таблицу с ключевыми полями и вычисленной датой. Обязательно индексируем ее по полям соединения.
Соединяем исходную таблицу левым соединением с физической таблицей регистра по условию совпадения измерений и ограничению периода (Регистр.Период <= Исходная.Дата).
Группируем результат и получаем максимальный период (МАКСИМУМ(Регистр.Период)) для каждой строки исходной таблицы. Помещаем результат во вторую временную таблицу и индексируем ее.
Соединяем промежуточную таблицу с физической таблицей регистра по совпадению измерений и найденному актуальному периоду для получения ресурсов.
Для сохранения строк, по которым цен нет, используем функцию ЕСТЬNULL.

Посмотрим на пример текста запроса:


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

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    Дата
;

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

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    АктуальныйПериод
;

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

Способ 2. Использование коррелированного подзапроса в секции ПО

В некоторых случаях запрос можно сократить, выполнив поиск максимальной даты непосредственно в секции соединения таблиц. Рассмотрим этот вариант:


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

Обратите внимание: данный способ лаконичнее, однако на очень больших массивах данных вариант с пакетом временных таблиц и явной индексацией (Способ 1) работает стабильнее и быстрее.

Способ 3. Оптимизация связей наборов данных в СКД (Список параметров)

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

Разберем, как ускорить эту схему:

В подчиненном наборе данных условие на передаваемые параметры должно принимать список значений, например: ГДЕ Номенклатура В (&Номенклатура).
На вкладке Связи наборов данных в строке настройки связи между наборами необходимо установить флаг Список параметров.

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

Как гарантировать вывод строк с пустыми значениями

Выясним, почему могут пропадать строки, если на вычисленную дату в регистре нет записей, и как это исправить:

Всегда используйте ЛЕВОЕ СОЕДИНЕНИЕ между исходными данными и регистром.
Все условия по таблице регистра сведений должны находиться строго в секции ПО, а не в секции ГДЕ. Условие в блоке ГДЕ отсечет все строки со значениями NULL.
Оборачивайте числовые ресурсы регистра в функцию ЕСТЬNULL(Поле, 0) или строковые в ЕСТЬNULL(Поле, "") непосредственно в списке полей выборки.
Если отбор накладывается в пользовательских настройках СКД, убедитесь, что отбор по ресурсам регистра допускает пустое значение (например, через условие «Не заполнено» или составные группы условий «ИЛИ»).