create or replace procedure zhyw.SHA_QDCN_PGTX_CS(P_DAY VARCHAR2 DEFAULT TO_CHAR(SYSDATE - 1,'yyyymmdd')) as
V_ZMLX VARCHAR2(50);
V_ZMLX_SZ VARCHAR2(20);
SQL_STRING varchar2(30000);
v_day varchar2(8);
v_day1 varchar2(8);
v_day2 varchar2(8);
v_day5 varchar2(8);
v_day_qy1 varchar2(8);
v_month varchar2(6);
v_month_1 varchar2(6);
v_month_5 varchar2(6);
v_month1 varchar2(6);
v_monthq varchar2(6);
v_monthq_1 varchar2(6);
v_last_day varchar2(8);
v_last_day_2 varchar2(8);
v_last_day2 varchar2(8);
v_last_day3 varchar2(8);
v_monthS varchar2(6);
v_monthS3 varchar2(6);
v_yesr varchar2(4);
v_monsr varchar2(6);
v_monsrq varchar2(6);
v_monsrh varchar2(6);
---本月最后一天
v_monsr_last varchar2(8);
v_monsrq2 varchar2(6);
v_monsrq3 varchar2(6);
v_monsrq4 varchar2(6);
v_monsrq5 varchar2(6);
v_monsrq6 varchar2(6);
v_monsrq12 varchar2(6);
v_P_DAY varchar2(8);
V_TAB1 varchar2(900);
zdyf varchar2(10);
begin
v_day :=to_char(sysdate-1,'yyyymmdd');
v_day1 :=to_char(sysdate-2,'yyyymmdd');
v_day2 :=to_char(sysdate-3,'yyyymmdd');
v_day_qy1 :=to_char(add_months(sysdate-2,-1),'yyyymmdd');
v_day5 :=to_char(sysdate-5,'yyyymmdd');
v_month :=to_char(add_months(sysdate-1,-0),'yyyymm');
v_month_1 :=to_char(add_months(sysdate-2,-0),'yyyymm');
v_month_5 :=to_char(add_months(sysdate-5,-1),'yyyymm');
v_month1 :=to_char(add_months(sysdate-1,1),'yyyymm');
v_monthq :=to_char(add_months(sysdate-1,-1),'yyyymm');
v_monthq_1 :=to_char(add_months(sysdate-2,-1),'yyyymm');
v_monthS :=to_char(add_months(sysdate-1,-2),'yyyymm');
v_monthS3 :=to_char(add_months(sysdate-1,-3),'yyyymm');
v_last_day :=to_char(last_day(add_months(to_date(P_DAY,'yyyymmdd'),-1)),'yyyymmdd') ;
v_last_day_2 :=to_char(last_day(add_months(sysdate-2,-1)),'yyyymmdd') ;
v_last_day2 :=to_char(last_day(add_months(to_date(P_DAY,'yyyymmdd'),-2)),'yyyymmdd') ;
v_last_day3 :=to_char(last_day(add_months(to_date(P_DAY,'yyyymmdd'),-3)),'yyyymmdd') ;
v_yesr :=substr(P_DAY,1,4);
v_monsr :=substr(P_DAY,1,6);
v_monsrq :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-1),'yyyymm') ;
v_monsrh :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),1),'yyyymm') ;
v_monsr_last :=to_char(last_day(add_months(to_date(P_DAY,'yyyymmdd'),0)),'yyyymmdd') ;
v_monsrq2 :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-2),'yyyymm') ;
v_monsrq3 :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-3),'yyyymm') ;
v_monsrq4 :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-4),'yyyymm') ;
v_monsrq5 :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-5),'yyyymm') ;
v_monsrq6 :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-6),'yyyymm') ;
v_monsrq12 :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-12),'yyyymm') ;
v_P_DAY :=to_char(to_date(P_DAY,'yyyymmdd')-1,'yyyymmdd') ;
zdyf:=to_char(trunc(to_date(P_DAY,'yyyymmdd'),'mm'),'yyyymm');--统计月
---渠道产能评估体系 2022-1114
---渠道产能体系
----渠道基础目录 select * from shzc.zxb_qdcn_pgtx_qdtype for update ;
SQL_STRING:='insert into shzc.zxb_qdcn_pgtx_qdtype
select * from shzc.zxb_qdcn_pgtx_qdtype_dr a
where not exists ( select * from shzc.zxb_qdcn_pgtx_qdtype t where t.渠道编码=a.渠道编码
and t.userid=a.userid and t.in_time=a.in_time)
and 区县<>''区县'' ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;
----导入人员与时间字段
SQL_STRING:='update shzc.zxb_qdcn_pgtx_qdtype a set a.渠道编码=trim(a.渠道编码),a.userid=''xuce'',a.in_time=sysdate
where a.in_time is null ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;
----经分报表处理
SQL_STRING:='update shzc.zxb_qdcn_pgtx_qdtype a set a.in_time=sysdate ,a.渠道编码=trim(a.渠道编码)
where to_char(a.in_time,''hh24miss'')=''000000'' ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;
----月周日期对应表 select * from shzc.zxb_qdcn_pgtx_wwtype for update ;
-----------------------------------------------------代码开始-------------------------------------------------------
----渠道数据基础
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_sja'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_sja as
select * from shzc.zxb_qdcn_pgtx_qdtype a
where a.in_time = (
select max(a.in_time) from shzc.zxb_qdcn_pgtx_qdtype a ) ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_wwtype_sja'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_wwtype_sja as
select * from
(select a.*,row_number() over (partition by a.cycle,a.ww order by a.start_day desc ) 排名
from shzc.zxb_qdcn_pgtx_wwtype a
where a.cycle='''||v_monsr||''') a
where 排名=1 ';
EXECUTE IMMEDIATE (SQL_STRING);
---拉新客户按照(等效折算+1)户计入(2017vip月汇总,放号) ---2017vip指标汇总_当月
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_fhb'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_fhb as
select a.registerorgid,a.status,a.statusdate,a.subsid,a.servnumber,a.prodid,a.prodname,
a.是否活跃,a.最后一次活跃时间,a.停机锁,a.是否新入网信用购,a.是否融合, a.是否业务融合, a.是否宽带融合,
a.jiazhi,a.关怀打折,a.折后价值,a.是否折后48,a.是否69以上,a.等效69,
to_number(to_char(a.createdate,''dd'')) create_day,nvl(c.ww,1) 本月第几周
from zhyw.shc_fanghao_'||v_monsr||' a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.registerorgid=b.渠道编码
and to_number(to_char(a.createdate,''dd'')) between c.start_day and c.end_day
and a.createdate -1 <to_date('''||P_DAY||''',''yyyymmdd'')';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_fhbhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_fhbhz as
select a.registerorgid,count(distinct a.subsid) mon_subs,
count(distinct case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then a.subsid end) day_subs,
count(distinct case when a.本月第几周=1 then a.subsid end) ww1_subs,
count(distinct case when a.本月第几周=2 then a.subsid end) ww2_subs,
count(distinct case when a.本月第几周=3 then a.subsid end) ww3_subs,
count(distinct case when a.本月第几周=4 then a.subsid end) ww4_subs,
count(distinct case when a.本月第几周=5 then a.subsid end) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_fhb a
where a.是否活跃=1
and a.status=''US10''
group by a.registerorgid';
EXECUTE IMMEDIATE (SQL_STRING);
---5折算1户,原价算2户;高套餐按照69元等效折算;针对固移、信用购场景,按照1.2户折算(两个场景均符合,也按1.2户折算)。(放号明细,69等效)
---直接按照 69等效 这一列,如果为空取0
--- select t1.*,折算 折算_69 from zhyw.shc_pk_third_fanghao_mx_month t1 where to_char(createdate,'yyyymm')='202211'
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_dx69'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_dx69 as
select a.registerorgid,a.status,a.statusdate,a.subsid,a.servnumber,a.jiazhi,a.停机锁,a.is_yxkd,a.是否拉新,a.折算 等效69,
to_number(to_char(a.createdate,''dd'')) create_day,nvl(c.ww,1) 本月第几周
from zhyw.shc_pk_third_fanghao_mx_month a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.registerorgid=b.渠道编码
and to_number(to_char(a.createdate,''dd'')) between c.start_day and c.end_day
and to_char(createdate,''yyyymm'')='''||v_monsr||'''
and a.createdate -1 <to_date('''||P_DAY||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_dx69hz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_dx69hz as
select a.registerorgid,
sum(nvl(a.等效69,0)) mon_dx69,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.等效69,0) end,0)) day_dx69,
sum(nvl(case when a.本月第几周=1 then nvl(a.等效69,0) end,0)) ww1_dx69,
sum(nvl(case when a.本月第几周=2 then nvl(a.等效69,0) end,0)) ww2_dx69,
sum(nvl(case when a.本月第几周=3 then nvl(a.等效69,0) end,0)) ww3_dx69,
sum(nvl(case when a.本月第几周=4 then nvl(a.等效69,0) end,0)) ww4_dx69,
sum(nvl(case when a.本月第几周=5 then nvl(a.等效69,0) end,0)) ww5_dx69
from shzc.zxb_qdcn_pgtx_qdtype_dx69 a
group by a.registerorgid ';
EXECUTE IMMEDIATE (SQL_STRING);
----小合约+信用购_代办(2017vip月汇总,小合约+信用购_代办)
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_zdhy'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_zdhy as
select a.prod_type,a.orgid,a.subsid,a.servnumber,a.recdate,a.prodname,a.privname,a.备用字段1,a.oid,
to_number(to_char(a.recdate,''dd'')) create_day,nvl(c.ww,1) 本月第几周
from zhyw.report_mx_month_'||v_monsr||'_new a ,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.orgid=b.渠道编码
and a.prod_type=''终端合约''
and 备用字段1 in (''新入网信用购'',''存量换机优享'',''家庭合户信用购'',''权益信用购机'',''小合约'')
and to_number(to_char(a.recdate,''dd'')) between c.start_day and c.end_day
and a.recdate -1 <to_date('''||P_DAY||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_zdhyhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_zdhyhz as
select a.orgid registerorgid,count(distinct a.subsid||a.oid||a.prod_type) mon_subs,
count(distinct case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then a.subsid||a.oid||a.prod_type end) day_subs,
count(distinct case when a.本月第几周=1 then a.subsid||a.oid||a.prod_type end) ww1_subs,
count(distinct case when a.本月第几周=2 then a.subsid||a.oid||a.prod_type end) ww2_subs,
count(distinct case when a.本月第几周=3 then a.subsid||a.oid||a.prod_type end) ww3_subs,
count(distinct case when a.本月第几周=4 then a.subsid||a.oid||a.prod_type end) ww4_subs,
count(distinct case when a.本月第几周=5 then a.subsid||a.oid||a.prod_type end) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_zdhy a
group by a.orgid ';
EXECUTE IMMEDIATE (SQL_STRING);
---升档套包
---模组升档合约新年包 + 新模组升级 + 跨升档融权益礼包*2 + 不限量升档融权益 + 低端阶梯打折 + 小颗粒升档业务 *0.5+ 流量年包_5g (2017vip月汇总,小合约+信用购_代办)
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_sdtb'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_sdtb as
select a.prod_type,a.orgid,a.subsid,a.servnumber,a.recdate,a.prodname,a.privname,a.个性化 ,a.prodid,a.oid,
nvl(case when prod_type=''跨升档融权益礼包'' and prodid<> ''PROD20220715766.999'' then 2
when prod_type=''小颗粒升档业务'' then 0.5 end,1) 折算系数,
to_number(to_char(a.recdate,''dd'')) create_day,nvl(c.ww,1) 本月第几周
from zhyw.report_mx_month_'||v_monsr||'_new a ,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.orgid=b.渠道编码
and (a.prod_type in (''模组升档合约新年包'',''跨升档融权益礼包'',''低端阶梯打折'',''小颗粒升档业务'',''流量年包_5g'')
or (a.prod_type =''新模组升级'' and 个性化=''计入模组升级''))
and to_number(to_char(a.recdate,''dd'')) between c.start_day and c.end_day
and a.recdate -1 <to_date('''||P_DAY||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_sdtbhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_sdtbhz as
select a.orgid registerorgid,
sum(nvl(a.折算系数,0)) mon_subs,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.折算系数,0) end,0)) day_subs,
sum(nvl(case when a.本月第几周=1 then nvl(a.折算系数,0) end,0)) ww1_subs,
sum(nvl(case when a.本月第几周=2 then nvl(a.折算系数,0) end,0)) ww2_subs,
sum(nvl(case when a.本月第几周=3 then nvl(a.折算系数,0) end,0)) ww3_subs,
sum(nvl(case when a.本月第几周=4 then nvl(a.折算系数,0) end,0)) ww4_subs,
sum(nvl(case when a.本月第几周=5 then nvl(a.折算系数,0) end,0)) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_sdtb a
group by a.orgid ';
EXECUTE IMMEDIATE (SQL_STRING);
---线盒新增 ------------
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_xhxz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_xhxz as
select a.item,a.subsid,a.servnumber,a.recorgid,a.recdate,a.recoid,
a.startdate,a.enddate,a.ok_date,a.flow_m,a.flag,a.jf,a.flag_route_line,
to_number(to_char(a.ok_date,''dd'')) create_day,nvl(c.ww,1) 本月第几周
From zhyw.zb_kd_pk_mx_subs_'||v_monsr||' a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.recorgid=b.渠道编码
and (a.item like ''宽带新增%'' or a.item like ''魔百和新增%'')
and to_number(to_char(a.ok_date,''dd'')) between c.start_day and c.end_day
and to_char(a.ok_date,''yyyymm'')='''||v_monsr||'''
and a.ok_date -1 <to_date('''||P_DAY||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_xhxzhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_xhxzhz as
select a.recorgid registerorgid,
sum(nvl(a.flag,0)) mon_subs,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.flag,0) end,0)) day_subs,
sum(nvl(case when a.本月第几周=1 then nvl(a.flag,0) end,0)) ww1_subs,
sum(nvl(case when a.本月第几周=2 then nvl(a.flag,0) end,0)) ww2_subs,
sum(nvl(case when a.本月第几周=3 then nvl(a.flag,0) end,0)) ww3_subs,
sum(nvl(case when a.本月第几周=4 then nvl(a.flag,0) end,0)) ww4_subs,
sum(nvl(case when a.本月第几周=5 then nvl(a.flag,0) end,0)) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_xhxz a
group by a.recorgid ';
EXECUTE IMMEDIATE (SQL_STRING);
----2022-1114宽带线盒不核减
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_xhxzb'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_xhxzb as
select a.item,a.subsid,a.servnumber,a.recorgid,a.recdate,a.recoid,
a.startdate,a.enddate,a.ok_date,a.flow_m,a.flag,a.jf,a.flag_route_line,
to_number(to_char(a.ok_date,''dd'')) create_day,nvl(c.ww,1) 本月第几周
from zhyw.zb_kd_pk_mx_subs_'||v_monsr||' a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.recorgid=b.渠道编码
and a.item like ''%核减%''
and to_number(to_char(a.ok_date,''dd'')) between c.start_day and c.end_day
and to_char(a.ok_date,''yyyymm'')='''||v_monsr||'''
and a.ok_date -1 <to_date('''||P_DAY||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_xhxzbhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_xhxzbhz as
select a.recorgid registerorgid,
sum(nvl(a.flag,0)) mon_subs,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.flag,0) end,0)) day_subs,
sum(nvl(case when a.本月第几周=1 then nvl(a.flag,0) end,0)) ww1_subs,
sum(nvl(case when a.本月第几周=2 then nvl(a.flag,0) end,0)) ww2_subs,
sum(nvl(case when a.本月第几周=3 then nvl(a.flag,0) end,0)) ww3_subs,
sum(nvl(case when a.本月第几周=4 then nvl(a.flag,0) end,0)) ww4_subs,
sum(nvl(case when a.本月第几周=5 then nvl(a.flag,0) end,0)) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_xhxzb a
group by a.recorgid ';
EXECUTE IMMEDIATE (SQL_STRING);
---智能组网
---考核智能组网新增发展量。(同竞赛口径)
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_znzw'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_znzw as
select a.item,a.subsid,a.servnumber,a.recorgid,a.recdate,a.recoid,
a.startdate,a.enddate,a.ok_date,a.flow_m,a.flag,a.jf,a.flag_route_line,
to_number(to_char(a.ok_date,''dd'')) create_day,nvl(c.ww,1) 本月第几周
From zhyw.zb_kd_pk_mx_subs_'||v_monsr||' a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.recorgid=b.渠道编码
and a.item like ''%路由器%''
and to_number(to_char(a.ok_date,''dd'')) between c.start_day and c.end_day
and to_char(a.ok_date,''yyyymm'')='''||v_monsr||'''
and a.ok_date -1 <to_date('''||P_DAY||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_znzwhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_znzwhz as
select a.recorgid registerorgid,
sum(nvl(a.flag,0)) mon_subs,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.flag,0) end,0)) day_subs,
sum(nvl(case when a.本月第几周=1 then nvl(a.flag,0) end,0)) ww1_subs,
sum(nvl(case when a.本月第几周=2 then nvl(a.flag,0) end,0)) ww2_subs,
sum(nvl(case when a.本月第几周=3 then nvl(a.flag,0) end,0)) ww3_subs,
sum(nvl(case when a.本月第几周=4 then nvl(a.flag,0) end,0)) ww4_subs,
sum(nvl(case when a.本月第几周=5 then nvl(a.flag,0) end,0)) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_znzw a
group by a.recorgid ';
EXECUTE IMMEDIATE (SQL_STRING);
--家庭安防 nvl(宽带权益包,0)+nvl(家庭安防,0)家庭安防
---考核家庭安防新增发展量(家庭安防客户开通宽带权益包、1元接盘包另计入1户)(同竞赛口径)
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_jtaf'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_jtaf as
select a.item,a.subsid,a.servnumber,a.recorgid,a.recdate,a.recoid,
a.startdate,a.enddate,a.ok_date,a.flow_m,a.flag,a.jf,a.flag_route_line,
to_number(to_char(a.ok_date,''dd'')) create_day,nvl(c.ww,1) 本月第几周
From zhyw.zb_kd_pk_mx_subs_'||v_monsr||' a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.recorgid=b.渠道编码
and (a.item like ''%宽带权益包%'' or a.item like ''%家庭安防%'')
and to_number(to_char(a.ok_date,''dd'')) between c.start_day and c.end_day
and to_char(a.ok_date,''yyyymm'')='''||v_monsr||'''
and a.ok_date -1 <to_date('''||P_DAY||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_jtafhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_jtafhz as
select a.recorgid registerorgid,
sum(nvl(a.flag,0)) mon_subs,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.flag,0) end,0)) day_subs,
sum(nvl(case when a.本月第几周=1 then nvl(a.flag,0) end,0)) ww1_subs,
sum(nvl(case when a.本月第几周=2 then nvl(a.flag,0) end,0)) ww2_subs,
sum(nvl(case when a.本月第几周=3 then nvl(a.flag,0) end,0)) ww3_subs,
sum(nvl(case when a.本月第几周=4 then nvl(a.flag,0) end,0)) ww4_subs,
sum(nvl(case when a.本月第几周=5 then nvl(a.flag,0) end,0)) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_jtaf a
group by a.recorgid ';
EXECUTE IMMEDIATE (SQL_STRING);
--点播年包
---考核点播年包新增发展量,按照10元等效(1元体验包按照0.5户计入)(同竞赛口径)
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_dbnb'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_dbnb as
select a.item,a.subsid,a.servnumber,a.recorgid,a.recdate,a.recoid,
a.startdate,a.enddate,a.ok_date,a.flow_m,a.flag,a.jf,a.flag_route_line,
to_number(to_char(a.ok_date,''dd'')) create_day,nvl(c.ww,1) 本月第几周
From zhyw.zb_kd_pk_mx_subs_'||v_monsr||' a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.recorgid=b.渠道编码
and a.item like ''%点播年包%''
and to_number(to_char(a.ok_date,''dd'')) between c.start_day and c.end_day
and to_char(a.ok_date,''yyyymm'')='''||v_monsr||'''
and a.ok_date -1 <to_date('''||P_DAY||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_dbnbhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_dbnbhz as
select a.recorgid registerorgid,
sum(nvl(a.flag,0)) mon_subs,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.flag,0) end,0)) day_subs,
sum(nvl(case when a.本月第几周=1 then nvl(a.flag,0) end,0)) ww1_subs,
sum(nvl(case when a.本月第几周=2 then nvl(a.flag,0) end,0)) ww2_subs,
sum(nvl(case when a.本月第几周=3 then nvl(a.flag,0) end,0)) ww3_subs,
sum(nvl(case when a.本月第几周=4 then nvl(a.flag,0) end,0)) ww4_subs,
sum(nvl(case when a.本月第几周=5 then nvl(a.flag,0) end,0)) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_dbnb a
group by a.recorgid ';
EXECUTE IMMEDIATE (SQL_STRING);
---权益+视频彩铃 ZXR_RACE_2022Q4_CEL
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_qycl'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_qycl as
select a.prod_type,a.orgid,a.subsid,a.servnumber,a.recdate,a.prodname,a.privname,
a.prodid,a.oid,to_number(a.score) 折算系数,
to_number(to_char(a.recdate,''dd'')) create_day,nvl(c.ww,1) 本月第几周
from zhyw.zxr_race_2022q4_ticket_qy_'||v_monsr||' a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.orgid=b.渠道编码
and to_number(to_char(a.recdate,''dd'')) between c.start_day and c.end_day
and to_char(a.recdate,''yyyymm'')='''||v_monsr||'''
and a.recdate -1 <to_date('''||P_DAY||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_qyclhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_qyclhz as
select a.orgid registerorgid,
sum(nvl(a.折算系数,0)) mon_subs,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.折算系数,0) end,0)) day_subs,
sum(nvl(case when a.本月第几周=1 then nvl(a.折算系数,0) end,0)) ww1_subs,
sum(nvl(case when a.本月第几周=2 then nvl(a.折算系数,0) end,0)) ww2_subs,
sum(nvl(case when a.本月第几周=3 then nvl(a.折算系数,0) end,0)) ww3_subs,
sum(nvl(case when a.本月第几周=4 then nvl(a.折算系数,0) end,0)) ww4_subs,
sum(nvl(case when a.本月第几周=5 then nvl(a.折算系数,0) end,0)) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_qycl a
group by a.orgid ';
EXECUTE IMMEDIATE (SQL_STRING);
----数据组合
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_hzbd'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_hzbd as
select '''||P_DAY||''' in_time,a.区县,a.是否改革,a.网格名称,a.渠道编码,a.渠道名称,a.大类,a.职位,a.渠道归属,
----月数据
nvl(fh.mon_subs,0) 放号_月数据,
nvl(dx.mon_dx69,0) 等效69_月数据,
nvl(zd.mon_subs,0) 信用购_月数据,
nvl(sd.mon_subs,0) 升档套包_月数据,
nvl(xh.mon_subs,0) 线盒新增_月数据,
--nvl(xhb.mon_subs,0) 线盒核减_月数据,
nvl(zn.mon_subs,0) 智能组网_月数据,
nvl(jt.mon_subs,0) 家庭安防_月数据,
nvl(db.mon_subs,0) 点播年包_月数据,
nvl(qy.mon_subs,0) 权益彩铃_月数据,
nvl(fh.mon_subs,0) +nvl(dx.mon_dx69,0) +nvl(zd.mon_subs,0) +nvl(sd.mon_subs,0) +
nvl(xh.mon_subs,0) -- -nvl(xhb.mon_subs,0)
+nvl(zn.mon_subs,0) +nvl(jt.mon_subs,0) +
nvl(db.mon_subs,0) +nvl(qy.mon_subs,0) 合计_月数据,
----日数据
nvl(fh.day_subs,0) 放号_日数据,
nvl(dx.day_dx69,0) 等效69_日数据,
nvl(zd.day_subs,0) 信用购_日数据,
nvl(sd.day_subs,0) 升档套包_日数据,
nvl(xh.day_subs,0) 线盒新增_日数据,
--nvl(xhb.day_subs,0) 线盒核减_日数据,
nvl(zn.day_subs,0) 智能组网_日数据,
nvl(jt.day_subs,0) 家庭安防_日数据,
nvl(db.day_subs,0) 点播年包_日数据,
nvl(qy.day_subs,0) 权益彩铃_日数据,
nvl(fh.day_subs,0) +nvl(dx.day_dx69,0) +nvl(zd.day_subs,0) +nvl(sd.day_subs,0) +
nvl(xh.day_subs,0) -- -nvl(xhb.day_subs,0)
+nvl(zn.day_subs,0) +nvl(jt.day_subs,0) +
nvl(db.day_subs,0) +nvl(qy.day_subs,0) 合计_日数据,
----第一周
nvl(fh.ww1_subs,0) 放号_第一周数据,
nvl(dx.ww1_dx69,0) 等效69_第一周数据,
nvl(zd.ww1_subs,0) 信用购_第一周数据,
nvl(sd.ww1_subs,0) 升档套包_第一周数据,
nvl(xh.ww1_subs,0) 线盒新增_第一周数据,
--nvl(xhb.ww1_subs,0) 线盒核减_第一周数据,
nvl(zn.ww1_subs,0) 智能组网_第一周数据,
nvl(jt.ww1_subs,0) 家庭安防_第一周数据,
nvl(db.ww1_subs,0) 点播年包_第一周数据,
nvl(qy.ww1_subs,0) 权益彩铃_第一周数据,
nvl(fh.ww1_subs,0) +nvl(dx.ww1_dx69,0) +nvl(zd.ww1_subs,0) +nvl(sd.ww1_subs,0) +
nvl(xh.ww1_subs,0) -- -nvl(xhb.ww1_subs,0)
+nvl(zn.ww1_subs,0) +nvl(jt.ww1_subs,0) +
nvl(db.ww1_subs,0) +nvl(qy.ww1_subs,0) 合计_第一周数据,
----第二周
nvl(fh.ww2_subs,0) 放号_第二周数据,
nvl(dx.ww2_dx69,0) 等效69_第二周数据,
nvl(zd.ww2_subs,0) 信用购_第二周数据,
nvl(sd.ww2_subs,0) 升档套包_第二周数据,
nvl(xh.ww2_subs,0) 线盒新增_第二周数据,
--nvl(xhb.ww2_subs,0) 线盒核减_第二周数据,
nvl(zn.ww2_subs,0) 智能组网_第二周数据,
nvl(jt.ww2_subs,0) 家庭安防_第二周数据,
nvl(db.ww2_subs,0) 点播年包_第二周数据,
nvl(qy.ww2_subs,0) 权益彩铃_第二周数据,
nvl(fh.ww2_subs,0) +nvl(dx.ww2_dx69,0) +nvl(zd.ww2_subs,0) +nvl(sd.ww2_subs,0) +
nvl(xh.ww2_subs,0) -- -nvl(xhb.ww2_subs,0)
+nvl(zn.ww2_subs,0) +nvl(jt.ww2_subs,0) +
nvl(db.ww2_subs,0) +nvl(qy.ww2_subs,0) 合计_第二周数据,
----第三周
nvl(fh.ww3_subs,0) 放号_第三周数据,
nvl(dx.ww3_dx69,0) 等效69_第三周数据,
nvl(zd.ww3_subs,0) 信用购_第三周数据,
nvl(sd.ww3_subs,0) 升档套包_第三周数据,
nvl(xh.ww3_subs,0) 线盒新增_第三周数据,
--nvl(xhb.ww3_subs,0) 线盒核减_第三周数据,
nvl(zn.ww3_subs,0) 智能组网_第三周数据,
nvl(jt.ww3_subs,0) 家庭安防_第三周数据,
nvl(db.ww3_subs,0) 点播年包_第三周数据,
nvl(qy.ww3_subs,0) 权益彩铃_第三周数据,
nvl(fh.ww3_subs,0) +nvl(dx.ww3_dx69,0) +nvl(zd.ww3_subs,0) +nvl(sd.ww3_subs,0) +
nvl(xh.ww3_subs,0) -- -nvl(xhb.ww3_subs,0)
+nvl(zn.ww3_subs,0) +nvl(jt.ww3_subs,0) +
nvl(db.ww3_subs,0) +nvl(qy.ww3_subs,0) 合计_第三周数据,
----第四周
nvl(fh.ww4_subs,0) 放号_第四周数据,
nvl(dx.ww4_dx69,0) 等效69_第四周数据,
nvl(zd.ww4_subs,0) 信用购_第四周数据,
nvl(sd.ww4_subs,0) 升档套包_第四周数据,
nvl(xh.ww4_subs,0) 线盒新增_第四周数据,
--nvl(xhb.ww4_subs,0) 线盒核减_第四周数据,
nvl(zn.ww4_subs,0) 智能组网_第四周数据,
nvl(jt.ww4_subs,0) 家庭安防_第四周数据,
nvl(db.ww4_subs,0) 点播年包_第四周数据,
nvl(qy.ww4_subs,0) 权益彩铃_第四周数据,
nvl(fh.ww4_subs,0) +nvl(dx.ww4_dx69,0) +nvl(zd.ww4_subs,0) +nvl(sd.ww4_subs,0) +
nvl(xh.ww4_subs,0) -- -nvl(xhb.ww4_subs,0)
+nvl(zn.ww4_subs,0) +nvl(jt.ww4_subs,0) +
nvl(db.ww4_subs,0) +nvl(qy.ww4_subs,0) 合计_第四周数据,
----第五周
nvl(fh.ww5_subs,0) 放号_第五周数据,
nvl(dx.ww5_dx69,0) 等效69_第五周数据,
nvl(zd.ww5_subs,0) 信用购_第五周数据,
nvl(sd.ww5_subs,0) 升档套包_第五周数据,
nvl(xh.ww5_subs,0) 线盒新增_第五周数据,
--nvl(xhb.ww5_subs,0) 线盒核减_第五周数据,
nvl(zn.ww5_subs,0) 智能组网_第五周数据,
nvl(jt.ww5_subs,0) 家庭安防_第五周数据,
nvl(db.ww5_subs,0) 点播年包_第五周数据,
nvl(qy.ww5_subs,0) 权益彩铃_第五周数据,
nvl(fh.ww5_subs,0) +nvl(dx.ww5_dx69,0) +nvl(zd.ww5_subs,0) +nvl(sd.ww5_subs,0) +
nvl(xh.ww5_subs,0) -- -nvl(xhb.ww5_subs,0)
+nvl(zn.ww5_subs,0) +nvl(jt.ww5_subs,0) +
nvl(db.ww5_subs,0) +nvl(qy.ww5_subs,0) 合计_第五周数据
from shzc.zxb_qdcn_pgtx_qdtype_sja a,
shzc.zxb_qdcn_pgtx_qdtype_fhbhz fh,---放号
shzc.zxb_qdcn_pgtx_qdtype_dx69hz dx,---等效69
shzc.zxb_qdcn_pgtx_qdtype_zdhyhz zd,---信用购
shzc.zxb_qdcn_pgtx_qdtype_sdtbhz sd,---升档套包
shzc.zxb_qdcn_pgtx_qdtype_xhxzhz xh,---线盒新增
--shzc.zxb_qdcn_pgtx_qdtype_xhxzbhz xhb,---线盒新增 核减
shzc.zxb_qdcn_pgtx_qdtype_znzwhz zn,---智能组网
shzc.zxb_qdcn_pgtx_qdtype_jtafhz jt,---家庭安防
shzc.zxb_qdcn_pgtx_qdtype_dbnbhz db,---点播年包
shzc.zxb_qdcn_pgtx_qdtype_qyclhz qy ---权益彩铃
where a.渠道编码=fh.registerorgid(+)
and a.渠道编码=dx.registerorgid(+)
and a.渠道编码=zd.registerorgid(+)
and a.渠道编码=sd.registerorgid(+)
and a.渠道编码=xh.registerorgid(+)
--and a.渠道编码=xhb.registerorgid(+)
and a.渠道编码=zn.registerorgid(+)
and a.渠道编码=jt.registerorgid(+)
and a.渠道编码=db.registerorgid(+)
and a.渠道编码=qy.registerorgid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);
---每天渠道数据存档
SQL_STRING:='delete shzc.zxb_qdcn_pgtx_qdtype_bdcd where in_time = '''||v_monsr||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;
SQL_STRING:=' insert into shzc.zxb_qdcn_pgtx_qdtype_bdcd
select a.* from shzc.zxb_qdcn_pgtx_qdtype_hzbd a ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;
exception
--增加异常处理 提高代码健壮性
when others then
shc_add_exception('xxxxx', 'c0000972'); --异常处理
end;
20221118_数据库过程_渠道产能过程存档
最新推荐文章于 2023-02-15 06:55:17 发布