如何在BigQuery中从时序数据提取状态序列？

这种把连续相同状态的行合并成区间的需求，属于SQL里经典的「间隙与岛屿」问题。针对你的场景，我们可以通过生成分组ID的方式，把每个vehicle_id下连续相同mode和activity的行归为一组，再聚合出每个组的起始和结束时间。

完整实现SQL #

with dataset as (
 select
    timestamp('2023-01-25 00:00:00') as last_seen, 1 as vehicle_id, 1 as mode, 0 as activity 
    union all select timestamp('2023-01-25 00:00:02'), 1, 1, 0
    union all select timestamp('2023-01-25 00:00:04'), 1, 1, 0
    union all select timestamp('2023-01-25 00:00:00'), 2, 1, 0
    union all select timestamp('2023-01-25 00:00:02'), 2, 1, 0
    union all select timestamp('2023-01-25 00:00:04'), 2, 1, 0
    union all select timestamp('2023-01-25 00:00:06'), 1, 2, 1
    union all select timestamp('2023-01-25 00:00:08'), 1, 2, 1
    union all select timestamp('2023-01-25 00:00:10'), 1, 2, 1
    union all select timestamp('2023-01-25 00:00:12'), 1, 1, 0
    union all select timestamp('2023-01-25 00:00:14'), 1, 1, 0
    union all select timestamp('2023-01-25 00:00:16'), 1, 1, 0
    union all select timestamp('2023-01-25 00:00:12'), 2, 1, 1
    union all select timestamp('2023-01-25 00:00:14'), 2, 1, 1
    union all select timestamp('2023-01-25 00:00:17'), 2, 1, 1
),
-- 第一步：为每个连续相同状态的"岛屿"生成唯一ID
island_groups as (
 select
    *,
    sum(
        case
            -- 对比当前行与上一行的mode和activity，不同则标记为新岛屿的开始
            when lag(mode) over (partition by vehicle_id order by last_seen) = mode 
                 and lag(activity) over (partition by vehicle_id order by last_seen) = activity
            then 0
            else 1
        end
    ) over (partition by vehicle_id order by last_seen) as island_id
 from dataset
)
-- 第二步：按分组聚合得到状态区间
select
    vehicle_id,
    mode,
    activity,
    min(last_seen) as start,
    max(last_seen) as end
from island_groups
group by vehicle_id, island_id, mode, activity
order by vehicle_id, start;

运行结果 #

执行后会得到你期望的状态区间结果：

原方法的问题 #

你之前用qualify结合lead/lag的写法，只是筛选出了状态变化的边界行（每个区间的首尾），但没有把连续相同状态的行合并成一个整体。这样不仅会得到分散的行，还没法直接得到完整的区间时间范围，而「间隙与岛屿」的思路刚好能解决这个问题——通过分组ID把连续相同状态的行绑定在一起，再聚合出起始和结束时间。

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