Как в запросе 1С найти ближайшую запись по времени и рассчитать модуль разности дат?

← На главную

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

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

Особенности функции РАЗНОСТЬДАТ и учет знака

Функция РАЗНОСТЬДАТ(Дата1, Дата2, Единица) в языке запросов 1С вычисляет количество указанных единиц времени между двумя датами. При этом результат рассчитывается как Дата2 - Дата1:

Если Дата2 > Дата1, результат положительный.
Если Дата2 < Дата1, результат отрицательный.

В платформе 1С нет встроенной функции ABS() (модуль числа) для языка запросов. Чтобы получить абсолютное значение разности, необходимо реализовать вычисление модуля самостоятельно.

Способ 1. Получение модуля разности через конструкцию ВЫБОР...КОГДА

Проанализируем простейший способ: мы вычисляем разность и, если она отрицательная, меняем ее знак на противоположный либо меняем местами параметры функции РАЗНОСТЬДАТ.

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


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

Разберем по шагам, как работает данный запрос:

В блоке ВЫБОР КОГДА сравнивается дата записи Период и переданный параметр &Время.
Если запись позже целевого времени, мы вычитаем из Период значение &Время, передавая параметры в порядке (&Время, Период).
Если запись раньше или равна целевому времени, мы передаем параметры в обратном порядке (Период, &Время). В обоих случаях мы получаем неотрицательное число.
Конструкция УПОРЯДОЧИТЬ ПО РазностьСекунд ВОЗР сортирует строки от наименьшего расстояния к наибольшему.
Инструкция ПЕРВЫЕ 1 возвращает ту запись, которая находится ближе всего к целевому моменту.

Способ 2. Оптимизированный поиск через объединение выборок ДО и ПОСЛЕ

Рассмотрим важный нюанс производительности. Если в таблице содержится большой объем данных, сортировка по вычисляемому полю РазностьСекунд заставит СУБД просканировать все подходящие строки и выполнить для каждой из них расчет. Индекс по полю Период в сортировке задействован не будет.

Чтобы ускорить выполнение запроса и задействовать индекс СУБД, разделим поиск на две точечные операции: выборку ближайшей записи слева (до момента времени) и справа (после момента времени), после чего сравним только эти две строки.

Посмотрим на реализацию запроса:


ВЫБРАТЬ ПЕРВЫЕ 1
    БлижайшиеЗаписи.Период КАК Период,
    БлижайшиеЗаписи.Пациент КАК Пациент,
    БлижайшиеЗаписи.СостояниеВизита КАК СостояниеВизита,
    БлижайшиеЗаписи.Разность КАК Разность
ИЗ
    (
        // 1. Ближайшая запись ДО указанного времени
        ВЫБРАТЬ ПЕРВЫЕ 1
            ПосещенияДо.Период КАК Период,
            ПосещенияДо.Пациент КАК Пациент,
            ПосещенияДо.СостояниеВизита КАК СостояниеВизита,
            РАЗНОСТЬДАТ(ПосещенияДо.Период, &Время, СЕКУНДА) КАК Разность
        ИЗ
            РегистрСведений.ПосещенияПациентов КАК ПосещенияДо
        ГДЕ
            ПосещенияДо.Период <= &Время
            И ПосещенияДо.Период >= &НачалоПериода
            И ПосещенияДо.Пациент = &Пациент
            И ПосещенияДо.СостояниеВизита = &СостояниеВизита
        УПОРЯДОЧИТЬ ПО
            ПосещенияДо.Период УБЫВ

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

        // 2. Ближайшая запись ПОСЛЕ указанного времени
        ВЫБРАТЬ ПЕРВЫЕ 1
            ПосещенияПосле.Период КАК Период,
            ПосещенияПосле.Пациент КАК Пациент,
            ПосещенияПосле.СостояниеВизита КАК СостояниеВизита,
            РАЗНОСТЬДАТ(&Время, ПосещенияПосле.Период, СЕКУНДА) КАК Разность
        ИЗ
            РегистрСведений.ПосещенияПациентов КАК ПосещенияПосле
        ГДЕ
            ПосещенияПосле.Период >= &Время
            И ПосещенияПосле.Период <= &КонецПериода
            И ПосещенияПосле.Пациент = &Пациент
            И ПосещенияПосле.СостояниеВизита = &СостояниеВизита
        УПОРЯДОЧИТЬ ПО
            ПосещенияПосле.Период ВОЗР
    ) КАК БлижайшиеЗаписи
УПОРЯДОЧИТЬ ПО
    БлижайшиеЗаписи.Разность ВОЗР

Выясним преимущества такого подхода:

Каждая ветка запроса использует индекс по периоду: первая ищет максимум слева (УБЫВ), вторая — минимум справа (ВОЗР).
СУБД быстро находит ровно по одной записи с каждой стороны.
Во внешнем запросе сравниваются максимум две строки, что гарантирует максимальное быстродействие даже на многомиллионных таблицах.

Важные замечания и потенциальные ошибки

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

Переполнение при расчете в секундах (DATEDIFF): Функция РАЗНОСТЬДАТ(..., ..., СЕКУНДА) в клиент-серверном режиме транслируется в функцию DATEDIFF СУБД, которая возвращает 32-битное целое число со знаком. Если разница между датами превышает ~68 лет (или одна из дат не заполнена и равна пустой дате 0001-01-01), запрос аварийно завершится с ошибкой переполнения. Всегда ограничивайте интервал или проверяйте даты на заполненность.
Коллизия равных расстояний: Если найдутся две записи, равноудаленные от указанного времени (например, ровно на 5 минут раньше и ровно на 5 минут позже), сортировка по разности вернет одну из них случайным образом. Чтобы поведение было детерминированным, добавьте в УПОРЯДОЧИТЬ ПО дополнительное поле (например, Период УБЫВ, если приоритет отдается более свежим записям).