回答

历史上，数据工程师面临着处理缺失传感器读数的挑战，通常会丢弃记录或使用常量插补，这两种方法都扭曲了分析结果。线性插值已成为连续物理过程的统计上首选方法，假设在两个已知数据点之间的直线轨迹。

这个问题要求通过计算与周围有效测量的时间距离来重建有序序列中的NULL值。必须在不采用过程循环或将表自我连接的情况下实现这一点，保持纯粹的集合逻辑。

该解决方案使用条件窗口函数来建立边界锚点。通过扩展到所有前一行的 MAX 捕获当前间隙之前最近的非空值和时间戳。相反，通过扩展到所有后续行的 MIN 捕获间隙之后下一个非空值和时间戳。插值公式根据这些边界之间经过的时间的比例计算加权平均值。

WITH boundaries AS (
  SELECT 
    device_id,
    reading_time,
    reading,
    MAX(CASE WHEN reading IS NOT NULL THEN reading_time END) 
      OVER (PARTITION BY device_id ORDER BY reading_time 
            ROWS UNBOUNDED PRECEDING) as prev_time,
    MAX(CASE WHEN reading IS NOT NULL THEN reading END) 
      OVER (PARTITION BY device_id ORDER BY reading_time 
            ROWS UNBOUNDED PRECEDING) as prev_val,
    MIN(CASE WHEN reading IS NOT NULL THEN reading_time END) 
      OVER (PARTITION BY device_id ORDER BY reading_time 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as next_time,
    MIN(CASE WHEN reading IS NOT NULL THEN reading END) 
      OVER (PARTITION BY device_id ORDER BY reading_time 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as next_val
  FROM sensor_readings
)
SELECT 
  device_id,
  reading_time,
  COALESCE(
    reading,
    prev_val + (next_val - prev_val) * 
      (EXTRACT(EPOCH FROM (reading_time - prev_time)) / 
       NULLIF(EXTRACT(EPOCH FROM (next_time - prev_time)), 0))
  ) as interpolated_reading
FROM boundaries;

现实情况

一家制药公司使用每分钟报告的物联网温度传感器监控疫苗冷藏单位。在高峰工作时间，网络拥塞导致间歇性的NULL读数持续3-5分钟。监管FDA合规要求完整的温度历史记录而没有间隙，而简单删除违反了连续监测协议。

**首先考虑删除NULL记录。**这种方法通过仅存储观察到的值来维护事实完整性。然而，它产生了时间上的不连续性，打破了基于持续时间的聚合，如计算超过临界温度阈值的总时间，导致数据集不符合审计标准。

**评估了最后观察值向前延续(LOCF)**作为一种计算上简单的替代方案，使用LAST_VALUE窗口函数。虽然性能良好，但该方法假设在停机期间温度保持不变，这违反了制冷系统中逐渐热漂移的热力学原则，给方差和趋势分析引入了显著偏差。

**最终选择了线性插值。**这种方法模拟了测量之间的实际热惯性，保持了一级趋势，并为典型的网络波动的短时间间隔提供了数学上合理的估计。尽管它假设单调线性变化，但在隔离疫苗存储过程中压缩机循环之间的短暂稳定期内证明是准确的。

实施在Q3合规审计期间成功重建了99.2%的丢失读数。插值数据集将温度方差保持在物理记录值的0.1°C内，满足FDA要求，而无需昂贵的硬件升级或冗余传感器网络。

候选人常常忽视的内容

您如何处理在分区开头或结尾出现的NULL值，其中只有一个边界值可用于插值？

候选人经常在实现插值公式时没有考虑边缘情况，导致计算在前导或尾随间隙中返回NULL，因为prev_val或next_val未定义。解决方案需要将逻辑包裹在CASE表达式中：当prev_time IS NULL时，使用next_val（向后外推）；当next_time IS NULL时，使用prev_val（向前外推或LOCF）；否则应用完整的插值公式。这确保查询返回整个数据集的结果，而不仅是内部范围。

为什么表达式(next_val - prev_val) / (next_time - prev_time)在严格的ANSI SQL中可能失败，什么修改可以确保数值正确？

ANSI SQL日期时间算术返回INTERVAL类型，而不是数值标量。在间隔间进行算术除法或将间隔与小数混合会引发类型不兼容错误。此外，整数除法会截断小数秒，破坏精度。候选人必须使用EXTRACT(EPOCH FROM (next_time - prev_time))提取时间戳中的纪元秒以获得数字表示。同时，他们还必须将分母包裹在NULLIF(..., 0)中，以防止在由于批量插入而导致连续有效读取共享相同时间戳时出现的零除错误。

这种基于窗口函数的方法与使用相关子查询的自连接方法之间的基本计算复杂度差异是什么，为什么对于高频流数据集重要？

窗口函数方法的执行时间为O(n log n)，主要受到排序操作的支配，相对于分区大小保持线性内存使用。自连接方法与标量子查询（例如，查找下一个非空值通过MIN(time) WHERE time > current）的复杂度降到O(n²)，因为每行扫描表以查找其邻居，创建了限制性的嵌套循环连接。对于生成数百万行的高频遥测，窗口函数方法利用有序索引扫描和单通道执行，而自连接会导致笛卡尔积和内存溢出。候选人经常忽视UNBOUNDED FOLLOWING帧可能需要针对大分区进行磁盘溢出，尽管这在渐近上优于二次复杂性。

在处理因传感器掉线而出现间歇性空值的时间序列遥测时，阐明使用ANSI SQL通过线性插值重建这些缺口的方法，完全利用窗口函数而不使用自连接或标量子查询？

回答

现实情况

候选人常常忽视的内容