基于特定限制维护活跃注册列表的BigQuery实现需求

样本数据集 #

DECLARE _MAX_ACTIVE_ENROLLMENTS INT64 DEFAULT 2;

WITH `activity` AS (
    SELECT "2022-01-01" AS `date_time`, "tim" AS `username`,  "enrolled" AS `activity` UNION ALL
    SELECT "2022-01-02" AS `date_time`, "sarah" AS `username`,  "enrolled" AS `activity` UNION ALL
    SELECT "2022-01-04" AS `date_time`, "tim" AS `username`,  "extended" AS `activity` UNION ALL
    SELECT "2022-01-05" AS `date_time`, "ed" AS `username`,  "enrolled" AS `activity` UNION ALL
    SELECT "2022-01-06" AS `date_time`, "ed" AS `username`,  "extended" AS `activity` UNION ALL
    SELECT "2022-01-07" AS `date_time`, "tim" AS `username`,  "canceled" AS `activity` UNION ALL
    SELECT "2022-01-07" AS `date_time`, "tim" AS `username`,  "canceled" AS `activity` UNION ALL
    SELECT "2022-01-08" AS `date_time`, "ed" AS `username`,  "canceled" AS `activity` UNION ALL
    SELECT "2022-01-09" AS `date_time`, "lisa" AS `username`,  "enrolled" AS `activity` UNION ALL
    SELECT "2022-01-10" AS `date_time`, "sarah" AS `username`,  "canceled" AS `activity`
)

需求说明 #

根据设定的_MAX_ACTIVE_ENROLLMENTS（最大活跃注册数），判断每个用户活动事件的注册有效性：

• enrolled/extended事件：若当前全局活跃注册数未达上限，且用户符合对应条件（enrolled要求无前置有效注册，extended要求已有有效注册），则标记为有效（valid_enrollment=1），否则无效（0）
• canceled事件：仅当用户当前存在有效活跃注册时，标记为有效（1），否则无效（0）

核心难点 #

需要动态跟踪全局活跃注册用户的数量，以及单个用户的有效活跃状态，以此判断每个事件是否应该生效。直接维护动态列表会增加复杂度，需要更高效的计算方式。

期望输出 #

当_MAX_ACTIVE_ENROLLMENTS=1时： #

当_MAX_ACTIVE_ENROLLMENTS=2时： #

解决方案 #

思路 #

通过窗口函数和累计状态计算替代动态列表维护：

1. 按时间顺序为所有事件分配全局处理顺序
2. 对每个用户，累计计算其历史有效事件的净值（enrolled/extended记+1，有效canceled记-1），得到事件发生前的用户有效活跃数
3. 全局层面累计维护活跃注册数，结合用户状态判断每个事件的有效性

完整SQL代码 #

DECLARE _MAX_ACTIVE_ENROLLMENTS INT64 DEFAULT 2;

WITH `activity` AS (
    SELECT "2022-01-01" AS `date_time`, "tim" AS `username`,  "enrolled" AS `activity` UNION ALL
    SELECT "2022-01-02" AS `date_time`, "sarah" AS `username`,  "enrolled" AS `activity` UNION ALL
    SELECT "2022-01-04" AS `date_time`, "tim" AS `username`,  "extended" AS `activity` UNION ALL
    SELECT "2022-01-05" AS `date_time`, "ed" AS `username`,  "enrolled" AS `activity` UNION ALL
    SELECT "2022-01-06" AS `date_time`, "ed" AS `username`,  "extended" AS `activity` UNION ALL
    SELECT "2022-01-07" AS `date_time`, "tim" AS `username`,  "canceled" AS `activity` UNION ALL
    SELECT "2022-01-07" AS `date_time`, "tim" AS `username`,  "canceled" AS `activity` UNION ALL
    SELECT "2022-01-08" AS `date_time`, "ed" AS `username`,  "canceled" AS `activity` UNION ALL
    SELECT "2022-01-09" AS `date_time`, "lisa" AS `username`,  "enrolled" AS `activity` UNION ALL
    SELECT "2022-01-10" AS `date_time`, "sarah" AS `username`,  "canceled" AS `activity`
),
ordered_events AS (
    -- 按时间排序，分配全局处理顺序
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY date_time) AS global_order
    FROM activity
),
user_event_history AS (
    -- 计算每个事件发生前，用户的有效活跃数
    SELECT *,
           COALESCE(SUM(IF(valid_enrollment = 1, 
                          CASE activity WHEN 'enrolled' THEN 1 WHEN 'extended' THEN 1 WHEN 'canceled' THEN -1 ELSE 0 END, 
                          0)) OVER (PARTITION BY username ORDER BY global_order ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS prev_user_active
    FROM ordered_events
),
validated_events AS (
    -- 计算全局活跃数，判断每个事件的有效性
    SELECT *,
           CASE 
               WHEN activity = 'enrolled' THEN 
                   IF((COALESCE(SUM(IF(valid_enrollment = 1, 
                                      CASE activity WHEN 'enrolled' THEN 1 WHEN 'canceled' THEN -1 ELSE 0 END, 
                                      0)) OVER (ORDER BY global_order ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)) < _MAX_ACTIVE_ENROLLMENTS 
                      AND prev_user_active = 0, 1, 0)
               WHEN activity = 'extended' THEN 
                   IF(prev_user_active > 0, 1, 0)
               WHEN activity = 'canceled' THEN 
                   IF(prev_user_active > 0, 1, 0)
               ELSE 0
           END AS valid_enrollment
    FROM user_event_history
)
SELECT date_time, username, activity, valid_enrollment
FROM validated_events
ORDER BY global_order;

代码解释 #

1. ordered_events：确保事件按时间顺序处理，避免乱序导致的计算错误
2. user_event_history：统计每个事件发生前，用户的有效活跃次数，用于判断extended和canceled事件的有效性
3. validated_events：
   • enrolled：检查全局活跃数是否未达上限，且用户无前置有效注册，满足则标记有效
   • extended：只要用户已有有效活跃，直接标记有效（用户已占用活跃名额）
   • canceled：只要用户已有有效活跃，标记有效并减少全局活跃数

调整_MAX_ACTIVE_ENROLLMENTS的值，即可得到对应限制下的结果。

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