(P_DAY VARCHAR2 DEFAULT TO_CHAR(SYSDATE - 1,'yyyymmdd')) as
--- P_DAY 这个传入的是查询数据当月最后一天，也就是执行时是传入要看的呢个月月数据，比如202210输入20221031 就行
V_ZMLX VARCHAR2(50);
V_ZMLX_SZ VARCHAR2(20);
SQLSTMT0 varchar2(30000);
SQLSTMT1 varchar2(30000);
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_yesrq varchar2(4);
---明年
v_yesrm varchar2(4);
---本月最后一天
v_monsr_last varchar2(8);
v_monsr varchar2(6);
v_monsrq varchar2(6);
v_monsrh varchar2(6);
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);
---前一月与本月mm
v_monsrq_mm varchar2(2);
v_monsr_mm varchar2(2);
COLUMN_NUM number; ----表格字符数
num_xl number;
qx_name varchar2(10);
v_qx_name 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_yesrq :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-12),'yyyy') ;
v_yesrm :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),12),'yyyy') ;
v_monsr_last :=to_char(last_day(add_months(to_date(P_DAY,'yyyymmdd'),0)),'yyyymmdd') ;
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_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');--统计月
v_monsrq_mm :=substr(v_monsrq,5,2);
v_monsr_mm :=substr(v_monsr,5,2);
---分类汇总
zhyw.shc_drop_retable(upper('jzc_all_priv_sjqxqz_'||v_monsr),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_sjqxqz_'||v_monsr||' as
select ''q''qx_id,''合计''qx_name,count(distinct a.subsid) user_qn,sum(a.q12arpu) q12arpu_qn
from zhyw.jzc_all_priv_hmmxg a where a.lx='''||v_yesrq||''' and a.q12_zczy=1 ' ;
execute immediate (SQL_STRING);
------分区县
zhyw.shc_drop_retable(upper('jzc_all_priv_sjqxfh_'||v_monsr),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_sjqxfh_'||v_monsr||' as
select a.qx_id,a.qx_name,count(distinct a.subsid) user_qn,sum(a.q12arpu) q12arpu_qn
from zhyw.jzc_all_priv_hmmxg a where a.lx='''||v_yesrq||''' and a.q12_zczy=1
group by a.qx_id,a.qx_name
union all
select ''q''qx_id,''合计''qx_name,count(distinct a.subsid) user_qn,sum(a.q12arpu) q12arpu_qn
from zhyw.jzc_all_priv_hmmxg a where a.lx='''||v_yesrq||''' and a.q12_zczy=1 ';
execute immediate (SQL_STRING);
---前一月与本月mm
-- '||v_monsrq_mm||'
-- '||v_monsr_mm||' ''||v_monsr||''
---如果不是01月
if v_monsr_mm <> '01' then
zhyw.shc_drop_retable(upper('jzc_all_priv_sjaphz_'||v_monsr),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_sjaphz_'||v_monsr||' as
select '''||v_monsr||''' cycle,a.qx_id,a.qx_name,
a.b'||v_monsr_mm||'_arpu_dc arpu_dcid,
decode(a.b'||v_monsr_mm||'_arpu_dc,0,''[0,30)'',1,''[30,50)'',2,''[50,80)'',3,''[80,120)'',4,''120+'') arpu_dc,
0 全量客户,0 客户流动,0 去年客户情况,
count(case when a.lx='''||v_yesrq||''' and a.q12_zczy=1 then a.subsid end) qn_ql_zw_yh,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.q12_zczy=1 then a.q12arpu end,0)) qn_ql_zw_arpu,
0 今年客户到达情况,
count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end) all_dy_zw_yh,
count(case when a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.subsid end) all_dy_zw_dc_up_yh,
count(case when a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.subsid end) all_dy_zw_dc_dow_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end) qn_dy_zw_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.subsid end) qn_dy_zw_dc_up_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.subsid end) qn_dy_zw_dc_dow_yh,
count(case when to_char(a.createdate,''yyyymm'')='''||v_monsr||''' then a.subsid end) jn_rw_yh,
count(case when nvl(a.携入cycle,''0'')='''||v_monsr||''' then a.subsid end) jn_xr_yh,
count(case when a.b'||v_monsr_mm||'_dylw=1 then a.subsid end) jn_lw_yh,
count(case when nvl(a.携出cycle,''0'')='''||v_monsr||''' then a.subsid end) jn_xc_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_dylw=1 then a.subsid end) qn_pz_dy_lw_yh,
0 今年客户到达出账收入,
sum(nvl(case when a.lx in('''||v_yesrq||''','''||v_yesr||''') then a.b'||v_monsr_mm||'arpu end,0)) all_dy_zw_arpu,
sum(nvl(case when a.lx in('''||v_yesrq||''','''||v_yesr||''') and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.b'||v_monsr_mm||'arpu end,0)) all_dy_zw_dc_up_arpu,
sum(nvl(case when a.lx in('''||v_yesrq||''','''||v_yesr||''') and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.b'||v_monsr_mm||'arpu end,0)) all_dy_zw_dc_dow_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 then a.b'||v_monsr_mm||'arpu end,0)) qn_pz_jn_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.b'||v_monsr_mm||'arpu end,0)) qn_pz_jn_dc_up_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.b'||v_monsr_mm||'arpu end,0)) qn_pz_jn_dc_dow_arpu,
sum(nvl(case when to_char(a.createdate,''yyyymm'')='''||v_monsr||''' then a.b'||v_monsr_mm||'arpu end,0)) jn_rw_arpu,
sum(nvl(case when nvl(a.携入cycle,''0'')='''||v_monsr||''' then a.b'||v_monsr_mm||'arpu end,0)) jn_xr_arpu,
sum(nvl(case when a.b'||v_monsr_mm||'_dylw=1 then a.b'||v_monsrq_mm||'arpu end,0)) jn_lw_arpu,
sum(nvl(case when nvl(a.携出cycle,''0'')='''||v_monsr||''' then a.b'||v_monsrq_mm||'arpu end,0)) jn_xc_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_dylw=1 then a.b'||v_monsrq_mm||'arpu end,0)) qn_pz_lw_arpu,
0 业务渗透,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_宽带,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_kd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_家庭宽带,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_jtkd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_学校宽带,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_xykd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'_1000,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_qzkd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_企业宽带,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_qykd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_htv,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_htv_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_点播,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_dianb_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_编码_生态,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_shengt_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_智能组网,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_znzw_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_全屋智能,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_qwzn_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_家庭安防,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_jtaf_yh,
count(case when nvl(a.b'||v_monsr_mm||'_up,0)=1 then a.subsid end) all_arpu_up_yh,
count(case when nvl(a.b'||v_monsr_mm||'_dow,0)=1 then a.subsid end) all_arpu_low_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_编码_权益,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_quany_yh,
count(case when nvl(a.b'||v_monsr_mm||'yw_zdhy,0)>=1 then a.subsid end) all_zdhy_yh,
0 客户特征,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_jkw,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gjkw_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_zd,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gzd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_taob,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gtaob_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_wang,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gwang_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_rongh,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5grongh_yh,
count(case when nvl(a.b'||v_monsr_mm||'tz_oneth,0)=0 and nvl(a.b'||v_monsr_mm||'status,''0'') <>''0'' then a.subsid end) all_5goneth_yh,
to_char(sysdate,''yyyy/mm/dd hh24:mi:ss'') in_time
from zhyw.jzc_all_priv_hmmxg a
where a.createdate <to_date('''||v_monsrh||''' ,''yyyymm'')
group by a.b'||v_monsr_mm||'_arpu_dc,a.qx_id,a.qx_name
union all
select '''||v_monsr||''' cycle,''q''qx_id,''淄博'' qx_name,
a.b'||v_monsr_mm||'_arpu_dc arpu_dcid,
decode(a.b'||v_monsr_mm||'_arpu_dc,0,''[0,30)'',1,''[30,50)'',2,''[50,80)'',3,''[80,120)'',4,''120+'') arpu_dc,
0 全量客户,0 客户流动,0 去年客户情况,
count(case when a.lx='''||v_yesrq||''' and a.q12_zczy=1 then a.subsid end) qn_ql_zw_yh,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.q12_zczy=1 then a.q12arpu end,0)) qn_ql_zw_arpu,
0 今年客户到达情况,
count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end) all_dy_zw_yh,
count(case when a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.subsid end) all_dy_zw_dc_up_yh,
count(case when a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.subsid end) all_dy_zw_dc_dow_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end) qn_dy_zw_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.subsid end) qn_dy_zw_dc_up_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.subsid end) qn_dy_zw_dc_dow_yh,
count(case when to_char(a.createdate,''yyyymm'')='''||v_monsr||''' then a.subsid end) jn_rw_yh,
count(case when nvl(a.携入cycle,''0'')='''||v_monsr||''' then a.subsid end) jn_xr_yh,
count(case when a.b'||v_monsr_mm||'_dylw=1 then a.subsid end) jn_lw_yh,
count(case when nvl(a.携出cycle,''0'')='''||v_monsr||''' then a.subsid end) jn_xc_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_dylw=1 then a.subsid end) qn_pz_dy_lw_yh,
0 今年客户到达出账收入,
sum(nvl(case when a.lx in('''||v_yesrq||''','''||v_yesr||''') then a.b'||v_monsr_mm||'arpu end,0)) all_dy_zw_arpu,
sum(nvl(case when a.lx in('''||v_yesrq||''','''||v_yesr||''') and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.b'||v_monsr_mm||'arpu end,0)) all_dy_zw_dc_up_arpu,
sum(nvl(case when a.lx in('''||v_yesrq||''','''||v_yesr||''') and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.b'||v_monsr_mm||'arpu end,0)) all_dy_zw_dc_dow_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 then a.b'||v_monsr_mm||'arpu end,0)) qn_pz_jn_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.b'||v_monsr_mm||'arpu end,0)) qn_pz_jn_dc_up_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.b'||v_monsr_mm||'arpu end,0)) qn_pz_jn_dc_dow_arpu,
sum(nvl(case when to_char(a.createdate,''yyyymm'')='''||v_monsr||''' then a.b'||v_monsr_mm||'arpu end,0)) jn_rw_arpu,
sum(nvl(case when nvl(a.携入cycle,''0'')='''||v_monsr||''' then a.b'||v_monsr_mm||'arpu end,0)) jn_xr_arpu,
sum(nvl(case when a.b'||v_monsr_mm||'_dylw=1 then a.b'||v_monsrq_mm||'arpu end,0)) jn_lw_arpu,
sum(nvl(case when nvl(a.携出cycle,''0'')='''||v_monsr||''' then a.b'||v_monsrq_mm||'arpu end,0)) jn_xc_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_dylw=1 then a.b'||v_monsrq_mm||'arpu end,0)) qn_pz_lw_arpu,
0 业务渗透,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_宽带,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_kd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_家庭宽带,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_jtkd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_学校宽带,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_xykd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'_1000,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_qzkd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_企业宽带,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_qykd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_htv,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_htv_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_点播,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_dianb_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_编码_生态,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_shengt_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_智能组网,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_znzw_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_全屋智能,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_qwzn_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_家庭安防,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_jtaf_yh,
count(case when nvl(a.b'||v_monsr_mm||'_up,0)=1 then a.subsid end) all_arpu_up_yh,
count(case when nvl(a.b'||v_monsr_mm||'_dow,0)=1 then a.subsid end) all_arpu_low_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_编码_权益,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_quany_yh,
count(case when nvl(a.b'||v_monsr_mm||'yw_zdhy,0)>=1 then a.subsid end) all_zdhy_yh,
0 客户特征,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_jkw,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gjkw_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_zd,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gzd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_taob,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gtaob_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_wang,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gwang_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_rongh,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5grongh_yh,
count(case when nvl(a.b'||v_monsr_mm||'tz_oneth,0)=0 and nvl(a.b'||v_monsr_mm||'status,''0'') <>''0'' then a.subsid end) all_5goneth_yh,
to_char(sysdate,''yyyy/mm/dd hh24:mi:ss'') in_time
from zhyw.jzc_all_priv_hmmxg a
where a.createdate <to_date('''||v_monsrh||''' ,''yyyymm'')
group by a.b'||v_monsr_mm||'_arpu_dc ' ;
execute immediate (SQL_STRING);
zhyw.shc_drop_retable(upper('jzc_all_priv_sjaphz_'||v_monsr||'_jg'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_sjaphz_'||v_monsr||'_jg as
select a.cycle,
a.qx_id,
a.qx_name,
a.arpu_dcid,
a.arpu_dc,
a.全量客户,
a.客户流动,
a.去年客户情况,
a.qn_ql_zw_yh,
round(a.qn_ql_zw_yh/decode(nvl(b.user_qn,0),0,1,b.user_qn),6) qn_ql_zw_yh_zb,
a.qn_ql_zw_arpu,
round(a.qn_ql_zw_arpu/decode(nvl(b.q12arpu_qn,0),0,1,b.q12arpu_qn),6) qn_ql_zw_arpu_zb,
a.今年客户到达情况,
a.all_dy_zw_yh,
a.all_dy_zw_dc_up_yh,
a.all_dy_zw_dc_dow_yh,
a.qn_dy_zw_yh,
a.qn_dy_zw_dc_up_yh,
a.qn_dy_zw_dc_dow_yh,
a.jn_rw_yh,
a.jn_xr_yh,
a.jn_lw_yh,
a.jn_xc_yh,
a.qn_pz_dy_lw_yh,
a.今年客户到达出账收入,
a.all_dy_zw_arpu,
a.all_dy_zw_dc_up_arpu,
a.all_dy_zw_dc_dow_arpu,
a.qn_pz_jn_arpu,
a.qn_pz_jn_dc_up_arpu,
a.qn_pz_jn_dc_dow_arpu,
a.jn_rw_arpu,
a.jn_xr_arpu,
a.jn_lw_arpu,
a.jn_xc_arpu,
a.qn_pz_lw_arpu,
a.业务渗透,
a.all_kd_yh,
a.all_jtkd_yh,
a.all_xykd_yh,
a.all_qzkd_yh,
a.all_qykd_yh,
a.all_htv_yh,
a.all_dianb_yh,
a.all_shengt_yh,
a.all_znzw_yh,
a.all_qwzn_yh,
a.all_jtaf_yh,
a.all_arpu_up_yh,
a.all_arpu_low_yh,
a.all_quany_yh,
a.all_zdhy_yh,
a.客户特征,
a.all_5gjkw_yh,
a.all_5gzd_yh,
a.all_5gtaob_yh,
a.all_5gwang_yh,
a.all_5grongh_yh,
a.all_5goneth_yh,
a.in_time
from zhyw.jzc_all_priv_sjaphz_'||v_monsr||' a,
zhyw.jzc_all_priv_sjqxqz_'||v_monsr||' b ' ;
execute immediate (SQL_STRING);
----全量
zhyw.shc_drop_retable(upper('jzc_all_priv_sjqxhz_'||v_monsr),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_sjqxhz_'||v_monsr||' as
select '''||v_monsr||''' cycle,a.qx_id,a.qx_name,
0 全量客户,0 客户流动,0 去年客户情况,
count(case when a.lx='''||v_yesrq||''' and a.q12_zczy=1 then a.subsid end) qn_ql_zw_yh,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.q12_zczy=1 then a.q12arpu end,0)) qn_ql_zw_arpu,
0 今年客户到达情况,
count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end) all_dy_zw_yh,
count(case when a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.subsid end) all_dy_zw_dc_up_yh,
count(case when a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.subsid end) all_dy_zw_dc_dow_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end) qn_dy_zw_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.subsid end) qn_dy_zw_dc_up_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.subsid end) qn_dy_zw_dc_dow_yh,
count(case when to_char(a.createdate,''yyyymm'')='''||v_monsr||''' then a.subsid end) jn_rw_yh,
count(case when nvl(a.携入cycle,''0'')='''||v_monsr||''' then a.subsid end) jn_xr_yh,
count(case when a.b'||v_monsr_mm||'_dylw=1 then a.subsid end) jn_lw_yh,
count(case when nvl(a.携出cycle,''0'')='''||v_monsr||''' then a.subsid end) jn_xc_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_dylw=1 then a.subsid end) qn_pz_dy_lw_yh,
0 今年客户到达出账收入,
sum(nvl(case when a.lx in('''||v_yesrq||''','''||v_yesr||''') then a.b'||v_monsr_mm||'arpu end,0)) all_dy_zw_arpu,
sum(nvl(case when a.lx in('''||v_yesrq||''','''||v_yesr||''') and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.b'||v_monsr_mm||'arpu end,0)) all_dy_zw_dc_up_arpu,
sum(nvl(case when a.lx in('''||v_yesrq||''','''||v_yesr||''') and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.b'||v_monsr_mm||'arpu end,0)) all_dy_zw_dc_dow_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 then a.b'||v_monsr_mm||'arpu end,0)) qn_pz_jn_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.b'||v_monsr_mm||'arpu end,0)) qn_pz_jn_dc_up_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.b'||v_monsr_mm||'arpu end,0)) qn_pz_jn_dc_dow_arpu,
sum(nvl(case when to_char(a.createdate,''yyyymm'')='''||v_monsr||''' then a.b'||v_monsr_mm||'arpu end,0)) jn_rw_arpu,
sum(nvl(case when nvl(a.携入cycle,''0'')='''||v_monsr||''' then a.b'||v_monsr_mm||'arpu end,0)) jn_xr_arpu,
sum(nvl(case when a.b'||v_monsr_mm||'_dylw=1 then a.b'||v_monsrq_mm||'arpu end,0)) jn_lw_arpu,
sum(nvl(case when nvl(a.携出cycle,''0'')='''||v_monsr||''' then a.b'||v_monsrq_mm||'arpu end,0)) jn_xc_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_dylw=1 then a.b'||v_monsrq_mm||'arpu end,0)) qn_pz_lw_arpu,
0 业务渗透,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_宽带,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_kd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_家庭宽带,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_jtkd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_学校宽带,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_xykd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'_1000,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_qzkd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_企业宽带,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_qykd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_htv,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_htv_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_点播,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_dianb_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_编码_生态,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_shengt_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_智能组网,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_znzw_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_全屋智能,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_qwzn_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_家庭安防,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_jtaf_yh,
count(case when nvl(a.b'||v_monsr_mm||'_up,0)=1 then a.subsid end) all_arpu_up_yh,
count(case when nvl(a.b'||v_monsr_mm||'_dow,0)=1 then a.subsid end) all_arpu_low_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_编码_权益,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_quany_yh,
count(case when nvl(a.b'||v_monsr_mm||'yw_zdhy,0)>=1 then a.subsid end) all_zdhy_yh,
0 客户特征,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_jkw,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gjkw_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_zd,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gzd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_taob,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gtaob_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_wang,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gwang_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_rongh,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5grongh_yh,
count(case when nvl(a.b'||v_monsr_mm||'tz_oneth,0)=0 and nvl(a.b'||v_monsr_mm||'status,''0'') <>''0'' then a.subsid end) all_5goneth_yh,
to_char(sysdate,''yyyy/mm/dd hh24:mi:ss'') in_time
from zhyw.jzc_all_priv_hmmxg a
where a.createdate <to_date('''||v_monsrh||''' ,''yyyymm'')
group by a.qx_id,a.qx_name
union all
select '''||v_monsr||''' cycle,''q'' qx_id,''淄博''qx_name,
0 全量客户,0 客户流动,0 去年客户情况,
count(case when a.lx='''||v_yesrq||''' and a.q12_zczy=1 then a.subsid end) qn_ql_zw_yh,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.q12_zczy=1 then a.q12arpu end,0)) qn_ql_zw_arpu,
0 今年客户到达情况,
count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end) all_dy_zw_yh,
count(case when a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.subsid end) all_dy_zw_dc_up_yh,
count(case when a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.subsid end) all_dy_zw_dc_dow_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end) qn_dy_zw_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.subsid end) qn_dy_zw_dc_up_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.subsid end) qn_dy_zw_dc_dow_yh,
count(case when to_char(a.createdate,''yyyymm'')='''||v_monsr||''' then a.subsid end) jn_rw_yh,
count(case when nvl(a.携入cycle,''0'')='''||v_monsr||''' then a.subsid end) jn_xr_yh,
count(case when a.b'||v_monsr_mm||'_dylw=1 then a.subsid end) jn_lw_yh,
count(case when nvl(a.携出cycle,''0'')='''||v_monsr||''' then a.subsid end) jn_xc_yh,
count(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_dylw=1 then a.subsid end) qn_pz_dy_lw_yh,
0 今年客户到达出账收入,
sum(nvl(case when a.lx in('''||v_yesrq||''','''||v_yesr||''') then a.b'||v_monsr_mm||'arpu end,0)) all_dy_zw_arpu,
sum(nvl(case when a.lx in('''||v_yesrq||''','''||v_yesr||''') and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.b'||v_monsr_mm||'arpu end,0)) all_dy_zw_dc_up_arpu,
sum(nvl(case when a.lx in('''||v_yesrq||''','''||v_yesr||''') and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.b'||v_monsr_mm||'arpu end,0)) all_dy_zw_dc_dow_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 then a.b'||v_monsr_mm||'arpu end,0)) qn_pz_jn_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_up=1 then a.b'||v_monsr_mm||'arpu end,0)) qn_pz_jn_dc_up_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_zczy=1 and a.b'||v_monsr_mm||'_arpu_dc_dow=1 then a.b'||v_monsr_mm||'arpu end,0)) qn_pz_jn_dc_dow_arpu,
sum(nvl(case when to_char(a.createdate,''yyyymm'')='''||v_monsr||''' then a.b'||v_monsr_mm||'arpu end,0)) jn_rw_arpu,
sum(nvl(case when nvl(a.携入cycle,''0'')='''||v_monsr||''' then a.b'||v_monsr_mm||'arpu end,0)) jn_xr_arpu,
sum(nvl(case when a.b'||v_monsr_mm||'_dylw=1 then a.b'||v_monsrq_mm||'arpu end,0)) jn_lw_arpu,
sum(nvl(case when nvl(a.携出cycle,''0'')='''||v_monsr||''' then a.b'||v_monsrq_mm||'arpu end,0)) jn_xc_arpu,
sum(nvl(case when a.lx='''||v_yesrq||''' and a.b'||v_monsr_mm||'_dylw=1 then a.b'||v_monsrq_mm||'arpu end,0)) qn_pz_lw_arpu,
0 业务渗透,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_宽带,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_kd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_家庭宽带,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_jtkd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_学校宽带,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_xykd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'_1000,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_qzkd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_企业宽带,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_qykd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_htv,0)=1 and a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_htv_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_点播,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_dianb_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_编码_生态,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_shengt_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_智能组网,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_znzw_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_全屋智能,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_qwzn_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_家庭安防,0)=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_jtaf_yh,
count(case when nvl(a.b'||v_monsr_mm||'_up,0)=1 then a.subsid end) all_arpu_up_yh,
count(case when nvl(a.b'||v_monsr_mm||'_dow,0)=1 then a.subsid end) all_arpu_low_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'yw_编码_权益,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_quany_yh,
count(case when nvl(a.b'||v_monsr_mm||'yw_zdhy,0)>=1 then a.subsid end) all_zdhy_yh,
0 客户特征,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_jkw,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gjkw_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_zd,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gzd_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_taob,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gtaob_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_wang,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5gwang_yh,
round(count(case when nvl(a.b'||v_monsr_mm||'tz_rongh,0)>=1 then a.subsid end)/decode(count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end),0,1,count(case when a.b'||v_monsr_mm||'_zczy=1 then a.subsid end)),6) all_5grongh_yh,
count(case when nvl(a.b'||v_monsr_mm||'tz_oneth,0)=0 and nvl(a.b'||v_monsr_mm||'status,''0'') <>''0'' then a.subsid end) all_5goneth_yh,
to_char(sysdate,''yyyy/mm/dd hh24:mi:ss'') in_time
from zhyw.jzc_all_priv_hmmxg a
where a.createdate <to_date('''||v_monsrh||''' ,''yyyymm'') ';
execute immediate (SQL_STRING);
----------------------------------------------循环添加
---jzc_all_priv_sjaphz_'||v_monsr||'_jg
-----arpu分档次 arpu_dcid排序防止数据顺序问题
zhyw.shc_drop_retable(upper('jzc_all_priv_sjaphz_zzq'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_sjaphz_zzq as
select * from
(select a.*,row_number() over (partition by a.cycle,a.qx_id,a.arpu_dcid order by a.in_time desc ) 排名
from zhyw.jzc_all_priv_sjaphz_'||v_monsr||'_jg a
where nvl(a.arpu_dcid,9) not in (9)
order by a.qx_id,a.arpu_dcid ) a
where a.排名=1 ';
EXECUTE IMMEDIATE (SQL_STRING);
-----获取表头按照 COLUMN_ID 顺序
zhyw.shc_drop_retable(upper('jzc_all_priv_sjaphz_zzqml'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_sjaphz_zzqml as
select a.COLUMN_NAME from ALL_TAB_COLUMNS a where a.TABLE_NAME =upper(''jzc_all_priv_sjaphz_zzq'')
and a.OWNER=''ZHYW'' and a.COLUMN_ID>=2
and a.COLUMN_NAME not in (''QX_ID'',''QX_NAME'',''ARPU_DC'',''IN_TIME'',''排名'')
order by COLUMN_ID ';
EXECUTE IMMEDIATE (SQL_STRING);
---COLUMN_NUM，增加 cycle，name，in_time，num_xl，COLUMN_NAME
select count(distinct a.arpu_dcid)+5 into COLUMN_NUM from zhyw.jzc_all_priv_sjaphz_zzq a;
----获取表头数量，然后创建一个全字符的表格
zhyw.shc_drop_retable(upper('jzc_all_priv_sjaphz_zzq_tab'),'ZHYW');
---清空后创建
SQLSTMT0 := 'create table zhyw.jzc_all_priv_sjaphz_zzq_tab ( ';
FOR a IN (select level num from dual connect by level<=(COLUMN_NUM) ) LOOP
SQLSTMT0 := SQLSTMT0 || 'COLUMN'||a.num||' varchar2(40),';
END LOOP;
SQLSTMT1 := SUBSTR(SQLSTMT0 , 1 , length( SQLSTMT0 )-1) ;
SQLSTMT1 :=SQLSTMT1 || ')';
EXECUTE IMMEDIATE (SQLSTMT1);
----获取区县名称，分别存储
zhyw.shc_drop_retable(upper('jzc_all_priv_sjaphz_zzq_qx'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_sjaphz_zzq_qx as
select a.qx_id,a.qx_name from zhyw.jzc_all_priv_sjaphz_zzq a group by a.qx_id,a.qx_name
order by a.qx_id ';
EXECUTE IMMEDIATE (SQL_STRING);
---清空当月数据
SQL_STRING:='delete zhyw.jzc_all_priv_sjaphz_zzq_bd where cycle='''||v_monsr||''' ' ;
execute immediate (SQL_STRING);
commit;
FOR c IN (select qx_name from zhyw.jzc_all_priv_sjaphz_zzq_qx ) LOOP -----外循环开始
-----变量声明
num_xl := 0;
FOR d IN (select COLUMN_NAME from zhyw.jzc_all_priv_sjaphz_zzqml ) LOOP-----内循环开始
SQL_STRING := 'insert into zhyw.jzc_all_priv_sjaphz_zzq_bd
select '''||v_monsr||''' cycle,'''||c.qx_name||''' name,
zhyw.shzc_zfc_zftq_tscl(a.note,''^1^'',''&'') note1,
zhyw.shzc_zfc_zftq_tscl(a.note,''^2^'',''&'') note2,
zhyw.shzc_zfc_zftq_tscl(a.note,''^3^'',''&'') note3,
zhyw.shzc_zfc_zftq_tscl(a.note,''^4^'',''&'') note4,
zhyw.shzc_zfc_zftq_tscl(a.note,''^5^'',''&'') note5,
sysdate in_time,'''||num_xl||''' num_xl,'''||d.column_name||'''COLUMN_NAME
from
(select listagg (''^''||a.排名||''^''||a.'||d.column_name||',''&'') within group (order by rownum) note
from
(select a.'||d.column_name||', row_number() over (partition by 1 order by rownum ) 排名
from zhyw.jzc_all_priv_sjaphz_zzq a where a.qx_name='''||c.qx_name||'''
order by a.arpu_dcid ) a
group by 1) a ';
execute immediate (SQL_STRING);
commit;
num_xl := num_xl + 1; ----num_xl 增加 1
END LOOP;-----内循环结束
END LOOP;-----外循环结束
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
