含大量空值的Gaps and Islands问题求解

需求为统计直至status变为FAIL时的累计值，已知这属于Gaps and Islands问题，但遇到status为非FAIL的空值时不知如何处理。

源数据表与期望输出 #

解决方案 #

无需复杂的Gaps and Islands分组逻辑，直接通过窗口函数的条件累计求和即可实现需求：

SELECT 
    date,
    id,
    tg_id,
    status,
    SUM(CASE WHEN status = 'FAIL' THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY date) AS DESIRED_OUTPUT
FROM your_table_name
ORDER BY date;

逻辑说明 #

• PARTITION BY id：确保每个id单独统计累计值，不会跨id混淆
• ORDER BY date：保证按时间顺序依次累计，符合业务逻辑
• CASE WHEN status = 'FAIL' THEN 1 ELSE 0 END：仅当status为FAIL时计1，空值或其他非FAIL状态计0
• 窗口函数SUM(...) OVER (...)：对当前行及之前所有行的条件计数求和，自动为后续空值行填充最新的累计值

如果你的SQL方言支持，也可以用COUNT简化写法：

SELECT 
    date,
    id,
    tg_id,
    status,
    COUNT(CASE WHEN status = 'FAIL' THEN 1 END) OVER (PARTITION BY id ORDER BY date) AS DESIRED_OUTPUT
FROM your_table_name
ORDER BY date;

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