BigQuery
Record类型的数据查询-Array&Struct
背景:工作中遇到将请求响应耗时的firebase埋点数据同步至bigquery存储后,需要得到相应的API的平均响应耗时,做个记录
- 存储数据示例
{ "event_date": "20220405", "event_timestamp": "1649124444204884", "event_name": "api_request_time", "event_params": [{ "key": "page_location", "value": { "string_value": "xxxxx", "int_value": null, "float_value": null, "double_value": null } }, { "key": "method", "value": { "string_value": "POST", "int_value": null, "float_value": null, "double_value": null } }, { "key": "engagement_time_msec", "value": { "string_value": null, "int_value": "4828", "float_value": null, "double_value": null } }, { "key": "page_title", "value": { "string_value": "xxxx", "int_value": null, "float_value": null, "double_value": null } }, { "key": "name", "value": { "string_value": "xxxx", "int_value": null, "float_value": null, "double_value": null } }, { "key": "ga_session_id", "value": { "string_value": null, "int_value": "1649124418", "float_value": null, "double_value": null } }, { "key": "duration", "value": { "string_value": null, "int_value": "6620", "float_value": null, "double_value": null } }, { "key": "ga_session_number", "value": { "string_value": null, "int_value": "3", "float_value": null, "double_value": null } }, { "key": "session_engaged", "value": { "string_value": "1", "int_value": null, "float_value": null, "double_value": null } }, { "key": "ignore_referrer", "value": { "string_value": "true", "int_value": null, "float_value": null, "double_value": null } }, { "key": "page_referrer", "value": { "string_value": "https://accounts.google.com/", "int_value": null, "float_value": null, "double_value": null } }], "event_previous_timestamp": null, "event_value_in_usd": null, "event_bundle_sequence_id": "-1263538348", "event_server_timestamp_offset": null, "user_id": null, "user_pseudo_id": "663212794.1649124415", "privacy_info": { "analytics_storage": null, "ads_storage": null, "uses_transient_token": "No" }, "user_properties": [], "user_first_touch_timestamp": null, "user_ltv": { "revenue": "0.0", "currency": "USD" }, "device": { "category": "desktop", "mobile_brand_name": "Google", "mobile_model_name": "Chrome", "mobile_marketing_name": null, "mobile_os_hardware_model": null, "operating_system": "Windows", "operating_system_version": "Windows 10", "vendor_id": null, "advertising_id": null, "language": "en-us", "is_limited_ad_tracking": "No", "time_zone_offset_seconds": null, "browser": null, "browser_version": null, "web_info": { "browser": "Chrome", "browser_version": "xxxxx", "hostname": "xxxx" } }, "geo": { "continent": "Asia", "country": "Malaysia", "region": "Selangor", "city": "Shah Alam", "sub_continent": "Southeast Asia", "metro": "(not set)" }, "app_info": null, "traffic_source": null, "stream_id": "3270187873", "platform": "WEB", "event_dimensions": null, "ecommerce": null, "items": [], "key": "engagement_time_msec", "value": { "string_value": null, "int_value": "4828", "float_value": null, "double_value": null }}
- 查询语句
select sum(value.int_value) as total_duration,count(*) as total_count, sum(value.int_value)/count(*) as average_duration, string_value from (select event_params,string_value from (SELECT event_date,event_params,value.string_value FROM `dataset.table` cross join unnest(event_params)
where key = 'name') cross join unnest(event_params) where value.string_value = 'POST' )cross join unnest(event_params) where key='duration' group by string_value
- 进一步查询
select sum(total_duration),sum(total_count),sum(total_duration)/sum(total_count) from (select sum(value.int_value) as total_duration,count(*) as total_count, sum(value.int_value)/count(*) as average_duration, string_value from (select event_params,string_value from (SELECT event_date,event_params,value.string_value FROM `xxxxx.events_intraday_20220405` cross join unnest(event_params)
where key = 'name') cross join unnest(event_params) where value.string_value = 'POST' )cross join unnest(event_params) where key='duration' group by string_value) where string_value like 'xxxx/%';