sql - 如何在 BigQuery 中获取多个 event_params 作为列

Question

我需要的是：事件的总发生次数以及与该事件相关的数据。

我尝试了什么：在设备中，我向 Firebase Analytics 发送了一些事件。事件看起来像：

event_name = "business_global_view_profile"

带参数：

business_global_name :String
business_global_city:String
business_global_ID:String

所以在 BigQuery 中我得到了类似的东西（在更多不需要的值和事件之间）：

我只需要获取我发送的参数，所以我需要丢弃所有的东西，比如ga_session_id，firebase_event_origin等等......

所以首先我尝试从所有其他事件中“过滤”我需要的事件：

SELECT params.key AS ParamKey, params.value.string_value as Value
FROM `xxxxxx.xxxxx.xxxxx`,
UNNEST(event_params) as params
WHERE event_name = "business_global_view_profile"

之后，我得到一张像：

此刻，我实现了按 ID 获取出现次数

SELECT Value AS BusinessID, COUNT(Value) Occurrences
  FROM(SELECT params.key AS ParamKey, params.value.string_value as Value
  FROM `xxxxxx.xxxxx.xxxxx`,
  UNNEST(event_params) as params
  WHERE event_name = "business_global_view_profile"
  AND event_date = "20190409")
WHERE ParamKey = "business_global_ID"
Group by Value

但这是我迷路的地方，因为我需要的是：

|BusinessID | Occurrences | BusinessName | BusinessCity |

在哪里

• BusinessName = business_global_name
• BusinessCity = business_global_city

我怎样才能把所有这些信息放在一起？当然有更短的方法可以做到这一点，但我对 SQL 的工作并不多

标签： sqlgoogle-bigqueryfirebase-analytics

Answer 1

我不太确定我是否正确地满足了您的要求，但这是一个可能的解决方案。为简单起见，我使用了以下模式：

并提出以下查询：

SELECT occ_count.BusinessID, occ_count.Occurrences, flat_data.BusinessName, flat_data.BusinessCity
FROM
(
SELECT params.value AS BusinessID, count(params.value) AS Occurrences
FROM `XXXX.XXXX.XXXX`, UNNEST(event_params) as params
WHERE event_name = 'business_global_view_profile'
AND event_date = "20190409"
AND params.key = 'business_global_id'
GROUP BY params.value
) AS occ_count
JOIN
(
SELECT DISTINCT arr1.value AS BusinessID, arr2.value AS BusinessName, arr3.value as BusinessCity
FROM `XXXX.XXXX.XXXX`, UNNEST(event_params) as arr1, UNNEST(event_params) arr2, UNNEST(event_params) arr3
WHERE arr2.key = 'business_global_name'
AND arr3.key = 'business_global_city'
) AS flat_data
ON occ_count.BusinessID = flat_data.BusinessID

请注意，我假设对于任何business_global_id一个business_global_name和的组合business_global_city。如前所述，为了简单起见，我还从而event_params.value不是取值。event_params.value.string_value