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

История вопроса

Средние значения с учетом времени стали критически важным показателем в промышленном IoT и финансовом анализе временных рядов. Простые арифметические средние искажают реальность, поскольку значения датчиков действительны до следующего измерения. До стандарта ANSI SQL:2003 вычисление этих средних требовало процедурных курсоров или дорогих самосоединений, что выполнялось за O(n²).

Введение оконных функций LEAD и LAG произвело революцию в этой области. Они позволили выполнять одномоментные, основанные на наборах интервалов вычисления, которые выполняются за O(n). Это делает реалтайм-анализ на миллиардах строк возможным в рамках слоя базы данных.

Проблема

Учитывая таблицу readings с колонками device_id, ts (метка времени) и value, цель состоит в вычислении взвешенного среднего. Каждая строка должна пропорционально способствовать времени до следующего показания. В математическом выражении это $\frac{\sum (value_i \times (ts_{i+1} - ts_i))}{\sum (ts_{i+1} - ts_i)}$.

Последняя строка представляет собой граничное условие. У нее нет последующей метки времени, поэтому ее интервал должен быть определен либо как ноль, экстраполирован до текущего времени, либо ограничен известным конечным временем. Решение должно избегать курсоров, пользовательских функций или самосоединений, чтобы оставаться чисто декларативным.

Решение

Используйте оконную функцию LEAD для проекции следующей метки времени в текущую строку. Вычислите разницу веков, чтобы получить вес. Затем примените стандартные формулы взвешенного среднего.

WITH weighted AS (
  SELECT
    device_id,
    value,
    ts,
    COALESCE(
      EXTRACT(EPOCH FROM (LEAD(ts) OVER (PARTITION BY device_id ORDER BY ts) - ts)),
      0
    ) AS duration_seconds
  FROM readings
)
SELECT
  device_id,
  SUM(value * duration_seconds) / NULLIF(SUM(duration_seconds), 0) AS time_weighted_avg
FROM weighted
GROUP BY device_id;

Этот подход использует PARTITION BY для того, чтобы окно сбрасывалось для каждого устройства. Это предотвращает смешивание меток времени от разных датчиков. COALESCE обрабатывает конечную строку, присваивая нулевой вес, эффективно исключая ее из знаменателя.

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

Линия фармацевтического производства контролирует 200 биореакторов. Каждый из них передает данные о температуре в нерегулярные интервалы — каждые 10 секунд в фазах нагрева, но каждые 30 минут во время простоя. Команда качества требовала ежедневного среднего значения с учетом времени для обеспечения соответствия. Простое среднее переоценивало быстрые образцы нагрева и недооценивало стабильные удержания, потенциально скрывая опасные температурные отклонения.

Одно из предложенных решений заключалось в извлечении всех данных в Python pandas DataFrame. Инженеры бы вычисляли diff() по меткам времени и вычисляли взвешенное среднее. Хотя этот подход был гибким, он передавал гигабайты данных по сети. Он также зависал на аналитической рабочей станции при обработке отчетов в конце квартала, охватывающих 90 дней высокочастотных данных.

Другой вариант использовал коррелированный подзапрос для нахождения MIN(ts), больших чем текущая строка для каждого устройства. Этот подход корректно работал по тестовым наборам из 1,000 строк. Однако он показал квадратичное снижение производительности, заняв 45 минут для полной истории одного реактора.

Команда выбрала подход с оконными функциями ANSI SQL. Держась в пределах кластера PostgreSQL, запрос использовал параллельные последовательные сканирования и избегал сетевых накладных расходов. Окончательная реализация обработала 50 миллионов строк по всем реакторам за менее чем 12 секунд. Это позволило обновлять панель мониторинга в реальном времени, что позволяло операторам обнаруживать тепловые отклонения в течение нескольких минут, а не часов.

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

Как вы обрабатываете последнее наблюдение в каждой партии, где нет последующей метки времени, чтобы определить вес интервала?

Кандидаты часто упускают граничное условие. Это приводит к тому, что интервал последней строки оценивается как NULL, что игнорируют агрегаты SQL. В результате вклад окончательного чтения отбрасывается, и среднее значение искажается. Правильный подход использует COALESCE, чтобы заменить либо ноль, либо EXTRACT(EPOCH FROM (boundary_time - ts)), если среднее должно расширяться до известного конечного времени, как, например, CURRENT_TIMESTAMP.

Почему формула SUM(value * duration) / SUM(duration) математически представляет собой среднее значение с учетом времени, и что происходит, если вы вместо этого используете AVG(value)?

Это вычисляет взвешенное арифметическое среднее, где продолжительность служит весом $w_i$. Кандидаты часто путают это с геометрическим средним или пытаются использовать AVG(value * duration), что приводит к сумме произведений без нормализации. Использование AVG(value) рассматривает каждую строку поровну, предполагая единообразные временные интервалы, что нарушает требование, что более долгие значения оказывают большее влияние.

Как временные разрывы или дублирующиеся метки времени в одной и той же партии влияют на поведение функции LEAD, и почему PARTITION BY device_idessent?

Кандидаты иногда забывают, что LEAD работает на физическом порядке строк внутри спецификации окна. Без PARTITION BY device_id функция вычисляет интервалы между разными датчиками, создавая бессмысленные отрицательные или огромные продолжительности. Кроме того, если существуют дублирующиеся метки времени, LEAD возвращает следующую отличительную строку, что потенциально создает нулевые интервалы. Кандидаты должны решить, следует ли сначала удалить дубликаты, используя DISTINCT или фильтрацию ROW_NUMBER(), чтобы избежать ошибок деления на ноль.