delimiter $$ drop procedure if exists p_it_web_sum_ultra; $$ create procedure p_it_web_sum_ultra ( p_where varchar(8000), p_yyyymmdd varchar(10), p_localDnsIp varchar(20), p_remoteDnsIp varchar(20), p_provincecode varchar(800), p_teststarttime varchar(50), p_testendtime varchar(50) ) begin declare v_yyyymm varchar(20); set v_yyyymm=concat(substr(p_teststarttime,1,4),substr(p_teststarttime,6,2)); drop table if exists internetsumrecord_hour_tmp; create table if not exists internetsumrecord_hour_tmp ( sumstarttime datetime default null, servicetype varchar(3) default null, subservicetype varchar(2) default null, localoperatorcode varchar(20) default null, localnet tinyint(4) default null, provincecode int(11) default null, citycode int(11) default null, testtotalnum int(11) default null, successtotalnum int(11) default null, hour tinyint(4) default null, dnsserver varchar(50) default null, url varchar(200) default null, urlflag int, domainnamegroup varchar(100) default null, loginusername varchar(50) default null, othernet tinyint(4) default null, localmodulecode tinyint(4) default null, expservicetype varchar(3) default null, ispprovincecode int(11) default null, resourcetype tinyint(4) default null, emptybusy varchar(10), scenecode varchar(20), timeouttimes int, firstscreenoffsettime float, url90poffsettime float, dealtotaloffsettime float, dnsqueryoffsettime float, dealspeed float, pingoffsettime float, firstbyteoffsettime float, setupconnectoffsettime float, lostrate float, cmcacherate float, dealtotalspeed float, pageresourcenum float, wanzhengrate float, yinrurate float, selfprovinceinelerate float, filesize float, key internetsumrecord_hour_tmp_provincecode (provincecode,localoperatorcode,localnet,url,urlflag,emptybusy,scenecode,domainnamegroup) ); set @SQL=concat_ws('',' insert into internetsumrecord_hour_tmp select sumstarttime, servicetype, subservicetype, localoperatorcode, localnet, provincecode, citycode, sum(testtotalnum), sum(successtotalnum), hour, dnsserver, url, urlflag, domainnamegroup, loginusername, othernet, localmodulecode, expservicetype, ispprovincecode, resourcetype, emptybusy, scenecode, sum(timeouttimes_total)*1.0/sum(timeouttimes_count), sum(firstscreenoffsettime_total)*1.0/sum(firstscreenoffsettime_count), sum(url90poffsettime_total)*1.0/sum(url90poffsettime_count), sum(dealtotaloffsettime_total)*1.0/sum(dealtotaloffsettime_count), sum(dnsqueryoffsettime_total)*1.0/sum(dnsqueryoffsettime_count), sum(dealspeed_total)*1.0/sum(dealspeed_count), sum(pingoffsettime_total)*1.0/sum(pingoffsettime_count), sum(firstbyteoffsettime_total)*1.0/sum(firstbyteoffsettime_count), sum(setupconnectoffsettime_total)*1.0/sum(setupconnectoffsettime_count), sum(lostrate_total)*1.0/sum(lostrate_count), sum(cmcacherate_total)*1.0/sum(cmcacherate_count), sum(dealtotalspeed_total)*1.0/sum(dealtotalspeed_count), sum(pageresourcenum_total)*1.0/sum(pageresourcenum_count), sum(wanzhengrate_total)*1.0/sum(wanzhengrate_count), sum(yinrurate_total)*1.0/sum(yinrurate_count), sum(selfprovinceinelerate_total)*1.0/sum(selfprovinceinelerate_count), avg(filesize) from ( select rec.id, rec.sumstarttime, SUBSTRING(rec.servicetype, 1, 3) as servicetype, SUBSTRING(rec.servicetype, 4, 2) as subservicetype, ifnull(rec.localoperatorcode,'''') as localoperatorcode, rec.localnet, rec.provincecode, rec.citycode, rec.testtotalnum, rec.successtotalnum, rec.hour, rec.dnsserver, rec.url, ifnull(rec.urlflag,-1) as urlflag, rec.domainnamegroup, rec.loginusername, rec.othernet, rec.localmodulecode, rec.intime, SUBSTRING(rec.servicetype, 6, 3) as expservicetype, rec.ispprovincecode, rec.resourcetype, case when rec.hour in (20,21) then 1 when rec.hour in (3,4) then 2 else 0 end as emptybusy, rec.scenecode, sum(normtotal_213) as timeouttimes_total, sum(normcount_213) as timeouttimes_count, sum(normtotal_83) as firstscreenoffsettime_total, sum(normcount_83) as firstscreenoffsettime_count, sum(normtotal_171) as url90poffsettime_total, sum(normcount_171) as url90poffsettime_count, sum(normtotal_112) as dealtotaloffsettime_total, sum(normcount_112) as dealtotaloffsettime_count, sum(normtotal_84) as dnsqueryoffsettime_total, sum(normcount_84) as dnsqueryoffsettime_count, sum(normtotal_23) as dealspeed_total, sum(normcount_23) as dealspeed_count, sum(normtotal_6) as pingoffsettime_total, sum(normcount_6) as pingoffsettime_count, sum(normtotal_134) as firstbyteoffsettime_total, sum(normcount_134) as firstbyteoffsettime_count, sum(normtotal_88) as setupconnectoffsettime_total, sum(normcount_88) as setupconnectoffsettime_count, sum(normtotal_32) as lostrate_total, sum(normcount_32) as lostrate_count , sum(normtotal_260) as cmcacherate_total, sum(normcount_260) as cmcacherate_count, sum(normtotal_287) as dealtotalspeed_total, sum(normcount_287) as dealtotalspeed_count, sum(normtotal_288) as pageresourcenum_total, sum(normcount_288) as pageresourcenum_count, sum(normtotal_192) as wanzhengrate_total , sum(normcount_192) as wanzhengrate_count, sum(normtotal_203) as yinrurate_total, sum(normcount_203) as yinrurate_count, sum(normtotal_266) as selfprovinceinelerate_total, sum(normcount_266) as selfprovinceinelerate_count , sum((normtotal_112/1000.0/normcount_112)*(normtotal_287*1.0/normcount_287)) as filesize from InternetSum_hour40000',v_yyyymm,' rec where servicetype=''40000000'' and sumstarttime>=''',p_teststarttime,''' and sumstarttime<''',p_testendtime,''' and (''',p_provincecode,'''='''' or charindex(rec.provincecode,''',p_provincecode,''')>0) and rec.provincecode!=-1 and rec.provincecode!=0 and ',p_where,' group by rec.id ) a group by sumstarttime,servicetype,subservicetype,localoperatorcode,localnet,provincecode,citycode,hour,dnsserver,url,urlflag, domainnamegroup,loginusername,othernet,localmodulecode,expservicetype,ispprovincecode,resourcetype,emptybusy,scenecode' ); prepare stmt from @SQL; execute stmt; drop table if exists standvalue_common; create table if not exists standvalue_common ( provincecode int(11), localoperatorcode varchar(20), localnet tinyint(4), url varchar(200), urlflag int, emptybusy varchar(10), scenecode varchar(20), domainnamegroup varchar(100), firstscreenoffsettime_min float, firstscreenoffsettime_max float, url90poffsettime_min float, url90poffsettime_max float, dealtotaloffsettime_min float, dealtotaloffsettime_max float, dnsqueryoffsettime_min float, dnsqueryoffsettime_max float, dealspeed_min float, dealspeed_max float, pingoffsettime_min float, pingoffsettime_max float, firstbyteoffsettime_min float, firstbyteoffsettime_max float, setupconnectoffsettime_min float, setupconnectoffsettime_max float, lostrate_min float, lostrate_max float, CmCacheRate_min float, CmCacheRate_max float, DealTotalSpeed_min float, DealTotalSpeed_max float, PageResourceNum_min float, PageResourceNum_max float, wanzhengrate_min float, wanzhengrate_max float, yinrurate_min float, yinrurate_max float, selfprovinceinelerate_min float, selfprovinceinelerate_max float, key standvalue_common_provincecode (provincecode,localoperatorcode,localnet,url,urlflag,emptybusy,scenecode,domainnamegroup) ); insert into standvalue_common select a.provincecode, a.localoperatorcode, a.localnet, a.url, a.urlflag, a.emptybusy, a.scenecode, a.domainnamegroup, case when firstscreenoffsettime_avg is null then null else firstscreenoffsettime_avg-1.5*firstscreenoffsettime_cha end as firstscreenoffsettime_min, case when firstscreenoffsettime_avg is null then null else firstscreenoffsettime_avg+1.5*firstscreenoffsettime_cha end as firstscreenoffsettime_max, case when url90poffsettime_avg is null then null else url90poffsettime_avg-1.5*url90poffsettime_cha end as url90poffsettime_min, case when url90poffsettime_avg is null then null else url90poffsettime_avg+1.5*url90poffsettime_cha end as url90poffsettime_max, case when dealtotaloffsettime_avg is null then null else dealtotaloffsettime_avg-1.5*dealtotaloffsettime_cha end as dealtotaloffsettime_min, case when dealtotaloffsettime_avg is null then null else dealtotaloffsettime_avg+1.5*dealtotaloffsettime_cha end as dealtotaloffsettime_max, case when dnsqueryoffsettime_avg is null then null else dnsqueryoffsettime_avg-1.5*dnsqueryoffsettime_cha end as dnsqueryoffsettime_min, case when dnsqueryoffsettime_avg is null then null else dnsqueryoffsettime_avg+1.5*dnsqueryoffsettime_cha end as dnsqueryoffsettime_max, case when dealspeed_avg is null then null else dealspeed_avg -1.5*dealspeed_cha end as dealspeed_min, case when dealspeed_avg is null then null else dealspeed_avg +1.5*dealspeed_cha end as dealspeed_max, case when pingoffsettime_avg is null then null else pingoffsettime_avg-1.5*pingoffsettime_cha end as pingoffsettime_min, case when pingoffsettime_avg is null then null else pingoffsettime_avg+1.5*pingoffsettime_cha end as pingoffsettime_max, case when firstbyteoffsettime_avg is null then null else firstbyteoffsettime_avg-1.5*firstbyteoffsettime_cha end as firstbyteoffsettime_min, case when firstbyteoffsettime_avg is null then null else firstbyteoffsettime_avg+1.5*firstbyteoffsettime_cha end as firstbyteoffsettime_max, case when setupconnectoffsettime_avg is null then null else setupconnectoffsettime_avg-1.5*setupconnectoffsettime_cha end as setupconnectoffsettime_min, case when setupconnectoffsettime_avg is null then null else setupconnectoffsettime_avg+1.5*setupconnectoffsettime_cha end as setupconnectoffsettime_max, case when lostrate_avg is null then null else lostrate_avg-1.5*lostrate_cha end as lostrate_min, case when lostrate_avg is null then null else lostrate_avg+1.5*lostrate_cha end as lostrate_max, case when CmCacheRate_avg is null then null else CmCacheRate_avg-1.5*CmCacheRate_cha end as CmCacheRate_min, case when CmCacheRate_avg is null then null else CmCacheRate_avg+1.5*CmCacheRate_cha end as CmCacheRate_max, case when DealTotalSpeed_avg is null then null else DealTotalSpeed_avg-1.5*DealTotalSpeed_cha end as DealTotalSpeed_min, case when DealTotalSpeed_avg is null then null else DealTotalSpeed_avg+1.5*DealTotalSpeed_cha end as DealTotalSpeed_max, case when PageResourceNum_avg is null then null else PageResourceNum_avg-1.5*PageResourceNum_cha end as PageResourceNum_min, case when PageResourceNum_avg is null then null else PageResourceNum_avg+1.5*PageResourceNum_cha end as PageResourceNum_max, case when wanzhengrate_avg is null then null else wanzhengrate_avg-1.5*wanzhengrate_cha end as wanzhengrate_min, case when wanzhengrate_avg is null then null else wanzhengrate_avg+1.5*wanzhengrate_cha end as wanzhengrate_max, case when yinrurate_avg is null then null else yinrurate_avg-1.5*yinrurate_cha end as yinrurate_min, case when yinrurate_avg is null then null else yinrurate_avg+1.5*yinrurate_cha end as yinrurate_max, case when selfprovinceinelerate_avg is null then null else selfprovinceinelerate_avg-1.5*selfprovinceinelerate_cha end as selfprovinceinelerate_min, case when selfprovinceinelerate_avg is null then null else selfprovinceinelerate_avg+1.5*selfprovinceinelerate_cha end as selfprovinceinelerate_max from ( select rec.provincecode, rec.localoperatorcode, rec.localnet, rec.url, rec.urlflag, rec.emptybusy, rec.scenecode, rec.domainnamegroup, avg(firstscreenoffsettime) as firstscreenoffsettime_avg, stddev(firstscreenoffsettime) as firstscreenoffsettime_cha, avg(url90poffsettime) as url90poffsettime_avg, stddev(url90poffsettime) as url90poffsettime_cha, avg(dealtotaloffsettime) as dealtotaloffsettime_avg, stddev(dealtotaloffsettime) as dealtotaloffsettime_cha, avg(dnsqueryoffsettime) as dnsqueryoffsettime_avg, stddev(dnsqueryoffsettime) as dnsqueryoffsettime_cha, avg(dealspeed) as dealspeed_avg, stddev(dealspeed) as dealspeed_cha, avg(pingoffsettime) as pingoffsettime_avg, stddev(pingoffsettime) as pingoffsettime_cha, avg(firstbyteoffsettime) as firstbyteoffsettime_avg, stddev(firstbyteoffsettime) as firstbyteoffsettime_cha, avg(setupconnectoffsettime) as setupconnectoffsettime_avg, stddev(setupconnectoffsettime) as setupconnectoffsettime_cha, avg(lostrate) as lostrate_avg, stddev(lostrate) as lostrate_cha, avg(cmcacherate) as CmCacheRate_avg, stddev(cmcacherate) as CmCacheRate_cha, avg(dealtotalspeed) as DealTotalSpeed_avg, stddev(dealtotalspeed) as DealTotalSpeed_cha, avg(pageresourcenum) as PageResourceNum_avg, stddev(pageresourcenum) as PageResourceNum_cha, avg(wanzhengrate) as wanzhengrate_avg, stddev(wanzhengrate) as wanzhengrate_cha, avg(yinrurate) as yinrurate_avg, stddev(yinrurate) as yinrurate_cha, avg(selfprovinceinelerate) as selfprovinceinelerate_avg, stddev(selfprovinceinelerate) as selfprovinceinelerate_cha from internetsumrecord_hour_tmp rec group by rec.provincecode,rec.localoperatorcode,rec.localnet,rec.url,rec.urlflag,emptybusy,rec.scenecode,rec.domainnamegroup ) a; update standvalue_common set firstscreenoffsettime_min=0 where firstscreenoffsettime_min<0; update standvalue_common set url90poffsettime_min=0 where url90poffsettime_min<0; update standvalue_common set dealtotaloffsettime_min=0 where dealtotaloffsettime_min<0; update standvalue_common set dnsqueryoffsettime_min=0 where dnsqueryoffsettime_min<0; update standvalue_common set dealspeed_min=0 where dealspeed_min<0; update standvalue_common set pingoffsettime_min=0 where pingoffsettime_min<0; update standvalue_common set firstbyteoffsettime_min=0 where firstbyteoffsettime_min<0; update standvalue_common set setupconnectoffsettime_min=0 where setupconnectoffsettime_min<0; update standvalue_common set lostrate_min=0 where lostrate_min<0; update standvalue_common set CmCacheRate_min=0 where CmCacheRate_min<0; update standvalue_common set DealTotalSpeed_min=0 where DealTotalSpeed_min<0; update standvalue_common set PageResourceNum_min=0 where PageResourceNum_min<0; update standvalue_common set wanzhengrate_min=0 where wanzhengrate_min<0; update standvalue_common set yinrurate_min=0 where yinrurate_min<0; update standvalue_common set selfprovinceinelerate_min=0 where selfprovinceinelerate_min<0; drop table if exists result_common_auto_tmp; create table if not exists result_common_auto_tmp ( yyyymmdd varchar(10), provincecode varchar(20), provincename varchar(20), localnet varchar(20), localnetname varchar(20), url varchar(200), emptybusy varchar(20), scenecode varchar(50), domainnamegroup varchar(200), testtimes int, successtimes int, timeouttimes int, cachetimes int, isintroduce int, firstscreenoffsettime_min float, firstscreenoffsettime_max float, firstscreenoffsettime_stdev_min float, firstscreenoffsettime_stdev_max float, firstscreenoffsettime_in float, firstscreenoffsettime_out float, firstscreenoffsettime_cache float, firstscreenoffsettime_all float, firstscreenoffsettime_count int, url90poffsettime_min float, url90poffsettime_max float, url90poffsettime_stdev_min float, url90poffsettime_stdev_max float, url90poffsettime_in float, url90poffsettime_out float, url90poffsettime_cache float, url90poffsettime_all float, url90poffsettime_count int, dealtotaloffsettime_min float, dealtotaloffsettime_max float, dealtotaloffsettime_stdev_min float, dealtotaloffsettime_stdev_max float, dealtotaloffsettime_in float, dealtotaloffsettime_out float, dealtotaloffsettime_cache float, dealtotaloffsettime_all float, dealtotaloffsettime_count int, dnsqueryoffsettime_min float, dnsqueryoffsettime_max float, dnsqueryoffsettime_stdev_min float, dnsqueryoffsettime_stdev_max float, dnsqueryoffsettime_all float, dnsqueryoffsettime_count int, dealspeed_min float, dealspeed_max float, dealspeed_stdev_min float, dealspeed_stdev_max float, dealspeed_in float, dealspeed_out float, dealspeed_cache float, dealspeed_all float, dealspeed_count int, pingoffsettime_min float, pingoffsettime_max float, pingoffsettime_stdev_min float, pingoffsettime_stdev_max float, pingoffsettime_in float, pingoffsettime_out float, pingoffsettime_cache float, pingoffsettime_all float, pingoffsettime_count int, firstbyteoffsettime_min float, firstbyteoffsettime_max float, firstbyteoffsettime_stdev_min float, firstbyteoffsettime_stdev_max float, firstbyteoffsettime_in float, firstbyteoffsettime_out float, firstbyteoffsettime_cache float, firstbyteoffsettime_all float, firstbyteoffsettime_count int, setupconnectoffsettime_min float, setupconnectoffsettime_max float, setupconnectoffsettime_stdev_min float, setupconnectoffsettime_stdev_max float, setupconnectoffsettime_in float, setupconnectoffsettime_out float, setupconnectoffsettime_cache float, setupconnectoffsettime_all float, setupconnectoffsettime_count int, lostrate_min float, lostrate_max float, lostrate_stdev_min float, lostrate_stdev_max float, lostrate_in float, lostrate_out float, lostrate_cache float, lostrate_all float, lostrate_count int, #缓存率 CmCacheRate_min float, CmCacheRate_max float, CmCacheRate_stdev_min float, CmCacheRate_stdev_max float, CmCacheRate_in float, CmCacheRate_out float, CmCacheRate_cache float, CmCacheRate_all float, CmCacheRate_count int, DealTotalSpeed_min float, DealTotalSpeed_max float, DealTotalSpeed_stdev_min float, DealTotalSpeed_stdev_max float, DealTotalSpeed_in float, DealTotalSpeed_out float, DealTotalSpeed_cache float, DealTotalSpeed_all float, DealTotalSpeed_count int, PageResourceNum_min float, PageResourceNum_max float, PageResourceNum_stdev_min float, PageResourceNum_stdev_max float, PageResourceNum_in float, PageResourceNum_out float, PageResourceNum_cache float, PageResourceNum_all float, PageResourceNum_count int, wanzhengrate_stdev_min float, wanzhengrate_stdev_max float, wanzhengrate_all float, wanzhengrate_count int, yinrurate_stdev_min float, yinrurate_stdev_max float, yinrurate_all float, yinrurate_count int, urlflag int, localoperatorcode varchar(20), loginusername varchar(100), selfprovinceinelerate_stdev_min float, selfprovinceinelerate_stdev_max float, selfprovinceinelerate_all float, selfprovinceinelerate_count int, filesize float ); insert into result_common_auto_tmp select p_yyyymmdd, rec.provincecode, province.namecn as provincename, rec.localnet, dic_net.codenamecn as localnetname, rec.url, rec.emptybusy, rec.scenecode, rec.domainnamegroup, sum(testtotalnum) as testtimes, sum(successtotalnum) as successtimes, sum(timeouttimes) as timeouttimes, null as cachetimes, sum(case when resourcetype in (1,2,3) then successtotalnum else 0 end) as isintroduce, avg(st.firstscreenoffsettime_min), avg(st.firstscreenoffsettime_max), min(case when rec.firstscreenoffsettime>=st.firstscreenoffsettime_min and rec.firstscreenoffsettime<=st.firstscreenoffsettime_max then rec.firstscreenoffsettime else null end) as firstscreenoffsettime_stdev_min, max(case when rec.firstscreenoffsettime>=st.firstscreenoffsettime_min and rec.firstscreenoffsettime<=st.firstscreenoffsettime_max then rec.firstscreenoffsettime else null end) as firstscreenoffsettime_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.firstscreenoffsettime>=st.firstscreenoffsettime_min and rec.firstscreenoffsettime<=st.firstscreenoffsettime_max then rec.firstscreenoffsettime else null end) as firstscreenoffsettime_in, avg(case when rec.resourcetype in (4,5,6) and rec.firstscreenoffsettime>=st.firstscreenoffsettime_min and rec.firstscreenoffsettime<=st.firstscreenoffsettime_max then rec.firstscreenoffsettime else null end) as firstscreenoffsettime_out, null as firstscreenoffsettime_cache, avg(case when rec.firstscreenoffsettime>=st.firstscreenoffsettime_min and rec.firstscreenoffsettime<=st.firstscreenoffsettime_max then rec.firstscreenoffsettime else null end) as firstscreenoffsettime_all, sum(case when rec.firstscreenoffsettime>=st.firstscreenoffsettime_min and rec.firstscreenoffsettime<=st.firstscreenoffsettime_max then 1 else 0 end) as firstscreenoffsettime_count, avg(st.url90poffsettime_min), avg(st.url90poffsettime_max), min(case when rec.url90poffsettime>=st.url90poffsettime_min and rec.url90poffsettime<=st.url90poffsettime_max then rec.url90poffsettime else null end) as url90poffsettime_stdev_min, max(case when rec.url90poffsettime>=st.url90poffsettime_min and rec.url90poffsettime<=st.url90poffsettime_max then rec.url90poffsettime else null end) as url90poffsettime_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.url90poffsettime>=st.url90poffsettime_min and rec.url90poffsettime<=st.url90poffsettime_max then rec.url90poffsettime else null end) as url90poffsettime_in, avg(case when rec.resourcetype in (4,5,6) and rec.url90poffsettime>=st.url90poffsettime_min and rec.url90poffsettime<=st.url90poffsettime_max then rec.url90poffsettime else null end) as url90poffsettime_out, null as url90poffsettime_cache, avg(case when rec.url90poffsettime>=st.url90poffsettime_min and rec.url90poffsettime<=st.url90poffsettime_max then rec.url90poffsettime else null end) as url90poffsettime_all, sum(case when rec.url90poffsettime>=st.url90poffsettime_min and rec.url90poffsettime<=st.url90poffsettime_max then 1 else 0 end) as url90poffsettime_count, avg(st.dealtotaloffsettime_min), avg(st.dealtotaloffsettime_max), min(case when rec.dealtotaloffsettime>=st.dealtotaloffsettime_min and rec.dealtotaloffsettime<=st.dealtotaloffsettime_max then rec.dealtotaloffsettime else null end) as dealtotaloffsettime_stdev_min, max(case when rec.dealtotaloffsettime>=st.dealtotaloffsettime_min and rec.dealtotaloffsettime<=st.dealtotaloffsettime_max then rec.dealtotaloffsettime else null end) as dealtotaloffsettime_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.dealtotaloffsettime>=st.dealtotaloffsettime_min and rec.dealtotaloffsettime<=st.dealtotaloffsettime_max then rec.dealtotaloffsettime else null end) as dealtotaloffsettime_in, avg(case when rec.resourcetype in (4,5,6) and rec.dealtotaloffsettime>=st.dealtotaloffsettime_min and rec.dealtotaloffsettime<=st.dealtotaloffsettime_max then rec.dealtotaloffsettime else null end) as dealtotaloffsettime_out, null as dealtotaloffsettime_cache, avg(case when rec.dealtotaloffsettime>=st.dealtotaloffsettime_min and rec.dealtotaloffsettime<=st.dealtotaloffsettime_max then rec.dealtotaloffsettime else null end) as dealtotaloffsettime_all, sum(case when rec.dealtotaloffsettime>=st.dealtotaloffsettime_min and rec.dealtotaloffsettime<=st.dealtotaloffsettime_max then 1 else 0 end) as dealtotaloffsettime_count, avg(st.dnsqueryoffsettime_min), avg(st.dnsqueryoffsettime_max), min(case when rec.dnsqueryoffsettime>=st.dnsqueryoffsettime_min and rec.dnsqueryoffsettime<=st.dnsqueryoffsettime_max then rec.dnsqueryoffsettime else null end) as dnsqueryoffsettime_stdev_min, max(case when rec.dnsqueryoffsettime>=st.dnsqueryoffsettime_min and rec.dnsqueryoffsettime<=st.dnsqueryoffsettime_max then rec.dnsqueryoffsettime else null end) as dnsqueryoffsettime_stdev_max, avg(case when rec.dnsqueryoffsettime>=st.dnsqueryoffsettime_min and rec.dnsqueryoffsettime<=st.dnsqueryoffsettime_max then rec.dnsqueryoffsettime else null end) as dnsqueryoffsettime_all, sum(case when rec.dnsqueryoffsettime>=st.dnsqueryoffsettime_min and rec.dnsqueryoffsettime<=st.dnsqueryoffsettime_max then 1 else 0 end) as dnsqueryoffsettime_count, avg(st.dealspeed_min), avg(st.dealspeed_max), min(case when rec.dealspeed>=st.dealspeed_min and rec.dealspeed<=st.dealspeed_max then rec.dealspeed else null end) as dealspeed_stdev_min, max(case when rec.dealspeed>=st.dealspeed_min and rec.dealspeed<=st.dealspeed_max then rec.dealspeed else null end) as dealspeed_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.dealspeed>=st.dealspeed_min and rec.dealspeed<=st.dealspeed_max then rec.dealspeed else null end) as dealspeed_in, avg(case when rec.resourcetype in (4,5,6) and rec.dealspeed>=st.dealspeed_min and rec.dealspeed<=st.dealspeed_max then rec.dealspeed else null end) as dealspeed_out, null as dealspeed_cache, avg(case when rec.dealspeed>=st.dealspeed_min and rec.dealspeed<=st.dealspeed_max then rec.dealspeed else null end) as dealspeed_all, sum(case when rec.dealspeed>=st.dealspeed_min and rec.dealspeed<=st.dealspeed_max then 1 else 0 end) as dealspeed_count, avg(st.pingoffsettime_min), avg(st.pingoffsettime_max), min(case when rec.pingoffsettime>=st.pingoffsettime_min and rec.pingoffsettime<=st.pingoffsettime_max then rec.pingoffsettime else null end) as pingoffsettime_stdev_min, max(case when rec.pingoffsettime>=st.pingoffsettime_min and rec.pingoffsettime<=st.pingoffsettime_max then rec.pingoffsettime else null end) as pingoffsettime_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.pingoffsettime>=st.pingoffsettime_min and rec.pingoffsettime<=st.pingoffsettime_max then rec.pingoffsettime else null end) as pingoffsettime_in, avg(case when rec.resourcetype in (4,5,6) and rec.pingoffsettime>=st.pingoffsettime_min and rec.pingoffsettime<=st.pingoffsettime_max then rec.pingoffsettime else null end) as pingoffsettime_out, null as pingoffsettime_cache, avg(case when rec.pingoffsettime>=st.pingoffsettime_min and rec.pingoffsettime<=st.pingoffsettime_max then rec.pingoffsettime else null end) as pingoffsettime_all, sum(case when rec.pingoffsettime>=st.pingoffsettime_min and rec.pingoffsettime<=st.pingoffsettime_max then 1 else 0 end) as pingoffsettime_count, avg(st.firstbyteoffsettime_min), avg(st.firstbyteoffsettime_max), min(case when rec.firstbyteoffsettime>=st.firstbyteoffsettime_min and rec.firstbyteoffsettime<=st.firstbyteoffsettime_max then rec.firstbyteoffsettime else null end) as firstbyteoffsettime_stdev_min, max(case when rec.firstbyteoffsettime>=st.firstbyteoffsettime_min and rec.firstbyteoffsettime<=st.firstbyteoffsettime_max then rec.firstbyteoffsettime else null end) as firstbyteoffsettime_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.firstbyteoffsettime>=st.firstbyteoffsettime_min and rec.firstbyteoffsettime<=st.firstbyteoffsettime_max then rec.firstbyteoffsettime else null end) as firstbyteoffsettime_in, avg(case when rec.resourcetype in (4,5,6) and rec.firstbyteoffsettime>=st.firstbyteoffsettime_min and rec.firstbyteoffsettime<=st.firstbyteoffsettime_max then rec.firstbyteoffsettime else null end) as firstbyteoffsettime_out, null as firstbyteoffsettime_cache, avg(case when rec.firstbyteoffsettime>=st.firstbyteoffsettime_min and rec.firstbyteoffsettime<=st.firstbyteoffsettime_max then rec.firstbyteoffsettime else null end) as firstbyteoffsettime_all, sum(case when rec.firstbyteoffsettime>=st.firstbyteoffsettime_min and rec.firstbyteoffsettime<=st.firstbyteoffsettime_max then 1 else 0 end) as firstbyteoffsettime_count, avg(st.setupconnectoffsettime_min), avg(st.setupconnectoffsettime_max), min(case when rec.setupconnectoffsettime>=st.setupconnectoffsettime_min and rec.setupconnectoffsettime<=st.setupconnectoffsettime_max then rec.setupconnectoffsettime else null end) as setupconnectoffsettime_stdev_min, max(case when rec.setupconnectoffsettime>=st.setupconnectoffsettime_min and rec.setupconnectoffsettime<=st.setupconnectoffsettime_max then rec.setupconnectoffsettime else null end) as setupconnectoffsettime_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.setupconnectoffsettime>=st.setupconnectoffsettime_min and rec.setupconnectoffsettime<=st.setupconnectoffsettime_max then rec.setupconnectoffsettime else null end) as setupconnectoffsettime_in, avg(case when rec.resourcetype in (4,5,6) and rec.setupconnectoffsettime>=st.setupconnectoffsettime_min and rec.setupconnectoffsettime<=st.setupconnectoffsettime_max then rec.setupconnectoffsettime else null end) as setupconnectoffsettime_out, null as setupconnectoffsettime_cache, avg(case when rec.setupconnectoffsettime>=st.setupconnectoffsettime_min and rec.setupconnectoffsettime<=st.setupconnectoffsettime_max then rec.setupconnectoffsettime else null end) as setupconnectoffsettime_all, sum(case when rec.setupconnectoffsettime>=st.setupconnectoffsettime_min and rec.setupconnectoffsettime<=st.setupconnectoffsettime_max then 1 else 0 end) as setupconnectoffsettime_count, avg(st.lostrate_min), avg(st.lostrate_max), min(rec.lostrate) as lostrate_stdev_min, max(rec.lostrate) as lostrate_stdev_max, avg(case when rec.resourcetype in (1,2,3) then rec.lostrate else null end) as lostrate_in, avg(case when rec.resourcetype in (4,5,6) then rec.lostrate else null end) as lostrate_out, null as lostrate_cache, avg(rec.lostrate) as lostrate_all, sum(case when rec.lostrate>=0 then 1 else 0 end) as lostrate_count, avg(st.cmcacherate_min), avg(st.cmcacherate_max), min(rec.cmcacherate) as CmCacheRate_stdev_min, max(rec.cmcacherate) as CmCacheRate_stdev_max, avg(case when rec.resourcetype in (1,2,3) then rec.cmcacherate else null end) as CmCacheRate_in, avg(case when rec.resourcetype in (4,5,6) then rec.cmcacherate else null end) as CmCacheRate_out, null as CmCacheRate_cache, avg(rec.cmcacherate) as CmCacheRate_all, sum(case when rec.cmcacherate>=0 then 1 else 0 end) as CmCacheRate_count, avg(st.dealtotalspeed_min), avg(st.dealtotalspeed_max), min(case when rec.dealtotalspeed>=st.DealTotalSpeed_min and rec.dealtotalspeed<=st.DealTotalSpeed_max then rec.dealtotalspeed else null end) as DealTotalSpeed_stdev_min, max(case when rec.dealtotalspeed>=st.DealTotalSpeed_min and rec.dealtotalspeed<=st.DealTotalSpeed_max then rec.dealtotalspeed else null end) as DealTotalSpeed_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.dealtotalspeed>=st.DealTotalSpeed_min and rec.dealtotalspeed<=st.DealTotalSpeed_max then rec.dealtotalspeed else null end) as DealTotalSpeed_in, avg(case when rec.resourcetype in (4,5,6) and rec.dealtotalspeed>=st.DealTotalSpeed_min and rec.dealtotalspeed<=st.DealTotalSpeed_max then rec.dealtotalspeed else null end) as DealTotalSpeed_out, null as DealTotalSpeed_cache, avg(case when rec.dealtotalspeed>=st.DealTotalSpeed_min and rec.dealtotalspeed<=st.DealTotalSpeed_max then rec.dealtotalspeed else null end) as DealTotalSpeed_all, sum(case when rec.dealtotalspeed>=st.DealTotalSpeed_min and rec.dealtotalspeed<=st.DealTotalSpeed_max then 1 else 0 end) as DealTotalSpeed_count, avg(st.pageresourcenum_min), avg(st.PageResourceNum_max), min(case when rec.pageresourcenum>=st.PageResourceNum_min and rec.pageresourcenum<=st.PageResourceNum_max then rec.pageresourcenum else null end) as PageResourceNum_stdev_min, max(case when rec.pageresourcenum>=st.PageResourceNum_min and rec.pageresourcenum<=st.PageResourceNum_max then rec.pageresourcenum else null end) as PageResourceNum_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.pageresourcenum>=st.PageResourceNum_min and rec.pageresourcenum<=st.PageResourceNum_max then rec.pageresourcenum else null end) as PageResourceNum_in, avg(case when rec.resourcetype in (4,5,6) and rec.pageresourcenum>=st.PageResourceNum_min and rec.pageresourcenum<=st.PageResourceNum_max then rec.pageresourcenum else null end) as PageResourceNum_out, null as PageResourceNum_cache, avg(case when rec.pageresourcenum>=st.PageResourceNum_min and rec.pageresourcenum<=st.PageResourceNum_max then rec.pageresourcenum else null end) as PageResourceNum_all, sum(case when rec.pageresourcenum>=st.PageResourceNum_min and rec.pageresourcenum<=st.PageResourceNum_max then 1 else 0 end) as PageResourceNum_count, min(case when rec.wanzhengrate>=st.wanzhengrate_min and rec.wanzhengrate<=st.wanzhengrate_max then rec.wanzhengrate else null end) as wanzhengrate_stdev_min, max(case when rec.wanzhengrate>=st.wanzhengrate_min and rec.wanzhengrate<=st.wanzhengrate_max then rec.wanzhengrate else null end) as wanzhengrate_stdev_max, avg(case when rec.wanzhengrate>=st.wanzhengrate_min and rec.wanzhengrate<=st.wanzhengrate_max then rec.wanzhengrate else null end) as wanzhengrate_all, sum(case when rec.wanzhengrate>=st.wanzhengrate_min and rec.wanzhengrate<=st.wanzhengrate_max then 1 else 0 end) as wanzhengrate_count, min(rec.yinrurate) as yinrurate_stdev_min, max(rec.yinrurate) as yinrurate_stdev_max, avg(rec.yinrurate) as yinrurate_all, sum(case when rec.yinrurate>=0 then 1 else 0 end) as yinrurate_count, rec.urlflag, rec.localoperatorcode, rec.loginusername, min(rec.selfprovinceinelerate) as selfprovinceinelerate_stdev_min, max(rec.selfprovinceinelerate) as selfprovinceinelerate_stdev_max, avg(rec.selfprovinceinelerate) as selfprovinceinelerate_all, sum(case when rec.selfprovinceinelerate>=0 then 1 else 0 end) as selfprovinceinelerate_count, avg(rec.filesize) from internetsumrecord_hour_tmp rec left join standvalue_common st on rec.provincecode=st.provincecode and rec.localoperatorcode=st.localoperatorcode and rec.localnet=st.localnet and rec.url=st.url and rec.urlflag=st.urlflag and rec.emptybusy=st.emptybusy and rec.scenecode=st.scenecode and rec.domainnamegroup=st.domainnamegroup left join province on rec.provincecode=province.provincecode left join systemcode as dic_net on rec.localnet=dic_net.systemcode and dic_net.typecode=11 group by rec.provincecode,province.namecn,rec.localoperatorcode,rec.localnet,dic_net.codenamecn,rec.url,rec.urlflag,rec.emptybusy,rec.scenecode,rec.domainnamegroup; delete from it_web_sum_jt_common_emptybusy_auto where yyyymmdd=p_yyyymmdd and (p_provincecode='' or charindex(provincecode,p_provincecode)>0); insert into it_web_sum_jt_common_emptybusy_auto select * from result_common_auto_tmp; truncate table standvalue_common; insert into standvalue_common select a.provincecode, a.localoperatorcode, a.localnet, a.url, a.urlflag, a.emptybusy, a.scenecode, a.domainnamegroup, case when firstscreenoffsettime_avg is null then null else firstscreenoffsettime_avg-1.5*firstscreenoffsettime_cha end as firstscreenoffsettime_min, case when firstscreenoffsettime_avg is null then null else firstscreenoffsettime_avg+1.5*firstscreenoffsettime_cha end as firstscreenoffsettime_max, case when url90poffsettime_avg is null then null else url90poffsettime_avg-1.5*url90poffsettime_cha end as url90poffsettime_min, case when url90poffsettime_avg is null then null else url90poffsettime_avg+1.5*url90poffsettime_cha end as url90poffsettime_max, case when dealtotaloffsettime_avg is null then null else dealtotaloffsettime_avg-1.5*dealtotaloffsettime_cha end as dealtotaloffsettime_min, case when dealtotaloffsettime_avg is null then null else dealtotaloffsettime_avg+1.5*dealtotaloffsettime_cha end as dealtotaloffsettime_max, case when dnsqueryoffsettime_avg is null then null else dnsqueryoffsettime_avg-1.5*dnsqueryoffsettime_cha end as dnsqueryoffsettime_min, case when dnsqueryoffsettime_avg is null then null else dnsqueryoffsettime_avg+1.5*dnsqueryoffsettime_cha end as dnsqueryoffsettime_max, case when dealspeed_avg is null then null else dealspeed_avg -1.5*dealspeed_cha end as dealspeed_min, case when dealspeed_avg is null then null else dealspeed_avg +1.5*dealspeed_cha end as dealspeed_max, case when pingoffsettime_avg is null then null else pingoffsettime_avg-1.5*pingoffsettime_cha end as pingoffsettime_min, case when pingoffsettime_avg is null then null else pingoffsettime_avg+1.5*pingoffsettime_cha end as pingoffsettime_max, case when firstbyteoffsettime_avg is null then null else firstbyteoffsettime_avg-1.5*firstbyteoffsettime_cha end as firstbyteoffsettime_min, case when firstbyteoffsettime_avg is null then null else firstbyteoffsettime_avg+1.5*firstbyteoffsettime_cha end as firstbyteoffsettime_max, case when setupconnectoffsettime_avg is null then null else setupconnectoffsettime_avg-1.5*setupconnectoffsettime_cha end as setupconnectoffsettime_min, case when setupconnectoffsettime_avg is null then null else setupconnectoffsettime_avg+1.5*setupconnectoffsettime_cha end as setupconnectoffsettime_max, case when lostrate_avg is null then null else lostrate_avg-1.5*lostrate_cha end as lostrate_min, case when lostrate_avg is null then null else lostrate_avg+1.5*lostrate_cha end as lostrate_max, case when CmCacheRate_avg is null then null else CmCacheRate_avg-1.5*CmCacheRate_cha end as CmCacheRate_min, case when CmCacheRate_avg is null then null else CmCacheRate_avg+1.5*CmCacheRate_cha end as CmCacheRate_max, case when DealTotalSpeed_avg is null then null else DealTotalSpeed_avg-1.5*DealTotalSpeed_cha end as DealTotalSpeed_min, case when DealTotalSpeed_avg is null then null else DealTotalSpeed_avg+1.5*DealTotalSpeed_cha end as DealTotalSpeed_max, case when PageResourceNum_avg is null then null else PageResourceNum_avg-1.5*PageResourceNum_cha end as PageResourceNum_min, case when PageResourceNum_avg is null then null else PageResourceNum_avg+1.5*PageResourceNum_cha end as PageResourceNum_max, case when wanzhengrate_avg is null then null else wanzhengrate_avg-1.5*wanzhengrate_cha end as wanzhengrate_min, case when wanzhengrate_avg is null then null else wanzhengrate_avg+1.5*wanzhengrate_cha end as wanzhengrate_max, case when yinrurate_avg is null then null else yinrurate_avg-1.5*yinrurate_cha end as yinrurate_min, case when yinrurate_avg is null then null else yinrurate_avg+1.5*yinrurate_cha end as yinrurate_max, case when selfprovinceinelerate_avg is null then null else selfprovinceinelerate_avg-1.5*selfprovinceinelerate_cha end as selfprovinceinelerate_min, case when selfprovinceinelerate_avg is null then null else selfprovinceinelerate_avg+1.5*selfprovinceinelerate_cha end as selfprovinceinelerate_max from ( select rec.provincecode, rec.localoperatorcode, rec.localnet, rec.url, rec.urlflag, '全部' as emptybusy, rec.scenecode, rec.domainnamegroup, avg(firstscreenoffsettime) as firstscreenoffsettime_avg, stddev(firstscreenoffsettime) as firstscreenoffsettime_cha, avg(url90poffsettime) as url90poffsettime_avg, stddev(url90poffsettime) as url90poffsettime_cha, avg(dealtotaloffsettime) as dealtotaloffsettime_avg, stddev(dealtotaloffsettime) as dealtotaloffsettime_cha, avg(dnsqueryoffsettime) as dnsqueryoffsettime_avg, stddev(dnsqueryoffsettime) as dnsqueryoffsettime_cha, avg(dealspeed) as dealspeed_avg, stddev(dealspeed) as dealspeed_cha, avg(pingoffsettime) as pingoffsettime_avg, stddev(pingoffsettime) as pingoffsettime_cha, avg(firstbyteoffsettime) as firstbyteoffsettime_avg, stddev(firstbyteoffsettime) as firstbyteoffsettime_cha, avg(setupconnectoffsettime) as setupconnectoffsettime_avg, stddev(setupconnectoffsettime) as setupconnectoffsettime_cha, avg(lostrate) as lostrate_avg, stddev(lostrate) as lostrate_cha, avg(cmcacherate) as CmCacheRate_avg, stddev(cmcacherate) as CmCacheRate_cha, avg(dealtotalspeed) as DealTotalSpeed_avg, stddev(dealtotalspeed) as DealTotalSpeed_cha, avg(pageresourcenum) as PageResourceNum_avg, stddev(pageresourcenum) as PageResourceNum_cha, avg(wanzhengrate) as wanzhengrate_avg, stddev(wanzhengrate) as wanzhengrate_cha, avg(yinrurate) as yinrurate_avg, stddev(yinrurate) as yinrurate_cha, avg(selfprovinceinelerate) as selfprovinceinelerate_avg, stddev(selfprovinceinelerate) as selfprovinceinelerate_cha from internetsumrecord_hour_tmp rec group by rec.provincecode,rec.localoperatorcode,rec.localnet,rec.url,rec.urlflag,rec.scenecode,rec.domainnamegroup ) a; update standvalue_common set firstscreenoffsettime_min=0 where firstscreenoffsettime_min<0; update standvalue_common set url90poffsettime_min=0 where url90poffsettime_min<0; update standvalue_common set dealtotaloffsettime_min=0 where dealtotaloffsettime_min<0; update standvalue_common set dnsqueryoffsettime_min=0 where dnsqueryoffsettime_min<0; update standvalue_common set dealspeed_min=0 where dealspeed_min<0; update standvalue_common set pingoffsettime_min=0 where pingoffsettime_min<0; update standvalue_common set firstbyteoffsettime_min=0 where firstbyteoffsettime_min<0; update standvalue_common set setupconnectoffsettime_min=0 where setupconnectoffsettime_min<0; update standvalue_common set lostrate_min=0 where lostrate_min<0; update standvalue_common set CmCacheRate_min=0 where CmCacheRate_min<0; update standvalue_common set DealTotalSpeed_min=0 where DealTotalSpeed_min<0; update standvalue_common set PageResourceNum_min=0 where PageResourceNum_min<0; update standvalue_common set wanzhengrate_min=0 where wanzhengrate_min<0; update standvalue_common set yinrurate_min=0 where yinrurate_min<0; update standvalue_common set selfprovinceinelerate_min=0 where selfprovinceinelerate_min<0; truncate table result_common_auto_tmp; insert into result_common_auto_tmp select p_yyyymmdd, rec.provincecode, province.namecn as provincename, rec.localnet, dic_net.codenamecn as localnetname, rec.url, '全部', rec.scenecode, rec.domainnamegroup, sum(testtotalnum) as testtimes, sum(successtotalnum) as successtimes, sum(timeouttimes) as timeouttimes, null as cachetimes, sum(case when resourcetype in (1,2,3) then successtotalnum else 0 end) as isintroduce, avg(st.firstscreenoffsettime_min), avg(st.firstscreenoffsettime_max), min(case when rec.firstscreenoffsettime>=st.firstscreenoffsettime_min and rec.firstscreenoffsettime<=st.firstscreenoffsettime_max then rec.firstscreenoffsettime else null end) as firstscreenoffsettime_stdev_min, max(case when rec.firstscreenoffsettime>=st.firstscreenoffsettime_min and rec.firstscreenoffsettime<=st.firstscreenoffsettime_max then rec.firstscreenoffsettime else null end) as firstscreenoffsettime_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.firstscreenoffsettime>=st.firstscreenoffsettime_min and rec.firstscreenoffsettime<=st.firstscreenoffsettime_max then rec.firstscreenoffsettime else null end) as firstscreenoffsettime_in, avg(case when rec.resourcetype in (4,5,6) and rec.firstscreenoffsettime>=st.firstscreenoffsettime_min and rec.firstscreenoffsettime<=st.firstscreenoffsettime_max then rec.firstscreenoffsettime else null end) as firstscreenoffsettime_out, null as firstscreenoffsettime_cache, avg(case when rec.firstscreenoffsettime>=st.firstscreenoffsettime_min and rec.firstscreenoffsettime<=st.firstscreenoffsettime_max then rec.firstscreenoffsettime else null end) as firstscreenoffsettime_all, sum(case when rec.firstscreenoffsettime>=st.firstscreenoffsettime_min and rec.firstscreenoffsettime<=st.firstscreenoffsettime_max then 1 else 0 end) as firstscreenoffsettime_count, avg(st.url90poffsettime_min), avg(st.url90poffsettime_max), min(case when rec.url90poffsettime>=st.url90poffsettime_min and rec.url90poffsettime<=st.url90poffsettime_max then rec.url90poffsettime else null end) as url90poffsettime_stdev_min, max(case when rec.url90poffsettime>=st.url90poffsettime_min and rec.url90poffsettime<=st.url90poffsettime_max then rec.url90poffsettime else null end) as url90poffsettime_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.url90poffsettime>=st.url90poffsettime_min and rec.url90poffsettime<=st.url90poffsettime_max then rec.url90poffsettime else null end) as url90poffsettime_in, avg(case when rec.resourcetype in (4,5,6) and rec.url90poffsettime>=st.url90poffsettime_min and rec.url90poffsettime<=st.url90poffsettime_max then rec.url90poffsettime else null end) as url90poffsettime_out, null as url90poffsettime_cache, avg(case when rec.url90poffsettime>=st.url90poffsettime_min and rec.url90poffsettime<=st.url90poffsettime_max then rec.url90poffsettime else null end) as url90poffsettime_all, sum(case when rec.url90poffsettime>=st.url90poffsettime_min and rec.url90poffsettime<=st.url90poffsettime_max then 1 else 0 end) as url90poffsettime_count, avg(st.dealtotaloffsettime_min), avg(st.dealtotaloffsettime_max), min(case when rec.dealtotaloffsettime>=st.dealtotaloffsettime_min and rec.dealtotaloffsettime<=st.dealtotaloffsettime_max then rec.dealtotaloffsettime else null end) as dealtotaloffsettime_stdev_min, max(case when rec.dealtotaloffsettime>=st.dealtotaloffsettime_min and rec.dealtotaloffsettime<=st.dealtotaloffsettime_max then rec.dealtotaloffsettime else null end) as dealtotaloffsettime_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.dealtotaloffsettime>=st.dealtotaloffsettime_min and rec.dealtotaloffsettime<=st.dealtotaloffsettime_max then rec.dealtotaloffsettime else null end) as dealtotaloffsettime_in, avg(case when rec.resourcetype in (4,5,6) and rec.dealtotaloffsettime>=st.dealtotaloffsettime_min and rec.dealtotaloffsettime<=st.dealtotaloffsettime_max then rec.dealtotaloffsettime else null end) as dealtotaloffsettime_out, null as dealtotaloffsettime_cache, avg(case when rec.dealtotaloffsettime>=st.dealtotaloffsettime_min and rec.dealtotaloffsettime<=st.dealtotaloffsettime_max then rec.dealtotaloffsettime else null end) as dealtotaloffsettime_all, sum(case when rec.dealtotaloffsettime>=st.dealtotaloffsettime_min and rec.dealtotaloffsettime<=st.dealtotaloffsettime_max then 1 else 0 end) as dealtotaloffsettime_count, avg(st.dnsqueryoffsettime_min), avg(st.dnsqueryoffsettime_max), min(case when rec.dnsqueryoffsettime>=st.dnsqueryoffsettime_min and rec.dnsqueryoffsettime<=st.dnsqueryoffsettime_max then rec.dnsqueryoffsettime else null end) as dnsqueryoffsettime_stdev_min, max(case when rec.dnsqueryoffsettime>=st.dnsqueryoffsettime_min and rec.dnsqueryoffsettime<=st.dnsqueryoffsettime_max then rec.dnsqueryoffsettime else null end) as dnsqueryoffsettime_stdev_max, avg(case when rec.dnsqueryoffsettime>=st.dnsqueryoffsettime_min and rec.dnsqueryoffsettime<=st.dnsqueryoffsettime_max then rec.dnsqueryoffsettime else null end) as dnsqueryoffsettime_all, sum(case when rec.dnsqueryoffsettime>=st.dnsqueryoffsettime_min and rec.dnsqueryoffsettime<=st.dnsqueryoffsettime_max then 1 else 0 end) as dnsqueryoffsettime_count, avg(st.dealspeed_min), avg(st.dealspeed_max), min(case when rec.dealspeed>=st.dealspeed_min and rec.dealspeed<=st.dealspeed_max then rec.dealspeed else null end) as dealspeed_stdev_min, max(case when rec.dealspeed>=st.dealspeed_min and rec.dealspeed<=st.dealspeed_max then rec.dealspeed else null end) as dealspeed_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.dealspeed>=st.dealspeed_min and rec.dealspeed<=st.dealspeed_max then rec.dealspeed else null end) as dealspeed_in, avg(case when rec.resourcetype in (4,5,6) and rec.dealspeed>=st.dealspeed_min and rec.dealspeed<=st.dealspeed_max then rec.dealspeed else null end) as dealspeed_out, null as dealspeed_cache, avg(case when rec.dealspeed>=st.dealspeed_min and rec.dealspeed<=st.dealspeed_max then rec.dealspeed else null end) as dealspeed_all, sum(case when rec.dealspeed>=st.dealspeed_min and rec.dealspeed<=st.dealspeed_max then 1 else 0 end) as dealspeed_count, avg(st.pingoffsettime_min), avg(st.pingoffsettime_max), min(case when rec.pingoffsettime>=st.pingoffsettime_min and rec.pingoffsettime<=st.pingoffsettime_max then rec.pingoffsettime else null end) as pingoffsettime_stdev_min, max(case when rec.pingoffsettime>=st.pingoffsettime_min and rec.pingoffsettime<=st.pingoffsettime_max then rec.pingoffsettime else null end) as pingoffsettime_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.pingoffsettime>=st.pingoffsettime_min and rec.pingoffsettime<=st.pingoffsettime_max then rec.pingoffsettime else null end) as pingoffsettime_in, avg(case when rec.resourcetype in (4,5,6) and rec.pingoffsettime>=st.pingoffsettime_min and rec.pingoffsettime<=st.pingoffsettime_max then rec.pingoffsettime else null end) as pingoffsettime_out, null as pingoffsettime_cache, avg(case when rec.pingoffsettime>=st.pingoffsettime_min and rec.pingoffsettime<=st.pingoffsettime_max then rec.pingoffsettime else null end) as pingoffsettime_all, sum(case when rec.pingoffsettime>=st.pingoffsettime_min and rec.pingoffsettime<=st.pingoffsettime_max then 1 else 0 end) as pingoffsettime_count, avg(st.firstbyteoffsettime_min), avg(st.firstbyteoffsettime_max), min(case when rec.firstbyteoffsettime>=st.firstbyteoffsettime_min and rec.firstbyteoffsettime<=st.firstbyteoffsettime_max then rec.firstbyteoffsettime else null end) as firstbyteoffsettime_stdev_min, max(case when rec.firstbyteoffsettime>=st.firstbyteoffsettime_min and rec.firstbyteoffsettime<=st.firstbyteoffsettime_max then rec.firstbyteoffsettime else null end) as firstbyteoffsettime_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.firstbyteoffsettime>=st.firstbyteoffsettime_min and rec.firstbyteoffsettime<=st.firstbyteoffsettime_max then rec.firstbyteoffsettime else null end) as firstbyteoffsettime_in, avg(case when rec.resourcetype in (4,5,6) and rec.firstbyteoffsettime>=st.firstbyteoffsettime_min and rec.firstbyteoffsettime<=st.firstbyteoffsettime_max then rec.firstbyteoffsettime else null end) as firstbyteoffsettime_out, null as firstbyteoffsettime_cache, avg(case when rec.firstbyteoffsettime>=st.firstbyteoffsettime_min and rec.firstbyteoffsettime<=st.firstbyteoffsettime_max then rec.firstbyteoffsettime else null end) as firstbyteoffsettime_all, sum(case when rec.firstbyteoffsettime>=st.firstbyteoffsettime_min and rec.firstbyteoffsettime<=st.firstbyteoffsettime_max then 1 else 0 end) as firstbyteoffsettime_count, avg(st.setupconnectoffsettime_min), avg(st.setupconnectoffsettime_max), min(case when rec.setupconnectoffsettime>=st.setupconnectoffsettime_min and rec.setupconnectoffsettime<=st.setupconnectoffsettime_max then rec.setupconnectoffsettime else null end) as setupconnectoffsettime_stdev_min, max(case when rec.setupconnectoffsettime>=st.setupconnectoffsettime_min and rec.setupconnectoffsettime<=st.setupconnectoffsettime_max then rec.setupconnectoffsettime else null end) as setupconnectoffsettime_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.setupconnectoffsettime>=st.setupconnectoffsettime_min and rec.setupconnectoffsettime<=st.setupconnectoffsettime_max then rec.setupconnectoffsettime else null end) as setupconnectoffsettime_in, avg(case when rec.resourcetype in (4,5,6) and rec.setupconnectoffsettime>=st.setupconnectoffsettime_min and rec.setupconnectoffsettime<=st.setupconnectoffsettime_max then rec.setupconnectoffsettime else null end) as setupconnectoffsettime_out, null as setupconnectoffsettime_cache, avg(case when rec.setupconnectoffsettime>=st.setupconnectoffsettime_min and rec.setupconnectoffsettime<=st.setupconnectoffsettime_max then rec.setupconnectoffsettime else null end) as setupconnectoffsettime_all, sum(case when rec.setupconnectoffsettime>=st.setupconnectoffsettime_min and rec.setupconnectoffsettime<=st.setupconnectoffsettime_max then 1 else 0 end) as setupconnectoffsettime_count, avg(st.lostrate_min), avg(st.lostrate_max), min(rec.lostrate) as lostrate_stdev_min, max(rec.lostrate) as lostrate_stdev_max, avg(case when rec.resourcetype in (1,2,3) then rec.lostrate else null end) as lostrate_in, avg(case when rec.resourcetype in (4,5,6) then rec.lostrate else null end) as lostrate_out, null as lostrate_cache, avg(rec.lostrate) as lostrate_all, sum(case when rec.lostrate>=0 then 1 else 0 end) as lostrate_count, avg(st.cmcacherate_min), avg(st.cmcacherate_max), min(rec.cmcacherate) as CmCacheRate_stdev_min, max(rec.cmcacherate) as CmCacheRate_stdev_max, avg(case when rec.resourcetype in (1,2,3) then rec.cmcacherate else null end) as CmCacheRate_in, avg(case when rec.resourcetype in (4,5,6) then rec.cmcacherate else null end) as CmCacheRate_out, null as CmCacheRate_cache, avg(rec.cmcacherate) as CmCacheRate_all, sum(case when rec.cmcacherate>=0 then 1 else 0 end) as CmCacheRate_count, avg(st.dealtotalspeed_min), avg(st.dealtotalspeed_max), min(case when rec.dealtotalspeed>=st.DealTotalSpeed_min and rec.dealtotalspeed<=st.DealTotalSpeed_max then rec.dealtotalspeed else null end) as DealTotalSpeed_stdev_min, max(case when rec.dealtotalspeed>=st.DealTotalSpeed_min and rec.dealtotalspeed<=st.DealTotalSpeed_max then rec.dealtotalspeed else null end) as DealTotalSpeed_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.dealtotalspeed>=st.DealTotalSpeed_min and rec.dealtotalspeed<=st.DealTotalSpeed_max then rec.dealtotalspeed else null end) as DealTotalSpeed_in, avg(case when rec.resourcetype in (4,5,6) and rec.dealtotalspeed>=st.DealTotalSpeed_min and rec.dealtotalspeed<=st.DealTotalSpeed_max then rec.dealtotalspeed else null end) as DealTotalSpeed_out, null as DealTotalSpeed_cache, avg(case when rec.dealtotalspeed>=st.DealTotalSpeed_min and rec.dealtotalspeed<=st.DealTotalSpeed_max then rec.dealtotalspeed else null end) as DealTotalSpeed_all, sum(case when rec.dealtotalspeed>=st.DealTotalSpeed_min and rec.dealtotalspeed<=st.DealTotalSpeed_max then 1 else 0 end) as DealTotalSpeed_count, avg(st.pageresourcenum_min), avg(st.PageResourceNum_max), min(case when rec.pageresourcenum>=st.PageResourceNum_min and rec.pageresourcenum<=st.PageResourceNum_max then rec.pageresourcenum else null end) as PageResourceNum_stdev_min, max(case when rec.pageresourcenum>=st.PageResourceNum_min and rec.pageresourcenum<=st.PageResourceNum_max then rec.pageresourcenum else null end) as PageResourceNum_stdev_max, avg(case when rec.resourcetype in (1,2,3) and rec.pageresourcenum>=st.PageResourceNum_min and rec.pageresourcenum<=st.PageResourceNum_max then rec.pageresourcenum else null end) as PageResourceNum_in, avg(case when rec.resourcetype in (4,5,6) and rec.pageresourcenum>=st.PageResourceNum_min and rec.pageresourcenum<=st.PageResourceNum_max then rec.pageresourcenum else null end) as PageResourceNum_out, null as PageResourceNum_cache, avg(case when rec.pageresourcenum>=st.PageResourceNum_min and rec.pageresourcenum<=st.PageResourceNum_max then rec.pageresourcenum else null end) as PageResourceNum_all, sum(case when rec.pageresourcenum>=st.PageResourceNum_min and rec.pageresourcenum<=st.PageResourceNum_max then 1 else 0 end) as PageResourceNum_count, min(case when rec.wanzhengrate>=st.wanzhengrate_min and rec.wanzhengrate<=st.wanzhengrate_max then rec.wanzhengrate else null end) as wanzhengrate_stdev_min, max(case when rec.wanzhengrate>=st.wanzhengrate_min and rec.wanzhengrate<=st.wanzhengrate_max then rec.wanzhengrate else null end) as wanzhengrate_stdev_max, avg(case when rec.wanzhengrate>=st.wanzhengrate_min and rec.wanzhengrate<=st.wanzhengrate_max then rec.wanzhengrate else null end) as wanzhengrate_all, sum(case when rec.wanzhengrate>=st.wanzhengrate_min and rec.wanzhengrate<=st.wanzhengrate_max then 1 else 0 end) as wanzhengrate_count, min(rec.yinrurate) as yinrurate_stdev_min, max(rec.yinrurate) as yinrurate_stdev_max, avg(rec.yinrurate) as yinrurate_all, sum(case when rec.yinrurate>=0 then 1 else 0 end) as yinrurate_count, rec.urlflag, rec.localoperatorcode, rec.loginusername, min(rec.selfprovinceinelerate) as selfprovinceinelerate_stdev_min, max(rec.selfprovinceinelerate) as selfprovinceinelerate_stdev_max, avg(rec.selfprovinceinelerate) as selfprovinceinelerate_all, sum(case when rec.selfprovinceinelerate>=0 then 1 else 0 end) as selfprovinceinelerate_count, avg(rec.filesize) from internetsumrecord_hour_tmp rec left join standvalue_common st on rec.provincecode=st.provincecode and rec.localoperatorcode=st.localoperatorcode and rec.localnet=st.localnet and rec.url=st.url and rec.urlflag=st.urlflag and rec.scenecode=st.scenecode and rec.domainnamegroup=st.domainnamegroup left join province on rec.provincecode=province.provincecode left join systemcode as dic_net on rec.localnet=dic_net.systemcode and dic_net.typecode=11 group by rec.provincecode,province.namecn,rec.localoperatorcode,rec.localnet,dic_net.codenamecn,rec.url,rec.urlflag,rec.scenecode,rec.domainnamegroup; delete from it_web_sum_jt_common_auto where yyyymmdd=p_yyyymmdd and (p_provincecode='' or charindex(provincecode,p_provincecode)>0); insert into it_web_sum_jt_common_auto select * from result_common_auto_tmp; select 'success'; end$$ delimiter ; delimiter $$ drop procedure if exists p_it_sum_ultra_jt_auto $$ create procedure p_it_sum_ultra_jt_auto( p_where varchar(8000), p_yyyymmdd varchar(10), p_localDnsIp varchar(20), p_remoteDnsIp varchar(20), p_provincecode varchar(800), p_teststarttime varchar(50), p_testendtime varchar(50) ) begin declare v_yyyymm varchar(20); set v_yyyymm=concat(substr(p_teststarttime,1,4),substr(p_teststarttime,6,2)); drop table if exists internetsumrecord_hour_tmp; create table if not exists internetsumrecord_hour_tmp ( id char(36) not null, sumstarttime datetime default null, servicetype varchar(3) default null, subservicetype varchar(2) default null, localnet tinyint(4) default null, provincecode int(11) default null, citycode int(11) default null, testtotalnum int(11) default null, successtotalnum int(11) default null, hour tinyint(4) default null, dnsserver varchar(50) default null, url varchar(200) default null, ispcode int(11) default null, domainnamegroup varchar(100) default null, loginusername varchar(50) default null, othernet tinyint(4) default null, localmodulecode tinyint(4) default null, intime datetime default null, expservicetype varchar(3) default null, ispprovincecode int(11) default null, resourcetype tinyint(4) default null, emptybusy varchar(10), scenecode varchar(50), primary key (id), key internetsumrecord_hour_tmp_provincecode (provincecode), key internetsumrecord_hour_tmp_ispprovincecode(ispprovincecode), key internetsumrecord_hour_tmp_localnet (localnet), key internetsumrecord_hour_tmp_url (url), key internetsumrecord_hour_tmp_emptybusy (emptybusy), key internetsumrecord_hour_tmp_ispcode (ispcode) ); set @SQL=concat_ws('',' insert into internetsumrecord_hour_tmp select id,sumstarttime,SUBSTRING(servicetype, 1, 3),SUBSTRING(servicetype, 4, 2),localnet,provincecode,citycode,testtotalnum,successtotalnum, hour,dnsserver,url,ispcode,domainnamegroup,loginusername,othernet,localmodulecode,intime,SUBSTRING(servicetype, 6, 3) as expservicetype, ispprovincecode,resourcetype, case when hour in (20,21) then 1 when hour in (3,4) then 2 else 0 end as emptybusy, ifnull(scenecode,0) as scenecode from InternetSum_hour40800',v_yyyymm,' rec where sumstarttime>=''',p_teststarttime,''' and sumstarttime<''',p_testendtime,''' and (''',p_provincecode,'''='''' or charindex(rec.provincecode,''',p_provincecode,''')>0) and rec.provincecode!=-1 and rec.provincecode!=0 and ',p_where ); prepare stmt from @SQL; execute stmt; drop table if exists result_dns_auto_temp; create table if not exists result_dns_auto_temp ( yyyymmdd varchar(10), provincecode varchar(20), provincename varchar(20), localnet varchar(20), localnetname varchar(20), url varchar(200), emptybusy varchar(20), scenecode varchar(50), domainnamegroup varchar(200), testtimes int, successtimes int, dnssuccessrate decimal(10,2), isintroduce int, dealoffsettime_min float, dealoffsettime_max float, dealoffsettime_stdev_min float, dealoffsettime_stdev_max float, dealoffsettime_all float ); insert into result_dns_auto_temp(yyyymmdd,provincecode,provincename,localnet,localnetname, url,emptybusy,scenecode,domainnamegroup,testtimes,successtimes,dnssuccessrate,isintroduce) select p_yyyymmdd, rec.provincecode, province.namecn as provincename, rec.localnet, dic_net.codenamecn as localnetname, rec.url, rec.emptybusy, rec.scenecode, rec.domainnamegroup, sum(testtotalnum), sum(successtotalnum), case when sum(testtotalnum)=0 then 0 else cast(sum(successtotalnum)*100.0/sum(testtotalnum) as decimal(10,2)) end as dnssuccessrate, sum(case when resourcetype in (1,2,3) then 1 else 0 end) from internetsumrecord_hour_tmp rec left join province on rec.provincecode=province.provincecode left join systemcode as dic_net on rec.localnet=dic_net.systemcode and dic_net.typecode=11 group by rec.provincecode,province.namecn,rec.localnet,dic_net.codenamecn,rec.url,rec.emptybusy,rec.scenecode,rec.domainnamegroup; delete from it_dns_sum_jt_common_emptybusy_auto where yyyymmdd=p_yyyymmdd and (p_provincecode='' or charindex(provincecode,p_provincecode)>0); insert into it_dns_sum_jt_common_emptybusy_auto select * from result_dns_auto_temp; truncate table result_dns_auto_temp; insert into result_dns_auto_temp(yyyymmdd,provincecode,provincename,localnet,localnetname,url, emptybusy,scenecode,domainnamegroup,testtimes,successtimes,dnssuccessrate,isintroduce) select p_yyyymmdd, rec.provincecode, province.namecn as provincename, rec.localnet, dic_net.codenamecn as localnetname, rec.url, '全部', rec.scenecode, rec.domainnamegroup, sum(testtotalnum), sum(successtotalnum), case when sum(testtotalnum)=0 then 0 else cast(sum(successtotalnum)*100.0/sum(testtotalnum) as decimal(10,2)) end as dnssuccessrate, sum(case when resourcetype in (1,2,3) then 1 else 0 end) from internetsumrecord_hour_tmp rec left join province on rec.provincecode=province.provincecode left join systemcode as dic_net on rec.localnet=dic_net.systemcode and dic_net.typecode=11 group by rec.provincecode,province.namecn,rec.localnet,dic_net.codenamecn,rec.url,rec.scenecode,rec.domainnamegroup; delete from it_dns_sum_jt_common_auto where yyyymmdd=p_yyyymmdd and (p_provincecode='' or charindex(provincecode,p_provincecode)>0); insert into it_dns_sum_jt_common_auto select * from result_dns_auto_temp; select 'success'; end$$ delimiter ;