Ответ на вопрос.

История: До того как ANSI SQL:2003 ввел аналитические функции, заполнение разреженных временных рядов требовало неэффективных самосоединений или процедурных курсоров, которые обрабатывали строки по отдельности. Шаблон LOCF возник в статистических пакетах, таких как SAS и R, где перенос последнего известного наблюдения является стандартной техникой очистки данных. Позже вендоры баз данных реализовали эту логику в SQL с помощью оконных функций, с формализованным в ANSI SQL:2011 вариантом IGNORE NULLS, специально предназначенным для обработки таких пробелов декларативно.

Проблема: Сенсорные сети и финансовые торговые системы часто генерируют значения NULL из-за сбоев передачи или нерабочих часов. Простые функции LAG не работают, потому что они возвращают немедленного предшественника, который также может быть NULL, создавая пробелы в вычисляемых метриках. Задача требует сканирования назад по упорядоченному разделу до тех пор, пока не будет найдено самое недавнее ненулевое значение, без использования самосоединений, которые ухудшают производительность квадратично.

Решение: Используйте оконную функцию LAST_VALUE с опцией IGNORE NULLS и спецификацией рамки, расширяющейся от начала раздела до текущей строки. Эта конфигурация инструктирует движок поддерживать запущенный буфер ненулевых значений, эффективно оглядываясь назад через NULLs, чтобы извлечь последнее действительное наблюдение. Для систем, не поддерживающих IGNORE NULLS, возможно, потребуется обойти ограничение, используя COUNT ненулевых значений для создания стабильных групп, хотя это технически вовлекает подзапрос.

SELECT 
    device_id,
    reading_time,
    temperature,
    LAST_VALUE(temperature IGNORE NULLS) OVER (
        PARTITION BY device_id 
        ORDER BY reading_time 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS locf_temperature
FROM sensor_readings;

Ситуация из жизни

Платформа анализа здоровья контролирует непрерывные уровни глюкозы у диабетиков с помощью носимых устройств. Из-за помех Bluetooth приблизительно 12% показаний поступают как NULL, но клиницисты требуют полные кривые для расчетов дозировки инсулина, где интерполяция может быть медико-опасной. Точный алгоритм LOCF имеет решающее значение, поскольку пропущенные значения во время сна или еды могут вызывать ложные сигналы гипогликемии.

Решение A: Обновление на основе курсоров. Процедура, написанная на PL/SQL, итерируется по записям пациентов в хронологическом порядке, поддерживая сессионную переменную для хранения последнего действительного показания глюкозы и немедленно обновляя строки NULL. Плюсы: совместимость со старыми версиями Oracle, предшествующими поддержке оконных функций; легко понимать разработчикам с фоновым опытом в императивном программировании. Минусы: обработка по строкам создает избыточные операции I/O и блокировки таблиц; обработка 10 миллионов строк занимает 45 минут, что делает невозможным создание реальных информационных панелей.

Решение B: Самосоединение с коррелированным подзапросом. Запрос выполняет левое соединение, чтобы найти максимальную отметку времени, меньшую текущей строки, где глюкоза НЕ NULL, эффективно подбирая предыдущее значение для каждой пропущенной линии. Плюсы: декларативный SQL без процедурного кода; работает на системах, соответствующих ANSI SQL-92. Минусы: сложность O(n²) вызывает экспоненциальное замедление; запрос превышает лимит времени через 6 часов на производственных наборах данных из-за повторных полных сканирований таблицы.

Решение C: Оконная функция с IGNORE NULLS. Реализует LAST_VALUE(glucose IGNORE NULLS), разделенный по пациенту и упорядоченный по времени, используя один проход по индексу. Плюсы: сложность O(n log n) выполняется за 28 секунд на том же наборе данных из 10 миллионов строк; минимальный объем памяти и отсутствие проблем с блокировкой. Минусы: требуется поддержка ANSI SQL:2011, необходима модернизация базы данных с существующей версии PostgreSQL 9.5.

Команда выбрала решение C после определения того, что стоимость обновления базы данных оправдана за счет 99% улучшения производительности. Реализация позволила получать уведомления о глюкозе в реальном времени и снизила использование CPU сервера на 94%. В результате клиника успешно контролировала 50 000 пациентов одновременно без задержек и пропусков критических всплесков глюкозы.

Что часто упускают кандидаты

Вопрос 1: Почему LAST_VALUE без IGNORE NULLS возвращает NULL, даже когда предыдущие ненулевые значения существуют в разделе?

По умолчанию LAST_VALUE оценивает рамку, включая текущую строку. Когда текущая строка содержит NULL, и рамка расширяется до CURRENT ROW, функция видит этот NULL как последнее значение в окне. Кандидаты неправильно предполагают, что функция сканирует назад бесконечно; однако без IGNORE NULLS она рассматривает NULL как действительные значения. Оконная рамка ROWS UNBOUNDED PRECEDING включает текущую строку, делая LAST_VALUE эквивалентным значению текущей строки, если не указано явно игнорировать NULLs.

Вопрос 2: Как можно реализовать LOCF в предшествующем 2011 году ANSI SQL без IGNORE NULLS, и в чем логическая ошибка в использовании разницы ROW_NUMBER вместо COUNT?

Вы можете использовать COUNT(non_null_col) OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING), чтобы создать идентификатор группы, который инкрементируется только при встрече ненулевых значений. Все последующие NULLs делят этот счет, образуя группу переноса. Кандидаты иногда пытаются вычесть ROW_NUMBER() OVER (ORDER BY ...) из ROW_NUMBER() OVER (PARTITION BY non_null_flag ORDER BY ...). Это не срабатывает, потому что создает новые группы для каждого пробела между ненулевыми, а не продлевает предыдущую группу вперед. Метод COUNT работает, потому что он создает стабильный идентификатор для всего периода последних известных значений.

Вопрос 3: При использовании RANGE вместо ROWS для LOCF по меткам времени с дубликатами, почему результаты могут стать недетерминированными?

RANGE разбивает строки с одинаковыми значениями ORDER BY на группы коллег, рассматривая их как единое целое. Если несколько показаний сенсоров имеют одинаковую отметку времени в миллисекундах, RANGE UNBOUNDED PRECEDING не может различить их физический порядок. Когда некоторые дубликаты содержат NULL, а другие имеют значения, оконная функция может случайно выбрать из группы коллег в зависимости от плана выполнения. Оконная рамка ROWS гарантирует детерминированные результаты, обрабатывая физический порядок строк, обеспечивая, чтобы конкретная последовательность вставок определяла, какое значение переносится вперед. Это отличие критично для данных высокочастотной торговли, где важны микросекунды.