При работе с данными, которые представлены в виде последовательности временных интервалов, часто возникает задача по их агрегации или "сворачиванию". Это необходимо, когда несколько последовательных или пересекающихся интервалов обладают одинаковыми ключевыми характеристиками, и мы хотим представить их как один укрупненный период. Рассмотрим эту проблему подробнее на примере из запроса.
Исходная таблица данных выглядит следующим образом:
ТН ДатаНачала ДатаОкончания График
01934000 03.02.2022 03.02.2022 График1
01934000 04.02.2022 13.02.2022 График1
01934000 14.02.2022 16.02.2022 График1
01934000 17.02.2022 21.02.2022 График25
01934000 22.02.2022 31.12.3999 График1
Цель — получить свернутые периоды, где непрерывные интервалы с одинаковым ТН и Графиком объединены в один:
ТН ДатаНачала ДатаОкончания График
01934000 03.02.2022 16.02.2022 График1
01934000 17.02.2022 21.02.2022 График25
01934000 22.02.2022 31.12.3999 График1
Первая интуитивная попытка решения часто заключается в использовании функций агрегации МИНИМУМ(ДатаНачала) и МАКСИМУМ(ДатаОкончания) с группировкой по ТН и График. Давайте посмотрим на такой запрос:
ВЫБРАТЬ
ТЗ.ТН КАК ТН,
МИНИМУМ(ТЗ.ДатаНачала) КАК ДатаНачала,
МАКСИМУМ(ТЗ.ДатаОкончания) КАК ДатаОкончания,
ТЗ.График КАК График
ИЗ
&ТвояТЗ КАК ТЗ
СГРУППИРОВАТЬ ПО
ТЗ.ТН,
ТЗ.График
Если мы применим этот запрос к нашим исходным данным, мы получим следующий результат:
ТН ДатаНачала ДатаОкончания График
01934000 03.02.2022 31.12.3999 График1
01934000 17.02.2022 21.02.2022 График25
Как видим, этот результат неправильный. Проблема заключается в том, что График1 прерывается другим графиком (График25). Простая группировка объединяет все записи с График1 в один большой интервал, игнорируя эту прерывность. Нам же нужно разделить График1 на два отдельных периода: до График25 и после него.
Задача сворачивания непрерывных или смежных периодов с одинаковыми атрибутами известна в SQL как проблема "островов и промежутков" (Gaps and Islands). Суть в том, чтобы найти "острова" (непрерывные последовательности) одинаковых данных, разделенные "промежутками" (изменениями атрибутов или разрывами в датах).
Для корректного сворачивания необходимо определить, когда начинается новый непрерывный период. Это происходит в двух случаях:
График).ДатаНачала текущей записи не равна ДатаОкончания предыдущей записи + 1 день.Если мы сможем пометить начало каждого такого "острова", то сможем затем сгруппировать записи по этим меткам и получить корректные свернутые периоды.
В стандартном SQL эта задача элегантно решается с помощью оконных функций, таких как LAG (для получения значения из предыдущей строки) и LEAD (для получения значения из следующей строки). Язык запросов 1С не поддерживает эти функции напрямую, но мы можем эмулировать их с помощью подзапросов или самосоединений (self-join).
Мы должны для каждой записи определить, является ли она началом нового непрерывного периода. Это происходит, если:
ТН (нет предыдущей).График текущей записи отличается от Графика предыдущей записи.ДатаНачала текущей записи не следует непосредственно за ДатаОкончания предыдущей записи.Для этого нам понадобится временная таблица, которая для каждой записи будет содержать информацию о предыдущей записи (по ТН и упорядоченная по ДатаНачала).
ВЫБРАТЬ
Т.ТН,
Т.ДатаНачала,
Т.ДатаОкончания,
Т.График,
(ВЫБРАТЬ
МАКСИМУМ(Предыдущая.ДатаНачала)
ИЗ
&ТвояТЗ КАК Предыдущая
ГДЕ
Предыдущая.ТН = Т.ТН
И Предыдущая.ДатаНачала < Т.ДатаНачала
) КАК ДатаНачалаПредыдущейЗаписи
ПОМЕСТИТЬ ИсходныеДанныеСПредыдущимиДатами
ИЗ
&ТвояТЗ КАК Т
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т.ТН,
Т.ДатаНачала,
Т.ДатаОкончания,
Т.График,
ВТПредыдущие.График КАК ПредыдущийГрафик,
ВТПредыдущие.ДатаОкончания КАК ДатаОкончанияПредыдущейЗаписи,
ВЫБОР
КОГДА ВТПредыдущие.График ЕСТЬ NULL ИЛИ
Т.График <> ВТПредыдущие.График ИЛИ
РАЗНОСТЬДАТ(ВТПредыдущие.ДатаОкончания, Т.ДатаНачала, ДЕНЬ) <> -1
ТОГДА ИСТИНА
ИНАЧЕ ЛОЖЬ
КОНЕЦ КАК НачалоНовойГруппы
ПОМЕСТИТЬ ДанныеСМеткамиГрупп
ИЗ
ИсходныеДанныеСПредыдущимиДатами КАК Т
ЛЕВОЕ СОЕДИНЕНИЕ &ТвояТЗ КАК ВТПредыдущие
ПО Т.ТН = ВТПредыдущие.ТН
И Т.ДатаНачалаПредыдущейЗаписи = ВТПредыдущие.ДатаНачала
УПОРЯДОЧИТЬ ПО
Т.ТН,
Т.ДатаНачала
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ДанныеСМеткамиГрупп.ТН,
ДанныеСМеткамиГрупп.ДатаНачала,
ДанныеСМеткамиГрупп.ДатаОкончания,
ДанныеСМеткамиГрупп.График,
СУММА(ВЫБОР
КОГДА ДанныеСМеткамиГрупп.НачалоНовойГруппы
ТОГДА 1
ИНАЧЕ 0
КОНЕЦ) ПЕРЕГРУППИРОВАТЬ ПО ДанныеСМеткамиГрупп.ТН УПОРЯДОЧИТЬ ПО ДанныеСМеткамиГрупп.ДатаНачала КАК НомерГруппы
ПОМЕСТИТЬ СгруппированныеПериоды
ИЗ
ДанныеСМеткамиГрупп КАК ДанныеСМеткамиГрупп
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
СгруппированныеПериоды.ТН,
СгруппированныеПериоды.График,
МИНИМУМ(СгруппированныеПериоды.ДатаНачала) КАК ДатаНачала,
МАКСИМУМ(СгруппированныеПериоды.ДатаОкончания) КАК ДатаОкончания
ИЗ
СгруппированныеПериоды КАК СгруппированныеПериоды
СГРУППИРОВАТЬ ПО
СгруппированныеПериоды.ТН,
СгруппированныеПериоды.График,
СгруппированныеПериоды.НомерГруппы
УПОРЯДОЧИТЬ ПО
СгруппированныеПериоды.ТН,
ДатаНачала
Разберем этот запрос по шагам:
ИсходныеДанныеСПредыдущимиДатами: На этом шаге мы обогащаем каждую запись из исходной таблицы полем ДатаНачалаПредыдущейЗаписи. Это делается с помощью подзапроса, который для каждой строки ищет максимальную ДатаНачала для того же ТН, которая меньше текущей ДатаНачала. Это позволяет нам имитировать функцию LAG по полю ДатаНачала.ДанныеСМеткамиГрупп: Здесь мы делаем левое соединение нашей промежуточной таблицы с исходной таблицей, чтобы получить все поля предыдущей записи (График и ДатаОкончанияПредыдущейЗаписи). Затем мы используем конструкцию ВЫБОР КОГДА для определения флага НачалоНовойГруппы. Если предыдущей записи нет (ВТПредыдущие.График ЕСТЬ NULL), или График изменился, или даты не смежные (разница в днях не равна -1, что означает, что текущая дата начала не равна предыдущей дате окончания + 1 день), то это начало новой группы.СгруппированныеПериоды: На этом шаге мы используем оконную функцию СУММА() ПЕРЕГРУППИРОВАТЬ ПО ... УПОРЯДОЧИТЬ ПО ... для вычисления накопительной суммы флага НачалоНовойГруппы. Это эффективно присваивает уникальный номер каждой непрерывной группе. Каждый раз, когда начинается новая группа, счетчик увеличивается. Таким образом, все записи в одном "острове" будут иметь одинаковый НомерГруппы.НомерГруппы, мы можем просто сгруппировать данные по ТН, Графику и НомерГруппы, используя МИНИМУМ(ДатаНачала) и МАКСИМУМ(ДатаОкончания), чтобы свернуть каждую группу в один интервал.Этот подход достаточно гибок и позволяет получить требуемый результат в языке запросов 1С, эмулируя поведение оконных функций.
Другой, более общий подход, особенно подходящий для случаев, когда интервалы могут произвольно пересекаться, а не только быть строго смежными, основывается на концепции транзитивного замыкания (Transitive Closure). Метод, представленный в сообщении 3, использует итеративные самосоединения для построения цепочек смежных интервалов.
Рассмотрим код, представленный на форуме, и подробно разберем его работу:
// Создаем временную таблицу с исходными данными
ВЫБРАТЬ
"Отрезок1" КАК Ссылка,
ДАТАВРЕМЯ(2021, 10, 1) КАК ДатаНачала,
ДАТАВРЕМЯ(2021, 10, 2) КАК ДатаКонца,
"+" КАК Аналитика1
ПОМЕСТИТЬ мТаб
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Отрезок1",
ДАТАВРЕМЯ(2021, 10, 2),
ДАТАВРЕМЯ(2021, 10, 5),
"+"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Отрезок1",
ДАТАВРЕМЯ(2021, 10, 5),
ДАТАВРЕМЯ(2021, 10, 6),
"+"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Отрезок1",
ДАТАВРЕМЯ(2021, 10, 6),
ДАТАВРЕМЯ(2021, 10, 7),
"+"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Отрезок1",
ДАТАВРЕМЯ(2021, 10, 7),
ДАТАВРЕМЯ(2021, 10, 9),
"+"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Отрезок1",
ДАТАВРЕМЯ(2021, 10, 9),
ДАТАВРЕМЯ(2021, 10, 10),
"+"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Отрезок1",
ДАТАВРЕМЯ(2021, 10, 10),
ДАТАВРЕМЯ(2021, 10, 11),
"+"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Отрезок1",
ДАТАВРЕМЯ(2021, 10, 5),
ДАТАВРЕМЯ(2021, 10, 8),
"-"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Отрезок1",
ДАТАВРЕМЯ(2021, 10, 22),
ДАТАВРЕМЯ(2021, 10, 25),
"+"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Отрезок1",
ДАТАВРЕМЯ(2021, 10, 25),
ДАТАВРЕМЯ(2021, 10, 26),
"+"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Отрезок2",
ДАТАВРЕМЯ(2021, 10, 1),
ДАТАВРЕМЯ(2021, 10, 7),
"+"
;
////////////////////////////////////////////////////////////////////////////////
// Исходные "дуги" (интервалы)
ВЫБРАТЬ
Таб.Ссылка КАК Ссылка,
Таб.ДатаНачала КАК НачалоДуги,
Таб.ДатаКонца КАК КонецДуги,
Таб.Аналитика1
ПОМЕСТИТЬ ЗамыканияДлины1
ИЗ
мТаб КАК Таб
;
////////////////////////////////////////////////////////////////////////////////
// Объединяем "дуги" длиной 1 в "дуги" длиной до 2
// Здесь мы ищем смежные интервалы: ПерваяДуга.КонецДуги = ВтораяДуга.НачалоДуги
ВЫБРАТЬ РАЗЛИЧНЫЕ
ПерваяДуга.НачалоДуги,
ВтораяДуга.КонецДуги,
ВтораяДуга.Ссылка,
ВтораяДуга.Аналитика1
ПОМЕСТИТЬ ЗамыканияДлины2
ИЗ
ЗамыканияДлины1 КАК ПерваяДуга
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ЗамыканияДлины1 КАК ВтораяДуга
ПО ПерваяДуга.КонецДуги = ВтораяДуга.НачалоДуги
И ПерваяДуга.Ссылка = ВтораяДуга.Ссылка
И ПерваяДуга.Аналитика1 = ВтораяДуга.Аналитика1
ОБЪЕДИНИТЬ
ВЫБРАТЬ
Таб.ДатаНачала,
Таб.ДатаКонца,
Таб.Ссылка,
Таб.Аналитика1
ИЗ
мТаб КАК Таб
;
////////////////////////////////////////////////////////////////////////////////
// Объединяем "дуги" длиной до 2 в "дуги" длиной до 4
// Повторяем процесс, используя уже удлиненные интервалы
ВЫБРАТЬ РАЗЛИЧНЫЕ
ПерваяДуга.НачалоДуги,
ВтораяДуга.КонецДуги,
ВтораяДуга.Ссылка,
ВтораяДуга.Аналитика1
ПОМЕСТИТЬ ЗамыканияДлины4
ИЗ
ЗамыканияДлины2 КАК ПерваяДуга
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ЗамыканияДлины2 КАК ВтораяДуга
ПО ПерваяДуга.КонецДуги = ВтораяДуга.НачалоДуги
И ПерваяДуга.Ссылка = ВтораяДуга.Ссылка
И ПерваяДуга.Аналитика1 = ВтораяДуга.Аналитика1
ОБЪЕДИНИТЬ
ВЫБРАТЬ
Таб.ДатаНачала,
Таб.ДатаКонца,
Таб.Ссылка,
Таб.Аналитика1
ИЗ
мТаб КАК Таб
;
////////////////////////////////////////////////////////////////////////////////
// Объединяем "дуги" длиной до 4 в "дуги" длиной до 8
// Продолжаем процесс укрупнения
ВЫБРАТЬ РАЗЛИЧНЫЕ
ПерваяДуга.НачалоДуги,
ВтораяДуга.КонецДуги,
ВтораяДуга.Ссылка,
ВтораяДуга.Аналитика1
ПОМЕСТИТЬ ЗамыканияДлины8
ИЗ
ЗамыканияДлины4 КАК ПерваяДуга
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ЗамыканияДлины4 КАК ВтораяДуга
ПО ПерваяДуга.КонецДуги = ВтораяДуга.НачалоДуги
И ПерваяДуга.Ссылка = ВтораяДуга.Ссылка
И ПерваяДуга.Аналитика1 = ВтораяДуга.Аналитика1
ОБЪЕДИНИТЬ
ВЫБРАТЬ
Таб.ДатаНачала,
Таб.ДатаКонца,
Таб.Ссылка,
Таб.Аналитика1
ИЗ
мТаб КАК Таб
;
////////////////////////////////////////////////////////////////////////////////
// Находим максимальную конечную дату для каждой начальной даты внутри группы
ВЫБРАТЬ
ЗамыканияДлины8.НачалоДуги КАК Предок,
МАКСИМУМ(ЗамыканияДлины8.КонецДуги) КАК Потомок,
ЗамыканияДлины8.Ссылка,
ЗамыканияДлины8.Аналитика1
ПОМЕСТИТЬ м1
ИЗ
ЗамыканияДлины8 КАК ЗамыканияДлины8
ГДЕ
ЗамыканияДлины8.НачалоДуги <> ЗамыканияДлины8.КонецДуги
СГРУППИРОВАТЬ ПО
ЗамыканияДлины8.Ссылка,
ЗамыканияДлины8.НачалоДуги,
ЗамыканияДлины8.Аналитика1
;
////////////////////////////////////////////////////////////////////////////////
// Находим минимальную начальную дату для каждой максимальной конечной даты,
// чтобы получить финальные свернутые интервалы.
ВЫБРАТЬ
МИНИМУМ(м1.Предок) КАК ДатаНачала,
м1.Потомок КАК ДатаОкончания,
м1.Ссылка,
м1.Аналитика1
ИЗ
м1 КАК м1
СГРУППИРОВАТЬ ПО
м1.Ссылка,
м1.Потомок,
м1.Аналитика1
УПОРЯДОЧИТЬ ПО
м1.Ссылка,
ДатаНачала
мТаб: Это исходный набор данных, который имитирует нашу таблицу с периодами. В нем есть Ссылка (аналог ТН), ДатаНачала, ДатаКонца (аналог ДатаОкончания) и Аналитика1 (аналог График). Обратите внимание, что здесь есть как смежные, так и пересекающиеся интервалы, а также интервалы с разной аналитикой.ЗамыканияДлины1: Просто копирует исходные данные из мТаб, обозначая их как "дуги длиной 1". НачалоДуги и КонецДуги — это наши ДатаНачала и ДатаКонца.ЗамыканияДлины2: Здесь начинается магия транзитивного замыкания. Мы выполняем внутреннее соединение ЗамыканияДлины1 саму с собой. Условие соединения: ПерваяДуга.КонецДуги = ВтораяДуга.НачалоДуги, а также совпадение Ссылка и Аналитика1. Это условие находит все пары интервалов, где конец первого совпадает с началом второго, т.е. они непрерывно следуют друг за другом. Результат такого соединения — новые, более длинные интервалы, которые "протягиваются" от начала первой дуги до конца второй. Затем мы ОБЪЕДИНЯЕМ их с исходными "дугами длиной 1", чтобы не потерять короткие интервалы, которые не удалось объединить. Так мы получаем все возможные "дуги" длиной до 2.ЗамыканияДлины4: Мы повторяем тот же процесс, но теперь уже соединяем ЗамыканияДлины2 (интервалы длиной до 2) саму с собой. Это позволяет нам находить цепочки из четырех исходных интервалов (или из двух "удвоенных" интервалов). Таким образом, мы получаем "дуги" длиной до 4.ЗамыканияДлины8: Аналогично, соединяем ЗамыканияДлины4 саму с собой, чтобы получить "дуги" длиной до 8. Принцип заключается в удвоении длины на каждом шаге, пока не будут найдены все возможные непрерывные цепочки. Количество таких итераций зависит от максимальной теоретической длины цепочки в ваших данных (2^N должно быть больше или равно максимальной длине цепочки). Для наших данных, где периоды исчисляются днями, а общая длина может быть большой, количество итераций может быть значительным. В примере используются 3 итерации (длина 1 -> 2 -> 4 -> 8), что достаточно для не очень длинных цепочек.м1: После того как мы получили все возможные "дуги" (т.е. все непрерывные пути между начальными и конечными точками с одинаковыми атрибутами), нам нужно выбрать из них те, которые представляют собой максимальное расширение. Для каждой уникальной НачалоДуги и Аналитика1 мы находим МАКСИМУМ(КонецДуги). Это дает нам самые длинные интервалы, начинающиеся в данной точке. Условие ЗамыканияДлины8.НачалоДуги <> ЗамыканияДлины8.КонецДуги исключает однодневные интервалы, которые не являются частью более длинной цепочки (хотя их можно оставить, если требуется).м1. Теперь для каждой Потомок (максимальной конечной даты свернутого интервала) мы находим МИНИМУМ(Предок) (минимальную начальную дату). Это позволяет окончательно "схлопнуть" все пересекающиеся или смежные интервалы в один, получив итоговую ДатаНачала и ДатаОкончания.Этот метод с транзитивным замыканием является мощным, но может быть очень ресурсоемким для больших объемов данных из-за многократных самосоединений. Он хорош для демонстрации принципов, но в продакшене для большого количества записей предпочтительнее искать более оптимизированные решения.
Как мы уже упоминали, в "чистом" SQL существуют более современные и производительные методы для решения задачи "островов и промежутков":
LAG или LEAD: Эти функции позволяют получить значение из предыдущей или следующей строки в пределах определенной группы (партиции) и порядка. Это идеальный инструмент для определения моментов, когда График меняется или периоды перестают быть смежными.
LAG(График, 1, NULL) OVER (PARTITION BY ТН ORDER BY ДатаНачала)).SUM(CASE WHEN НачалоНовойГруппы THEN 1 ELSE 0 END) OVER (PARTITION BY ТН ORDER BY ДатаНачала)).ТН, График и этому номеру группы для получения MIN(ДатаНачала) и MAX(ДатаОкончания).К сожалению, язык запросов 1С не имеет прямых аналогов LAG/LEAD. Однако, как мы показали в первом примере, их функциональность можно эмулировать с помощью подзапросов или самосоединений для поиска предыдущей/следующей записи, а затем использовать встроенную оконную функцию СУММА() ПЕРЕГРУППИРОВАТЬ ПО ... УПОРЯДОЧИТЬ ПО ... для создания номеров групп.
Язык запросов 1С также не поддерживает рекурсивные CTE. Если ваша задача требует такой сложности, возможно, придется выгружать данные во временные таблицы и обрабатывать их на языке 1С или использовать прямой доступ к СУБД с полноценными SQL-возможностями.
T1.ДатаНачала <= T2.ДатаОкончания И T1.ДатаОкончания >= T2.ДатаНачала находит все пары интервалов, которые пересекаются. Само по себе тэта-соединение не сворачивает периоды, но является инструментом для идентификации их взаимосвязей, который затем используется в более сложных запросах (например, с рекурсивными CTE или при поиске максимального охвата).Выбор метода зависит от специфики ваших данных и требований к производительности. Для большинства стандартных задач свертывания непрерывных периодов в 1С подход с эмуляцией оконных функций (как в первом примере) или модифицированный подход с поиском точек изменения будет оптимальным. Метод транзитивного замыкания, хоть и является рабочим, может быть избыточно сложным и менее производительным для простых случаев, но демонстрирует мощные возможности языка запросов 1С.
Важно помнить, что эффективное использование временных таблиц (ПОМЕСТИТЬ) и правильная структура запроса могут значительно повысить производительность при работе с большими объемами данных в 1С.