Oracle SQL Developer中按ID分区验证日期序列并新增CTRL列需求

1. 新增CTRL列 #

首先执行ALTER TABLE语句添加取值为0或1的CTRL列：

ALTER TABLE 你的表名 ADD CTRL NUMBER(1) DEFAULT 0;

2. 更新CTRL列验证日期序列 #

由于数据已按ID、NUM_ROLL、REFERENCE_DATE排序，我们可以通过窗口函数LAG获取同一ID分区内上一行的REFERENCE_DATE，对比当前行的日期是否符合预期序列，不符合则将CTRL设为1。

方案一：使用UPDATE语句

UPDATE 你的表名 t
SET CTRL = CASE
    -- 同一ID下，当前行日期与上一行日期不符合1天递增规则时标记为1
    WHEN LAG(REFERENCE_DATE) OVER (PARTITION BY ID ORDER BY NUM_ROLL) + INTERVAL '1' DAY != REFERENCE_DATE THEN 1
    -- 分区内第一行无前置数据，默认标记为0
    WHEN LAG(REFERENCE_DATE) OVER (PARTITION BY ID ORDER BY NUM_ROLL) IS NULL THEN 0
    ELSE 0
END;

方案二：使用MERGE语句（避免单行子查询报错）

如果执行UPDATE时遇到ORA-01427错误，推荐使用MERGE写法：

MERGE INTO 你的表名 t
USING (
    SELECT 
        ROWID AS row_id,
        CASE
            WHEN LAG(REFERENCE_DATE) OVER (PARTITION BY ID ORDER BY NUM_ROLL) + INTERVAL '1' DAY != REFERENCE_DATE THEN 1
            WHEN LAG(REFERENCE_DATE) OVER (PARTITION BY ID ORDER BY NUM_ROLL) IS NULL THEN 0
            ELSE 0
        END AS new_ctrl
    FROM 你的表名
) s
ON (t.ROWID = s.row_id)
WHEN MATCHED THEN
    UPDATE SET t.CTRL = s.new_ctrl;

3. 逻辑调整说明 #

• 若业务中日期间隔不是1天，可将INTERVAL '1' DAY替换为对应间隔，比如每周递增则改为INTERVAL '7' DAY
• 若仅需验证日期随NUM_ROLL递增（不限制固定间隔），可将判断条件改为：

  WHEN LAG(REFERENCE_DATE) OVER (PARTITION BY ID ORDER BY NUM_ROLL) >= REFERENCE_DATE THEN 1
  

• 替换语句中的你的表名为实际表名即可

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