如何按指定列分区计算滚动时间戳累加值？（SQL实现）

我有一张表，其中包含一系列从00:00:00.00000开始标准化的时间线。我希望根据order_key值按顺序对这些时间线进行累加并拼接。

样例数据 #

id    order_key  start_time         end_time           activity_type
1     1          00:00:00.00000     00:00:00.01000     A
1     1          00:00:00.01000     00:00:00.02000     B
1     1          00:00:00.02000     00:00:00.03000     C
1     1          00:00:00.03000     00:00:00.03500     A
1     2          00:00:00.00000     00:00:00.01500     A
1     2          00:00:00.01500     00:00:00.04500     B
1     3          00:00:00.00000     00:00:00.05500     B

期望输出 #

id  start_time         end_time           activity_type
1   00:00:00.00000     00:00:00.01000     A
1   00:00:00.01000     00:00:00.02000     B
1   00:00:00.02000     00:00:00.03000     C
1   00:00:00.03000     00:00:00.03500     A
1   00:00:00.03500     00:00:00.05000     A
1   00:00:00.05000     00:00:00.08000     B
1   00:00:00.08000     00:00:00.13500     B

我的尝试 #

SELECT 
   id
 , -- CASE WHEN new order_key THEN LAG(end_time) OVER (PARTITION BY id ORDER BY snap_view_index, start_time) ELSE start_time END AS start_time
 , -- CASE WHEN new order_key THEN TIME_ADD(LAG(end_time), INTERVAL TIME_DIFF(end_time, start_time, MILLISECOND) MILLISECOND) ELSE TIME_ADD(start_time, INTERVAL TIME_DIFF(end_time, start_time, MILLISECOND) MILLISECOND)
, activity_type
FROM my_table; 

解决方案 #

要实现时间线的累加拼接，核心是计算每个order_key对应的时间偏移量（即之前所有order_key组的总时长），再将该偏移量加到每条记录的起止时间上。以下是基于BigQuery SQL的实现：

WITH group_durations AS (
    -- 计算每个(id, order_key)组的总时长
    SELECT
        id,
        order_key,
        MAX(TIME_DIFF(end_time, start_time, MILLISECOND)) AS group_duration
    FROM my_table
    GROUP BY id, order_key
),
cumulative_offsets AS (
    -- 计算每个(id, order_key)的累计时间偏移量（之前所有组的总时长之和）
    SELECT
        id,
        order_key,
        SUM(group_duration) OVER (PARTITION BY id ORDER BY order_key ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS cumulative_offset
    FROM group_durations
)
-- 拼接最终结果，将偏移量加到原时间上
SELECT
    t.id,
    TIME_ADD(t.start_time, INTERVAL COALESCE(co.cumulative_offset, 0) MILLISECOND) AS start_time,
    TIME_ADD(t.end_time, INTERVAL COALESCE(co.cumulative_offset, 0) MILLISECOND) AS end_time,
    t.activity_type
FROM my_table t
LEFT JOIN cumulative_offsets co
    ON t.id = co.id AND t.order_key = co.order_key
ORDER BY t.id, t.order_key, t.start_time;

逻辑说明 #

1. group_durations：按id和order_key分组，计算每组的总耗时（即该组内最后一个活动的结束时间减去第一个活动的开始时间，用MAX(TIME_DIFF(...))等价于取组内总时长）。
2. cumulative_offsets：用窗口函数SUM(...) OVER (...)计算每个组之前所有组的总时长之和，作为当前组的时间偏移量；第一个组的偏移量为0（用COALESCE处理NULL）。
3. 最终查询：将原表的起止时间加上对应的累计偏移量，得到拼接后的时间线，再按顺序排序。

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