SQL实现：为RESULT为PROCESSED的记录填充New Date字段求助

核心思路 #

通过连续分组标记识别连续的PROCESSED记录段，结合窗口函数获取段前最近的not_processed日期，再判断当前记录是否为段内最后一条，最终按规则填充New Date。

实现代码 #

假设你的表名为your_table，字段为Comments、Date、RESULT，可以用以下SQL实现：

WITH grouped_data AS (
    SELECT 
        *,
        -- 生成连续RESULT的分组ID：RESULT变化时分组ID递增
        SUM(CASE WHEN LAG(RESULT) OVER (ORDER BY Date) != RESULT THEN 1 ELSE 0 END) 
            OVER (ORDER BY Date) AS group_id,
        -- 滚动获取当前记录之前最后一条not_processed的日期
        MAX(CASE WHEN RESULT = 'not_processed' THEN Date END) 
            OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_not_processed_date,
        -- 获取下一条记录的RESULT，用于判断是否为当前PROCESSED段的最后一条
        LEAD(RESULT) OVER (ORDER BY Date) AS next_result
    FROM your_table
),
processed_groups AS (
    SELECT 
        *,
        -- 为每个PROCESSED分组统一绑定对应的前置not_processed日期
        MAX(prev_not_processed_date) OVER (PARTITION BY group_id) AS group_prev_not_processed_date
    FROM grouped_data
    WHERE RESULT = 'PROCESSED'
)
-- 合并PROCESSED记录（已填充New Date）和非PROCESSED记录（New Date为NULL）
SELECT 
    Comments,
    Date,
    RESULT,
    CASE 
        WHEN next_result != 'PROCESSED' OR next_result IS NULL THEN group_prev_not_processed_date
        ELSE Date
    END AS New_Date
FROM processed_groups
UNION ALL
SELECT 
    Comments,
    Date,
    RESULT,
    NULL AS New_Date
FROM your_table
WHERE RESULT != 'PROCESSED'
ORDER BY Date;

代码逻辑拆解 #

1. grouped_data CTE：
   • group_id：通过对比前一条记录的RESULT值，为连续相同的RESULT记录生成唯一分组ID，方便识别连续的PROCESSED段。
   • prev_not_processed_date：滚动计算当前记录之前所有not_processed记录的最新日期，即段前最近的not_processed日期。
   • next_result：获取下一条记录的RESULT，用来判断当前记录是否是PROCESSED段的最后一条。
2. processed_groups CTE：
   筛选出所有PROCESSED记录，同时为每个PROCESSED分组统一分配对应的前置not_processed日期（同一分组内该值一致）。
3. 最终查询：
   • 对PROCESSED记录：如果下一条记录不是PROCESSED（或无下一条），则使用分组对应的前置not_processed日期；否则使用自身Date。
   • 保留所有非PROCESSED记录，New_Date设为NULL。

示例验证 #

假设原始数据：

执行后结果：

完全符合你提出的填充规则。

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