Google-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/%';