我正在尝试对 Firebase 存储的事件执行 Google BigQuery。我已经执行了以下查询
SELECT * FROM `myTable` LIMIT 6
其结果如下：
+-----+----------+--------+------------------+---------------------------------+
| Row | date | name | event_params.key | event_params.value.string_value |
+-----+----------+--------+------------------+---------------------------------+
| 1 | 20200922 | Event1 | errorName | BLE_Not_connected |
| | | | appDetails | 2.2.2 |
| | | | errorDetails | iOS-Error |
+-----+----------+--------+------------------+---------------------------------+
所以，这里 row-1 有多个条目event_params.key他们的价值体现在event_params.value.string_value柱子。现在，我想执行一个 Google Big-Query 来扁平化event_params.key列值并在下面显示结果
+-----+----------+--------+------------------+---------------------------------+
| Row | date | name | errorName | appDetails | errorDetails |
+-----+----------+--------+------------------+---------------------------------+
| 1 | 20200922 | Event1 | BLE_Not_connected| 2.2.2 | iOS-Error |
+-----+----------+--------+------------------+---------------------------------+
有人可以帮助我吗？提前致谢。
以下是 BigQuery 标准 SQL
EXECUTE IMMEDIATE (
SELECT """
SELECT date, name, """ ||
STRING_AGG("""MAX(IF(key = '""" || key || """', value.string_value, NULL)) AS """ || key, ', ')
|| """
FROM `project.dataset.table` t, t.event_params
GROUP BY date, name
"""
FROM (
SELECT DISTINCT key
FROM `project.dataset.table` t, t.event_params
)
);
如果适用于您的问题中的样本数据 - 输出是
Row date name errorName appDetails errorDetails
1 20200922 Event1 BLE_Not_connected 2.2.2 iOS-Error
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
