BigQuery统计前序周期购买但下一同期未购买的独立客户数

原SQL问题说明 #

现有SQL的核心逻辑偏差为：仅校验了用户在统计月向后推1年的单月是否有消费，没有覆盖需求要求的完整12个月区间，同时也没有按「上年同月到当年同月」的12个月范围聚合用户，仅按单月用户做统计，因此输出结果不符合预期。

正确实现SQL #

WITH
-- 步骤1：生成所有需要统计的目标月份列表，统计起始日期可按需调整
target_months AS (
  SELECT month
  FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 1 MONTH)) AS month
),
-- 步骤2：拉取全量用户的月度消费标记，提前去重减少计算量
user_monthly_active AS (
  SELECT DISTINCT
    DATE_TRUNC(order_date, MONTH) AS active_month,
    customer_id -- 如果你的表客户字段为CUSTOMER_WID，替换此处即可
  FROM t
  WHERE order_date >= '2019-01-01' -- 提前过滤最早需要用到的历史数据
),
-- 步骤3：对每个目标月，匹配两个区间的用户消费情况
user_interval_match AS (
  SELECT
    tm.month AS target_month,
    prev_user.customer_id,
    IF(next_user.customer_id IS NOT NULL, 1, 0) AS has_buy_next_period
  FROM target_months tm
  -- 关联上年同月到当年同月区间的活跃用户
  LEFT JOIN user_monthly_active prev_user
    ON prev_user.active_month >= DATE_SUB(tm.month, INTERVAL 1 YEAR)
    AND prev_user.active_month < tm.month
  -- 左关联当年同月到下年同月区间的活跃用户，匹配不到就是未消费用户
  LEFT JOIN user_monthly_active next_user
    ON next_user.active_month >= tm.month
    AND next_user.active_month < DATE_ADD(tm.month, INTERVAL 1 YEAR)
    AND prev_user.customer_id = next_user.customer_id
  WHERE prev_user.customer_id IS NOT NULL -- 过滤掉没有前序活跃用户的空记录
)
-- 步骤4：聚合统计结果
SELECT
  target_month AS order_date,
  COUNT(DISTINCT customer_id) AS `count distinct CustomerID`,
  COUNT(DISTINCT IF(has_buy_next_period = 0, customer_id, NULL)) AS `who not buy in the next period`
FROM user_interval_match
GROUP BY target_month
ORDER BY target_month

示例数据验证 #

用你提供的示例数据，统计2020-01-01的结果：

• 前12个月区间（2019-01-01~2020-01-01）活跃用户为111、112，统计值为2
• 后12个月区间（2020-01-01~2021-01-01）活跃用户为111、113，前序用户中仅112未消费，未消费用户统计值为1
  完全匹配需求逻辑。

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