При построении отчетов по продажам нередко возникает необходимость сопоставить каждую строку продажи с ценой или себестоимостью, действовавшей именно на момент совершения операции. Основная сложность заключается в том, что цена из периодического регистра сведений действует в течение определенного интервала времени — до момента установки нового значения.
Рассмотрим, почему стандартная виртуальная таблица СрезПоследних не решает эту задачу для каждой строки, разберем типичные ошибки при использовании соединений и выясним, как построить эффективный запрос.
Виртуальная таблица РегистрСведений.ЦеныНоменклатуры.СрезПоследних принимает в качестве параметра Период только фиксированную дату или параметр запроса. Передать в срез поле периода из другой соединяемой таблицы (например, дату конкретной продажи) на уровне языка запросов 1С невозможно.
Если выполнить простое левое соединение регистра сведений с таблицей продаж по условию Продажи.Период >= Цены.Период, мы получим задвоение строк: для одной продажи выберутся абсолютно все цены, установленные до этой даты. Наша цель — отобрать ровно одну запись с максимальной датой, предшествующей или равной дате документа продажи.
Разберем вариант, к которому часто прибегают для быстрого решения задачи. В этом подходе в условие соединения помещается подзапрос, выбирающий ПЕРВЫЕ 1 запись на дату продажи или ранее.
ВЫБРАТЬ
Продажи.Период КАК Период,
Продажи.Контрагент КАК Контрагент,
Продажи.Номенклатура КАК Номенклатура,
Продажи.КоличествоОборот КАК Количество,
Продажи.СтоимостьОборот КАК Стоимость,
ЕСТЬNULL(ЦеныНоменклатуры.Цена, 0) КАК Цена
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, Номенклатура В (&Номенклатура)) КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО Продажи.Номенклатура = ЦеныНоменклатуры.Номенклатура
И (ЦеныНоменклатуры.ТипЦен = &ТипЦен)
И (ЦеныНоменклатуры.Период В
(ВЫБРАТЬ ПЕРВЫЕ 1
Цены.Период
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК Цены
ГДЕ
Цены.Номенклатура = Продажи.Номенклатура
И Цены.Период <= Продажи.Период
И Цены.ТипЦен = &ТипЦен
УПОРЯДОЧИТЬ ПО
Цены.Период УБЫВ))
Важное предостережение: обратите внимание на условие по месяцам, которое иногда ошибочно добавляют разработчики (например, МЕСЯЦ(Цены.Период) = МЕСЯЦ(Продажи.Период)). Если цена была установлена в прошлом месяце и с тех пор не менялась, такое условие вернет NULL (или 0), что приведет к искажению данных. Ограничивать выборку месяцем нельзя.
Кроме того, конструкция с ПЕРВЫЕ 1 внутри условий соединения транслируется СУБД во вложенные циклы (Nested Loops), что на больших объемах данных вызывает серьезные проблемы с производительностью.
Для высоконагруженных систем и отчетов по большим массивам данных рекомендуется использовать классический паттерн вычисления максимального периода через группировку во временных таблицах. Разберем этот алгоритм по шагам:
ВТ_Продажи с обязательной индексацией по полям связи.ВТ_Продажи с физической таблицей регистра сведений по условию Цены.Период <= ВТ_Продажи.Период и берем агрегат МАКСИМУМ(Цены.Период). Результат сохраняем в ВТ_МаксимальныеДаты.ВТ_МаксимальныеДаты с регистром сведений по точному совпадению номенклатуры, типа цен и рассчитанной максимальной даты.
ВЫБРАТЬ РАЗЛИЧНЫЕ
Продажи.Номенклатура КАК Номенклатура,
Продажи.Период КАК Период
ПОМЕСТИТЬ ВТ_ТочкиСреза
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК Продажи
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Период
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Точки.Номенклатура КАК Номенклатура,
Точки.Период КАК Период,
МАКСИМУМ(Цены.Период) КАК ПериодЦены
ПОМЕСТИТЬ ВТ_МаксимальныеДаты
ИЗ
ВТ_ТочкиСреза КАК Точки
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК Цены
ПО Точки.Номенклатура = Цены.Номенклатура
И Цены.Период <= Точки.Период
И (Цены.ТипЦен = &ТипЦен)
СГРУППИРОВАТЬ ПО
Точки.Номенклатура,
Точки.Период
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
ПериодЦены
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Даты.Номенклатура КАК Номенклатура,
Даты.Период КАК Период,
Цены.Цена КАК Цена
ПОМЕСТИТЬ ВТ_ЦеныНаДату
ИЗ
ВТ_МаксимальныеДаты КАК Даты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК Цены
ПО Даты.Номенклатура = Цены.Номенклатура
И Даты.ПериодЦены = Цены.Период
И (Цены.ТипЦен = &ТипЦен)
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Период
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Продажи.Период КАК Период,
Продажи.Номенклатура КАК Номенклатура,
Продажи.КоличествоОборот КАК Количество,
Продажи.СтоимостьОборот КАК Стоимость,
ЕСТЬNULL(Цены.Цена, 0) КАК ЦенаСебестоимости
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ЦеныНаДату КАК Цены
ПО Продажи.Номенклатура = Цены.Номенклатура
И Продажи.Период = Цены.Период
Такой подход полностью исключает задвоение данных и позволяет СУБД строить оптимальные планы выполнения (Merge Join или Hash Match), что гарантирует высокую скорость работы даже на миллионах записей.
Если разработка ведется в рамках отчета на Системе компоновки данных (СКД), писать сложные пакетные запросы вручную не обязательно. Платформа 1С умеет выполнять срез на каждую строку автоматически через механизм связей наборов данных:
Номенклатура, Период, Количество.РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ПериодСреза, ТипЦен = &ТипЦен).Источник связи — НаборДанных1, Приемник связи — НаборДанных2.
Условие связи: Номенклатура = Номенклатура.
В колонке Параметры для параметра ПериодСреза указываем выражение Период (дата строки из первого набора данных).
При таком подходе СКД сама оптимизирует выборку и передает дату каждой строки в виртуальную таблицу среза.