BigQuery / Firebase查询事件，按用户属性排序-Java 学习之路

我将Firebase项目连接到BigQuery，并收到了昨天的所有活动 .

我有一个具有Integer参数的用户定义事件 . 以及String类型的用户属性“user_job”（例如“Pilot”，“Mechanic”，“Programmer”，...）我想知道用户作业分组的整数参数的平均值 .

是否有提取该信息的SQL查询？

我知道SQL但我的问题是数据以某种方式嵌套在RECORD中，我不知道如何处理它 . 如果每个事件一行，每个用户属性一列，我就不会遇到这个问题 . 如果是这样的话，我可以这样写：

SELECT AVG( param ), user_job FROM table WHERE event_type = "my_event" GROUP BY user_job

但是用户属性在RECORD（数组？）中，并且RECORD中也有多个事件 .

或者使用SQL查询是处理这类请求的错误方法 . 如果是这样，有人可以指点我的方向 . 我看到BigQuery中有user defined functions . 因此，我应该创建一个用户定义的函数，将Firebase BigQuery行转换为如上所述的平面结构，这是正确的方法吗？

Update: 我运行了Sara和Elliot的查询，处理的字节在两种情况下均相同（这是成本） . Saras查询比Elliots跑得更快，但这可能是随机的机会（谷歌收费数据，而不是时间） . 即便如此，我还是将Elliots的答案标记为最终答案，因为根据文档，Standart SQL是推荐的答案 . 我仍然不知道这一切与UDF相比如何，可能会对此进行测试 .

Update2: 我使用UDF实现对其进行了测试，该实现为每个round_completed事件发出了一个新行（语言，硬币） . 与Sara和Elliot发布的仅限SQL解决方案相比，它运行时间更长，处理的数据更多 .

|_Solution___|_Time___|_Data Processed___|
|Sara        |2.1s    |399 KB            |
|Elliot      |2.4s    |399 KB            |
|UDF         |3.4s    |437 KB            |

*请注意，时间可能是一个糟糕的指标，因为不知道googles数据中心在给定时间的繁忙程度 . 最终，处理数据的成本更低 . 我仍然包括时间，也许它在某种程度上是有用的 .

2 回答

2
我建议使用standard SQL而不是遗留SQL进行此类分析，因为它使语义更清晰 . 用户定义的函数，特别是在遗留SQL中，将比使用SQL更难维护和更昂贵 .

这是一个可能有用的示例查询 . 要运行它，请取消选中UI中“显示选项”下的“使用旧版SQL”：
```
SELECT
  user_properties.value.value.string_value as lang,
  AVG((SELECT SUM(value.int_value)
       FROM UNNEST(event_dim),
         UNNEST(params)
       WHERE key = "coins_awarded")) as avg_coins 
FROM 
  `firebase-analytics-sample-data.ios_dataset.app_events_20160607` t,
    UNNEST(user_dim.user_properties) user_properties
WHERE 
  user_properties.key = "language"
GROUP BY 1;
```
在此查询中，带有 UNNEST(user_dim.user_properties) 的逗号运算符为重复的 user_properties 中的每个元素返回一行：
```
`firebase-analytics-sample-data.ios_dataset.app_events_20160607`,
  UNNEST(user_dim.user_properties) user_properties
```
在所有重复中计算 int_value 之和的子查询中， UNNEST 用于将重复的 event_dim 和 params 转换为行，逗号运算符在它们之间获取叉积：
```
SELECT SUM(value.int_value)
FROM UNNEST(event_dim),
  UNNEST(params)
WHERE key = "coins_awarded"
```
有关旧版SQL中的 FLATTEN 以及标准SQL中的等效内容的更多信息，请参阅迁移指南中的Removing repetition with FLATTEN .
回复于 2024-04-20T02:16:21+08:00

要完成此任务，您需要FLATTEN其中一个重复的字段 . 使用示例Firebase Analytics datasets，这里's a query that does something similar to what you'试图实现：

SELECT
  user_dim.user_properties.value.value.string_value as lang,
  AVG(event_dim.params.value.int_value) as avg_coins 
FROM 
  FLATTEN([firebase-analytics-sample-data:ios_dataset.app_events_20160607], user_dim.user_properties)
WHERE 
  user_dim.user_properties.key = "language"
  AND event_dim.params.key = "coins_awarded"
GROUP BY 1

这将获得为用户授予的平均硬币数量，按其语言偏好分组 .

回复于 2024-04-20T02:16:21+08:00

BigQuery / Firebase查询事件，按用户属性排序

2 回答

相关问题