如何改写含LEAD()的SQL查询以处理缺失日触发器数据

问题说明 #

现有一张表存储业务日期（CTRL_DT）与对应触发时间戳（CAPTURE_DT），需要为每个业务日期生成最近前序业务日期的触发时间到当前业务日期触发时间的区间数据。原查询使用LEAD()分析函数，仅在每日都有触发记录时有效；一旦某业务日的触发记录缺失（如CTRL_DT = '2023-02-16'无记录），输出结果的区间逻辑就会出错。

示例数据 #

输入表数据 #

CTRL_DT     | CAPTURE_DT
------------|-------------------------
2023-02-15  | 2023-02-15 23:59:00.000
2023-02-17  | 2023-02-17 23:59:00.000
2023-02-18  | 2023-02-18 23:59:00.000

预期输出 #

即使中间业务日期缺失，每个业务日期的区间仍关联到最近的前序有效触发时间：

CTRL_DT     | START_TIME              | END_TIME
------------|-------------------------|-------------------------
2023-02-15  | NULL                    | 2023-02-15 23:59:00.000
2023-02-17  | 2023-02-15 23:59:00.000 | 2023-02-17 23:59:00.000
2023-02-18  | 2023-02-17 23:59:00.000 | 2023-02-18 23:59:00.000

原查询错误输出 #

原LEAD()查询会跳过缺失日期，导致区间关联错误：

CTRL_DT     | START_TIME              | END_TIME
------------|-------------------------|-------------------------
2023-02-15  | NULL                    | 2023-02-17 23:59:00.000
2023-02-17  | 2023-02-15 23:59:00.000 | 2023-02-18 23:59:00.000
2023-02-18  | 2023-02-17 23:59:00.000 | NULL

原错误查询 #

SELECT
  CTRL_DT,
  CAPTURE_DT AS START_TIME,
  LEAD(CAPTURE_DT) OVER (ORDER BY CTRL_DT) AS END_TIME
FROM your_table
ORDER BY CTRL_DT;

改写后的正确查询 #

使用LAG()分析函数替代LEAD()，直接获取当前业务日期的最近前序有效触发时间：

SELECT
  CTRL_DT,
  LAG(CAPTURE_DT) OVER (ORDER BY CTRL_DT) AS START_TIME,
  CAPTURE_DT AS END_TIME
FROM your_table
ORDER BY CTRL_DT;

逻辑说明 #

• LAG(CAPTURE_DT) OVER (ORDER BY CTRL_DT)会按业务日期排序，取当前行的上一行（最近的前序业务日）的触发时间戳，完全忽略中间缺失的业务日期，确保区间始终关联到实际存在的前序触发点。
• 该方案复杂度极低，无需额外生成日历表或关联其他数据集，直接基于原表数据即可得到正确结果。

内容的提问来源于stack exchange，提问作者Naveen K Reddy