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

Программист 1С v8.3 (Управляемые формы) 1С:Зарплата и Управление Персоналом IT и автоматизация бизнеса
← На главную

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

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

Способ 1. Классическое левое соединение (Базовый метод)

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

Проанализируем структуру такого запроса на примере кадровой истории сотрудников:


ВЫБРАТЬ
    Т1.Сотрудник КАК Сотрудник,
    Т1.Период КАК ДатаНачала,
    МИНИМУМ(ЕСТЬNULL(Т2.Период, ДАТАВРЕМЯ(3999, 12, 31, 23, 59, 59))) КАК ДатаОкончания
ИЗ
    РегистрСведений.КадроваяИсторияСотрудников КАК Т1
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КадроваяИсторияСотрудников КАК Т2
        ПО Т1.Сотрудник = Т2.Сотрудник
        И Т1.Период < Т2.Период
СГРУППИРОВАТЬ ПО
    Т1.Сотрудник,
    Т1.Период

Разберем по шагам, как работает этот механизм:

  1. Левое соединение: Мы берем каждую запись сотрудника из таблицы Т1 и пытаемся сопоставить ей все записи того же сотрудника из таблицы Т2, у которых дата строго больше (Т1.Период < Т2.Период).
  2. Группировка по минимуму: С помощью функции МИНИМУМ(Т2.Период) мы выбираем из всех последующих дат самую раннюю. Она и становится датой окончания для текущей строки.
  3. Обработка последней записи: Для самой последней (актуальной) записи сотрудника в таблице Т2 не найдется строк с более поздней датой. Благодаря использованию левого соединения (а не внутреннего), эта строка не исчезнет из результата — вместо даты окончания в ней вернется значение NULL. С помощью функции ЕСТЬNULL мы заменяем этот NULL на максимальное системное время (например, 31 декабря 3999 года), обозначая открытый интервал.

Способ 2. Оптимизация производительности и борьба с «треугольным» соединением

Несмотря на простоту, базовый метод таит в себе серьезную опасность для производительности системы. В теории баз данных соединение по неравенству дат (Т1.Период < Т2.Период) называют квадратичным или «треугольным» соединением. Если по одному сотруднику накопилось много записей (например, частые кадровые изменения или ежедневный расчет показателей), СУБД будет вынуждена перебирать строки в геометрической прогрессии ($O(N^2)$). На реальных базах это гарантированно приведет к зависанию запроса.

Чтобы избежать критического падения производительности, мы должны обязательно оптимизировать запрос с использованием временных таблиц и индексации. Выясним, как это сделать:


// Шаг 1: Помещаем исходные данные во временную таблицу и индексируем их
ВЫБРАТЬ
    КадроваяИстория.Сотрудник КАК Сотрудник,
    КадроваяИстория.Период КАК Период
ПОМЕСТИТЬ ВТ_ИсходныеДанные
ИЗ
    РегистрСведений.КадроваяИсторияСотрудников КАК КадроваяИстория
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Создаем индекс по ключевым полям для быстрого поиска СУБД
ИНДЕКСИРОВАТЬ ПО
    Сотрудник,
    Период
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Выполняем соединение на проиндексированных таблицах
ВЫБРАТЬ
    Т1.Сотрудник КАК Сотрудник,
    Т1.Период КАК ДатаНачала,
    МИНИМУМ(ЕСТЬNULL(Т2.Период, ДАТАВРЕМЯ(3999, 12, 31, 23, 59, 59))) КАК ДатаОкончания
ИЗ
    ВТ_ИсходныеДанные КАК Т1
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИсходныеДанные КАК Т2
        ПО Т1.Сотрудник = Т2.Сотрудник
        И Т1.Период < Т2.Период
СГРУППИРОВАТЬ ПО
    Т1.Сотрудник,
    Т1.Период

Благодаря предварительному помещению данных во временную таблицу ВТ_ИсходныеДанные и созданию индекса по полям Сотрудник, Период, СУБД сможет применить быстрый индексный поиск (Index Seek) вместо полного сканирования таблиц. Это снизит нагрузку на сервер в десятки раз.

