Срез на даты за период. Шаблон запроса

Введение

Довольно часто возникает задача в запросе получить актуальное значение ресурса на каждую дату выбранного периода.

Вот несколько таких случаев:

• Курс валюты на дату каждого документа за выбранный период
• Цену товара на дату реализации по всем реализациям за период
• Должность сотрудника на каждую дату периода

Традиционный подход

Наиболее интуитивный и часто применяемый способ следующий:

/// первый запрос - выбирает все даты документов

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

/// второй запрос - выбирает из нашего регистра сведений измерения и максимальный период на каждую дату
ВЫБРАТЬ
	ВТ_ДатыДокументов.ДатаДокумента,
	Цены.Номенклатура,
	МАКСИМУМ(Цены.Период) КАК ДатаЦены
ПОМЕСТИТЬ 
	ВТ_ДатыНачалаДействия
ИЗ 
	ВТ_ДатыДокументов КАК ВТ_ДатыДокументов
	ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК Цены
    	ПО Цены.Период <= ВТ_ДатыДокументов.ДатаДокумента
	
ГДЕ
	Цены.ТипЦены = &ТипЦены // например, такой отбор, или какой-нибудь другой

СГРУППИРОВАТЬ ПО
	ВТ_ДатыДокументов.ДатаДокумента,
	Цены.Номенклатура
;

/// третий запрос - выбирает из нашего регистра актуальные значения ресурсов на каждую дату документа
ВЫБРАТЬ
	ВТ_ДатыНачалаДействия.ДатаДокумента,
	Цены.Номенклатура,
	Цены.Цена
	
ПОМЕСТИТЬ 
	ВТ_ЗначенияРесурсов
ИЗ 
	РегистрСведений.ЦеныНоменклатуры КАК Цены
	ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ДатыНачалаДействия КАК ВТ_ДатыНачалаДействия
    	ПО ВТ_ДатыНачалаДействия.Период = Цены.Период
    	И ВТ_ДатыНачалаДействия.Номенклатура = Цены.Номенклатура
ГДЕ
	Цены.ТипЦены = &ТипЦены // не забываем еще раз указать тот же отбор
;

/// четвертый запрос - основной, который получает нужные данные из временной таблицы, полученной на предыдущем шаге
ВЫБРАТЬ
	Товары.Ссылка КАК Документ,
	Товары.Номенклатура,
	ВТ_ЗначенияРесурсов.Цена * Товары.Количество КАК Сумма // тут делаем что-то нужное нам с полученными значениями
ИЗ 
	Документ.Реализация.Товары КАК Товары
	ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ЗначенияРесурсов КАК ВТ_ЗначенияРесурсов
    	ПО Товары.Номенклатура = ВТ_ЗначенияРесурсов.Номенклатура
    	И НАЧАЛОПЕРИОДА(Товары.Ссылка.Дата, ДЕНЬ) = ВТ_ЗначенияРесурсов.ДатаДокумента

Более продвинутые программисты могут объединить третий и четвертый шаг, что сократит количество временных таблиц. Но в примере оставим четыре шага ради большей наглядности

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

Но последовательность шагов обычно такая, как в этом типовом примере.

Я и сам по началу долгое время использовал именно такую схему.

Но данная схема неудобна по следующим причинам:

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

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

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

Подход со слабой связностью 

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

/// первый запрос - вытаскиваем из регистра сведений все что нужно за один раз
ВЫБРАТЬ
    Период,
    Номенклатура,
    Цена
ПОМЕСТИТЬ ВТ_Срез
ИЗ 
    РегистрСведений.ЦеныНоменклатуры КАК Движения
ГДЕ 
    Период > &НачалоПериода
    И Период <= &КонецПериода
    И ТипЦены = &ТипЦены // например, такой отбор, или какой-нибудь другой

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    Период,
    Номенклатура,
    Цена
ИЗ 
    РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&НачалоПериода, ТипЦены = &ТипЦены // тут надо повторить отбор
		) КАК Срез
ИНДЕКСИРОВАТЬ ПО
    Движения.Номенклатура,
    Движения.Период

;
/// второй запрос - собираем таблицу с периодами действия ресурса
ВЫБРАТЬ
    НачалоПериода.Номенклатура,
    НачалоПериода.Цена,
    НачалоПериода.Период КАК НачалоПериода,
    ЕСТЬNULL(ДОБАВИТЬКДАТЕ(МИНИМУМ(КонецПериода.Период), СЕКУНДА, -1), &КонецПериода) КАК КонецПериода
ПОМЕСТИТЬ ВТ_ЗначенияРесурсов
ИЗ
    ВТ_Срез КАК НачалоПериода
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Срез КАК КонецПериода
        ПО НачалоПериода.Номенклатура = КонецПериода.Номенклатура
        И НачалоПериода.Период < КонецПериода.Период

СГРУППИРОВАТЬ ПО
    НачалоПериода.Период,
    НачалоПериода.Номенклатура,
    НачалоПериода.Цена
;

/// третий запрос - основной, который получает нужные данные из временной таблицы, полученной на предыдущем шаге
ВЫБРАТЬ
	Товары.Ссылка КАК Документ,
	Товары.Номенклатура,
	ВТ_ЗначенияРесурсов.Цена * Товары.Количество КАК Сумма // тут делаем что-то нужное нам с полученными значениями
ИЗ 
	Документ.Реализация.Товары КАК Товары
	ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ЗначенияРесурсов КАК ВТ_ЗначенияРесурсов
    	ПО Товары.Номенклатура = ВТ_ЗначенияРесурсов.Номенклатура
    	И Товары.Ссылка.Дата МЕЖДУ ВТ_ЗначенияРесурсов.НачалоПериода И ВТ_ЗначенияРесурсов.КонецПериода

Плюсы интервального способа:

1. Первые два шага довольно простые и не связаны с бизнес логикой основного запроса.
2. Можно использовать данный запрос как шаблон, что значительно экономит время при разработке. 

В итоге вся бизнес логика у нас перенеслась на последний запрос (то есть собралась в одно место). Это большой плюс при модификациях основного запроса.

Ну и конечно, в зависимости от задачи можно накладывать дополнительные отборы в первом шаге, чтобы оптимизировать работу запроса.

Заключение

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

Я выложил данный шаблон запроса главным образом для себя, т.к. довольно часто он пригождается. Но будет неплохо, если кто-нибудь сочтет его подходящим для себя.