BigQuery实现各月末用户累计签约购机去重数量统计

源表字段说明 #

现有合约用户月末状态快照表包含以下字段：

• Month ID：月末统计节点的月份标识
• Phone Model：合约对应手机机型
• Sub ID：订阅用户唯一ID
• Contract Start Date：合约开始日期

核心统计规则：针对每个月末统计节点，计算对应订阅用户自数据统计起始以来，累计签约购买的不同手机机型总数。计数严格按手机机型维度去重，同一用户同一款机型无论对应多少条不同合约起始日期的记录，仅计数1次，不得按合约条数/起始日期计数。例如订阅用户S2存在3条不同合约起始日期的记录，但仅对应2款不同机型，该用户2022年4月、5月的统计结果应为2而非3。

实现SQL代码 #

替换代码里的表路径为实际的BigQuery表地址即可运行：

WITH user_unique_model AS (
  -- 按用户+机型去重，取每个机型首次进入统计的月份，从根源避免同机型多合约重复计数
  SELECT
    `Sub ID`,
    `Phone Model`,
    MIN(`Month ID`) AS first_show_month
  FROM `你的项目名.你的数据集名.合约用户月末状态表`
  GROUP BY `Sub ID`, `Phone Model`
),
stat_month_list AS (
  -- 取出全量需要统计的月末节点
  SELECT DISTINCT `Month ID` AS current_month
  FROM `你的项目名.你的数据集名.合约用户月末状态表`
)
SELECT
  s.current_month AS `Month ID`,
  u.`Sub ID`,
  COUNT(DISTINCT u.`Phone Model`) AS cumulative_unique_model_cnt
FROM stat_month_list s
LEFT JOIN user_unique_model u
  ON u.first_show_month <= s.current_month
-- 若仅需统计当月末仍处于合约期内的用户，放开下方注释即可
-- INNER JOIN (
--   SELECT DISTINCT `Month ID`, `Sub ID` 
--   FROM `你的项目名.你的数据集名.合约用户月末状态表`
-- ) active
--   ON s.current_month = active.`Month ID` 
--   AND u.`Sub ID` = active.`Sub ID`
GROUP BY s.current_month, u.`Sub ID`
ORDER BY s.current_month, u.`Sub ID`

逻辑说明 #

1. 第一层CTE对用户和机型做聚合，仅保留每个用户对应每款机型的第一次统计月份，同一款机型无论对应多少个不同起始日期的合约，都只会保留一条记录，完全符合去重要求
2. 第二层CTE提取所有历史月末统计节点，避免遗漏统计月份
3. 主查询通过first_show_month <= current_month的关联条件，筛选出截止当前统计月已经签约过的所有机型，分组计数后得到累计去重机型数
4. 代码内置可选逻辑：如果不需要统计已经合约到期、当月末不在合约期内的用户，放开注释的关联语句即可，输出结果会自动过滤非当月有效用户

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