如何在含嵌套与非嵌套字段的表中正确聚合生成扁平表？

这个问题是处理嵌套数组时非常常见的陷阱——当你UNNEST(hits)数组后，每个会话（对应一条uniqueid记录）会被拆分成多条hit行，你的非嵌套字段visits和hits会在每一行重复出现。直接SUM的话，相当于把同一个会话的数值重复加了N次（N是该会话的hit数量），结果自然就错了。

给你两种靠谱的解决方案，任选其一即可：

方案一：分阶段聚合（更清晰易维护） #

先分别计算会话维度的聚合结果和hit维度的聚合结果，再把它们关联起来，从根源上避免重复计算：

WITH session_agg AS (
  -- 第一步：拿到每个会话的核心数据，确保每个uniqueid只出现一次
  SELECT
    uniqueid,
    date,
    visits,
    hits AS session_total_hits  -- 这里的hits是非嵌套的会话总hit数
  FROM `global-digital.175914969.ga_sessions_20190427`
),
hits_agg AS (
  -- 第二步：计算每个会话下，每个website的engagements总和
  SELECT
    uniqueid,
    hits.website,
    SUM(hits.engagements) AS session_website_engagements
  FROM `global-digital.175914969.ga_sessions_20190427`,
  UNNEST(hits) AS hits
  GROUP BY uniqueid, hits.website
)
-- 第三步：关联两个结果，按date和website汇总最终数值
SELECT
  sa.date,
  ha.website,
  SUM(sa.visits) AS total_visits,
  SUM(sa.session_total_hits) AS total_hits,
  SUM(ha.session_website_engagements) AS total_engagements
FROM session_agg sa
JOIN hits_agg ha ON sa.uniqueid = ha.uniqueid
GROUP BY sa.date, ha.website
ORDER BY sa.date, ha.website;

方案二：用窗口函数简化查询 #

如果不想写CTE，可以用窗口函数在UNNEST后直接提取每个会话的唯一值，确保每个会话的visits和hits只被计算一次：

SELECT
  date,
  website,
  SUM(visits_per_session) AS total_visits,
  SUM(hits_per_session) AS total_hits,
  SUM(engagements) AS total_engagements
FROM (
  SELECT
    date,
    hits.website,
    hits.engagements,
    -- 对每个会话，只取一次visits值（所有hit行共享同一个值）
    FIRST_VALUE(visits) OVER (PARTITION BY uniqueid) AS visits_per_session,
    -- 同理，取会话级的总hit数
    FIRST_VALUE(hits) OVER (PARTITION BY uniqueid) AS hits_per_session
  FROM `global-digital.175914969.ga_sessions_20190427`,
  UNNEST(hits) AS hits
)
GROUP BY date, website
ORDER BY date, website;

补充说明 #

• 如果你的数据里存在没有hits的会话（即hits数组为空），上面的JOIN会过滤掉这些会话。如果需要保留它们，可以把JOIN改成LEFT JOIN，并给website设置默认值（比如COALESCE(ha.website, 'No Hits')）。
• 两种方法的核心思路都是：确保非嵌套字段的聚合是基于会话维度，而不是被UNNEST后的hit维度。

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