Как в запросе 1С получить предыдущую дату записи регистра накопления?

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

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

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

ВЫБРАТЬ
    МАКСИМУМ(ПродажиОбороты1.Период) КАК ПредыдущаяДата,
    МИНИМУМ(ВложенныйЗапрос.ПерваяДата) КАК ПерваяДата,
    ВложенныйЗапрос.Контрагент
ИЗ
    (ВЫБРАТЬ
        МИНИМУМ(ПродажиОбороты.Период) КАК ПерваяДата,
        ПродажиОбороты.Контрагент КАК Контрагент
    ИЗ
        РегистрНакопления.Продажи.Обороты(&НП, &КП, Регистратор, ) КАК ПродажиОбороты
    СГРУППИРОВАТЬ ПО
        ПродажиОбороты.Контрагент) КАК ВложенныйЗапрос
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты(, , Регистратор, ) КАК ПродажиОбороты1
        ПО ВложенныйЗапрос.Контрагент = ПродажиОбороты1.Контрагент
        И ВложенныйЗапрос.ПерваяДата > ПродажиОбороты1.Период
СГРУППИРОВАТЬ ПО
    ВложенныйЗапрос.Контрагент

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

1. Избыточное использование виртуальной таблицы оборотов. Виртуальная таблица Обороты предназначена для подсчета агрегированных ресурсов. Если нам требуются только даты движений без ресурсов, обращение к таблице оборотов с периодичностью Регистратор создает неоправданную нагрузку на СУБД.
2. Полное сканирование базы данных. Обращение к РегистрНакопления.Продажи.Обороты(, , Регистратор, ) без указания параметров дат считывает всю историю регистра. Соединение по нестрогому равенству > приводит к квадратичному росту объема обрабатываемых данных.
3. Неоднозначность при совпадении секунд. Поле Период хранит дату и время с точностью до секунды. Если в одну секунду было несколько документов, строгое условие ПерваяДата > Период отсекает все записи этой секунды. Строгое понятие «предыдущей записи» в регистре накопления определяется только с учетом полей Период, Регистратор (или его моментом времени) и НомерСтроки.

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

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

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Продажи.Контрагент КАК Контрагент,
    МАКСИМУМ(Продажи.Период) КАК ПредыдущаяДата
ПОМЕСТИТЬ ВТ_ПредыдущиеДаты
ИЗ
    РегистрНакопления.Продажи КАК Продажи
ГДЕ
    Продажи.Период < &НП
    И Продажи.Контрагент В
        (ВЫБРАТЬ
            ВТ_ПервыеДаты.Контрагент
        ИЗ
            ВТ_ПервыеДаты)
СГРУППИРОВАТЬ ПО
    Продажи.Контрагент
;

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

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

Способ 2. Использование оконных функций для поиска предыдущей записи

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

ВЫБРАТЬ
    Продажи.Контрагент КАК Контрагент,
    Продажи.Период КАК Период,
    LAG(Продажи.Период, 1) OVER (PARTITION BY Продажи.Контрагент ORDER BY Продажи.Период, Продажи.Регистратор) КАК ПредыдущаяДата
ПОМЕСТИТЬ ВТ_ДвиженияСПредыдущейДатой
ИЗ
    РегистрНакопления.Продажи КАК Продажи
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ.Контрагент КАК Контрагент,
    МИНИМУМ(ВТ.Период) КАК ПерваяДата,
    МИНИМУМ(ВТ.ПредыдущаяДата) КАК ПредыдущаяДата
ИЗ
    ВТ_ДвиженияСПредыдущейДатой КАК ВТ
ГДЕ
    ВТ.Период МЕЖДУ &НП И &КП
СГРУППИРОВАТЬ ПО
    ВТ.Контрагент

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