请求编写Oracle查询实现多记录遍历并生成指定预期结果

原查询存在的问题 #

• 包含无效的空WHERE子句，属于语法错误
• 计算累计求和时，DAYSOVER_INT和DAYSOUT_INT的NULL值会被SUM()忽略，导致累计结果不符合预期
• DAYSOVERX的CASE表达式可以简化为更简洁的写法

修正后的Oracle查询 #

SELECT
    id,
    ABRV,
    NXTFILL,
    DAYSOUT_INT,
    DAYSOVER_INT,
    TIME,
    NVL(DAYSOVER_INT, 0) AS DAYSOVERX,
    SUM(NVL(DAYSOUT_INT, 0)) OVER (
        PARTITION BY id 
        ORDER BY TIME 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS daysout_sk,
    SUM(NVL(DAYSOVER_INT, 0)) OVER (
        PARTITION BY id 
        ORDER BY TIME 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS daysover_ck
FROM
    test1
-- 若有筛选需求，在此补充WHERE条件，例如 WHERE id > 0
ORDER BY
    ID, TIME;

关键说明 #

• 空值处理：用NVL(列名, 0)把NULL转换为0，确保空值不会拉低累计求和的结果
• 窗口函数逻辑：PARTITION BY id保证每个id独立计算累计值，ORDER BY TIME确保按时间顺序依次累加；ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW是窗口函数的默认范围，也可以省略不写
• 语法修正：移除了原查询中无效的空WHERE子句，有实际筛选需求时可自行添加条件

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