在BigQuery中按值的出现序列对连续相同值进行分组

原始数据 #

Order列递增但非连续，需要将连续出现的相同Value划分为同一组，后续再次出现的相同Value视为新组，预期结果如下：

预期结果 #

错误尝试说明 #

使用dense_rank() over(order by Value)无法满足需求，该函数仅按Value值进行排名，无法区分连续序列，错误结果对比：

测试数据SQL：

with test_data as(
 select 1 as _order, 'A' as value union all
 select 2 as _order, 'A' as value union all
 select 3 as _order, 'A' as value union all
 select 4 as _order, 'B' as value union all
 select 5 as _order, 'B' as value union all
 select 7 as _order, 'A' as value union all
 select 8 as _order, 'A' as value union all
 select 10 as _order, 'A' as value union all
 select 11 as _order, 'C' as value
)
select _order, value, dense_rank() over(order by value) value_order
from test_data
order by _order

正确解决方案 #

这是典型的连续相同值分组问题，通过标记值的变化并累计求和即可实现：

with test_data as(
 select 1 as _order, 'A' as value union all
 select 2 as _order, 'A' as value union all
 select 3 as _order, 'A' as value union all
 select 4 as _order, 'B' as value union all
 select 5 as _order, 'B' as value union all
 select 7 as _order, 'A' as value union all
 select 8 as _order, 'A' as value union all
 select 10 as _order, 'A' as value union all
 select 11 as _order, 'C' as value
),
value_change as (
 select 
   _order,
   value,
   -- 第一行默认标记为新组，后续行与上一行值不同则标记为新组
   case when lag(value) over(order by _order) is null or lag(value) over(order by _order) != value then 1 else 0 end as is_new_group
 from test_data
)
select 
 _order,
 value,
 -- 累计求和生成组编号
 sum(is_new_group) over(order by _order) as `Group`
from value_change
order by _order;

逻辑说明 #

1. lag(value) over(order by _order)：按Order排序后获取上一行的Value值，第一行的lag结果为null
2. case语句：第一行或当前行与上一行Value不同时，标记为1（新组），否则为0
3. sum(is_new_group) over(order by _order)：对标记值进行累计求和，每遇到一个1，组编号递增，从而实现连续相同值的分组

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