BigQuery UNNEST重复值

Question

我正在尝试创建Google数据摘要:按小时和按源属性(在我的例子中命名为服务)的会话、事务和转换速率。我正在从rollup属性数据集中查询一个名为"ga_realtime_view“的表。它是从"ga_realtime_sessions_“表创建的虚拟视图，它允许我们使用标准SQL。

为了有服务栏，我必须使用UNNEST操作。但是，当我这样做时，它会复制所有会话和事务值。

以下是查询：

SELECT
EXTRACT(HOUR FROM TIMESTAMP_SECONDS(visitStartTime) AT TIME ZONE 'Europe/Paris') AS Hour,
hits.sourcePropertyInfo.sourcePropertyDisplayName AS service,
IFNULL(SUM(totals.visits),0) as sessions,
IFNULL(SUM(totals.transactions),0) as transactions,
IFNULL(ROUND((SUM(totals.transactions)/SUM(totals.visits))*100,2),0) AS conversionRate
FROM `XX.ga_realtime_view` AS session, UNNEST(session.hits) AS hits
GROUP BY
Hour,
service
ORDER BY
Hour

我知道关于这个重复问题还有其他的话题，但是在我的例子中，我似乎找不到解决办法。

谢谢你的帮忙

Answer 1

听起来，您希望避免使用数组(通过, UNNEST(session.hits))连接表，因为这样做的效果是复制所有总计。但是，还不清楚您想要对服务名称做什么:如果有多个名称，那么要返回所有名称的数组吗？这曾经是一种方法：

SELECT
EXTRACT(HOUR FROM TIMESTAMP_SECONDS(visitStartTime) AT TIME ZONE 'Europe/Paris') AS Hour,
ARRAY(
   SELECT sourcePropertyInfo.sourcePropertyDisplayName
   FROM UNNEST(session.hits) AS hits
) AS service,
IFNULL(SUM(totals.visits),0) as sessions,
IFNULL(SUM(totals.transactions),0) as transactions,
IFNULL(ROUND((SUM(totals.transactions)/SUM(totals.visits))*100,2),0) AS conversionRate
FROM `XX.ga_realtime_view` AS session
GROUP BY
Hour,
service
ORDER BY
Hour

但是，您会注意到，查询现在提供了一个不能按数组分组的错误。如果您期望在点击中只有一种类型的服务，您可以只提取一种：

SELECT
EXTRACT(HOUR FROM TIMESTAMP_SECONDS(visitStartTime) AT TIME ZONE 'Europe/Paris') AS Hour,
(SELECT MAX(sourcePropertyInfo.sourcePropertyDisplayName)
 FROM UNNEST(session.hits) AS hits) AS service,
IFNULL(SUM(totals.visits),0) as sessions,
IFNULL(SUM(totals.transactions),0) as transactions,
IFNULL(ROUND((SUM(totals.transactions)/SUM(totals.visits))*100,2),0) AS conversionRate
FROM `XX.ga_realtime_view` AS session
GROUP BY
Hour,
service
ORDER BY
Hour