如何不使用JOIN显示多表列？UNNEST关联promotion全为NULL问题

问题根源 #

你写的SQL里直接对promotion数组做UNNEST是错误的——在Google Analytics的BigQuery schema里，promotion是嵌套在hits对象里的子数组，不是会话级的顶层数组。直接关联顶层的promotion相当于找一个不存在的字段，结果自然全是NULL。另外补充：你之前关联product的写法也有问题，product同样是hits的子数组，不是顶层的。

正确的关联写法 #

必须先展开hits，再从每个hit里分别展开product和promotion：

SELECT 
  date,
  p.v2ProductName,
  SUM(p.productRevenue) AS Revenue,
  p.v2ProductCategory,
  promo.promoName,
  CONCAT(trafficSource.source,'/', trafficSource.medium) AS source_medium
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170*`
LEFT JOIN UNNEST(hits) AS h
LEFT JOIN UNNEST(h.product) AS p  -- 从hits里取product子数组
LEFT JOIN UNNEST(h.promotion) AS promo  -- 从hits里取promotion子数组
WHERE
  _TABLE_SUFFIX BETWEEN '601' AND '701'
  AND p.productRevenue IS NOT NULL
GROUP BY date, p.v2ProductName, p.v2ProductCategory, promo.promoName, source_medium
ORDER BY date ASC

不使用JOIN的替代实现方法 #

方法1：在SELECT语句内用子查询展开数组 #

适合不需要复杂层级关联的场景，通过子查询直接从嵌套数组中提取所需字段：

SELECT 
  date,
  (SELECT p.v2ProductName FROM UNNEST(h.product) p WHERE p.productRevenue IS NOT NULL LIMIT 1) AS v2ProductName,
  SUM((SELECT SUM(p.productRevenue) FROM UNNEST(h.product) p WHERE p.productRevenue IS NOT NULL)) AS Revenue,
  (SELECT p.v2ProductCategory FROM UNNEST(h.product) p WHERE p.productRevenue IS NOT NULL LIMIT 1) AS v2ProductCategory,
  (SELECT promo.promoName FROM UNNEST(h.promotion) promo LIMIT 1) AS promoName,
  CONCAT(trafficSource.source,'/', trafficSource.medium) AS source_medium
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170*`
LEFT JOIN UNNEST(hits) AS h
WHERE
  _TABLE_SUFFIX BETWEEN '601' AND '701'
  AND EXISTS (SELECT 1 FROM UNNEST(h.product) p WHERE p.productRevenue IS NOT NULL)
GROUP BY date, v2ProductName, v2ProductCategory, promoName, source_medium
ORDER BY date ASC

方法2：用CTE提前展开所有嵌套数据 #

先通过CTE把多层嵌套的数据全部展开，再做聚合，逻辑更直观：

WITH expanded_data AS (
  SELECT
    date,
    p.v2ProductName,
    p.productRevenue,
    p.v2ProductCategory,
    promo.promoName,
    CONCAT(trafficSource.source,'/', trafficSource.medium) AS source_medium
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170*`
  CROSS JOIN UNNEST(hits) h
  CROSS JOIN UNNEST(h.product) p
  LEFT JOIN UNNEST(h.promotion) promo
  WHERE
    _TABLE_SUFFIX BETWEEN '601' AND '701'
    AND p.productRevenue IS NOT NULL
)
SELECT
  date,
  v2ProductName,
  SUM(productRevenue) AS Revenue,
  v2ProductCategory,
  promoName,
  source_medium
FROM expanded_data
GROUP BY date, v2ProductName, v2ProductCategory, promoName, source_medium
ORDER BY date ASC

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