如何在DolphinDB中计算存在清零情况的时段累计体积

问题背景 #

字段vol记录仪表的累计读数，统计时段内仪表可能发生清零操作：

• 若未清零，常规SQL语句 select max(vol) - min(vol) from t 可得到正确累计值；
• 但若统计时段内发生清零（比如读数序列为 [10, 20, 30, 0, 11, 22, 33]，表示读数增长到30后清零，随后重新累计），常规方法会遗漏清零前的累计值，正确结果应为 (30-10)+(33-0)=53。

解决方案 #

以下提供两种在DolphinDB中实现该逻辑的SQL方案，均需确保数据按时间戳排序以保证读数序列的正确性：

方案1：基于分段统计的方法 #

通过识别清零节点将读数拆分为多个连续增长段，分别计算每段的增量后求和：

select sum(segment_increment) as total_volume
from (
    select 
        // 用累计求和标记分段：当前读数小于前一个时判定为清零，开启新分段
        cumSum(iff(vol < prev_vol, 1, 0)) as segment_id,
        // 计算当前分段的总增量
        max(vol) over (partition by segment_id) - min(vol) over (partition by segment_id) as segment_increment
    from (
        select 
            vol,
            // 取前一行读数，首行默认与当前值相同，避免首行误判为清零
            lag(vol, 1, vol) as prev_vol
        from t
        order by timestamp
    )
)
group by segment_id

方案2：基于相邻读数增量的高效方法 #

如果仪表读数仅在清零时出现下降（其余时间持续递增），可直接计算所有正增量的总和，清零时的读数直接计入增量：

select sum(iff(vol > prev_vol, vol - prev_vol, vol)) as total_volume
from (
    select 
        vol,
        // 首行前值设为0，对应初始读数的增量
        lag(vol, 1, 0) as prev_vol
    from t
    order by timestamp
)

结果验证 #

针对示例序列 [10, 20, 30, 0, 11, 22, 33]：

• 方案1会拆分为两个分段，分别计算得到20和33，总和为53；
• 方案2计算相邻增量：(20-10)+(30-20)+(0→0)+(11-0)+(22-11)+(33-22) = 10+10+0+11+11+11=53，结果一致。

注意事项 #

• 必须按时间戳字段排序，否则读数顺序混乱会导致计算错误；
• 若仪表清零后并非从0开始（比如跳转到某个较小值），只需调整清零判断条件（如 vol < prev_vol）即可适配。

内容的提问来源于stack exchange，提问作者Stella.W