基于event_time与is_active列计算split_test事件表ended_at的SQL实现

前置说明 #

原表为split_test_events，核心字段如下：

• EVENT_ID：事件唯一ID
• SPLIT_TEST_ID：AB测试唯一标识
• BRANCH：测试分支标识
• IS_ACTIVE：启停状态，1=分支启动，0=分支停止
• EVENT_TIME：事件发生时间

实现方案 #

方案1：自连接匹配（兼容性强，适配所有支持窗口函数的数据库） #

WITH ranked_events AS (
    SELECT 
        SPLIT_TEST_ID,
        BRANCH,
        IS_ACTIVE,
        EVENT_TIME,
        ROW_NUMBER() OVER (PARTITION BY SPLIT_TEST_ID, BRANCH ORDER BY EVENT_TIME ASC) AS rn
    FROM split_test_events
)
SELECT 
    start_event.SPLIT_TEST_ID,
    start_event.BRANCH,
    start_event.EVENT_TIME AS started_at,
    end_event.EVENT_TIME AS ended_at
FROM ranked_events start_event
LEFT JOIN ranked_events end_event
    ON start_event.SPLIT_TEST_ID = end_event.SPLIT_TEST_ID
    AND start_event.BRANCH = end_event.BRANCH
    AND end_event.IS_ACTIVE = 0
    AND end_event.rn = (
        SELECT MIN(rn) 
        FROM ranked_events 
        WHERE SPLIT_TEST_ID = start_event.SPLIT_TEST_ID
        AND BRANCH = start_event.BRANCH
        AND IS_ACTIVE = 0
        AND rn > start_event.rn
    )
WHERE start_event.IS_ACTIVE = 1
ORDER BY start_event.SPLIT_TEST_ID, start_event.BRANCH, started_at;

方案2：分组聚合（性能更优，适合大数据量场景） #

该方案避免了自连接开销，也解决了LAST_VALUE窗口函数常见的窗口帧配置错误问题：

WITH event_groups AS (
    SELECT
        SPLIT_TEST_ID,
        BRANCH,
        IS_ACTIVE,
        EVENT_TIME,
        SUM(CASE WHEN IS_ACTIVE = 1 THEN 1 ELSE 0 END) OVER (
            PARTITION BY SPLIT_TEST_ID, BRANCH 
            ORDER BY EVENT_TIME ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS run_group
    FROM split_test_events
)
SELECT
    SPLIT_TEST_ID,
    BRANCH,
    MIN(CASE WHEN IS_ACTIVE = 1 THEN EVENT_TIME END) AS started_at,
    CASE 
        WHEN MAX(IS_ACTIVE) = 1 THEN NULL 
        ELSE MAX(CASE WHEN IS_ACTIVE = 0 THEN EVENT_TIME END) 
    END AS ended_at
FROM event_groups
GROUP BY SPLIT_TEST_ID, BRANCH, run_group
ORDER BY SPLIT_TEST_ID, BRANCH, started_at;

逻辑验证说明 #

• 每次分支启动会生成一个新的run_group分组标记，同一次运行的启停事件会被划分到同一个分组
• 分组内最小的启动时间为started_at，如果分组内最大状态为1说明分支还在运行，ended_at返回null，否则返回分组内最大的停止时间
• 支持同一测试分支多次启停的场景，每次运行都会生成独立的一行记录

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