sql - 带BigQuery的customDimensions嵌套

我编写了一个标准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，与GA UI相比，此计数是准确的。

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

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，因为有问题的自定义维度基于用户级别，而不是基于点击级别。如果不对输出进行分组，则会出现错误。

如何在不丢失行的情况下取消基于非匹配的自定义维度的嵌套？

最佳答案

问题是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

关于sql - 带BigQuery的customDimensions嵌套，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49057954/

CustomDimensions

sql - 带BigQuery的customDimensions嵌套