nginx qos计算sql

petrel919

select host,concat(substr(lt,1,18),'01') as st,parse_url(concat('http://',host,regexp_extract(request,'([^ ]*) ([^ ]*)',2)),'PATH') as url,　　
sum(cast( case when status like '2%' or status like '3%' then body_bytes_sent else '0' end as int)) as  2xx3xx_body_size,
　　
sum(cast( case when status like '4%' then body_bytes_sent else '0' end as int)) as  4xx_body_size,
　　
sum(cast( case when status like '5%' then body_bytes_sent else '0' end as int)) as  5xx_body_size,
　　
sum(cast(body_bytes_sent as int)) as all_body_size ,
　　
sum(cast( case when status like '2%' or status like '3%' then request_time else '0' end as int)) as  2xx3xx_response_time,
　　
sum(cast( case when status like '4%' then request_time else '0' end as int)) as  4xx_response_time,
　　
sum(cast( case when status like '5%' then request_time else '0' end as int)) as  5xx_response_time,
　　
sum(cast(request_time as int)) as all_response_time ,
　　
sum(cast( case when status like '2%' or status like '3%' then 1 else 0 end as int)) as  2xx3xx_count,
　　
sum(cast( case when status like '4%' then 1 else 0 end as int)) as  4xx_count,
　　
sum(cast( case when status like '5%' then 1 else 0 end as int)) as  5xx_count,
　　
sum(1) as all_count
　　
from (
　　
select regexp_extract(line,'([^ ]*) - ([^ ]*) \\\[(.*)\\\] "([^"]*)" ("[^"]*" )?(-|[0-9]*) (-|[0-9]*) "([^"]*)" "([^"]*)" (.*) ([^ ]*com) ([^ ]*)($| ([^ ]*))', 3) as lt,regexp_extract(line,'([^ ]*) - ([^ ]*) \\\[(.*)\\\] "([^"]*)" ("[^"]*" )?(-|[0-9]*) (-|[0-9]*) "([^"]*)" "([^"]*)" (.*) ([^ ]*com) ([^ ]*)($| ([^ ]*))', 4) as request,
　　
regexp_extract(line,'([^ ]*) - ([^ ]*) \\\[(.*)\\\] "([^"]*)" ("[^"]*" )?(-|[0-9]*) (-|[0-9]*) "([^"]*)" "([^"]*)" (.*) ([^ ]*com) ([^ ]*)($| ([^ ]*))', 11) as    host,regexp_extract(line,'([^ ]*) - ([^ ]*) \\\[(.*)\\\] "([^"]*)" ("[^"]*" )?(-|[0-9]*) (-|[0-9]*) "([^"]*)" "([^"]*)" (.*) ([^ ]*com) ([^ ]*)($| ([^ ]*))', 6)   as status,trim(regexp_replace(regexp_replace(regexp_replace(regexp_extract(line,'([^ ]*) - ([^ ]*) \\\[(.*)\\\] "([^"]*)" ("[^"]*" )?(-|[0-9]*) (-|[0-9]*) "([^"]*)" "([^"]*)" (.*) ([^ ]*com) ([^ ]*)($| ([^ ]*))', 5),'\\"',''),'\\.',''),'^0*','')) as request_time,regexp_extract(line,'([^ ]*) - ([^ ]*) \\\[(.*)\\\] "([^"]*)" ("[^"]*" )?(-|[0-9]*) (-|[0-9]*) "([^"]*)" "([^"]*)" (.*) ([^ ]*com) ([^ ]*)($| ([^ ]*))', 7) as body_bytes_sent
　　
from ods_nginx_log_5min_impala)tmp where host !='' and length(host)