← 返回知识库← 上一题下一题 →
SQL (ANSI)编程SQL 开发人员

如何使用 ANSI SQL 窗口函数仅在有序分区内实现上一个观测值向前填充（LOCF）以填充 NULL 值，而不使用子查询或自连接？

用 Hintsage AI 助手通过面试

问题的答案。

背景：在 ANSI SQL:2003 引入分析函数之前，填充稀疏时间序列数据需要效率低下的自连接或逐行处理的游标。LOCF 模式起源于 SAS 和 R 等统计软件包，其中向前填充最后一个已知观测值是一种标准的数据清理技术。数据库供应商后来通过窗口函数在 SQL 中实现了这个逻辑，并在 ANSI SQL:2011 中正式规范了 IGNORE NULLS 子句，以声明性地处理此类缺口。

问题：传感器网络和金融交易系统经常由于传输故障或非交易时间而生成 NULL 值。简单的 LAG 函数会失败，因为它们返回的可能也是 NULL 的直接前驱，导致计算指标的缺口。这个挑战需要向后扫描有序分区，直到遇到最近的非 NULL 值，而不使用会使性能成平方级降低的自连接。

解决方案：使用 LAST_VALUE 窗口函数，选项为 IGNORE NULLS，并指定一个框架，从分区开始延伸到当前行。此配置指示引擎保持一个非 NULL 值的运行缓冲区，有效地向后查看 NULL 以获取最后有效的观测值。对于没有 IGNORE NULLS 的系统，一个变通方法是使用非 NULL 计数来创建稳定的组，尽管这在技术上涉及到一个子查询。

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;

生活中的情况

一个医疗分析平台监控糖尿病患者的连续血糖水平，使用可穿戴设备。由于蓝牙干扰，大约 12% 的读数为 NULL，但临床医生需要完整的曲线来计算胰岛素剂量，而插值可能在医学上不安全。准确的 LOCF 逻辑至关重要，因为在睡眠或进餐期间丢失的值可能会触发虚假低血糖警报。

解决方案 A：基于游标的过程性更新。 一个 PL/SQL 存储过程按时间顺序迭代患者记录，维护一个会话变量来存储最后有效的血糖读数并立即更新 NULL 行。优点：与早于窗口函数支持的传统 Oracle 版本兼容；对拥有命令式编程背景的开发人员易于理解。缺点：逐行处理会导致过多的 I/O 和表锁定；处理 1000 万行需要 45 分钟，使实时仪表板变得不可能。

解决方案 B：自连接与相关子查询。 查询执行左连接以查找当前行之前的最大时间戳，其中血糖值不为 NULL，有效地为每个缺口查找上一个值。优点：声明性 SQL，无需过程性代码；在符合 ANSI SQL-92 的系统上运行。缺点：O(n²) 复杂度导致指数级减速；由于重复的全表扫描，查询在生产数据集上超过 6 小时后超时。

解决方案 C：带有 IGNORE NULLS 的窗口函数。 实现 LAST_VALUE(glucose IGNORE NULLS)，按患者分区并按时间排序，利用单次通过索引。优点：O(n log n) 复杂度在相同的 1000 万行数据集中执行时仅需 28 秒；内存占用小且没有锁定问题。缺点：需要 ANSI SQL:2011 支持，需从现有的 PostgreSQL 9.5 实例升级数据库。

团队选择了解决方案 C，因为确认数据库升级成本由于 99% 的性能提升是合理的。实施后实时血糖警报得以启用，服务器 CPU 使用率降低了 94%。因此，诊所成功监控了 50,000 名并发患者，没有延迟或错过关键的血糖峰值。

候选人常常忽视的内容

问题 1：为什么没有 IGNORE NULLS 的 LAST_VALUE 即使在分区中存在先前的非 NULL 值时也返回 NULL？

默认情况下，LAST_VALUE 评估包括当前行的框架。当当前行包含 NULL 并且框架扩展到 CURRENT ROW 时，该函数将此 NULL 视为窗口中的最后值。候选人错误地假设该函数会无限向后扫描；然而，没有 IGNORE NULLS 的话，它将 NULL 视为有效值。窗口框架 ROWS UNBOUNDED PRECEDING 包括当前行，使得 LAST_VALUE 等同于当前行的值，除非明确指示忽略 NULL。

问题 2：如何在没有 IGNORE NULLS 的 2011 年前的 ANSI SQL 中实现 LOCF，使用 ROW_NUMBER 差异而不是 COUNT 的逻辑错误是什么？

您可以使用 COUNT(non_null_col) OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING) 创建一个仅在遇到非 NULL 值时才递增的分组标识符。所有后续 NULL 共享此计数，形成一个承载组。候选人有时尝试从 ROW_NUMBER() OVER (ORDER BY ...) 中减去 ROW_NUMBER() OVER (PARTITION BY non_null_flag ORDER BY ...)。这失败了，因为它为每个非 NULL 值之间的缺口创建了新组，而不是向前扩展先前的组。COUNT 方法有效，因为它为整个最后已知值期间生成一个稳定的标识符。

问题 3：在使用 RANGE 而不是 ROWS 框架进行带有重复值的时间戳 LOCF 时，为什么结果可能变得不确定？

RANGE 框架将具有相同 ORDER BY 值的行分组为同伴组，视其为一个单元。如果多个传感器读数共享相同的毫秒时间戳，则 RANGE UNBOUNDED PRECEDING 无法区分它们的物理顺序。当一些重复值包含 NULL 而其他值包含有效值时，窗口函数可能会根据执行计划随机从同伴组中选择。ROWS 框架通过处理物理行顺序来确保结果的确定性，确保插入的特定顺序决定哪个值向前传递。这个区别对于高频交易数据至关重要，因为微秒很重要。