nginx qos计算sql
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)) as2xx3xx_body_size,
sum(cast( case when status like '4%' then body_bytes_sent else '0' end as int)) as4xx_body_size,
sum(cast( case when status like '5%' then body_bytes_sent else '0' end as int)) as5xx_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)) as2xx3xx_response_time,
sum(cast( case when status like '4%' then request_time else '0' end as int)) as4xx_response_time,
sum(cast( case when status like '5%' then request_time else '0' end as int)) as5xx_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)) as2xx3xx_count,
sum(cast( case when status like '4%' then 1 else 0 end as int)) as4xx_count,
sum(cast( case when status like '5%' then 1 else 0 end as int)) as5xx_count,
sum(1) as all_count
from (
select regexp_extract(line,'([^ ]*) - ([^ ]*) \\\[(.*)\\\] "([^"]*)" ("[^"]*" )?(-|*) (-|*) "([^"]*)" "([^"]*)" (.*) ([^ ]*com) ([^ ]*)($| ([^ ]*))', 3) as lt,regexp_extract(line,'([^ ]*) - ([^ ]*) \\\[(.*)\\\] "([^"]*)" ("[^"]*" )?(-|*) (-|*) "([^"]*)" "([^"]*)" (.*) ([^ ]*com) ([^ ]*)($| ([^ ]*))', 4) as request,
regexp_extract(line,'([^ ]*) - ([^ ]*) \\\[(.*)\\\] "([^"]*)" ("[^"]*" )?(-|*) (-|*) "([^"]*)" "([^"]*)" (.*) ([^ ]*com) ([^ ]*)($| ([^ ]*))', 11) as host,regexp_extract(line,'([^ ]*) - ([^ ]*) \\\[(.*)\\\] "([^"]*)" ("[^"]*" )?(-|*) (-|*) "([^"]*)" "([^"]*)" (.*) ([^ ]*com) ([^ ]*)($| ([^ ]*))', 6) as status,trim(regexp_replace(regexp_replace(regexp_replace(regexp_extract(line,'([^ ]*) - ([^ ]*) \\\[(.*)\\\] "([^"]*)" ("[^"]*" )?(-|*) (-|*) "([^"]*)" "([^"]*)" (.*) ([^ ]*com) ([^ ]*)($| ([^ ]*))', 5),'\\"',''),'\\.',''),'^0*','')) as request_time,regexp_extract(line,'([^ ]*) - ([^ ]*) \\\[(.*)\\\] "([^"]*)" ("[^"]*" )?(-|*) (-|*) "([^"]*)" "([^"]*)" (.*) ([^ ]*com) ([^ ]*)($| ([^ ]*))', 7) as body_bytes_sent
from ods_nginx_log_5min_impala)tmp where host !='' and length(host)
页:
[1]