计算表中数值消耗量（delta）始终返回0的问题排查求助

看起来你的查询在处理累计值的差值计算时，没有正确处理0值的特殊情况，或者窗口函数的逻辑存在问题，导致所有Delta都返回0。结合你的需求（第一行Delta为0，0值行Delta为0，下一行基于0之前的有效数值计算），我来帮你修正这个问题：

核心问题分析 #

通常返回全0的常见原因包括：

• 窗口函数没有按正确的字段排序（比如没按时间排序，导致LAG()取到的不是前一行的有效累计值）
• 没有按设备/版本分区，导致跨不同分组计算差值
• 没有处理0值的特殊逻辑，直接用当前值减前一行值，当遇到0值后后续计算全部出错

正确的查询实现 #

假设你的表结构包含device_id、reading_time、value（累计值）、version字段，下面的查询会正确计算符合你预期的Delta：

SELECT
    device_id,
    reading_time,
    -- 处理Delta逻辑：0值行返回0，否则用当前值减去上一个非0的累计值
    CASE
        WHEN value = 0 THEN 0.00
        ELSE COALESCE(
            value - LAST_VALUE(CASE WHEN value != 0 THEN value END IGNORE NULLS) OVER (
                PARTITION BY device_id, version
                ORDER BY reading_time
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ),
            0.00 -- 第一行没有前置值，返回0
        )
    END AS delta,
    version
FROM your_table
ORDER BY reading_time;

逻辑说明 #

1. 分区与排序：用PARTITION BY device_id, version保证只在同一设备同一版本内计算差值，ORDER BY reading_time确保按时间顺序取前序值
2. 跳过0值：用CASE WHEN value !=0 THEN value END把0值转为NULL，再通过LAST_VALUE(...) IGNORE NULLS获取上一个非0的有效累计值
3. 特殊情况处理：第一行没有前置值，用COALESCE返回0；0值行直接返回0，完全匹配你的需求

验证你的预期结果 #

针对你给出的预期输出，这个查询会正确生成：

• 第一行Delta为0
• 0值行（第8行）Delta为0
• 第9行的Delta会基于第7行的非0值计算，得到你需要的结果

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