背景： 在时间数据仓库中，最后观察值向前传播（LOCF）技术主导缺失值估算，使用前置有效记录来填补空白。然而，特定的分析领域——例如将日终对账应用于日内金融交易或将实验室确认回溯到早期临时诊断——需要逆向的下一个观察值向后传播（NOCB）方法。历史上，NOCB 是通过相关的子查询或过程游标实现的，这两者都表现出O(n²) 的复杂性，且未能利用现代集合优化器。
问题： 给定一个完全有序的序列（例如，event_time），每个 NULL 值必须用序列中 其后 最近的非 NULL 值来替换。连续的 NULL 前置于有效记录时，也应接收相同的后续值。标准函数如 LEAD() 仅访问紧接着的下一行，当存在多个连续的 NULL 时会失败。在性能限制下禁止自连接和递归 CTE。
解决方案： 该解决方案利用 COUNT(expression) 的 NULL 忽略语义。通过从当前行到分区末尾计数非 NULL 值（ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING），生成一个稳定的“桶标识符”，该标识符在两个非 NULL 锚之间对所有行均相同。在每个桶内，MAX(val)——同样忽略 NULL——检索锚值并将其广播到该组中的所有行。
WITH bucketed AS ( SELECT record_id, event_time, status_code, COUNT(status_code) OVER ( ORDER BY event_time, record_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS bucket_id FROM audit_log ) SELECT record_id, event_time, COALESCE( MAX(status_code) OVER ( PARTITION BY bucket_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ), 'UNKNOWN' ) AS confirmed_status FROM bucketed;
背景和问题描述： 一家高频交易公司维护一个 execution 表，捕捉微秒级的股权交易。由于交易所报告协议，任何给定分钟的最终“合并价格”——由结算中心验证——在分钟结束后的 30 秒到达，并仅在边界上标记（例如，14:30:00.000）。对于监管 TWAP（时间加权平均价格）计算，该分钟的每毫秒都必须反映最终合并价格，需要回填到所有前置的 14:29:00.000 - 14:29:59.999 记录。每日交易量超过 5000 万行，批处理窗口为 10 分钟。
解决方案 1：相关标量子查询。 该方法对每一行使用标量子查询来查找未来行中 consolidated_price IS NOT NULL 的 MIN(event_time)，然后连接以检索该价格。
优点： 对于具有程序背景的开发者来说概念上简单。
缺点： 执行 O(n²) 比较。在生产数据中，查询运行时间超过 45 分钟，违反了批处理窗口。处理多个连续的 NULL 需要额外的逻辑进行跳过，增加复杂性和错误率。
解决方案 2：递归 CTE 遍历。 递归 CTE 逐行向后迭代，向后传播非 NULL 价格，直到遇到另一个非 NULL。
优点： 确保在任何 ANSI SQL 兼容的数据库上都能工作。
缺点： 递归 CTE 在许多引擎中（例如 PostgreSQL）按顺序处理行，导致单线程执行和在深度分区时可能出现堆栈溢出。基准测试显示高内存压力下的 20 分钟运行时间，使其不适合生产服务水平协议。
解决方案 3：窗口函数桶化（选择）。 实现 COUNT 和 MAX 模式。向后看的 COUNT 为所有需要相同未来值的行创建相同的桶，而 MAX 在桶内传播该值。
优点： 完全基于集合的，可并行化，并由于排序操作以 O(n log n) 时间执行。它与交易量线性扩展，并使用跨 PostgreSQL、SQL Server、Oracle 和 DB2 的标准 ANSI SQL。
缺点： 需要对数据进行两次遍历（CTE 和外部查询），尽管现代优化器通常会合并这些。它要求完全排序；重复时间戳需要一个平局打破列以确保确定性。
结果： 在 5000 万行数据集上，管道运行时间从 45 分钟减少到 8 秒。公司消除了脆弱的 Python 回填脚本，减少了基础设施复杂性，并确保在合规窗口内生成监管报告。
在构建分组密钥时，为什么必须使用 COUNT(column) 而不是 COUNT(*) 或 ROW_NUMBER()？
许多候选人直觉上使用 COUNT(*) 或 ROW_NUMBER()，认为这些可以对数据进行细分。COUNT(*) 对每一行进行计数，无论是NULL，为向后框架中的每一行生成唯一且单调变化的值，这会阻止形成稳定的组。ROW_NUMBER() 为每一行分配唯一标识符，同样会破坏分组。只有 COUNT(column) 仅在遇到非 NULL 值时递增，从而将相同的“桶 ID”分配给所有前置的 NULL，直到下一个非 NULL 边界。这个区别至关重要，因为它利用了聚合窗口函数的 NULL 忽略语义，在没有过程逻辑的情况下模拟“向前查看”。
如果分区以尾部 NULL 值结束，查询行为如何，以及什么修改确保在没有未来观察时进行确定性处理？
如果有序分区中的最后几行是 NULL，COUNT(status_code) 对这些行的评估为零。因此，MAX(status_code) 返回 NULL，这是逻辑上正确的——没有未来观察可向后传播。候选人常常忘记在下游业务逻辑中处理此问题。为了提供默认值（例如静态占位符或来自外部查找的值），必须将结果包装在 COALESCE 中。此外，为了在数据质量监控中区分“已填充的 NULL”和“不可填充的 NULL”，应该比较原始值和填充值：CASE WHEN status_code IS NULL AND bucket_id = 0 THEN 'UNCONFIRMED' END。
如果 ORDER BY 子句包含重复值，则会出现什么确定性问题，为什么从 ROWS 切换到 RANGE 会加剧问题？
当排序关键字包含重复值（平局）时，窗口框架的定义变得模棱两可。使用 ROWS（物理偏移量）根据物理表顺序分配组，这是任意的，除非提供唯一的次要排序键。切换到 RANGE（逻辑值范围）将具有相同排序值的所有行视为同伴，导致它们共享相同的框架。在此解决方案中，如果多行共享相同的 event_time，RANGE 可能错误地将 NULL 行与来自同一时间戳的非 NULL 行分组，或不可预测地拆分组。候选人必须通过在 ORDER BY 子句中附加唯一键（例如 record_id）确保完全排序：ORDER BY event_time, record_id 以保证在所有 ANSI SQL 实现中进行确定性桶分配。