В практике разработки на платформе 1С часто возникает задача нормализации временных периодов. Представьте ситуацию: у вас есть набор строк с датами начала и окончания (например, периоды отсутствия сотрудника или действия скидок), которые накладываются друг на друга или входят один в другой. Нам необходимо "схлопнуть" такие интервалы, чтобы на выходе получить непрерывные и непересекающиеся отрезки времени. Это классическая задача, которую в мире SQL часто называют "Gaps and Islands" (Разрывы и острова).
В этой статье мы разберем несколько подходов к решению этой задачи: от самых простых (группировка) до сложных итерационных запросов и алгоритмов поиска "начала острова". Мы проанализируем, как заставить язык запросов 1С эффективно обрабатывать цепочки пересечений без использования циклов во встроенном языке.
Начнем с самого простого сценария, описанного в начале обсуждения. Если наши интервалы имеют одинаковую дату начала, но разные даты окончания, или если один интервал полностью поглощается другим внутри одной "точки входа", мы можем применить обычную группировку. Рассмотрим ситуацию на примере из сообщения автора:
Исходные данные:
В данном случае вторая строка полностью поглощается третьей, так как у них совпадает дата начала 05.01.2020. Для решения такой задачи достаточно выполнить группировку по полю начала периода и вычислить максимум по полю окончания. Проанализируем пример кода:
ВЫБРАТЬ
ТЗ.Начало КАК Начало,
МАКСИМУМ(ТЗ.Конец) КАК Конец
ИЗ
ВременнаяТаблицаИнтервалов КАК ТЗ
СГРУППИРОВАТЬ ПО
ТЗ.Начало
Важно понимать: этот метод сработает только в том случае, если пересекающиеся интервалы всегда начинаются в одну и ту же дату. Если же интервалы пересекаются частично (например, 03.01.2020–10.01.2020 и 05.01.2020–31.01.2020), этот запрос выдаст две разные строки, что не решит нашу задачу. Поэтому перейдем к более универсальным методам.
Когда интервалы образуют "цепочки" (один цепляет другой), нам необходимо соединять таблицу саму с собой, чтобы проверить условия пересечения. Выясним причину, по которой это сложно: в языке запросов 1С нет рекурсии, поэтому мы ограничены количеством соединений, которые пропишем в коде.
Рассмотрим логику "в лоб", предложенную в сообщении 6. Идея заключается в том, чтобы соединить таблицу интервалов с самой собой по условию: "если начало или конец одного интервала попадает внутрь другого (с учетом дискретности +1 день)". Разберем этот алгоритм на примере:
// Шаг 1: Первичное объединение пересекающихся интервалов
ВЫБРАТЬ РАЗЛИЧНЫЕ
МИНИМУМ(ТЗСоед.Нач) КАК Нач,
МАКСИМУМ(ТЗСоед.Кон) КАК Кон
ПОМЕСТИТЬ ТЗ2
ИЗ
ТЗ КАК ТЗ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ТЗ КАК ТЗСоед
ПО (ТЗ.Нач МЕЖДУ ТЗСоед.Нач И ТЗСоед.Кон + 1)
ИЛИ (ТЗ.Кон МЕЖДУ ТЗСоед.Нач - 1 И ТЗСоед.Кон)
СГРУППИРОВАТЬ ПО
ТЗ.Нач,
ТЗ.Кон
В этом коде используется условие + 1 к дате. Это критически важный момент для работы с типом Дата (когда речь идет о днях). Если один период заканчивается 05.01.2020, а второй начинается 06.01.2020, они считаются примыкающими и должны быть объединены в один остров.
Однако у этого метода есть предел: одно такое соединение "схлопывает" только два звена цепи. Если у вас идет последовательность из 10 пересекающихся интервалов, вам придется повторить это соединение несколько раз (создавая ТЗ3, ТЗ4 и так далее), как и показано в примере форума. Это не самый элегантный способ, но для коротких цепочек (3-4 интервала) он вполне работоспособен.
Теперь разберем наиболее профессиональный и эффективный подход, который позволяет решить задачу за фиксированное количество шагов, независимо от длины цепочки пересечений. Суть метода заключается в том, чтобы идентифицировать те строки, которые являются началом нового объединенного интервала.
Проанализируем шаги этого алгоритма:
МАКСИМУМ(Конец).Посмотрим, как это выглядит в коде запроса 1С:
// 1. Находим все начала островов
ВЫБРАТЬ РАЗЛИЧНЫЕ
Т1.Начало КАК Начало
ПОМЕСТИТЬ НачалаОстровов
ИЗ
ВТ_Исходная КАК Т1
ГДЕ
НЕ ВЫБРАТЬ 1
ИЗ ВТ_Исходная КАК Т2
ГДЕ Т2.Начало < Т1.Начало
И Т2.Конец >= ДОБАВИТЬКДАТЕ(Т1.Начало, ДЕНЬ, -1)
;
// 2. Для каждого интервала находим его начало острова и схлопываем
ВЫБРАТЬ
Начала.Начало КАК Начало,
МАКСИМУМ(Т3.Конец) КАК Конец
ИЗ
НачалаОстровов КАК Начала
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Исходная КАК Т3
ПО Т3.Начало >= Начала.Начало
И Т3.Начало <= (
ВЫБРАТЬ МИНИМУМ(Н2.Начало)
ИЗ НачалаОстровов КАК Н2
ГДЕ Н2.Начало > Начала.Начало
) // Условие, чтобы не захватить данные из следующего острова
СГРУППИРОВАТЬ ПО
Начала.Начало
Этот метод работает значительно быстрее на больших объемах данных, так как он минимизирует количество соединений и использует логику исключения. Мы фактически разбиваем временную шкалу на независимые блоки (острова) и работаем с ними по отдельности.
При реализации любого из перечисленных методов крайне важно учитывать точность хранения времени. Выясним причину возможных ошибок при объединении:
ДОБАВИТЬКДАТЕ(Дата, ДЕНЬ, 1).01.01.2020 00:00:00 по 31.01.2020 23:59:59. В этом случае "следующей" секундой будет начало следующего дня.Рассмотрим пример правильного условия соединения для интервалов с точностью до секунды:
ПО Т1.Конец >= ДОБАВИТЬКДАТЕ(Т2.Начало, СЕКУНДА, -1). Это позволит объединить интервалы, которые идут строго друг за другом без зазора в одну секунду.
Перед тем как приступать к "тяжелым" соединениям для поиска островов, рекомендуем выполнить подготовительные действия, которые сократят нагрузку на сервер SQL:
ВЫБРАТЬ РАЗЛИЧНЫЕ или простую группировку по Начало, Конец. Если в исходных данных много полных дублей (одинаковые периоды для разных документов), это уменьшит количество записей в разы.Начало и Конец. В запросах с условиями МЕЖДУ или сравнениями Больше/Меньше индексы критически влияют на скорость выполнения соединения.Таким образом, "эквилибристика" в запросах при работе с датами требует понимания структуры данных и владения методами реляционной алгебры. Для простых случаев используйте МАКСИМУМ, а для сложных иерархий — алгоритм поиска начала островов.