使用Google Analytics数据在BigQuery中将嵌套行转置为列

Question

我希望通过自定义维度属性吸引访问者，其中每一行都是唯一的fullvisitorid，列是所需的customdimension.values。

以伦敦头盔为例，这里我正在提取我感兴趣的两个自定义维度的访问者：

SELECT fullvisitorid, customDimensions.index, customDimensions.value
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
where customDimensions.index in (2,3)
group by fullvisitorid, customDimensions.index, customDimensions.value

它会给出类似以下的结果：

+---------------+------------------------+------------------------+
| fullvisitorid | customDimensions_index | customDimensions_value |
+---------------+------------------------+------------------------+
|             1 |                      2 | Bronze                 |
|             1 |                      3 | Yes                    |
|             2 |                      2 | Bronze                 |
|             2 |                      3 | No                     |
|             3 |                      2 | Bronze                 |
|             3 |                      3 | Yes                    |
|             4 |                      2 | Platinum               |
|             4 |                      3 | Yes                    |
+---------------+------------------------+------------------------+

我希望将值转置，其中customDimension_index 2是颜色，customDimension_value 3是yesno，因此结果看起来应该像这样：

+---------------+----------+-------+
| fullvisitorid |  color   | yesno |
+---------------+----------+-------+
|             1 | Bronze   | Yes   |
|             2 | Bronze   | No    |
|             3 | Bronze   | Yes   |
|             4 | Platinum | Yes   |
+---------------+----------+-------+

我可以分别抓取这两个数据并按照fullvisitorid连接起来，但希望能够一次性地以这种方式提取数据。谢谢！

- amysong

Answer 1

2020-01更新：标准SQL更新

SELECT
  fullvisitorid,
  (SELECT value FROM UNNEST(customDimensions) WHERE index=2) color, 
  (SELECT value FROM UNNEST(customDimensions) WHERE index=3) yesno, 
FROM `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`

---

之前:

Mosha的回答是正确的，但我想补充一下，因为它利用了GA记录的嵌套特性:

SELECT
  fullvisitorid,
  FIRST(IF(customDimensions.index=2, customDimensions.value, NULL)) WITHIN RECORD color, 
  FIRST(IF(customDimensions.index=3, customDimensions.value, NULL)) WITHIN RECORD yesno
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
WHERE customDimensions.index in (2,3)

为什么要使用WITHIN RECORD而不是GROUP BY：使用GROUP BY需要消耗大量资源，因为它必须查找并按照可能具有相同customerid的任何记录进行分组。而WITHIN RECORD只在单个行内查找。

如果一个customerid有多行记录（例如，他们曾经访问过一次Bronze / Yes，之后又访问了Platinum / No），则结果会发出每一行和组合，而不仅仅是第一行记录。

- Felipe Hoffa

---

嗨，Felipe，感谢您的回复！但是，我认为按组排序更适合我的需求，因为我的数据中的自定义维度是以用户为中心的，因此每个fullvisitorid（理论上）都具有相同的属性。我发现这个查询有重复项（每个fullvisitorid有多行）- 这在将来需要按会话查看时会很有帮助。再次感谢您的回复。 - amysong

Answer 2

这里是解决方案：

SELECT
  fullvisitorid,
  FIRST(IF(customDimensions.index=2, customDimensions.value, NULL)) color, 
  FIRST(IF(customDimensions.index=3, customDimensions.value, NULL)) yesno
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
where customDimensions.index in (2,3)
group by fullvisitorid 

它依赖于这样一个事实，即任何汇总函数（包括FIRST）都会忽略NULL值。

- Mosha Pasumansky

---

对于已经转向 #standardSQL 的人们：请使用 ANY_VALUE() 代替 FIRST()。我已相应地更新了下面的答案。 - Felipe Hoffa