BigQuery使用SQL简便实现按城市、国家统计去重用户数的总计与小计

核心需求 #

• 基于已有的用户行为数据集，在BigQuery环境下用尽可能简便的方案完成分层去重用户统计
• 数据集包含国家、城市、唯一用户ID三类核心字段
• 统计范围覆盖三类聚合结果：
  • 全量数据整体去重用户总数
  • 按国家维度聚合的去重用户数小计
  • 按城市维度聚合的去重用户数明细
• 最终输出为带明确层级的结构化报表，不需要手动拼接多段查询结果

最简实现路径 #

不用写多段聚合SQL再拼UNION ALL，BigQuery原生支持GROUPING SETS多维聚合语法，单条查询就能出全部分层结果，还只会扫描一次原表，性能比多段拼接好很多。直接跑下面这段就行，把表路径替换成你自己的业务表：

SELECT
  country,
  city,
  COUNT(DISTINCT user_id) AS unique_user_cnt,
  GROUPING_ID(country, city) AS group_level
FROM
  `your_project.your_dataset.your_user_table`
GROUP BY GROUPING SETS (
  -- 全量总计行
  (),
  -- 国家维度小计行
  (country),
  -- 城市维度明细行
  (country, city)
)
-- 按层级排序，输出顺序和常规小计报表一致
ORDER BY group_level, country, city;

结果处理提示 #

• 自动生成的group_level字段用来标记行的类型，省得手动判断：
  • 值为3：这行是全量总计，此时country、city字段都是NULL
  • 值为1：这行是对应国家的小计，此时city字段是NULL
  • 值为0：这行是具体城市的明细，两个维度都有真实值
• 导报表的时候，对着group_level把NULL替换成对应文案就行：比如总计行换成“全量总计”，国家小计行的city列换成“合计”，直接就出来分层小计的效果
• 后面要加别的层级统计，比如再加个区域维度的小计，只要在GROUPING SETS里加一行对应的分组配置就行，不用重写整个查询

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