如何用Oracle分析函数编写SELECT语句填充空记录的START_DATE

需求场景 #

初始数据特征：

• 部分记录的START_DATE和END_DATE均为空
• 部分记录的END_DATE不为空，且对应有合法的START_DATE

需求规则：当某行的END_DATE不为空时，将该行之前所有连续的、START_DATE和END_DATE均为空的记录的START_DATE，填充为该行对应的START_DATE，得到补全后的数据。

解决方案 #

假设表中有用于排序的主键列ID，可以通过Oracle分析函数结合分组逻辑实现需求，完整SELECT语句如下：

WITH grouped_data AS (
  SELECT
    t.*,
    -- 反向排序后统计非空END_DATE行数，为目标记录及前置空行分配同一组ID
    COUNT(CASE WHEN END_DATE IS NOT NULL THEN 1 END) OVER (ORDER BY ID DESC) AS group_id
  FROM your_table t
)
SELECT
  ID,
  -- 提取分组内非空START_DATE填充空记录
  MAX(START_DATE) OVER (PARTITION BY group_id) AS START_DATE,
  END_DATE
FROM grouped_data
ORDER BY ID;

语句说明 #

• 分组逻辑：通过ORDER BY ID DESC反向排序，利用COUNT()分析函数统计非空END_DATE的行数生成分组ID，确保每个非空END_DATE的行和它前面的空行归为同一组。
• 填充逻辑：在每个分组内，MAX(START_DATE)会自动提取组内唯一的非空START_DATE，填充到组内所有空记录的START_DATE字段。
• 排序返回：最终按ID正向排序，输出符合需求的结果。

内容的提问来源于stack exchange，提问作者UltraCommit