如何为窗口（Partition）内的Start-Stop区间标记统一ID？

我们有按item_id分区、按time排序的事件数据，示例如下：

item_id    state    time
---------- -------- -----
a1         start    t1
a1         stall    t2
a1         restart  t3
a1         stall    t4
a1         restart  t5
a1         stop     t6

a2         start    t9
a2         stop     t10
a2         start    t11
a2         stop     t12

需要将每个item_id下的事件划分为多个区间：每个区间以start为起点，以下一个stop为终点；若后续无stop，则以start之后的最后一条记录为终点。目前已能为start记录生成唯一的interval_id，但无法为区间内其他记录填充相同的ID。

无需依赖临时表，直接用窗口函数即可完成interval_id的批量填充，核心逻辑是通过累计统计start状态的出现次数来标记区间。

基础实现（分区内唯一ID） #

SELECT
    item_id,
    state,
    time,
    -- 累计统计当前行及之前的start数量，作为区间ID
    SUM(CASE WHEN state = 'start' THEN 1 ELSE 0 END) 
        OVER (PARTITION BY item_id ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS interval_id
FROM
    your_table_name
ORDER BY
    item_id, time;

执行后结果如下，每个区间内的所有记录会共享同一interval_id：

item_id    state    time  interval_id
---------- -------- ----- ------------
a1         start    t1    1
a1         stall    t2    1
a1         restart  t3    1
a1         stall    t4    1
a1         restart  t5    1
a1         stop     t6    1

a2         start    t9    1
a2         stop     t10   1
a2         start    t11   2
a2         stop     t12   2

进阶实现（全局唯一ID） #

如果需要像示例中那样生成全局唯一的interval_id（如a2的第一个区间为2、第二个为3），可以在基础结果上叠加全局排序：

WITH interval_data AS (
    SELECT
        item_id,
        state,
        time,
        SUM(CASE WHEN state = 'start' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY item_id ORDER BY time) AS local_interval_id
    FROM your_table_name
)
SELECT
    item_id,
    state,
    time,
    DENSE_RANK() OVER (ORDER BY item_id, local_interval_id) AS interval_id
FROM interval_data
ORDER BY item_id, time;

该版本会生成全局唯一的interval_id，完全匹配你示例中的编号规则。

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