将嵌套的自定义维度列数据转置为 Bigquery 行

Question

我的 BigQuery 表如下所示

Fullvisitorid   CustomDimension.Index   CustomDimension.value
123                1                       red
2                       blue
3                       green
456                1                       red
3                       orange
4                       black

我希望我的最终输出如下所示

Fullvisitorid   Color1     Color2
123           red       green    
456           red       orange

以下是我编写的查询，但收到错误"找不到函数：第一">

SELECT
fullvisitorid,
FIRST(IF(customDimensions.index=1, customDimensions.value, NULL)) color1,
FIRST(IF(customDimensions.index=3, customDimensions.value, NULL)) color2
FROM `my_table`
cross join
unnest(customDimensions) customDimensions,
unnest(hits) hits
where customDimensions.index in (1,3)
group by fullvisitorid

我发现了一个类似的问题，它帮助我编写了查询：

[使用谷歌分析数据在bigquery中将嵌套行转置为列

我不确定为什么我的查询出现错误。 我真的很感激任何帮助！

谢谢

Answer 1

你现在正在使用 #standardSQL - 这很好。

而不是FIRST()使用ANY_VALUE().

我相应地更新了参考问题中的答案：

• https://stackoverflow.com/a/29664156/132438

Answer 2

您还可以创建一个用户定义函数，并始终调用它 您需要自定义维度：

SELECT
fullvisitorid,
PATH.CUSTOM_DIMENSION_BY_INDEX(1, h.customDimensions) AS color1,
PATH.CUSTOM_DIMENSION_BY_INDEX(3, h.customDimensions) AS color2,
FROM `my_table`
cross join
unnest(customDimensions) customDimensions,
unnest(hits) hits
where customDimensions.index in (1,3)
group by fullvisitorid

哪里路径。CUSTOM_DIMENSION_BY_INDEX(index， h.customDimensions( 是一个 UDF，格式为：

(SELECT x.value FROM UNNEST(arr) x WHERE indx=x.index)

您可以在此处找到有关它的更多信息。