Способ 3. Использование готовых интервальных регистров в ЗУП 3.1 / ЗКГУ 3.1

Поскольку автор вопроса упомянул конфигурацию ЗКГУ 3.1 (Зарплата и кадры государственного учреждения), важно отметить, что разработчики фирмы «1С» уже предусмотрели эту проблему на архитектурном уровне. В этих конфигурациях внедрены так называемые интервальные регистры сведений.

Для регистра КадроваяИсторияСотрудников существует автоматический парный регистр — РегистрСведений.КадроваяИсторияСотрудниковИнтервальный. При проведении кадровых документов система сама рассчитывает и записывает в него готовые поля ДатаНачала и ДатаОкончания.

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


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

Важное предупреждение: Методология разработки «1С» не рекомендует завязываться на физические таблицы интервальных регистров в кастомных отчетах, так как их структура является служебной и может измениться. Профессиональным стандартом является использование функций программного интерфейса БСП (Библиотеки стандартных подсистем), таких как функции общего модуля КадровыйУчет, которые генерируют правильные временные таблицы кадровых данных автоматически.

Также помните, что из-за механизмов исправления документов в ЗУП/ЗКГУ даты начала и окончания интервалов в регистре могут содержать смещения в секундах (например, окончание суток записывается не как 23:59:59, а со сдвигом). Всегда учитывайте это при написании собственных условий сравнения периодов.

Способ 4. Высокопроизводительный метод «нумерации строк» (Альтернатива оконным функциям)

В СУБД MS SQL или PostgreSQL аналогичная задача мгновенно решается с помощью оконной функции LEAD(), которая позволяет заглянуть на строку вперед без повторных соединений. К сожалению, встроенный язык запросов «1С:Предприятие» до сих пор не поддерживает оконные функции смещения.

В сообществе разработчиков 1С существует альтернативный высокопроизводительный алгоритм генерации интервалов (часто называемый «методом Ильдаровича»), который заменяет медленное неравенство дат быстрым соединением по равенству номеров строк. Рассмотрим этот метод детально:


// Шаг 1: Выбираем уникальные периоды во временную таблицу
ВЫБРАТЬ
    Т.Сотрудник КАК Сотрудник,
    Т.Период КАК Период
ПОМЕСТИТЬ ВТ_Данные
ИЗ
    РегистрСведений.КадроваяИсторияСотрудников КАК Т
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Нумеруем строки для каждого сотрудника
ВЫБРАТЬ
    Т1.Сотрудник КАК Сотрудник,
    Т1.Период КАК Период,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.Период) КАК НомерСтроки
ПОМЕСТИТЬ ВТ_ПронумерованныеДанные
ИЗ
    ВТ_Данные КАК Т1
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Данные КАК Т2
        ПО Т1.Сотрудник = Т2.Сотрудник
        И Т1.Период >= Т2.Период
СГРУППИРОВАТЬ ПО
    Т1.Сотрудник,
    Т1.Период
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Индексируем пронумерованную таблицу
ИНДЕКСИРОВАТЬ ПО
    Сотрудник,
    НомерСтроки
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 4: Соединяем строки по условию НомерСтроки = НомерСтроки + 1
ВЫБРАТЬ
    Т1.Сотрудник КАК Сотрудник,
    Т1.Период КАК ДатаНачала,
    ЕСТЬNULL(Т2.Период, ДАТАВРЕМЯ(3999, 12, 31, 23, 59, 59)) КАК ДатаОкончания
ИЗ
    ВТ_ПронумерованныеДанные КАК Т1
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПронумерованныеДанные КАК Т2
        ПО Т1.Сотрудник = Т2.Сотрудник
        И T1.НомерСтроки = T2.НомерСтроки - 1

Почему этот метод работает быстрее классического неравенства? На четвертом шаге мы осуществляем связь таблиц по строгому равенству T1.НомерСтроки = T2.НомерСтроки - 1. СУБД выполняет такое соединение моментально (через Merge Join или Hash Match), исключая необходимость сопоставлять каждую строку со всеми последующими. На таблицах объемом более 10 000 записей данный подход показывает колоссальный прирост скорости выполнения запроса.

← На главную