使用BigQuery Standard SQL UNNEST后数据重复问题求助

嘿，这个问题我之前也踩过坑！核心原因很简单：你把会话级别的汇总字段（totals.hits）和嵌套的hits记录一起展开了——每个会话有N条hits，UNNEST后就会生成N行数据，而每行的totals.hits都是整个会话的总命中数，SUM的时候自然就把这个值重复加了N次，结果肯定比实际高。

给你两种适配不同需求的解决方案：

方案一：先处理会话级数据，再提取交易信息（贴合你的原查询需求） #

这个方案先在子查询里保留每个会话的唯一行，彻底避免totals.hits被重复计算，同时在会话层面提取交易ID：

SELECT 
  date, 
  source, 
  medium, 
  SUM(total_hit_per_session) AS total_hit,
  MAX(transactionid) AS transaction
FROM (
  SELECT 
    date, 
    trafficSource.source AS source, 
    trafficSource.medium AS medium,
    totals.hits AS total_hit_per_session,
    -- 从当前会话的hits中提取非空的交易ID，这里用MAX取一个，也可以用ARRAY_AGG拿到所有交易ID
    (SELECT MAX(transaction.transactionid) 
     FROM UNNEST(hits) hits 
     WHERE hits.transaction.transactionid IS NOT NULL) AS transactionid
  FROM `test.test.session_streaming_*`
  WHERE _table_suffix BETWEEN '20180401' AND '20180501'
)
GROUP BY date, source, medium

方案二：如果需要关联每条交易记录（适合交易明细统计） #

如果你想把每条交易和会话维度关联，又不想重复计算totals.hits，可以用窗口函数标记每个会话的第一行，只在第一行计算totals.hits：

SELECT 
  date, 
  source, 
  medium,
  SUM(CASE WHEN is_first_hit_in_session THEN totals.hits ELSE 0 END) AS total_hit,
  transactionid AS transaction
FROM (
  SELECT 
    date,
    trafficSource.source AS source,
    trafficSource.medium AS medium,
    totals.hits,
    hits.transaction.transactionid AS transactionid,
    -- 标记每个会话的第一条hits记录
    ROW_NUMBER() OVER(PARTITION BY fullVisitorId, visitId) = 1 AS is_first_hit_in_session
  FROM `test.test.session_streaming_*`,
       UNNEST(hits) hits
  WHERE _table_suffix BETWEEN '20180401' AND '20180501'
    AND hits.transaction.transactionid IS NOT NULL
)
GROUP BY date, source, medium, transactionid

关键思路总结 #

• 会话级字段（比如totals.hits、totals.pageviews）是整个会话的汇总值，绝对不能在UNNEST(hits)后的行上直接SUM，否则会被重复计算。
• 要么先在会话层面聚合这些字段，再处理嵌套的hits数据；要么用窗口函数标记每个会话的唯一行，只计算一次会话级字段。

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