在BigQuery中基于历史月度数据生成带优先级的用户全周期月度类型表

核心实现思路 #

1. 给不同type映射可比较的数值优先级：questionnaire=1、creation=2、buy=3，数值越大优先级越高
2. 生成每个邮箱从首次出现月份到当前月的完整连续月份序列
3. 计算每个月份截至当前的用户历史最高优先级，映射回对应的type值即可满足「只升级不降级」的规则

完整可运行SQL代码 #

WITH
-- 步骤1：给原始数据的type字段加上优先级映射
typed_events AS (
  SELECT
    month,
    email,
    type,
    CASE type
      WHEN 'questionnaire' THEN 1
      WHEN 'creation' THEN 2
      WHEN 'buy' THEN 3
    END AS priority
  FROM `你的原始表名` -- 替换为你实际的表名称
),
-- 步骤2：计算每个用户的月份覆盖范围：首次出现月 → 当前月
user_month_range AS (
  SELECT
    email,
    MIN(month) AS first_month,
    DATE_TRUNC(CURRENT_DATE(), MONTH) AS current_month
  FROM typed_events
  GROUP BY email
),
-- 步骤3：生成每个用户的连续月份序列，补全中间缺失的月份
user_full_months AS (
  SELECT
    u.email,
    month
  FROM user_month_range u,
  UNNEST(GENERATE_DATE_ARRAY(u.first_month, u.current_month, INTERVAL 1 MONTH)) AS month
),
-- 步骤4：关联原始事件，计算每个用户单月最高优先级（处理同月多事件场景）
monthly_max_priority AS (
  SELECT
    m.email,
    m.month,
    MAX(e.priority) AS monthly_priority
  FROM user_full_months m
  LEFT JOIN typed_events e
    ON m.email = e.email AND m.month = e.month
  GROUP BY m.email, m.month
)
-- 步骤5：计算累积最高优先级，映射回type得到最终结果
SELECT
  email,
  month,
  CASE MAX(monthly_priority) OVER (
    PARTITION BY email 
    ORDER BY month 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )
    WHEN 1 THEN 'questionnaire'
    WHEN 2 THEN 'creation'
    WHEN 3 THEN 'buy'
  END AS type
FROM monthly_max_priority
ORDER BY email, month

注意事项 #

• 如果原始表的month字段不是DATE类型，需要先做类型转换，比如字符串转DATE可以用DATE(PARSE_TIMESTAMP("%Y-%m-%d", month))
• 窗口函数会自动取当前月份及之前所有月份的最高优先级，天然满足type只升级不降级的要求，一旦出现优先级为3的buy，后续所有月份的累积最大值都会固定为3，type自动保持为buy
• 自动覆盖用户首次出现月到当前月的所有月份，中间没有事件的月份会自动继承上一个月的最高优先级

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