考虑下面的方法
select type,
max(timestamp) resolved_timestamp,
min(timestamp) first_failure_timestamp
from (
select *, countif(status='resolved') over win as grp
from your_table
window win as (partition by type order by timestamp rows between unbounded preceding and 1 preceding)
)
group by type, grp
如果应用于y中的样本数据,我们的问题是-输出为