在BigQuery中使用UNNEST无数据返回？SQL语法问题排查

你的SQL无数据返回的核心问题在于连续用逗号UNNEST多个嵌套数组时，会触发隐式内连接（笛卡尔积）：

• 你同时展开了hits.product和hits.promotion，只有当同一条hits记录里同时存在商品数据和促销数据时，才会生成有效行。但Google Analytics的会话数据中，绝大多数hits只会包含其中一种数据（甚至都没有），交叉后自然没有匹配结果，返回空集。

修复后的SQL写法 #

把隐式内连接改成LEFT JOIN UNNEST，确保即使某类嵌套数据不存在，也能保留原有行：

SELECT 
  date,
  COUNT(visitNumber) AS visits,
  product.v2ProductName,
  SUM(productRevenue) AS Revenue,
  product.v2ProductCategory,
  CONCAT(trafficSource.source,'/', trafficSource.medium) AS source_medium, 
  promo.promoName
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170*` t
LEFT JOIN UNNEST(t.hits) AS hits
LEFT JOIN UNNEST(hits.product) AS product
LEFT JOIN UNNEST(hits.promotion) AS promo
WHERE
  _TABLE_SUFFIX BETWEEN '601' AND '605'
GROUP BY date, product.v2ProductName, product.v2ProductCategory, promo.promoName, source_medium
ORDER BY date ASC

额外注意点 #

• 如果你的visits是要统计会话数量，COUNT(visitNumber)可能不准确，建议换成COUNT(DISTINCT CONCAT(fullVisitorId, '-', visitId))，避免同一会话被多次统计（因为展开hits后同一会话会有多行）。
• 若不需要保留无商品/无促销的行，可以把部分LEFT JOIN改回JOIN，但根据业务场景，通常LEFT JOIN更符合需求。

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