BigQuery分组场景下如何高效获取用户首次分配的AB实验版本

核心优化思路 #

避免为每个用户全量收集、排序历史记录，仅记录最小时间对应的实验版本，减少中间内存和计算开销。

方案1：通用标准SQL实现（兼容所有支持窗口函数的引擎） #

如果你的SQL引擎不支持argMin聚合函数，用窗口函数+条件聚合的方式，仅需单次全表扫描即可同时完成首次实验提取和用户指标计算：

WITH user_records_ranked AS (
    SELECT
        user_id,
        experiment,
        is_click,
        -- 按用户分组，时间升序排列，行号为1的就是该用户第一条记录
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time ASC) AS rn
    FROM user_experiment
)
SELECT
    user_id,
    -- 提取行号为1的实验版本，即为首次分配的实验
    MAX(CASE WHEN rn = 1 THEN experiment END) AS first_experiment,
    -- 按需计算各类用户指标，示例为用户维度CTR
    SUM(is_click) AS user_total_click,
    COUNT(*) AS user_total_pv,
    SAFE_DIVIDE(SUM(is_click), COUNT(*)) AS user_ctr
FROM user_records_ranked
GROUP BY user_id

这个方案比你原有array_agg的写法性能提升明显，原因是不需要为每个用户生成并排序全量实验数组，仅需生成行号后通过条件判断提取首个值即可。

方案2：性能最优实现（支持argMin聚合函数的引擎适用） #

目前主流大数据引擎（Spark SQL、BigQuery、ClickHouse、Trino等）都原生支持argMin聚合函数，可以直接聚合得到最小时间对应的实验值，不需要额外窗口计算，性能是所有方案中最高的：

SELECT
    user_id,
    -- 直接取最小time对应的experiment值，即为首次分配实验
    argMin(experiment, time) AS first_experiment,
    -- 按需计算用户指标
    SAFE_DIVIDE(SUM(is_click), COUNT(*)) AS user_ctr
FROM user_experiment
GROUP BY user_id

该方案的核心优势是聚合过程中仅需为每个用户记录当前最小时间对应的实验值，不需要存储用户的所有历史实验记录，也不需要排序操作，内存和CPU开销仅为原有array_agg写法的几十分之一，TB级数据量下也可以在分钟级完成计算。

额外优化建议 #

• 如果你的表按时间分区，可以先根据业务逻辑限定扫描的时间范围，减少扫描的数据量
• 建议为表添加(user_id, time)联合索引，引擎可以直接通过索引定位每个用户的首条记录，无需额外排序计算
• 避免在SELECT子句中使用生成大中间对象的函数（如原有写法中的array_agg），大数据量下这类函数的序列化、排序开销会被指数级放大

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