sql - 使用 BigQuery 取消嵌套 customDimensions

Question

我编写了一个标准 SQL 查询，用于从 BigQuery 中提取一些 Google Analytics(分析)数据。我在下面粘贴了一个用于测试的简化版本:

SELECT COUNT(DISTINCT(session_id))
FROM (
  SELECT
    CONCAT(CAST(fullVisitorId AS STRING), '.', CAST(visitStartTime AS STRING)) AS session_id
  FROM `myproject.ga_sessions_20180227`, UNNEST(hits) AS hits
);

运行此查询会输出 6,696 的唯一 session 计数，与 GA UI 相比，这是准确的。

但是，当我在查询中引入 customDimensions 时，我丢失了许多行。运行此查询仅输出 6,606 个唯一 session 。

SELECT COUNT(DISTINCT(session_id))
FROM (
  SELECT
    CONCAT(CAST(fullVisitorId AS STRING), '.', CAST(visitStartTime AS STRING)) AS session_id,
    MAX(IF(customs.index = 14, customs.value, NULL)) AS custom_id
  FROM `myproject.ga_sessions_20180227`, UNNEST(customDimensions) AS customs
  GROUP BY fullVisitorId, visitStartTime
);

我没有使用 hits.customDimensions，因为有问题的自定义维度基于用户级别，而不是命中级别。如果我不对输出进行分组，则会出现错误。

如何在不丢失行的情况下取消嵌套非命中的自定义维度？

Answer 1

问题是 CROSS JOIN (逗号):当使用 NULL 交叉连接表时- 或者在这种情况下是一个空的 customDimensions - 结果是 NULL总共而不是仅左表。
您想使用 LEFT JOIN而不是 CROSS JOIN因为这保留了左表:

FROM `table` LEFT JOIN customDimensions AS cd

除此之外，您还可以使用简单的子选择而不是连接子表/数组:

SELECT
  (SELECT value FROM t.customDimensions WHERE index=1) AS cd1
  , COUNT(1) AS Qty
FROM `project.dataset.ga_sessions_20180202` AS t
GROUP BY 1