При работе с регистрами сведений или табличными частями, содержащими историю изменений (например, историю ставок сотрудника или цен номенклатуры), часто возникает задача превратить плоский список дат в таблицу временных интервалов. Каждый интервал должен иметь дату начала и дату окончания. Традиционным способом решения этой задачи в языке запросов 1С является левое соединение таблицы самой с собой по условию периода. Однако здесь программисты часто сталкиваются с тем, что последняя запись (самая актуальная на текущий момент) исчезает из результата или данные начинают "дублироваться".
Рассмотрим подробно, как правильно составить такой запрос, разберем типичные ошибки и современные методы решения этой задачи.
Проанализируем ситуацию, с которой столкнулся автор темы. Имеется таблица с данными по ставкам сотрудников, где указана дата, с которой ставка вступает в силу. Задача — получить для каждой записи следующую за ней дату, чтобы сформировать интервал "Дата С — Дата По".
Автор использовал следующий запрос (упрощенно):
ВЫБРАТЬ
ТЧРегСвед.Период КАК Период,
ТЧРегСвед1.Период КАК Период1,
ТЧРегСвед.Ставка КАК Ставка
ИЗ
ТЧРегСвед КАК ТЧРегСвед
ЛЕВОЕ СОЕДИНЕНИЕ ТЧРегСвед КАК ТЧРегСвед1
ПО ТЧРегСвед.Сотрудник = ТЧРегСвед1.Сотрудник
ГДЕ
ТЧРегСвед.Период < ТЧРегСвед1.Период
Проблема этого запроса заключается в секции ГДЕ. Когда мы используем ЛЕВОЕ СОЕДИНЕНИЕ, система сначала пытается найти соответствия в правой таблице (ТЧРегСвед1). Для самой последней записи в истории (например, от 29.09.2021) в таблице просто не существует даты, которая была бы больше нее. В результате в поле ТЧРегСвед1.Период попадает значение NULL.
В языке SQL и запросах 1С любое сравнение значения с NULL (например, 29.09.2021 < NULL) дает результат "Ложь" или "Неизвестно". Следовательно, секция ГДЕ просто отфильтровывает эту строку, и мы теряем информацию о текущей (последней) ставке сотрудника.
Чтобы сохранить все строки из левой таблицы (включая ту, для которой нет следующей даты), необходимо перенести условие сравнения периодов непосредственно в условие связи ПО. Рассмотрим исправленный вариант запроса:
ВЫБРАТЬ
ТЧРегСвед.Период КАК Период,
ЕСТЬNULL(ТЧРегСвед1.Период, ДАТАВРЕМЯ(3999, 12, 31, 23, 59, 59)) КАК ПериодЗавершения,
ТЧРегСвед.Ставка КАК Ставка
ИЗ
ТЧРегСвед КАК ТЧРегСвед
ЛЕВОЕ СОЕДИНЕНИЕ ТЧРегСвед КАК ТЧРегСвед1
ПО ТЧРегСвед.Сотрудник = ТЧРегСвед1.Сотрудник
И ТЧРегСвед.Период < ТЧРегСвед1.Период
В этом случае условие ТЧРегСвед.Период < ТЧРегСвед1.Период работает как фильтр для присоединяемых данных, а не для итоговой выборки. Если для записи не нашлось "будущей" даты, строка из левой таблицы все равно останется в результате, а справа будет NULL. Мы также добавили функцию ЕСТЬNULL, чтобы вместо пустого значения для последней записи выводилась "бесконечная" дата (31.12.3999), что удобно для последующих расчетов.
Если мы просто выполним соединение по условию "меньше", то для самой первой даты (например, 01.03.2021) подтянутся все последующие даты (02.09, 10.09, 20.09 и т.д.). В результате вместо одной строки для интервала мы получим столько строк, сколько записей идет следом. Это называется частичным декартовым произведением.
Для того чтобы получить именно ближайшую следующую дату (границу интервала), нам необходимо использовать группировку и агрегатную функцию МИНИМУМ. Разберем по шагам, как это реализовать:
ВЫБРАТЬ
ТЧ.Период КАК ДатаНачала,
МИНИМУМ(ЕСТЬNULL(ТЧ_След.Период, ДАТАВРЕМЯ(3999, 12, 31, 23, 59, 59))) КАК ДатаОкончания,
ТЧ.Сотрудник,
ТЧ.Ставка
ИЗ
ТЧРегСвед КАК ТЧ
ЛЕВОЕ СОЕДИНЕНИЕ ТЧРегСвед КАК ТЧ_След
ПО ТЧ.Сотрудник = ТЧ_След.Сотрудник
И ТЧ.Период < ТЧ_След.Период
СГРУППИРОВАТЬ ПО
ТЧ.Период,
ТЧ.Сотрудник,
ТЧ.Ставка
Важный момент: Функция ЕСТЬNULL внутри МИНИМУМ позволяет корректно обработать последнюю запись. Так как 3999 год заведомо больше любой даты в базе, он станет "минимумом", если других (реальных) будущих дат не найдено.
Если ваша база работает на платформе 1С:Предприятие 8.3.20 и выше, использование тяжелых левых соединений для этой задачи больше не требуется. В языке запросов появились оконные функции, которые позволяют заглянуть в "следующую" строку без повторного сканирования таблицы.
Проанализируем пример использования функции LEAD (получение значения из следующей строки):
ВЫБРАТЬ
ТЧ.Период КАК ДатаНачала,
LEAD(ТЧ.Период) OVER (PARTITION BY ТЧ.Сотрудник ПОРЯДОК ПО ТЧ.Период) КАК ДатаОкончания,
ТЧ.Сотрудник,
ТЧ.Ставка
ИЗ
ТЧРегСвед КАК ТЧ
Разберем, что здесь происходит:
LEAD(ТЧ.Период) — берет значение периода из следующей строки.OVER (...) — определяет "окно" видимости.PARTITION BY ТЧ.Сотрудник — указывает, что поиск следующей даты должен идти отдельно для каждого сотрудника (если данные по сотруднику закончились, функция вернет NULL).ПОРЯДОК ПО ТЧ.Период — гарантирует, что данные внутри окна отсортированы хронологически.Этот метод работает значительно быстрее, так как платформа выполняет всего один проход по индексам таблицы вместо сложного соединения.
При использовании левых соединений по условию < или > на больших объемах данных (например, регистр сведений "Цены номенклатуры" на миллионы записей) могут возникнуть серьезные проблемы с производительностью. Чтобы запрос работал быстро, придерживайтесь следующих правил:
Сотрудник и Период), обязательно должны быть проиндексированы. В регистрах сведений это происходит автоматически для измерений и периода.Таким образом, для решения задачи автора наиболее правильным (до версии 8.3.20) является использование левого соединения с условием в секции ПО и обязательной группировкой по минимуму следующей даты. Для современных версий платформы однозначным выбором являются оконные функции.