按月统计活跃促销客户数的BigQuery SQL问题求助

你的问题核心在于原CASE语句会把跨月的促销仅归到第一个匹配的月份，但实际上这类促销应该在每个覆盖的月份都被统计，这就导致了单月单独统计和批量统计结果不一致。

原代码的问题分析 #

比如一个促销从2022-06-20持续到2022-07-5，原CASE会优先匹配6月的条件，将其归到6月，但7月这个客户其实仍处于活跃促销状态，单独统计7月时就会漏掉该客户，结果自然不一致。

正确实现方案（BigQuery） #

我们需要先生成目标月份的列表，再将每个促销记录与所有月份做关联，判断促销是否覆盖该月份，最后按月份统计去重客户数：

WITH target_months AS (
  -- 生成需要统计的月份，这里是2022年6-8月，可按需调整
  SELECT DATE_TRUNC(month, MONTH) AS month_start
  FROM UNNEST(GENERATE_DATE_ARRAY('2022-06-01', '2022-08-01', INTERVAL 1 MONTH)) AS month
),
promotion_month_overlap AS (
  SELECT
    c.region,
    tm.month_start AS month,
    c.customer_id
  FROM customers_table c
  CROSS JOIN target_months tm
  -- 判断促销时间是否与当前月份有重叠
  WHERE DATE(c.start_time) <= LAST_DAY(tm.month_start)
    AND DATE(c.end_time) >= tm.month_start
)
SELECT
  region,
  month,
  COUNT(DISTINCT customer_id) AS active_customers
FROM promotion_month_overlap
GROUP BY region, month
ORDER BY region, month;

代码说明 #

1. target_months CTE：用GENERATE_DATE_ARRAY生成目标月份的第一天列表，避免手动写每个月份的条件。
2. promotion_month_overlap CTE：将每个促销记录与所有目标月份做交叉连接，通过DATE(start_time) <= LAST_DAY(month_start)和DATE(end_time) >= month_start判断促销是否覆盖该月份——只要促销开始时间不晚于当月最后一天，且结束时间不早于当月第一天，就说明该月份内这个客户有活跃促销。
3. 最后分组统计：按区域和月份分组，统计去重的客户ID数量，得到每个月的准确活跃客户数。

为什么这个方案能解决问题 #

跨月的促销会被关联到所有覆盖的月份，比如6-7月的促销会同时出现在6月和7月的结果中，这样无论单独统计哪个月，都能包含该客户，结果一致。

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