语句太长了,分几个楼层来放。part1 declare @pull varchar(100)
declare @startid bigint
declare @endid bigint
declare @sql varchar(1000)
declare @CSDGSM varchar(100)
declare @GPRS varchar(100)
select @CSDGSM=ParamValue from waprpt.Running_Parameter where ParamName='CSDGSM'
select @GPRS=ParamValue from waprpt.Running_Parameter where ParamName='GPRS'
exec waprpt.p_getProcessInfo 19,1,@monthday,@pull,@startid,@endid if @startid<=@endid
begin
set @sql=genTmpTabSql('#province_log_pull_temp',@pull,'StartTime,BearType,KBIn,KBOut,mdn,cpStatus,serviceType,ggsnip',@startid,@endid)
execute (@sql)
select t.timeIDD,z.ggsnip,count(distinct z.mdn) as total_user_num
into #Wap_Service_Province_Stat_TOTAL_USER_temp1
from #province_log_pull_temp z
left outer join waprpt.Time_Date_Dim t
on YEAR(z.startTime)=t.year
and MONTH(z.startTime)=t.month
and DAY(z.startTime)=t."date"
group by t.timeIDD,z.ggsnip
select t.timeIDD,z.ggsnip,count(distinct z.mdn) as csd_user_num
into #Wap_Service_Province_Stat_CSD_USER_temp1
from #province_log_pull_temp z
left outer join waprpt.Time_Date_Dim t
on YEAR(z.startTime)=t.year
and MONTH(z.startTime)=t.month
and DAY(z.startTime)=t."date"
where z.beartype=@CSDGSM
group by t.timeIDD,z.ggsnip
select t.timeIDD,z.ggsnip,count(distinct z.mdn) as gprs_user_num
into #Wap_Service_Province_Stat_GPRS_USER_temp1
from #province_log_pull_temp z
left outer join waprpt.Time_Date_Dim t
on YEAR(z.startTime)=t.year
and MONTH(z.startTime)=t.month
and DAY(z.startTime)=t."date"
where z.beartype=@GPRS
group by t.timeIDD,z.ggsnip
select t.timeIDD,z.ggsnip,z.beartype,count(1) as req_num,sum(isnull(z.kbin,0)+isnull(z.kbout,0)) as flow,z.cpStatus,z.servicetype
into #Wap_Service_Province_Stat_temp1
from #province_log_pull_temp z
left outer join waprpt.Time_Date_Dim t
on YEAR(z.startTime)=t.year
and MONTH(z.startTime)=t.month
and DAY(z.startTime)=t."date"
group by t.timeIDD,z.ggsnip,z.beartype,z.cpStatus,z.servicetype
select timeIDD,ggsnip,sum(req_num) as csd_req_num,sum(flow) as csd_flow
into #Wap_Service_Province_Stat_CSD_temp1
from #Wap_Service_Province_Stat_temp1
where beartype=@CSDGSM
group by timeIDD,ggsnip
declare @startid bigint
declare @endid bigint
declare @sql varchar(1000)
declare @CSDGSM varchar(100)
declare @GPRS varchar(100)
select @CSDGSM=ParamValue from waprpt.Running_Parameter where ParamName='CSDGSM'
select @GPRS=ParamValue from waprpt.Running_Parameter where ParamName='GPRS'
exec waprpt.p_getProcessInfo 19,1,@monthday,@pull,@startid,@endid if @startid<=@endid
begin
set @sql=genTmpTabSql('#province_log_pull_temp',@pull,'StartTime,BearType,KBIn,KBOut,mdn,cpStatus,serviceType,ggsnip',@startid,@endid)
execute (@sql)
select t.timeIDD,z.ggsnip,count(distinct z.mdn) as total_user_num
into #Wap_Service_Province_Stat_TOTAL_USER_temp1
from #province_log_pull_temp z
left outer join waprpt.Time_Date_Dim t
on YEAR(z.startTime)=t.year
and MONTH(z.startTime)=t.month
and DAY(z.startTime)=t."date"
group by t.timeIDD,z.ggsnip
select t.timeIDD,z.ggsnip,count(distinct z.mdn) as csd_user_num
into #Wap_Service_Province_Stat_CSD_USER_temp1
from #province_log_pull_temp z
left outer join waprpt.Time_Date_Dim t
on YEAR(z.startTime)=t.year
and MONTH(z.startTime)=t.month
and DAY(z.startTime)=t."date"
where z.beartype=@CSDGSM
group by t.timeIDD,z.ggsnip
select t.timeIDD,z.ggsnip,count(distinct z.mdn) as gprs_user_num
into #Wap_Service_Province_Stat_GPRS_USER_temp1
from #province_log_pull_temp z
left outer join waprpt.Time_Date_Dim t
on YEAR(z.startTime)=t.year
and MONTH(z.startTime)=t.month
and DAY(z.startTime)=t."date"
where z.beartype=@GPRS
group by t.timeIDD,z.ggsnip
select t.timeIDD,z.ggsnip,z.beartype,count(1) as req_num,sum(isnull(z.kbin,0)+isnull(z.kbout,0)) as flow,z.cpStatus,z.servicetype
into #Wap_Service_Province_Stat_temp1
from #province_log_pull_temp z
left outer join waprpt.Time_Date_Dim t
on YEAR(z.startTime)=t.year
and MONTH(z.startTime)=t.month
and DAY(z.startTime)=t."date"
group by t.timeIDD,z.ggsnip,z.beartype,z.cpStatus,z.servicetype
select timeIDD,ggsnip,sum(req_num) as csd_req_num,sum(flow) as csd_flow
into #Wap_Service_Province_Stat_CSD_temp1
from #Wap_Service_Province_Stat_temp1
where beartype=@CSDGSM
group by timeIDD,ggsnip
select timeIDD,ggsnip,sum(req_num) as csd_req_succ_num
into #Wap_Service_Province_Stat_CSD_SUCC_temp1
from #Wap_Service_Province_Stat_temp1
where beartype=@CSDGSM and cpStatus<400
group by timeIDD,ggsnip
select timeIDD,ggsnip,sum(req_num) as gprs_req_num,sum(flow) as gprs_flow
into #Wap_Service_Province_Stat_GPRS_temp1
from #Wap_Service_Province_Stat_temp1
where beartype=@GPRS
group by timeIDD,ggsnip
select timeIDD,ggsnip,sum(req_num) as gprs_req_succ_num
into #Wap_Service_Province_Stat_GPRS_SUCC_temp1
from #Wap_Service_Province_Stat_temp1
where beartype=@GPRS and cpStatus<400
group by timeIDD,ggsnip
select timeIDD,ggsnip,sum(req_num) as total_req_num,sum(flow) as total_flow
into #Wap_Service_Province_Stat_TOTAL_temp1
from #Wap_Service_Province_Stat_temp1
group by timeIDD,ggsnip
select timeIDD,ggsnip,sum(req_num) as total_succ_req_num
into #Wap_Service_Province_Stat_TOTAL_SUCC_temp1
from #Wap_Service_Province_Stat_temp1
where cpStatus<400
group by timeIDD,ggsnip
select timeIDD,ggsnip,serviceType,sum(req_num) as serviceType_req_num
into #Wap_Service_Province_Stat_ServiceType_temp1
from #Wap_Service_Province_Stat_temp1
group by timeIDD,ggsnip,serviceType
select timeIDD,ggsnip,0 as CSD_REQ_NUM,0 as CSD_SUCC_REQ_NUM,0 as CSD_USER_NUM,0 as CSD_TUNNEL_FLOW,0 as GPRS_REQ_NUM,0 as GPRS_SUCC_REQ_NUM,
0 as GPRS_USER_NUM,0 GPRS_TUNNEL_FLOW,0 as TOTAL_REQ_NUM,0 as TOTAL_SUCC_REQ_NUM,0 as TOTAL_USER_NUM,0 as TOTAL_TUNNEL_FLOW,0 as WAP_BROWSER_REQ_NUM,
0 as HTTP_BROWSER_REQ_NUM,0 as JAVA_LOAD_REQ_NUM,0 as MMS_POST_REQ_NUM,0 as MMS_GET_REQ_NUM,0 as PUSH_REQ_NUM,0 as ONLINE_USER_NUM
into #Wap_Service_Province_Stat_temp
from #Wap_Service_Province_Stat_temp1
group by timeIDD,ggsnip
update #Wap_Service_Province_Stat_temp set CSD_REQ_NUM=t1.csd_req_num,CSD_TUNNEL_FLOW=t1.csd_flow
from #Wap_Service_Province_Stat_temp t,#Wap_Service_Province_Stat_CSD_temp1 t1
where t.timeIDD=t1.timeIDD and t.ggsnip=t1.ggsnip
--更新csd成功请求总数
update #Wap_Service_Province_Stat_temp set CSD_SUCC_REQ_NUM=t1.csd_req_succ_num
from #Wap_Service_Province_Stat_temp t,#Wap_Service_Province_Stat_CSD_SUCC_temp1 t1
where t.timeIDD=t1.timeIDD and t.ggsnip=t1.ggsnip
--更新gprs请求总数、GPRS Tunnel流量
update #Wap_Service_Province_Stat_temp set GPRS_REQ_NUM=t1.gprs_req_num,GPRS_TUNNEL_FLOW=t1.gprs_flow
from #Wap_Service_Province_Stat_temp t,#Wap_Service_Province_Stat_GPRS_temp1 t1
where t.timeIDD=t1.timeIDD and t.ggsnip=t1.ggsnip
--更新gprs成功请求总数
update #Wap_Service_Province_Stat_temp set GPRS_SUCC_REQ_NUM=t1.gprs_req_succ_num
from #Wap_Service_Province_Stat_temp t,#Wap_Service_Province_Stat_GPRS_SUCC_temp1 t1
where t.timeIDD=t1.timeIDD and t.ggsnip=t1.ggsnip
--更新请求总数、Tunnel流量
update #Wap_Service_Province_Stat_temp set TOTAL_REQ_NUM=t1.total_req_num,TOTAL_TUNNEL_FLOW=t1.total_flow
from #Wap_Service_Province_Stat_temp t,#Wap_Service_Province_Stat_TOTAL_temp1 t1
where t.timeIDD=t1.timeIDD and t.ggsnip=t1.ggsnip
--更新成功请求总数
update #Wap_Service_Province_Stat_temp set TOTAL_SUCC_REQ_NUM=t1.total_succ_req_num
from #Wap_Service_Province_Stat_temp t,#Wap_Service_Province_Stat_TOTAL_SUCC_temp1 t1
where t.timeIDD=t1.timeIDD and t.ggsnip=t1.ggsnip
--更新WAP浏览请求数、HTTP浏览请求数、JAVA下载请求数、彩信POST请求数、彩信GET请求数
update #Wap_Service_Province_Stat_temp set WAP_BROWSER_REQ_NUM=t1.serviceType_req_num
from #Wap_Service_Province_Stat_temp t,#Wap_Service_Province_Stat_ServiceType_temp1 t1
where t.timeIDD=t1.timeIDD and t.ggsnip=t1.ggsnip and t1.serviceType='001'
update #Wap_Service_Province_Stat_temp set HTTP_BROWSER_REQ_NUM=t1.serviceType_req_num
from #Wap_Service_Province_Stat_temp t,#Wap_Service_Province_Stat_ServiceType_temp1 t1
where t.timeIDD=t1.timeIDD and t.ggsnip=t1.ggsnip and t1.serviceType='002'
update #Wap_Service_Province_Stat_temp set JAVA_LOAD_REQ_NUM=t1.serviceType_req_num
from #Wap_Service_Province_Stat_temp t,#Wap_Service_Province_Stat_ServiceType_temp1 t1
where t.timeIDD=t1.timeIDD and t.ggsnip=t1.ggsnip and t1.serviceType='003'
update #Wap_Service_Province_Stat_temp set MMS_POST_REQ_NUM=t1.serviceType_req_num
from #Wap_Service_Province_Stat_temp t,#Wap_Service_Province_Stat_ServiceType_temp1 t1
where t.timeIDD=t1.timeIDD and t.ggsnip=t1.ggsnip and t1.serviceType='004'
update #Wap_Service_Province_Stat_temp set MMS_GET_REQ_NUM=t1.serviceType_req_num
from #Wap_Service_Province_Stat_temp t,#Wap_Service_Province_Stat_ServiceType_temp1 t1
where t.timeIDD=t1.timeIDD and t.ggsnip=t1.ggsnip and t1.serviceType='005'
update #Wap_Service_Province_Stat_temp set TOTAL_USER_NUM=t1.total_user_num
from #Wap_Service_Province_Stat_temp t,#Wap_Service_Province_Stat_TOTAL_USER_temp1 t1
where t.timeIDD=t1.timeIDD and t.ggsnip=t1.ggsnip
update #Wap_Service_Province_Stat_temp set CSD_USER_NUM=t1.csd_user_num
from #Wap_Service_Province_Stat_temp t,#Wap_Service_Province_Stat_CSD_USER_temp1 t1
where t.timeIDD=t1.timeIDD and t.ggsnip=t1.ggsnip
update #Wap_Service_Province_Stat_temp set GPRS_USER_NUM=t1.gprs_user_num
from #Wap_Service_Province_Stat_temp t,#Wap_Service_Province_Stat_GPRS_USER_temp1 t1
where t.timeIDD=t1.timeIDD and t.ggsnip=t1.ggsnip
select * from #Wap_Service_Province_Stat_temp
exec waprpt.p_getProcessInfo 19,1,@monthday,@pull,@startid,@endid
--作用是把一部分记录取出指定表@pull的开始ID和结束ID,这里的@monthday是个入餐不用理会--part1里的这句话
set @sql=genTmpTabSql('#province_log_pull_temp',@pull,'StartTime,BearType,KBIn,KBOut,mdn,cpStatus,serviceType,ggsnip',@startid,@endid)
execute (@sql)
--作用是把表@pull里面大于开始ID和小于结束ID的记录取到临时表#province_log_pull_temp里面,具体不用关注。
into #Wap_Service_Province_Stat_CSD_SUCC_temp1
from #Wap_Service_Province_Stat_temp1
where beartype=@CSDGSM and cpStatus<400
group by timeIDD,ggsnip
与
select timeIDD,ggsnip,sum(req_num) as gprs_req_succ_num
into #Wap_Service_Province_Stat_GPRS_SUCC_temp1
from #Wap_Service_Province_Stat_temp1
where beartype=@GPRS and cpStatus<400
group by timeIDD,ggsnip
完全可以放到一张表啊,这样的话可以减少很多的临时表
合并成:
select timeIDD,ggsnip,sum(req_num) as csd_req_succ_num,sum(req_num) as gprs_req_succ_num
into #Wap_Service_Province_Stat_GPRS_SUCC_temp1
from #Wap_Service_Province_Stat_temp1
where beartype=@GPRS and cpStatus<400
group by timeIDD,ggsnip
--
可以合并
where (beartype=@CSDGSM or beartype=@GPRS)
简化?
用CSDN上的分 雇人给你干活呢啊?