Bigquery 多表查询

Firebase上报的数据会根据时间分到不同的表中,例如:

my_project.analytics_20190307.events_20211015
my_project.analytics_20190307.events_20211016
my_project.analytics_20190307.events_20211017
my_project.analytics_20190307.events_20211018

如果要对这四个表进行跨表查询,则需要建立一个临时表即可:

-- 查询活跃用户
WITH tmp_table AS (
    SELECT * FROM `my_project.analytics_20190307.events_20211015` 
    UNION ALL 
    SELECT * FROM `my_project.analytics_20190307.events_20211016`
    UNION ALL 
    SELECT * FROM `my_project.analytics_20190307.events_20211017`
    UNION ALL 
    SELECT * FROM `my_project.analytics_20190307.events_20211018`
)
SELECT COUNT(DISTINCT(user_pseudo_id)) FROM tmp_table limit 10

 或者可以使用模糊匹配来查询多个表:

1. 查询以`events`开头的所有表

SELECT * FROM `my_project.analytics_20190307.events_*`

2. 查询`events`开头的,2021年10月分的所有表

SELECT * FROM `my_project.analytics_20190307.events_202110*`

参考资料:

https://support.google.com/analytics/answer/4419694?hl=zh-Hans#multiplehttps://support.google.com/analytics/answer/4419694?hl=zh-Hans#multiple