如何基于ID和状态码为数据集添加活动块标识列

给定如下结构的数据集（已按datetime排序）：

需要为每个ID划分活动块，每个块以status=0为起始，最终得到带block列的结果：

实现思路 #

核心是利用窗口累加函数，对每个ID分组后，统计从第一条记录到当前记录中status=0出现的次数——这个次数就是当前记录所属的block编号，每遇到一个0，累加值加1，后续所有记录直到下一个0出现前都属于这个block。

通用SQL代码 #

SELECT
    id,
    status,
    datetime,
    SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) OVER (
        PARTITION BY id
        ORDER BY datetime
    ) AS block
FROM your_table_name
ORDER BY id, datetime;

代码说明 #

1. PARTITION BY id：按ID分组，确保每个ID的block编号独立计算
2. ORDER BY datetime：保证按时间顺序累加，符合数据已排序的业务逻辑
3. SUM(CASE WHEN status=0 THEN 1 ELSE 0 END)：遇到status=0时标记为1，否则为0，累加这些标记值得到block编号
4. 多数SQL引擎中，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW是窗口函数的默认范围，因此可以省略

执行上述SQL后，即可得到你期望的带block列的结果。

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