avg query
unknown
sql
2 years ago
2.4 kB
10
Indexable
select sub_region --, build_name --, case when build_name like "%scale%" then 1 else 0 end as hub_scale , os , AVG(duration) as duration , AVG(rails_start_time + firecmd_time + start_session_time) as session_start_time , AVG(inside_bs_network_time) as inside_bs_network_time , AVG(duration - inside_bs_network_time - (firecmd_time + start_session_time)) as user_to_hub_time , AVG(hubTime) as hubTime , AVG(inside_bs_network_time - totalJarTime - hubTime) as hub_to_terminal_time_new , AVG(totalJarTime) as inside_terminal_time , AVG(nginxToHub) as nginxToHub , AVG(numOfReqInJar) as numOfReqInJar , count(*) as total_sessions from ( select hashed_id -- , build_name , REGEXP_EXTRACT(build_name, r'disk(.*?)sel') as build_name , terminal_properties , terminal_region , bs_region , sub_region , selenium_version , feature_usage , duration*1000 as duration , start_time , stop_time , outside_bs_time , inside_bs_network_time , rails_start_time , firecmd_time , start_session_time , atsp.os as os ,created_day , cast(json_extract_scalar(feature_usage, '$.jarPerformance.data.totalJarTime') as int64) as totalJarTime , cast(json_extract_scalar(feature_usage, '$.hubProcessing.nginxToHubTime') as int64) as nginxToHub , cast(json_extract_scalar(feature_usage, '$.hubProcessing.hubTime') as int64) as hubTime , cast(json_extract_scalar(feature_usage, '$.jarPerformance.data.numOfReqInJar') as int64) as numOfReqInJar from `browserstack-production.automate.automate_test_sessions_partitioned` as atsp join `browserstack-production.common.terminals` as t on atsp.terminal = t.name where (DATE(_PARTITIONTIME) = "2023-02-24" or _PARTITIONTIME is null) and duration is not null and build_name like "automate latency Analysis- Core Hub Scale" -- and build_name like "%single" and status = "done" -- and json_extract_scalar(feature_usage, '$.jarPerformance.data.totalJarTime') is not null -- and json_extract_scalar(feature_usage, '$.jarPerformance.data.numOfReqInJar') is not null -- and json_extract_scalar(feature_usage, '$.hubProcessing.nginxToHubTime') is not null --limit 1000 ) -- where numOfReqInJar > 0 where SUBSTR(terminal_region,0,7) = SUBSTR(bs_region,0,7) group by 1,2 order by 2,1 -- order by sub_region, terminal_type,hub_scale, terminal_ip
Editor is loading...