删除重复的行,因此这些行相互跟随

使用行号差异方法根据连续的行标志相同来分配组。然后使用连续总和。

select distinct id,flag,sum(value) over(partition by id,grp) as finalvalue
from (
select t.*,row_number() over(partition by id order by datetime)-row_number() over(partition by id,flag order by datetime) as grp
from tbl t
) t

下面是一种使用CONDITIONAL\u CHANGE\u事件的方法:

select
    flag,
    id,
    sum(value) value
from (
    select
        conditional_change_event(flag) over (order by datetime desc) part,
        flag,
        id,
        value
    from so
) t
group by part, flag, id
order by part;

结果与问题中所述的预期结果不同,因为 order by datetime