BigQuery计算各月份对应前3个月累计去重CustomerID数量问题

实现思路 #

• 先提取所有待统计的月份维度列表
• 通过自关联匹配每个统计月份M对应的M-3月 ≤ 数据月份 < M范围内的所有客户记录
• 对每个统计月份分组后对CustomerID去重计数即可

参考SQL代码 #

WITH all_months AS (
  -- 提取表中所有存在的月份作为统计维度
  SELECT DISTINCT DATE AS stat_month
  FROM `your_project.your_dataset.your_table_name`
)
SELECT
  am.stat_month AS DATE,
  COUNT(DISTINCT t.CustomerID) AS `CustomerID Count`
FROM all_months am
LEFT JOIN `your_project.your_dataset.your_table_name` t
  ON t.DATE >= DATE_SUB(am.stat_month, INTERVAL 3 MONTH)
  AND t.DATE < am.stat_month
GROUP BY am.stat_month
ORDER BY am.stat_month

代码说明 #

• 请将代码中的your_project.your_dataset.your_table_name替换为你实际的表路径
• 若你的表中存在同一个月份同一个CustomerID的重复记录，可先对原表做一层SELECT DISTINCT DATE, CustomerID FROM ...的预处理再使用
• 对于前3个月无数据的月份（比如示例中的2021-01-01，往前推3个月没有数据），统计结果会返回0，如果你需要返回空值可将LEFT JOIN改为INNER JOIN，或对COUNT结果加NULLIF处理

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