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

← На главную

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

Понимание задачи и ее сложности

Представим, что у нас есть непериодический регистр сведений, в котором хранятся записи о перемещениях с полем ДатаПеремещения. Наша цель — для каждого набора измерений (или для всей таблицы, если измерений нет) найти ту запись, чья ДатаПеремещения находится ближе всего к определенной ТекущейДате. Здесь возникают вопросы: что если есть несколько дат, одинаково удаленных в прошлое и будущее? Что если нам нужна дата только из прошлого или только из будущего?

Мы рассмотрим несколько стратегий, которые помогут нам справиться с этими нюансами.

Метод 1: Поиск ближайшей даты «с двух сторон» (прошлое и будущее)

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

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

Шаг 1: Получение максимальной «прошлой» даты

Для этого мы можем использовать временную таблицу (ВТ). Допустим, наш регистр называется РегистрСведений.Перемещения и в нем есть поле ДатаПеремещения.


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

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


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

Важно помнить, что если по заданным условиям ни одной даты меньше или равной ТекущейДате не будет найдено, то поле ДатаДо в этой ВТ будет иметь значение NULL.

Шаг 2: Получение минимальной «будущей» даты

Аналогично, мы создаем вторую временную таблицу для дат, следующих за ТекущейДатой:


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

С группировкой по измерениям:


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

Здесь также, если дат больше или равных ТекущейДате не будет, ДатаПосле будет NULL.

Шаг 3: Объединение результатов и выбор ближайшей даты

Теперь, когда у нас есть потенциальные даты из прошлого и будущего, мы можем объединить эти данные и определить ближайшую. Мы используем функцию РАЗНОСТЬДАТ() для вычисления разницы в секундах между каждой из этих дат и ТекущейДатой. Затем мы сравниваем абсолютные значения этих разностей.


ВЫБРАТЬ
    ЕСТЬNULL(ВТ_ДатаДо.Номенклатура, ВТ_ДатаПосле.Номенклатура) КАК Номенклатура,
    ВЫБОР
        КОГДА ВТ_ДатаДо.ДатаДо ЕСТЬ NULL ТОГДА
            ВТ_ДатаПосле.ДатаПосле
        КОГДА ВТ_ДатаПосле.ДатаПосле ЕСТЬ NULL ТОГДА
            ВТ_ДатаДо.ДатаДо
        КОГДА РАЗНОСТЬДАТ(ВТ_ДатаДо.ДатаДо, &ТекущаяДата, СЕКУНДА) <= РАЗНОСТЬДАТ(&ТекущаяДата, ВТ_ДатаПосле.ДатаПосле, СЕКУНДА) ТОГДА
            ВТ_ДатаДо.ДатаДо
        ИНАЧЕ
            ВТ_ДатаПосле.ДатаПосле
    КОНЕЦ КАК БлижайшаяДата
ПОМЕСТИТЬ ВТ_БлижайшаяДата
ИЗ
    ВТ_ДатаДо КАК ВТ_ДатаДо
        ПОЛНОЕ СОЕДИНЕНИЕ ВТ_ДатаПосле КАК ВТ_ДатаПосле
        ПО ВТ_ДатаДо.Номенклатура = ВТ_ДатаПосле.Номенклатура
;

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

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

Шаг 4: Получение всех необходимых данных по ближайшей дате

Наконец, имея ВТ_БлижайшаяДата с нашей искомой датой и, при необходимости, измерениями, мы можем соединить ее с исходным регистром сведений, чтобы получить все остальные поля записи:


ВЫБРАТЬ
    Регистр.Поле1,
    Регистр.Поле2,
    Регистр.ДатаПеремещения,
    ...
ИЗ
    РегистрСведений.Перемещения КАК Регистр
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_БлижайшаяДата КАК Ближайшая
        ПО Регистр.Номенклатура = Ближайшая.Номенклатура
        И Регистр.ДатаПеремещения = Ближайшая.БлижайшаяДата
;

Важно убедиться, что условия соединения по дате и измерениям уникально идентифицируют нужную запись. Если в одну и ту же секунду может быть несколько записей с одинаковыми измерениями, то нужно добавить к условию соединения или сортировке дополнительные поля (например, Ссылка для документов-регистраторов или УникальныйИдентификатор для записей регистра).

Метод 2: Поиск ближайшей даты только в прошлом (или только в будущем)

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

Для этого мы можем использовать подзапрос с агрегатной функцией МАКСИМУМ():


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

В этом примере подзапрос возвращает максимальную дату для каждой Номенклатуры, которая меньше или равна &ТекущаяДата, и затем внешний запрос отбирает все поля для этой даты. Если нам нужна просто одна запись с максимальной датой из всего регистра, то условие по Номенклатура в подзапросе будет не нужно.

