Это задача возникает в области планирования емкости и распределения ресурсов, особенно в системах, таких как платформы бронирования отелей, автоматическое масштабирование облачной инфраструктуры и расписание медицинских учреждений. Ранее решения полагались на итерацию с помощью курсоров или внешней логики приложений для прохода по временным шкалам, что привело к серьезным потерь в производительности при работе с большими наборами данных. Появление функций окон ANSI SQL:2003 обеспечило чисто реляционные подходы к временному анализу, позволяя базам данных эффективно обрабатывать сложную интервальную арифметику внутри движка.
Имея таблицу бронирований ресурсов с временными метками start_time и end_time, цель заключается в том, чтобы определить максимальное количество одновременных резервирований, активных в любой момент времени, и определить конкретные временные окна, когда этот пик произошел. Сложность возникает из-за того, что стандартная агрегация сворачивает временные данные, в то время как простые объединения создают каскадный взрыв, когда интервалы перекрываются. Надежное решение должно рассматривать начало и конец интервала как дискретные события, вычисляя текущий подсчет активных ресурсов в каждой точке перехода.
Канонический подход преобразует интервалы в дискретные события, используя UNION ALL, чтобы отделить начала (вес +1) и концы (вес -1), затем применяет накопительную сумму с помощью SUM() OVER (ORDER BY timestamp) для отслеживания одновременности. Чтобы обработать одновременные начала/концы детерминированно, события окончания должны обрабатываться до событий начала в одной и той же временной метке (с использованием вторичного ключа сортировки). Наконец, оберните это в CTE для фильтрации максимального значения одновременности.
WITH events AS ( SELECT start_time AS ts, 1 AS delta, 0 AS is_end FROM reservations UNION ALL SELECT end_time AS ts, -1 AS delta, 1 AS is_end FROM reservations ), concurrency AS ( SELECT ts, SUM(delta) OVER (ORDER BY ts, is_end, delta ROWS UNBOUNDED PRECEDING) AS concurrent_count FROM events ) SELECT MAX(concurrent_count) AS peak_concurrency FROM concurrency;
Чтобы найти конкретные временные окна наивысшего использования, соедините обратно, чтобы идентифицировать периоды между последовательными временными метками, где количество равно максимальному.
Платформа SaaS отслеживала миллионы задач перекодирования видео в таблице jobs с временными метками started_at и completed_at. Операционной команде нужно было определить точные периоды, когда использование GPU достигало 100%, чтобы оптимизировать расписание очередей.
Один из подходов состоял в использовании курсора для итерации по временным меткам, увеличивая счетчик на стартах и уменьшая на окончаниях. Хотя это было просто для разработчиков, знакомых с императивными языками, этот метод обрабатывал строки последовательно, занимая более 45 минут на производственных данных и блокируя таблицы. Это также требовало сложного управления транзакциями для обеспечения консистентности чтения.
Другой альтернативой было создание таблицы временных рядов с одной строкой на минуту и объединение её с интервалами с использованием предикатов BETWEEN. Это давало точные результаты, но требовало миллиардов строк для точности на уровне минуты за год, потребляя терабайты временного хранения и не фиксируя пики под минуту.
Команда выбрала основанный на событиях подход UNION ALL с оконными функциями ANSI SQL. Обрабатывая начала и концы как события +1/-1, запрос выполнился за 12 секунд с использованием стандартных B-деревьев индексов по столбцам временных меток. Этот метод корректно обработал крайние случаи, когда задачи завершались точно в тот момент, когда другие начинались.
Анализ показал, что пик одновременности произошел во время ночной пакетной обработки между 02:00 и 02:07 UTC, достигнув 847 одновременных задач. Реализовав динамическое ограничение очередей специально для этого окна, они предотвратили каскадные сбои и сократили избыточное распределение инфраструктуры на 30%.
Как вы обрабатываете интервалы нулевой продолжительности (start_time = end_time) без неправильного увеличения подсчета одновременности?
Интервалы нулевой продолжительности представляют собой мгновенные события, которые не должны вносить вклад в одновременную нагрузку. Если их рассматривать как стандартные интервалы, они могут учитываться как активные во время своего собственного события окончания. Решение требует назначения строгого ключа упорядочивания: обрабатывайте события завершения (-1) перед событиями начала (+1), когда временные метки совпадают, и полностью исключайте интервалы нулевой продолжительности из потока событий или назначайте им дельту 0, в зависимости от бизнес-логики. В ANSI SQL это реализуется путем добавления столбца-дискриминатора: ORDER BY ts, is_end ASC, delta ASC, обеспечивая уменьшение подсчета при завершениях до того, как новые аллокации его увеличат в одинаковую временную метку.
Почему основанный на событиях подход потенциально может возвращать неправильные результаты, если вы используете UNION, а не UNION ALL при объединении событий начала и конца?
UNION неявно выполняет операцию DISTINCT, сворачивая дублирующиеся временные метки. Если два резервирования начинаются ровно в 2023-10-01 10:00:00, то UNION сокращает это до одной строки, в результате чего накопительная сумма пропускает одно увеличение +1. Это приводит к недоучету одновременности. UNION ALL сохраняет каждую отдельную границу интервала как отдельное событие, что математически необходимо, потому что каждое резервирование независимо вносит свой вклад в общую нагрузку. Кандидаты часто упускают эту разницу, предполагая уникальность временных меток, где многократность необходима для корректной агрегации.
При вычислении конкретных временных окон пиковой одновременности (а не только максимального значения), как вы избегаете пробелов в выводе, если несколько последовательных временных периодов имеют одно и то же пиковое значение?
После определения максимального значения одновременности, обратное соединение для поиска всех временных меток, где это происходит, дает дискретные точки. Чтобы реконструировать непрерывные блоки длительности, необходимо применить технику Gaps and Islands: используйте LAG(), чтобы проверить, если предыдущая строка также была на пике, и LEAD(), чтобы проверить, если следующая строка на пике. Выводите только строки, где предыдущее значение отличается (начало острова) или следующее значение отличается (конец острова). Затем соедините их, используя ROW_NUMBER(), чтобы создать пары начало-конец. Кандидаты часто выводят сырые списки временных меток или используют GROUP BY по значению счетчика, что теряет информацию о временной смежности, необходимую для различения отдельных инцидентов пиков из одного непрерывного пикового периода.