问题的答案

问题的历史

时间加权平均值作为工业 物联网 和金融时间序列分析中的一个关键指标而出现。简单的算术平均值会扭曲现实，因为传感器值在下一次测量之前会持续存在。在 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 集群内部，查询利用并行顺序扫描，避免了网络开销。最终实现处理了 5,000 万行数据，耗时不到 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_id 是必需的？

候选人有时会忘记 LEAD 在窗口规范中根据行的物理顺序进行操作。如果没有 PARTITION BY device_id，该函数将在不同传感器之间计算间隔，从而产生毫无意义的负值或巨大持续时间。此外，如果存在重复时间戳，LEAD 将返回下一个不同的行，无论如何，可能会产生零秒的间隔。候选人必须决定是否首先使用 DISTINCT 或 ROW_NUMBER() 过滤进行去重，以避免除以零的错误。

构建一个查询，计算不规则传感器读数的时间加权平均值，以后续样本的有效持续时间为权重，仅使用 ANSI SQL 窗口函数而没有过程逻辑。

问题的答案

生活中的情境

候选人常常忽视的内容