Google BigQuery：如何同时统计新访客数并使用自定义维度？

你的问题核心在于UNNEST操作导致的重复行累加——当你对hits和hit.customDimensions做CROSS JOIN UNNEST时，每个原始会话（对应一个fullvisitorid）会被拆分成多行（每个hit+每个符合条件的customDimension对应一行）。这时候totals.newVisits（会话级指标）会被重复计算多次，最终导致SUM结果远大于实际值。

修正思路 #

先将数据聚合到会话级别，确保每个fullvisitorid只对应一行数据，同时提取出你需要的自定义维度值，再进行最终的统计计算，避免重复累加。

修正后的查询语句 #

WITH session_level_metrics AS (
  SELECT
    PARSE_DATE('%Y%m%d', t.date) AS Date,
    fullvisitorid,
    -- 处理newVisits：null值转为0，避免SUM时忽略
    IF(totals.newVisits IS NOT NULL, totals.newVisits, 0) AS newVisits,
    -- 提取会话中任意一个index=1的自定义维度值（根据业务需求可替换为MAX/MIN）
    ANY_VALUE(
      (SELECT value FROM UNNEST(hits) hit, UNNEST(hit.customDimensions) cd WHERE cd.index = 1)
    ) AS Orig
  FROM `table` t
)
SELECT
  Date,
  Orig,
  COUNT(DISTINCT fullvisitorid) AS User,
  SUM(newVisits) AS New_Visitors
FROM session_level_metrics
GROUP BY Date, Orig

关键细节说明 #

1. 会话级聚合：用CTE先把数据收拢到会话维度，每个fullvisitorid只保留一行，确保newVisits只会被计算一次。
2. 自定义维度提取：用ANY_VALUE（或MAX/MIN）子查询提取会话内符合条件的自定义维度值，避免因多个hit包含相同index的维度而产生重复行。
3. null值处理：用IF把totals.newVisits的null转为0，确保SUM统计不会遗漏非新访客的会话（因为非新访客的newVisits是null）。

额外注意事项 #

如果你的Orig是hit级别的维度（即同一个会话内不同hit可能有不同的Orig值），那你的需求可能需要按Date、Orig和hit维度统计，但这时候COUNT(DISTINCT fullvisitorid)会把同一个用户在不同hit下的不同Orig分别计数，你需要明确业务需求后调整聚合逻辑。

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