История: В хранилищах временных данных доминирует техника Последнего наблюдения, перенесенного вперед (LOCF) для заполнения пропусков с помощью предыдущих действительных записей. Однако конкретные аналитические области, такие как применение окончательных сверок в конце дня к внутридневным финансовым транзакциям или обратное распространение лабораторных подтверждений к более ранним временным диагнозам, требуют обратного подхода Следующего наблюдения, перенесенного назад (NOCB). Исторически NOCB реализовывался через коррелированные подзапросы или процедурные курсоры, которые оба имеют O(n²) сложность и не используют современные наборные оптимизаторы.
Проблема: Учитывая полностью упорядоченную последовательность (например, event_time), каждое значение NULL должно быть заменено ближайшим не-NULL значением, которое происходит после него в последовательности. Последовательные NULL перед действительной записью должны получить одно и то же последующее значение. Стандартные функции, такие как LEAD(), получают доступ только к немедленной следующей строке, что не срабатывает, когда перед не-NULL якорем существуют несколько последовательных NULL. Самосоединения и рекурсивные CTE запрещены из-за ограничений производительности.
Решение: Решение использует семантику игнорирования NULL в COUNT(expression). Подсчитывая не-NULL значения от текущей строки до конца раздела (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), мы создаем стабильный "идентификатор ведра", который одинаков для всех строк между двумя не-NULL якорями. В каждом ведре MAX(val)—который также игнорирует NULL—извлекает значение якоря и передает его всем строкам в этой группе.
WITH bucketed AS ( SELECT record_id, event_time, status_code, COUNT(status_code) OVER ( ORDER BY event_time, record_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS bucket_id FROM audit_log ) SELECT record_id, event_time, COALESCE( MAX(status_code) OVER ( PARTITION BY bucket_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ), 'UNKNOWN' ) AS confirmed_status FROM bucketed;
Контекст и Описание Проблемы: Компания высокочастотной торговли ведет таблицу execution, фиксируя микросекундные сделки с акциями. Из-за протоколов отчетности биржи окончательная "консолидированная цена" для любой данной минуты—проверенная клиринговым домом—поступает через 30 секунд после окончания минуты и отмечается только на границе (например, 14:30:00.000). Для расчетов TWAP (временной взвешенной средней цены) каждая миллисекунда этой минуты должна отражать окончательную консолидированную цену, требуя заполнения всех предыдущих записей с 14:29:00.000 по 14:29:59.999. Дневной объем превышает 50 миллионов строк, а окно пакетной обработки составляет 10 минут.
Решение 1: Коррелированный скалярный подзапрос. Этот подход использует скалярный подзапрос для каждой строки, чтобы найти MIN(event_time) будущих строк, где consolidated_price IS NOT NULL, а затем присоединяется, чтобы извлечь эту цену.
Плюсы: Концептуально прост для разработчиков с процедурным опытом.
Минусы: Выполняет O(n²) сравнений. На производственных данных время выполнения запроса превышало 45 минут, что нарушало окно пакетной обработки. Обработка нескольких последовательных NULL требует дополнительной логики, чтобы пропустить вперед, увеличивая сложность и уровень ошибок.
Решение 2: Рекурсивный обход CTE. Рекурсивный CTE итерирует назад построчно, перенося не-NULL цену назад до тех пор, пока не встретит другую не-NULL.
Плюсы: Гарантированно работает на любой базе данных, совместимой с ANSI SQL.
Минусы: Рекурсивные CTE последовательно обрабатывают строки во многих движках (например, PostgreSQL), что приводит к однопоточной обработке и потенциальному переполнению стека на глубоких разделах. Бенчмарки показали время выполнения 20 минут при высокой нагрузке на память, что делает это неподходящим для производственных SLA.
Решение 3: Бакетизация с помощью оконной функции (Выбранное). Реализуйте шаблон COUNT и MAX. Обратный COUNT создает идентичные ведра для всех строк, которые требуют одного и того же будущего значения, в то время как MAX распространяет это значение в пределах ведра.
Плюсы: Полностью наборный, параллелизуемый и выполняется за O(n log n) время благодаря операции сортировки. Он линейно масштабируется с объемом и использует стандартный ANSI SQL, переносимый на PostgreSQL, SQL Server, Oracle и DB2.
Минусы: Требует два прохода по данным (первый CTE и внешний запрос), хотя современные оптимизаторы часто сливают их. Он требует полного порядка; дубликаты временных меток требуют колонки для разрыва ничьей, чтобы обеспечить детерминизм.
Результат: Время выполнения конвейера сократилось с 45 минут до 8 секунд на наборе данных в 50 миллионов строк. Компания устранила хрупкий Python-скрипт для заполнения, уменьшив сложность инфраструктуры и обеспечив генерацию регламентированных отчетов в пределах временного окна соблюдения.
Почему необходимо использовать COUNT(column), а не COUNT(*) или ROW_NUMBER() при построении ключа группировки?
Многие кандидаты инстинктивно используют COUNT(*) или ROW_NUMBER(), полагая, что эти функции могут сегментировать данные. COUNT(*) считает каждую строку, независимо от NULL, создавая уникальное, монотонно изменяющееся значение для каждой строки в обратной области, что предотвращает образование стабильных групп. ROW_NUMBER() присваивает уникальный идентификатор каждой строке, также разрушая группировку. Только COUNT(column) увеличивается исключительно при встрече не-NULL значений, тем самым присваивая одно и то же "идентификатор ведра" всем предшествующим NULL до следующей не-NULL границы. Это различие имеет решающее значение, так как оно использует семантику игнорирования NULL агрегатных оконных функций, чтобы смоделировать "предварительный обзор" без процедурной логики.
Как запрос ведет себя, если раздел заканчивается конечными значениями NULL, и какое изменение гарантирует детерминированное обращение, когда будущего наблюдения не существует?
Если последние строки в упорядоченном разделе являются NULL, то COUNT(status_code) оценивается как ноль для этих строк. Следовательно, MAX(status_code) возвращает NULL, что логически правильно—нет будущего наблюдения для переноса назад. Кандидаты часто забывают обработать это в последующей бизнес-логике. Чтобы предоставить значение по умолчанию (например, статический заполнитель или значение из внешнего поиска), необходимо обернуть результат в COALESCE. Более того, чтобы различить "заполненный NULL" и "незаполняемый NULL" для мониторинга качества данных, необходимо сравнить оригинальные и заполненные значения: CASE WHEN status_code IS NULL AND bucket_id = 0 THEN 'UNCONFIRMED' END.
Какая проблема детерминизма возникает, если в предложении ORDER BY содержатся дубликаты значений, и почему переход от ROWS к RANGE усугубляет проблему?
Когда ключи сортировки содержат дубликаты (ничьи), определение оконного фрейма становится неопределенным. Использование ROWS (физические смещения) присваивает группы на основе физического порядка таблицы, который является произвольным, если не предоставлен уникальный вторичный ключ сортировки. Переход к RANGE (логические диапазоны значений) рассматривает все строки с одинаковым значением сортировки как равных, заставляя их делить один и тот же фрейм. В этом решении, если несколько строк имеют одинаковое event_time, RANGE может неправильно сгруппировать NULL строки с не-NULL строками из того же временного штампа или непредсказуемо разделить группы. Кандидаты должны обеспечить полный порядок, добавив уникальный ключ (например, record_id) в предложение ORDER BY: ORDER BY event_time, record_id, чтобы гарантировать детерминированное распределение ведер между всеми реализациями ANSI SQL.