Еще один вариант, который может быть эффективным для получения одной или нескольких записей с ближайшей датой (по убыванию), это использование конструкции ПЕРВЫЕ 1 с сортировкой:


ВЫБРАТЬ ПЕРВЫЕ 1
    Т1.Поле1,
    Т1.Поле2,
    Т1.ДатаПеремещения
ИЗ
    РегистрСведений.Перемещения КАК Т1
ГДЕ
    Т1.ДатаПеремещения <= &ТекущаяДата
УПОРЯДОЧИТЬ ПО
    Т1.ДатаПеремещения УБЫВ
;

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


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

ВЫБРАТЬ
    Регистр.Поле1,
    Регистр.Поле2,
    Регистр.ДатаПеремещения,
    Регистр.Номенклатура
ИЗ
    РегистрСведений.Перемещения КАК Регистр
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_БлижайшаяДатаПоИзмерениям КАК Ближайшая
        ПО Регистр.Номенклатура = Ближайшая.Номенклатура
        И Регистр.ДатаПеремещения = Ближайшая.БлижайшаяДата
;

Метод 3: Решение через группировку по полю ДатаПеремещения

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

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

Пример (если нас интересует просто последняя дата из всего регистра, или если группировка по измерениям плюс МАКСИМУМ(ДатаПеремещения) дает нужный результат):


ВЫБРАТЬ
    МАКСИМУМ(Перемещения.ДатаПеремещения) КАК ПоследняяДатаПеремещения
ИЗ
    РегистрСведений.Перемещения КАК Перемещения
;

Или, если у нас есть измерения, и мы хотим для каждого измерения получить последнюю дату (что, по сути, равносильно подходу из Метод 2):


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

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

Дополнительные нюансы и полезные советы

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

Точность дат: В 1С тип "Дата" хранит значение с точностью до секунды. Это критически важно при сортировке и сравнении. Если в одну и ту же секунду может быть несколько записей, и нам нужно выбрать одну конкретную, нам потребуется добавить дополнительные поля к условиям сортировки или соединения, чтобы обеспечить уникальность. Например, можно использовать поле Ссылка (если регистр подчинен регистратору) или УникальныйИдентификатор записи.
Использование временных таблиц (ВТ): Для сложных запросов, особенно при реализации поиска ближайшей даты "с двух сторон", применение временных таблиц (ВТ) значительно повышает читаемость кода, упрощает отладку и может улучшить производительность, позволяя СУБД кэшировать промежуточные результаты. Менеджер временных таблиц автоматически управляет их жизненным циклом.
Параметры запросов: Всегда передавайте даты (например, &ТекущаяДата) и другие переменные в запросы через параметры. Это не только повышает безопасность, предотвращая SQL-инъекции, но и способствует оптимизации работы СУБД, так как она может кэшировать план выполнения запроса.
Функции для работы с датами:
- ДАТАВРЕМЯ(Год, Месяц, День, Час, Минута, Секунда): Для конструирования даты и времени.
- ГОД(Дата), МЕСЯЦ(Дата), ДЕНЬ(Дата), ЧАС(Дата), МИНУТА(Дата), СЕКУНДА(Дата): Для извлечения частей даты.
- ДОБАВИТЬКДАТЕ(Дата, ВидПериода, Количество): Для выполнения арифметических операций с датами (добавление или вычитание дней, месяцев, лет и т.д.).
- РАЗНОСТЬДАТ(ДатаНачала, ДатаОкончания, ВидПериода): Для вычисления разницы между двумя датами в указанных единицах (секундах, минутах, днях и т.д.). Эту функцию мы активно использовали в нашем примере для определения ближайшей даты.
Текущая дата в 1С: Для получения текущей даты и времени в коде 1С мы можем использовать функции ТекущаяДата() (системная дата компьютера пользователя) или ТекущаяДатаСеанса() (дата и время сервера, скорректированные с учетом часового пояса пользователя). Выбор зависит от требований к точности и учету часовых поясов в вашем решении.
Оптимизация запросов: При работе с большими объемами данных производительность запроса становится критически важной. Мы рекомендуем:
- Разбивать сложные запросы на более простые с использованием временных таблиц.
- Убедиться, что на поля, используемые в условиях ГДЕ, СОЕДИНЕНИЕ и СГРУППИРОВАТЬ ПО, созданы индексы в конфигурации.
- Избегать использования функций в условиях ГДЕ для индексируемых полей, так как это может привести к полнотабличному сканированию.

Применяя эти подходы и рекомендации, мы сможем эффективно и надежно выбирать записи с ближайшей датой из регистров сведений в 1С, решая даже самые сложные задачи, связанные с хронологическим анализом данных. При проектировании сложных пакетных запросов также пригодится инструмент для автоматической расстановки инструкций УНИЧТОЖИТЬ для временных таблиц.