Google Analytics + Firebase 提供了完美的手机应用、网站应用所需的事件统计服务，同时可以将统计数据导入到 Google Cloud BigQuery 服务中，通过 BigQuery 提供的类 SQL 查询，可以快速实现各类应用的统计报表需求。
但是，BigQuery 毕竟不是 MySQL 关系型数据库，最大的不同就是： BigQuery 数据中的字段类型不仅仅是整型、字符串这样的基础类型，还可能是 ARRAY, RECORD 这样的复杂类型。如图：
本文通过实现网站热门文章排行榜这一实际业务需求学习对 UNNEST 函数的运用。
常规查询
对于接入了 Google Analytics + Firebase 统计服务的网站应用，即使没有针对具体的定制事件进行埋点，默认情况下就会统计很多关键事件。
网站事件的统计查询：
$: select distinct(`event_name`), count(*) from `[project-Id].[dataset-Id].events_20220517` group by `event_name`
-- 行 event_name total
-- 1 session_start 24
-- 2 page_view 43
-- 3 first_visit 14
-- 4 user_engagement 26
基本上和常规 SQL 没有什么不同。
文章排行查询
现在就每天的文章阅读排行做一个统计。
文章阅读事件，可以根据 event_name = page_view 获得，但是具体的文章，就必须从 event_params 中获取。而 event_params 是一个 RECORD 类型，类似于常见开发语言中 map 类型。如下:
// select event_params from `[project-Id].[dataset-Id].events_20220517` where event_name = 'page_view' limit 1
event_params
key value
1 page_location { "string_value": "https://mbox.dev/", "int_value": null, "float_value": null, "double_value": null }
2 medium { "string_value": "referral", "int_value": null, "float_value": null, "double_value": null }
3 origin { "string_value": "firebase", "int_value": null, "float_value": null, "double_value": null }
4 entrances { "string_value": null, "int_value": "1", "float_value": null, "double_value": null }
5 source { "string_value": "", "int_value": null, "float_value": null, "double_value": null }
6 ga_session_number { "string_value": null, "int_value": "1", "float_value": null, "double_value": null }
7 page_title { "string_value": "mbox.dev", "int_value": null, "float_value": null, "double_value": null }
8 campaign { "string_value": "(referral)", "int_value": null, "float_value": null, "double_value": null }
9 ga_session_id { "string_value": null, "int_value": "1652781671", "float_value": null, "double_value": null }
10 page_referrer { "string_value": "", "int_value": null, "float_value": null, "double_value": null }
11 session_engaged { "string_value": "0", "int_value": null, "float_value": null, "double_value": null }
12 engaged_session_event { "string_value": null, "int_value": "1", "float_value": null, "double_value": null }
不难看出，一个简单的 event_params 字段中就有 12 各键值对。而现在，需要的根据其中的某一个 key + value 进行统计。
为了能够对复杂类型字段内部字段进行查询，这时就需要 UNNEST 函数，对还有复杂字段的数据进行展开。
$: select `event_name`, params.* from `[project-Id].[dataset-Id].events_20220517`, UNNEST(`event_params`) as params
-- 行 event_name key value
-- 1 page_view page_location { "string_value": "https://mbox.dev/", "int_value": null, "float_value": null, "double_value": null }
-- 2 page_view medium { "string_value": "referral", "int_value": null, "float_value": null, "double_value": null }
-- 3 page_view origin { "string_value": "firebase", "int_value": null, "float_value": null, "double_value": null }
-- 4 page_view entrances { "string_value": null, "int_value": "1", "float_value": null, "double_value": null }
-- 5 page_view source { "string_value": "", "int_value": null, "float_value": null, "double_value": null }
-- 6 page_view ga_session_number { "string_value": null, "int_value": "1", "float_value": null, "double_value": null }
-- 7 page_view page_title { "string_value": "mbox.dev", "int_value": null, "float_value": null, "double_value": null }
-- 8 page_view campaign { "string_value": "(referral)", "int_value": null, "float_value": null, "double_value": null }
-- 9 page_view ga_session_id { "string_value": null, "int_value": "1652781671", "float_value": null, "double_value": null }
-- 10 page_view page_referrer { "string_value": "", "int_value": null, "float_value": null, "double_value": null }
-- 11 page_view session_engaged { "string_value": "0", "int_value": null, "float_value": null, "double_value": null }
-- 12 page_view engaged_session_event { "string_value": null, "int_value": "1", "float_value": null, "double_value": null }
...
通过 UNNEST 展开 event_params 字段后，原来 1 条数据变成了 12 条数据，每条数据增加了 key + value 字段。 现在就可以就 key = 'page_location' 进行统计查询。
路径访问排行
SELECT count(*) as viewed, distinct(param.value.string_value) as location
where event_name = 'page_view' and param.key = 'page_location'
group by param.value.string_value order by viewed desc limit 5
-- 行 location viewed
-- 1 https://mbox.dev/ 12345
-- ...
这样就可统计当日路径的访问排行。
路径+标题排行
如果仅仅是看看统计报表，路径访问排行就足够了。但是，如果需要将排行数据再反馈给前端的话，还需要加上页面的标题信息。那么还需进一步处理数据。
- 首先，过滤出
page_location与page_title数据集。
对于展开后的数据，判断其是否同属相同的父数据，可以通过 user_pseudo_id + event_timestamp 两个字段进行标识。
SELECT `user_pseudo_id`, `event_timestamp`, param
FROM `[project-Id].[dataset-Id].events_20220517`, UNNEST(event_params) AS param
WHERE event_name = "page_view"
AND (param.key = "page_location" OR param.key = "page_title")
-- 行 user_pseudo_id event_timestamp param
-- 1 1074337057.1652682162 1652775792556920 { "key": "page_location", "value": {4} }
-- 2 1074337057.1652682162 1652775792556920 { "key": "page_title", "value": {4} }
-- 3 296593370.1652778176 1652778176493105 { "key": "page_title", "value": {4} }
-- 4 296593370.1652778176 1652778176493105 { "key": "page_location", "value": {4} }
...
从结果数据中可以看出 [1，2], [3, 4] 是属于同一条记录，现在需要将其合并成 1 条数据，构造成新的数据集。
- 再次，合并
page_location与page_title成行
现在可以通过 GROUP BY 命令进行合并数据。
SELECT `user_pseudo_id`, `event_timestamp`,
MAX(if(param.key = "page_title", param.value.string_value, NULL)) AS page_title,
MAX(if(param.key = "page_location", param.value.string_value, NULL)) AS page_location
FROM (
SELECT `user_pseudo_id`, `event_timestamp`, param
FROM `[project-Id].[dataset-Id].events_20220517`, UNNEST(event_params) AS param
WHERE event_name = "page_view"
AND (param.key = "page_location" OR param.key = "page_title")
)
GROUP BY `user_pseudo_id`, `event_timestamp`
-- 行 user_pseudo_id event_timestamp page_title page_location
-- 1 516121640.1652936859 1652936859079354 快速简明教程: css | mbox.dev https://mbox.dev/dev/quick-start/css/
-- 2 1895057944.1652931305 1652931305006932 mbox.dev https://mbox.dev/
-- ...
这样就合并出了新的数据集。
- 最后，实现路径+标题排行的统计
在新数据集的基础上，实现路径+标题排行就非常简单了。
SELECT page_title, page_location, count(*) as viewed
FROM (
SELECT `user_pseudo_id`, `event_timestamp`,
MAX(if(param.key = "page_title", param.value.string_value, NULL)) AS page_title,
MAX(if(param.key = "page_location", param.value.string_value, NULL)) AS page_location
FROM (
SELECT `user_pseudo_id`, `event_timestamp`, param
FROM `[project-Id].[dataset-Id].events_20220517`, UNNEST(event_params) AS param
WHERE event_name = "page_view"
AND (param.key = "page_location" OR param.key = "page_title")
)
GROUP BY `user_pseudo_id`, `event_timestamp`
)
GROUP BY page_title, page_location
ORDER BY viewed DESC
这就是最终的查询语句了。
小结
本文主要参考 using the unnest function in bigquery to analyze event parameters in analytics 该文，实现了定制化的查询需求。文中图片均来源于该文。