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

← На главную

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

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

Метод 1: Использование физической таблицы регистра и вложенного запроса

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

Разберем этот процесс по шагам:

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

Пример реализации в тексте запроса:


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

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

Проанализируем этот подход: он надежен, но при огромных объемах данных в регистре соединение "всех со всеми" по условию >= может привести к существенному замедлению. Чтобы оптимизировать работу, рекомендуется предварительно отбирать только нужную номенклатуру во временную таблицу.

Метод 2: Связь двух наборов данных в СКД

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

Набор данных 1: Запрос к документам.

Набор данных 2: Запрос к виртуальной таблице ЦеныНоменклатуры.СрезПоследних.

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

Тем не менее, у этого метода есть и плюсы: чистота кода и использование встроенных механизмов платформы. Если данных немного, этот вариант вполне допустим.

Метод 3: Использование интервальных данных (эффективный подход)

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

Посмотрим на логику построения интервалов:


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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Документы.Номенклатура,
    Документы.Количество,
    Интервалы.Цена
ИЗ
    Документ.ОтчетОПроизводстве КАК Документы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ИнтервалыЦен КАК Интервалы
    ПО Документы.Номенклатура = Интервалы.Номенклатура
    И Документы.Дата >= Интервалы.ДатаНачала
    И Документы.Дата < Интервалы.ДатаОкончания

Этот метод исключает вложенные циклы при выполнении запроса и работает очень быстро даже на миллионах записей. Важно помнить про ЕСТЬNULL для даты окончания (используем максимально возможную дату в системе), чтобы корректно обрабатывать последние (актуальные на данный момент) цены.

Метод 4: Оптимизация через временные таблицы уникальных пар

Чтобы еще больше ускорить отчет, проанализируем ситуацию: в одном месяце может быть 100 документов по одной и той же номенклатуре, но цена менялась всего один раз. Зачем искать цену 100 раз?

Сначала выберем все уникальные комбинации Номенклатура + ДатаДокумента во временную таблицу.
Для этого сокращенного списка получим цены любым из вышеперечисленных способов.
Соединим полученный результат с исходной таблицей документов.

Это сократит объем вычислений в разы, так как количество уникальных пар "Товар-День" обычно существенно меньше общего количества строк в документах.

Важные нюансы и обработка исключений

При работе с ценами в СКД необходимо учитывать следующие моменты:

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

2. Валюты и единицы измерения: Проверьте, в одной ли валюте хранятся цены в регистре. Если валюты разные, в запрос необходимо добавить соединение с регистром курсов валют на дату документа и приводить всё к единому знаменателю.

3. Точность времени: Если регистр сведений имеет периодичность "День", а документы создаются внутри дня с разным временем, стандартное сравнение ДатаДок >= Период работает корректно. Однако, если регистр имеет периодичность "В пределах секунды", убедитесь, что вы сравниваете именно моменты времени, а не только даты.

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