生成AB测试组合的交叉时间线 寻求技术实现支持

核心思路 #

要生成每个用户的AB测试组合时间区间，关键是先按激活时间对每个用户的测试排序，再依次生成每个时间节点对应的已激活测试组合，并匹配对应的时间范围（当前激活时间到下一个激活时间）。

具体SQL实现（以PostgreSQL/MySQL 8.0+/Oracle为例） #

假设原始表名为ab_test_table，字段为user_id、ab_test、ab_test_activation_time：

WITH ranked_tests AS (
    -- 1. 给每个用户的AB测试按激活时间排序，同时获取下一个测试的激活时间作为当前区间的结束时间
    SELECT
        user_id,
        ab_test,
        ab_test_activation_time,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ab_test_activation_time) AS test_rank,
        LEAD(ab_test_activation_time) OVER (PARTITION BY user_id ORDER BY ab_test_activation_time) AS next_activation_time
    FROM ab_test_table
),
test_combinations AS (
    -- 2. 关联当前及之前的所有测试，生成测试组合列
    SELECT
        rt.user_id,
        MAX(CASE WHEN rt_inner.test_rank = 1 THEN rt_inner.ab_test END) AS ab_test_1,
        MAX(CASE WHEN rt_inner.test_rank = 2 THEN rt_inner.ab_test END) AS ab_test_2,
        MAX(CASE WHEN rt_inner.test_rank = 3 THEN rt_inner.ab_test END) AS ab_test_3,
        rt.ab_test_activation_time AS ab_start,
        rt.next_activation_time AS ab_end
    FROM ranked_tests rt
    -- 自连接获取当前rank及之前的所有测试记录
    JOIN ranked_tests rt_inner 
        ON rt.user_id = rt_inner.user_id 
        AND rt_inner.test_rank <= rt.test_rank
    GROUP BY rt.user_id, rt.test_rank, rt.ab_test_activation_time, rt.next_activation_time
    ORDER BY rt.user_id, rt.test_rank
)
SELECT * FROM test_combinations;

逻辑说明 #

1. ranked_tests阶段：

   • 用ROW_NUMBER()给每个用户的测试按激活时间分配序号，确保顺序正确。
   • 用LEAD()函数获取下一条测试的激活时间，作为当前区间的ab_end；如果是最后一条测试，ab_end为null。

2. test_combinations阶段：

   • 通过自连接，把当前序号及之前的所有测试关联起来。
   • 用CASE语句+MAX()聚合，把不同序号的测试分别放到ab_test_1、ab_test_2等列中，未激活的测试列显示null。
   • 按用户和测试序号分组，得到每个时间区间的测试组合和时间范围。

扩展说明 #

如果用户的AB测试数量超过3个，只需在test_combinations中增加对应的CASE语句即可（比如MAX(CASE WHEN rt_inner.test_rank = 4 THEN rt_inner.ab_test END) AS ab_test_4）。如果需要动态适配任意数量的测试，可结合对应数据库的动态SQL或交叉表函数（如PostgreSQL的crosstab）实现。

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