如何为多维度自定义Firebase事件统计唯一用户数？

我完全懂你碰到的痛点——用APPROX_COUNT_DISTINCT做单维度统计没问题，但加多个维度分组后，它会把用户按分组切割，没法给出全局准确的唯一用户数。而HLL（HyperLogLog）的优势就是能先把用户标识转换成可合并的sketch结构，之后不管怎么筛选维度，都能通过合并sketch得到准确的去重结果，完美适配Data Studio的动态筛选需求。

下面是具体的解决方案：

第一步：生成带HLL Sketch的维度化视图 #

先创建一个视图，把所有你需要的维度（事件参数、用户地域、用户属性等）和对应的用户HLL Sketch存储起来。这样后续不管怎么筛选，都能基于这个视图快速计算唯一用户数：

CREATE OR REPLACE VIEW `project.info_project_TOTAL.event_user_sketches` AS
SELECT
  x.date AS event_date,
  x.name AS event_name,
  -- 提取事件关键参数维度
  (SELECT params.value.string_value FROM x.params WHERE params.key = 'grade') AS vl_grades,
  -- 用户地域与设备维度
  user_dim.geo_info.country AS user_country,
  user_dim.geo_info.region AS user_region,
  user_dim.geo_info.city AS user_city,
  user_dim.device_info.user_default_language AS user_language,
  user_dim.app_info.app_platform AS app_platform,
  -- 用户属性维度
  user_prop.key AS user_prop_key,
  user_prop.value.value.string_value AS user_prop_string_value,
  -- 事件计数（修正原查询的计数逻辑，避免UNNEST导致重复计数）
  COUNT(*) AS event_count,
  -- 生成每个维度组合对应的用户HLL Sketch
  HLL_COUNT.INIT(user_dim.app_info.app_instance_id) AS user_sketch
FROM 
  `project.info_project_TOTAL.TOTAL_results_jobs`,
  UNNEST(event_dim) AS x,
  UNNEST(user_dim.user_properties) AS user_prop
WHERE 
  x.name = 'Zlag_Click'
GROUP BY
  event_date,
  event_name,
  vl_grades,
  user_country,
  user_region,
  user_city,
  user_language,
  app_platform,
  user_prop_key,
  user_prop_string_value
ORDER BY 
  event_count DESC;

第二步：用HLL_COUNT.MERGE回答你的业务问题 #

基于上面的视图，你可以轻松通过筛选+合并sketch得到任意条件下的唯一用户数：

问题1：过去x天内，来自德国的触发事件的唯一用户数 #

SELECT
  HLL_COUNT.MERGE(user_sketch) AS unique_users
FROM
  `project.info_project_TOTAL.event_user_sketches`
WHERE
  event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL x DAY)
  AND user_country = 'Germany';

问题2：过去x天内，触发难度等级为5的事件的唯一用户数 #

SELECT
  HLL_COUNT.MERGE(user_sketch) AS unique_users
FROM
  `project.info_project_TOTAL.event_user_sketches`
WHERE
  event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL x DAY)
  AND vl_grades = '5'; -- 如果grade是数值类型，去掉引号即可

问题3：过去x天内，请求特定资源的唯一用户数 #

先给视图新增资源参数维度（假设事件参数里的key是resource）：

CREATE OR REPLACE VIEW `project.info_project_TOTAL.event_user_sketches` AS
SELECT
  -- 保留原有所有字段
  x.date AS event_date,
  x.name AS event_name,
  (SELECT params.value.string_value FROM x.params WHERE params.key = 'grade') AS vl_grades,
  -- 新增特定资源参数
  (SELECT params.value.string_value FROM x.params WHERE params.key = 'resource') AS requested_resource,
  user_dim.geo_info.country AS user_country,
  -- ... 其他原有维度字段 ...
  COUNT(*) AS event_count,
  HLL_COUNT.INIT(user_dim.app_info.app_instance_id) AS user_sketch
FROM 
  `project.info_project_TOTAL.TOTAL_results_jobs`,
  UNNEST(event_dim) AS x,
  UNNEST(user_dim.user_properties) AS user_prop
WHERE 
  x.name = 'Zlag_Click'
GROUP BY
  -- 把新增参数加入分组
  event_date,
  event_name,
  vl_grades,
  requested_resource,
  -- ... 其他原有分组字段 ...
ORDER BY 
  event_count DESC;

然后查询特定资源的唯一用户数：

SELECT
  HLL_COUNT.MERGE(user_sketch) AS unique_users
FROM
  `project.info_project_TOTAL.event_user_sketches`
WHERE
  event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL x DAY)
  AND requested_resource = 'your_target_resource';

适配Data Studio的技巧 #

把这个视图作为Data Studio的数据源后，你可以直接添加筛选器（比如国家、难度等级），然后创建一个计算字段：

HLL_COUNT_MERGE(user_sketch)

这样当你调整筛选条件时，Data Studio会自动合并符合条件的sketch，实时展示准确的唯一用户数。

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