Задача вывода списка сотрудников, клиентов или контактных лиц, у которых приближается день рождения, часто встречается при разработке CRM-систем, корпоративных порталов и систем оповещения. На первый взгляд решение кажется очевидным, но при реализации разработчики сталкиваются с целым рядом подводных камней: переход даты через границу года, учет високосных лет (наличие 29 февраля) и критическое падение производительности базы данных при росте количества записей. В этой статье мы подробно разберем все популярные варианты решения данной задачи в языке запросов 1С, проанализируем их плюсы и минусы, а также рассмотрим канонические методы оптимизации.
Самый очевидный вариант, который часто приходит в голову начинающим разработчикам — это использование встроенной функции языка запросов 1С ДЕНЬГОДА. Мы пытаемся сопоставить порядковый номер дня в году для даты рождения с аналогичным порядковым номером для дат начала и конца исследуемого периода.
Рассмотрим пример такого запроса:
ВЫБРАТЬ
КонтактныеЛица.Ссылка КАК КонтактноеЛицо,
КонтактныеЛица.ДатаРождения
ИЗ
Справочник.КонтактныеЛица КАК КонтактныеЛица
ГДЕ
ДЕНЬГОДА(КонтактныеЛица.ДатаРождения) МЕЖДУ ДЕНЬГОДА(&ДатаНачала) И ДЕНЬГОДА(&ДатаОкончания)
Проанализируем ситуацию и выясним, почему этот подход работает некорректно в реальных условиях эксплуатации базы данных:
МЕЖДУ 359 И 5 всегда будет возвращать ложь. Ни один именинник в этот интервал не попадет.Чтобы обойти проблему перехода периода через Новый год, разработчики часто прибегают к разделению запроса на два диапазона в зависимости от того, превышает ли год окончания период года начала. Посмотрим на пример реализации такого условия в запросе:
ВЫБРАТЬ
КонтактныеЛица.Ссылка КАК КонтактноеЛицо,
КонтактныеЛица.ДатаРождения
ИЗ
Справочник.КонтактныеЛица КАК КонтактныеЛица
ГДЕ
ВЫБОР
КОГДА ГОД(&ДатаНачала) = ГОД(&ДатаОкончания)
ТОГДА ДЕНЬГОДА(КонтактныеЛица.ДатаРождения) МЕЖДУ ДЕНЬГОДА(&ДатаНачала) И ДЕНЬГОДА(&ДатаОкончания)
ИНАЧЕ
ДЕНЬГОДА(КонтактныеЛица.ДатаРождения) МЕЖДУ ДЕНЬГОДА(&ДатаНачала) И ДЕНЬГОДА(КОНЕЦПЕРИОДА(&ДатаНачала, ГОД))
ИЛИ ДЕНЬГОДА(КонтактныеЛица.ДатаРождения) МЕЖДУ ДЕНЬГОДА(НАЧАЛОПЕРИОДА(&ДатаОкончания, ГОД)) И ДЕНЬГОДА(&ДатаОкончания)
КОНЕЦ
И КонтактныеЛица.ДатаРождения <> ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
Такой подход решает проблему перехода года, однако никак не исправляет смещение дат в високосные годы. Кроме того, этот код громоздок и неудобен для чтения.
Другой альтернативный вариант "в лоб" — создание временной таблицы дат. Если задача стоит узко (например, вывести именинников за 3 дня до и 3 дня после текущей даты), мы можем сформировать список целевых дат с помощью функции ДОБАВИТЬКДАТЕ прямо в запросе, поместить их во временную таблицу и затем связать по полям месяц и день. Рассмотрим этот алгоритм подробнее:
ВЫБРАТЬ ДОБАВИТЬКДАТЕ(&ТекущаяДата, ДЕНЬ, -3) КАК ДеньРождения ПОМЕСТИТЬ ВТ_Даты
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ДОБАВИТЬКДАТЕ(&ТекущаяДата, ДЕНЬ, -2)
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ДОБАВИТЬКДАТЕ(&ТекущаяДата, ДЕНЬ, -1)
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ &ТекущаяДата
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ДОБАВИТЬКДАТЕ(&ТекущаяДата, ДЕНЬ, 1)
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ДОБАВИТЬКДАТЕ(&ТекущаяДата, ДЕНЬ, 2)
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ДОБАВИТЬКДАТЕ(&ТекущаяДата, ДЕНЬ, 3)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Партнеры.Ссылка КАК Партнер,
Партнеры.ДатаРождения
ИЗ
Справочник.Партнеры КАК Партнеры
ГДЕ
(МЕСЯЦ(Партнеры.ДатаРождения), ДЕНЬ(Партнеры.ДатаРождения)) В
(ВЫБРАТЬ МЕСЯЦ(ВТ_Даты.ДеньРождения), ДЕНЬ(ВТ_Даты.ДеньРождения) ИЗ ВТ_Даты)
Преимущества данного метода: Он полностью игнорирует проблемы високосных лет и переходов через год, поскольку сопоставление идет по точным значениям месяца и дня конкретных рассчитанных дат.
Недостатки метода: Он абсолютно нединамичен. Если пользователю потребуется изменить интервал анализа с 7 дней на 30 дней, вам придется динамически формировать текст запроса в коде, добавляя десятки строк с ОБЪЕДИНИТЬ ВСЕ, что крайне нетехнологично.
Для создания универсального, элегантного и быстрого алгоритма нам необходимо научиться приводить дату рождения каждого человека к текущему исследуемому году. Это делается с помощью комбинации функций ДОБАВИТЬКДАТЕ и РАЗНОСТЬДАТ.
При этом важно понимать специфику работы функции РАЗНОСТЬДАТ(Д1, Д2, ГОД) в 1С. Она не считает количество полных лет между датами, а возвращает простую математическую разность между номерами годов этих дат (то есть ГОД(Д2) - ГОД(Д1)). Это делает ее поведение полностью предсказуемым.
Для того чтобы решить проблему перехода через год раз и навсегда без использования временных таблиц, мы применим метод двойного смещения. Мы сместим дату рождения каждого человека дважды: один раз на год начала периода поиска, второй раз — на год конца периода поиска. Если хотя бы одна из этих двух смещенных дат попадет в наш интервал, то это наш клиент!
Рассмотрим структуру этого запроса:
ВЫБРАТЬ
КонтактныеЛица.Ссылка КАК КонтактноеЛицо,
КонтактныеЛица.ДатаРождения,
ВЫБОР
КОГДА ДОБАВИТЬКДАТЕ(КонтактныеЛица.ДатаРождения, ГОД, РАЗНОСТЬДАТ(КонтактныеЛица.ДатаРождения, &ДатаНачала, ГОД)) МЕЖДУ &ДатаНачала И &ДатаОкончания
ТОГДА ДОБАВИТЬКДАТЕ(КонтактныеЛица.ДатаРождения, ГОД, РАЗНОСТЬДАТ(КонтактныеЛица.ДатаРождения, &ДатаНачала, ГОД))
ИНАЧЕ ДОБАВИТЬКДАТЕ(КонтактныеЛица.ДатаРождения, ГОД, РАЗНОСТЬДАТ(КонтактныеЛица.ДатаРождения, &ДатаОкончания, ГОД))
КОНЕЦ КАК БлижайшаяДатаРождения
ИЗ
Справочник.КонтактныеЛица КАК КонтактныеЛица
ГДЕ
КонтактныеЛица.ДатаРождения <> ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
И (
ДОБАВИТЬКДАТЕ(КонтактныеЛица.ДатаРождения, ГОД, РАЗНОСТЬДАТ(КонтактныеЛица.ДатаРождения, &ДатаНачала, ГОД)) МЕЖДУ &ДатаНачала И &ДатаОкончания
ИЛИ
ДОБАВИТЬКДАТЕ(КонтактныеЛица.ДатаРождения, ГОД, РАЗНОСТЬДАТ(КонтактныеЛица.ДатаРождения, &ДатаОкончания, ГОД)) МЕЖДУ &ДатаНачала И &ДатаОкончания
)
УПОРЯДОЧИТЬ ПО
БлижайшаяДатаРождения
Почему этот механизм работает безупречно?
Как ведет себя платформа 1С при работе с датой 29 февраля? Если мы берем дату рождения человека, родившегося в високосном году (например, 29.02.1980), и с помощью функции ДОБАВИТЬКДАТЕ смещаем ее на обычный, невисокосный год (например, 2023), платформа автоматически вернет дату 28.02.2023 (последний день февраля).
В большинстве бизнес-сценариев такое поведение считается абсолютно корректным — именинника поздравляют 28 февраля. Однако, если внутренний регламент вашей компании требует поздравлять таких людей строго 1 марта в невисокосные годы, стандартный механизм смещения дат необходимо доработать с помощью условной конструкции ВЫБОР:
ВЫБОР
КОГДА МЕСЯЦ(Партнеры.ДатаРождения) = 2
И ДЕНЬ(Партнеры.ДатаРождения) = 29
И ДЕНЬ(КОНЕЦПЕРИОДА(ДОБАВИТЬКДАТЕ(Партнеры.ДатаРождения, ГОД, РАЗНОСТЬДАТ(Партнеры.ДатаРождения, &ДатаНачала, ГОД)), МЕСЯЦ)) = 28
ТОГДА ДОБАВИТЬКДАТЕ(ДОБАВИТЬКДАТЕ(Партнеры.ДатаРождения, ГОД, РАЗНОСТЬДАТ(Партнеры.ДатаРождения, &ДатаНачала, ГОД)), ДЕНЬ, 1)
ИНАЧЕ ДОБАВИТЬКДАТЕ(Партнеры.ДатаРождения, ГОД, РАЗНОСТЬДАТ(Партнеры.ДатаРождения, &ДатаНачала, ГОД))
КОНЕЦ
В этой конструкции мы проверяем: если человек родился 29 февраля, а в году смещения февраль длится всего 28 дней, мы принудительно добавляем к смещенной дате (которая определилась как 28.02) еще 1 день, сдвигая поздравление на 1 марта.
Мы получили работающий, логически верный запрос. Но у него есть критически важный недостаток, который проявит себя при работе с крупными базами данных (сотни тысяч или миллионы записей контрагентов и физических лиц).
Суть проблемы: Использование любых функций преобразования дат (ДОБАВИТЬКДАТЕ, РАЗНОСТЬДАТ, МЕСЯЦ, ДЕНЬ) в условиях секции ГДЕ делает невозможным использование индексов СУБД по полю ДатаРождения. СУБД будет вынуждена произвести полное сканирование всей таблицы базы данных (операция Full Table Scan), вычисляя сложные математические функции для каждой строки без исключения. Это приведет к колоссальным задержкам и блокировкам СУБД.
Как оптимизировать этот процесс на крупных корпоративных проектах? Для этого применяется метод денормализации данных — добавление вычисляемого индексированного реквизита при записи элемента справочника.
Вариант решения:
ДеньМесяцРождения (тип: Число, длина: 4, без знака, индексировать).ПередЗаписью прописывается автоматическое заполнение этого реквизита по формуле: Месяц * 100 + День. Например, если день рождения 15 апреля, в реквизит запишется число 415. Если день рождения 1 декабря — число 1201.Теперь в модуле отчета мы можем перевести наш период поиска в аналогичные числа на стороне встроенного языка 1С и написать максимально простой и эффективный запрос, который СУБД выполнит за сотые доли секунды благодаря прямому индексному поиску (Index Seek):
// Код на встроенном языке 1С для подготовки параметров:
ЧислоНачала = Месяц(ДатаНачала) * 100 + День(ДатаНачала);
ЧислоОкончания = Месяц(ДатаОкончания) * 100 + День(ДатаОкончания);
// Если интервал переходит через Новый Год, в запросе используем ИЛИ:
Запрос.УстановитьПараметр("ЧислоНачала", ЧислоНачала);
Запрос.УстановитьПараметр("ЧислоОкончания", ЧислоОкончания);
Текст оптимизированного запроса будет выглядеть так:
ВЫБРАТЬ
ФизическиеЛица.Ссылка КАК ФизЛицо,
ФизическиеЛица.ДатаРождения
ИЗ
Справочник.ФизическиеЛица КАК ФизическиеЛица
ГДЕ
ФизическиеЛица.ДатаРождения <> ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
И (
ВЫБОР
КОГДА &ЧислоНачала <= &ЧислоОкончания
ТОГДА ФизическиеЛица.ДеньМесяцРождения МЕЖДУ &ЧислоНачала И &ЧислоОкончания
ИНАЧЕ
ФизическиеЛица.ДеньМесяцРождения МЕЖДУ &ЧислоНачала И 1231
ИЛИ ФизическиеЛица.ДеньМесяцРождения МЕЖДУ 101 И &ЧислоОкончания
КОНЕЦ
)
Этот запрос абсолютно плоский для планировщика СУБД. В нем нет вычислений над полями таблиц базы данных — только сравнения констант с проиндексированным полем ДеньМесяцРождения. Такой подход гарантирует стабильно высокую скорость работы отчета независимо от размера вашей информационной базы.
Если вы разрабатываете отчет на базе Системы компоновки данных (СКД), вам не обязательно писать сложные конструкции в самом запросе. Платформа 1С содержит встроенные интеллектуальные механизмы для работы с периодическими событиями.
Для активации автоматического разбора дней рождения выполним следующие шаги в конструкторе схемы компоновки данных:
ДатаРождения).После настройки этой роли в режиме "1С:Предприятие" при настройке отборов пользователю автоматически станет доступно виртуальное поле "День рождения". В условиях отбора СКД сама предложит пользователю удобные варианты фильтрации, такие как: "В этом месяце", "В следующем месяце", "Сегодня" или "Завтра". Платформа 1С самостоятельно преобразует эти условия в оптимальные SQL-запросы к СУБД с учетом всех високосных смещений и смен лет без вашего прямого участия в написании программного кода.