如何在BigQuery中基于特定设备值实现行号分段？

API 探路者

2026-8-17

按设备连续分段生成编号的实现方案

原始数据

User_id	Month	Device
001	01	A
001	02	A
001	03	B (Base)
001	04	A
002	01	C
002	02	C
002	03	B (Base)
002	04	B (Base)
003	01	A
003	02	B (Base)
003	03	A
003	04	B (Base)

需求说明

按User_id分组、月份顺序排序，将连续相同的设备归为同一段（连续的B (Base)算一段，连续的非B (Base)设备也算一段），生成递增的分段编号rn，期望结果如下：

期望结果

User_id	Month	Device	rn
001	01	A	1
001	02	A	1
001	03	B (Base)	2
001	04	A	3
002	01	C	1
002	02	C	1
002	03	B (Base)	2
002	04	B (Base)	2
003	01	A	1
003	02	B (Base)	2
003	03	A	3
003	04	B (Base)	4

实现方法

利用SQL窗口函数即可实现，核心思路是：按用户分组、月份排序，标记当前行与上一行设备是否不同，累计标记的总和后加1就是分段编号。

通用SQL代码（支持窗口函数的SQL方言适用，如Hive、Spark SQL、PostgreSQL等）

SELECT 
    User_id,
    Month,
    Device,
    1 + SUM(flag) OVER (PARTITION BY User_id ORDER BY Month) AS rn
FROM (
    SELECT 
        User_id,
        Month,
        Device,
        -- 标记当前行与上一行设备是否不同，不同则记1，否则记0
        CASE 
            WHEN LAG(Device) OVER (PARTITION BY User_id ORDER BY Month) != Device THEN 1
            ELSE 0
        END AS flag
    FROM your_table_name -- 替换为你的表名
) t
ORDER BY User_id, Month;