20230215_数据库过程_高质量发展

最新推荐文章于 2023-02-15 06:55:17 发布

szc_1985

最新推荐文章于 2023-02-15 06:55:17 发布

阅读量458

点赞数

分类专栏：数据库办公记录语句文章标签：数据库 sql

本文链接：https://blog.csdn.net/szc_1985/article/details/129036390

版权

数据库办公记录语句专栏收录该内容

40 篇文章 1 订阅

订阅专栏

高质量发展

—一、运营结果

SQL_STRING:=‘delete shzc.np_rec_lnpdb a
where exists (select * from tbcs.v_np_rec_lnpdb@bcv t where a.telnum=t.telnum
and a.outcarrier=t.OUTCARRIER
and a.incarrier=t.INCARRIER
and a.owncarrier=t.OWNCARRIER
and a.starttime=t.STARTTIME
and a.status<>t.status )’;
EXECUTE IMMEDIATE (SQL_STRING);
commit;

SQL_STRING:=‘insert into shzc.np_rec_lnpdb
select t.region,t.telnum,zhyw.shzc_hdpd_tscl(t.telnum) 号码资源情况,
t.OUTCARRIER,decode(substr(t.OUTCARRIER,1,3),’‘001’‘,’‘电信’‘,’‘002’‘,’‘移动’‘,’‘003’‘,’‘联通’‘) 携出运营商,
t.INCARRIER,decode(substr(t.INCARRIER,1,3),’‘001’‘,’‘电信’‘,’‘002’‘,’‘移动’‘,’‘003’‘,’‘联通’‘) 携入运营商,
t.OWNCARRIER,decode(substr(t.OWNCARRIER,1,3),’‘001’‘,’‘电信’‘,’‘002’‘,’‘移动’‘,’‘003’‘,’‘联通’‘) 归属运营商,
t.STARTTIME,t.ENDTIME,t.TRANSTYPE,t.STATUS,t.ISNEW,t.AUDITTIME
from tbcs.v_np_rec_lnpdb@bcv t
where not exists (select * from shzc.np_rec_lnpdb a where a.telnum=t.telnum
and a.outcarrier=t.OUTCARRIER
and a.incarrier=t.INCARRIER
and a.owncarrier=t.OWNCARRIER
and a.starttime=t.STARTTIME)’;
EXECUTE IMMEDIATE (SQL_STRING);
commit;

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx as
select a.region,a.telnum,a.携出运营商,a.携入运营商,a.归属运营商,a.starttime,a.endtime,a.transtype,a.status,a.isnew,
nvl(case when a.携入运营商=’‘移动’’ then ‘‘携入’’ when a.携出运营商=‘‘移动’’ then ‘‘携出’’ end,‘‘其他’’) 类型
from shzc.np_rec_lnpdb a
where a.region=‘‘533’’
and a.starttime-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’)';
EXECUTE IMMEDIATE (SQL_STRING);

—携入状态时间与开户时间最近

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xr’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xr as
select * from
(select a.subsid,a.nettype,a.servnumber,a.prodid,a.createdate,a.status,a.statusdate,b.starttime,
row_number() over (partition by a.servnumber,b.starttime order by a.createdate ) 排名
from zhyw.subscriber a,
(select distinct b.telnum,b.starttime from shzc.sm_gzl_gzl_hmmx b where b.类型=’‘携入’‘) b
where a.servnumber=b.telnum
and to_char(a.createdate,’‘yyyymmdd’‘)>=to_char(b.starttime,’‘yyyymmdd’‘)) a
where 排名=1’;
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xra’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xra as
select b.*,a.subsid, a.nettype,a.servnumber,a.prodid,a.createdate,a.status,
a.statusdate,a.排名
from
(select distinct b.telnum,b.starttime,b.类型 from shzc.sm_gzl_gzl_hmmx b where b.类型=’‘携入’‘) b,
shzc.sm_gzl_gzl_hmmx_xr a
where b.telnum=a.servnumber(+)
and b.starttime=a.starttime(+)’;
EXECUTE IMMEDIATE (SQL_STRING);

----写入本地存档----留档
SQL_STRING:=‘insert into shzc.sm_gzl_gzl_hmmx_xrbd
select * from shzc.sm_gzl_gzl_hmmx_xra a
where not exists ( select 1 from shzc.sm_gzl_gzl_hmmx_xrbd b
where b.telnum=a.telnum
and b.subsid=a.subsid
and b.starttime=a.starttime)’;
EXECUTE IMMEDIATE (SQL_STRING);
commit;

—携出状态时间与开户时间最近

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xc’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xc as
select * from
(select a.subsid,a.nettype,a.servnumber,a.prodid,a.createdate,a.status,a.statusdate,b.starttime,
row_number() over (partition by a.servnumber,b.starttime order by a.createdate desc,STATUSDATE desc ) 排名
from zhyw.subscriber a,
(select distinct b.telnum,b.starttime from shzc.sm_gzl_gzl_hmmx b where b.类型=’‘携出’‘) b
where a.servnumber=b.telnum
and to_char(a.createdate,’‘yyyymmdd’‘)<=to_char(b.starttime,’‘yyyymmdd’‘)) a
where 排名=1’;
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xca’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xca as
select b.*,a.subsid, a.nettype,a.servnumber,a.prodid,a.createdate,a.status,
a.statusdate,a.排名
from
(select distinct b.telnum,b.starttime,b.类型 from shzc.sm_gzl_gzl_hmmx b where b.类型=’‘携出’‘) b,
shzc.sm_gzl_gzl_hmmx_xc a
where b.telnum=a.servnumber(+)
and b.starttime=a.starttime(+)’;
EXECUTE IMMEDIATE (SQL_STRING);

----写入本地存档----留档
SQL_STRING:=‘insert into shzc.sm_gzl_gzl_hmmx_xcbd
select * from shzc.sm_gzl_gzl_hmmx_xca a
where not exists ( select 1 from shzc.sm_gzl_gzl_hmmx_xcbd b
where b.telnum=a.telnum
and b.subsid=a.subsid
and b.starttime=a.starttime)’;
EXECUTE IMMEDIATE (SQL_STRING);
commit;

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhz’),‘SHZC’);
SQL_STRING:='create table shzc.sm_gzl_gzl_hmmx_xzhz as
select * from shzc.sm_gzl_gzl_hmmx_xrbd a
union all
select * from shzc.sm_gzl_gzl_hmmx_xcbd a ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhm as
select a.region,a.telnum,a.携出运营商,a.携入运营商,a.starttime,a.transtype,a.类型,
b.subsid,b.nettype,b.servnumber,b.prodid,b.createdate,b.status,b.statusdate,c.ownerorgid,c.registerorgid,
ceil(nvl(case when b.status=’‘US10’’ then to_date(to_char(a.starttime,‘‘yyyymm’’)||substr(‘’‘||P_DAY||’‘’,7,2),‘‘yyyymmdd’’) + 1 end,b.statusdate)-b.createdate) 网龄天数,
nvl(case when nvl(substr(c.ownerorgid,8,1),‘‘0’’) not in (select t.county_id from zhyw.rpt_county t
where t.county_id not in (‘‘0’’,‘‘A’’,‘‘zbtyqd’’)) then ‘‘x’’ end,substr(c.ownerorgid,8,1)) qx,
nvl(case when nvl(b.createdate,sysdate) > sysdate - 31 then 1 end ,0) 近期入网
from shzc.sm_gzl_gzl_hmmx a,
shzc.sm_gzl_gzl_hmmx_xzhz b,
zhyw.subscriber c
where a.类型 in (‘‘携入’’,‘‘携出’’)
and b.subsid=c.subsid(+)
and a.telnum=b.telnum(+)
and a.starttime=b.starttime(+)
and a.类型=b.类型(+)
and to_char(a.starttime,‘‘yyyymm’’)>=
to_char(add_months(to_date(substr(‘’‘||v_monsr||’‘’,1,6),‘‘yyyymm’’),-3),‘‘yyyymm’’)';
EXECUTE IMMEDIATE (SQL_STRING);

----携入网龄按照过网时间

----携入会增加本月发展号码，所以需要插入上月底过网情况与截至昨天的过网情况
SQL_STRING:=‘insert into shzc.sm_gzl_gzl_hmmx_xrgw_day
select d.op_time,d.comp_product_no,to_date(d.open_date3,’‘yyyy-mm-dd’‘) open_date3,
to_date(d.last_date3,’‘yyyy-mm-dd’‘) last_date3,to_number(d.net_age) net_age
from zibo.DW_USER_NET_AGE_’||v_last_day3||’ d,
(select distinct b.telnum from shzc.sm_gzl_gzl_hmmx_xzhm b where b.类型=‘‘携入’’) b
where d.comp_product_no=b.telnum
and not exists (select 1 from shzc.sm_gzl_gzl_hmmx_xrgw_day t
where t.comp_product_no=d.comp_product_no
and t.open_date3=to_date(d.open_date3,‘‘yyyy-mm-dd’’))
union all
select d.op_time,d.comp_product_no,to_date(d.open_date3,‘‘yyyy-mm-dd’’) open_date3,
to_date(d.last_date3,‘‘yyyy-mm-dd’’) last_date3,to_number(d.net_age) net_age
from zibo.DW_USER_NET_AGE_‘||v_last_day2||’ d,
(select distinct b.telnum from shzc.sm_gzl_gzl_hmmx_xzhm b where b.类型=‘‘携入’’) b
where d.comp_product_no=b.telnum
and not exists (select 1 from shzc.sm_gzl_gzl_hmmx_xrgw_day t
where t.comp_product_no=d.comp_product_no
and t.open_date3=to_date(d.open_date3,‘‘yyyy-mm-dd’’))
union all
select d.op_time,d.comp_product_no,to_date(d.open_date3,‘‘yyyy-mm-dd’’) open_date3,
to_date(d.last_date3,‘‘yyyy-mm-dd’’) last_date3,to_number(d.net_age) net_age
from zibo.DW_USER_NET_AGE_‘||v_last_day||’ d,
(select distinct b.telnum from shzc.sm_gzl_gzl_hmmx_xzhm b where b.类型=‘‘携入’’) b
where d.comp_product_no=b.telnum
and not exists (select 1 from shzc.sm_gzl_gzl_hmmx_xrgw_day t
where t.comp_product_no=d.comp_product_no
and t.open_date3=to_date(d.open_date3,‘‘yyyy-mm-dd’’))
union all
select d.op_time,d.comp_product_no,to_date(d.open_date3,‘‘yyyy-mm-dd’’) open_date3,
to_date(d.last_date3,‘‘yyyy-mm-dd’’) last_date3,to_number(d.net_age) net_age
from zibo.DW_USER_NET_AGE_‘||P_DAY||’ d,
(select distinct b.telnum from shzc.sm_gzl_gzl_hmmx_xzhm b where b.类型=‘‘携入’’) b
where d.comp_product_no=b.telnum
and not exists (select 1 from shzc.sm_gzl_gzl_hmmx_xrgw_day t
where t.comp_product_no=d.comp_product_no
and t.open_date3=to_date(d.open_date3,‘‘yyyy-mm-dd’’)) ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

-----他网号码最早过网时间

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xrgw_daya’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xrgw_daya as
select a.comp_product_no,min(a.open_date3) open_date3 from shzc.sm_gzl_gzl_hmmx_xrgw_day a
group by a.comp_product_no’;
EXECUTE IMMEDIATE (SQL_STRING);

----携入携出增加号码划归a/b/c1/c2/d 类情况。
—近三个月集团成员情况

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xrgw_jthf’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xrgw_jthf as
select ‘’’||v_monsrq2||‘’’ cycle,a.* from
(select a.custid,a.集团编号,a.集团名称,集团类型,a.subsid,a.servnumber,a.加入集团时间,
nvl(case when upper(substr(集团类型,1,1))=‘‘A’’ then ‘‘A’’
when upper(substr(集团类型,1,1))=‘‘B’’ then ‘‘B’’
when upper(substr(集团类型,1,1))=‘‘D’’ then ‘‘D’’ end,upper(substr(集团类型,1,2))) 集团类型结果,
row_number() over (partition by a.subsid order by a.加入集团时间 desc ) 排名
from zhyw.qcy_busscustsubs_‘||v_monsrq2||’ a,
(select distinct b.subsid from shzc.sm_gzl_gzl_hmmx_xzhm b ) b
where a.subsid=b.subsid) a
where a.排名=1
union all
select ‘’‘||v_monsrq||’‘’ cycle,a.* from
(select a.custid,a.集团编号,a.集团名称,集团类型,a.subsid,a.servnumber,a.加入集团时间,
nvl(case when upper(substr(集团类型,1,1))=‘‘A’’ then ‘‘A’’
when upper(substr(集团类型,1,1))=‘‘B’’ then ‘‘B’’
when upper(substr(集团类型,1,1))=‘‘D’’ then ‘‘D’’ end,upper(substr(集团类型,1,2))) 集团类型结果,
row_number() over (partition by a.subsid order by a.加入集团时间 desc ) 排名
from zhyw.qcy_busscustsubs_‘||v_monsrq||’ a,
(select distinct b.subsid from shzc.sm_gzl_gzl_hmmx_xzhm b ) b
where a.subsid=b.subsid) a
where a.排名=1
union all
select ‘’‘||v_monsr||’‘’ cycle,a.* from
(select a.custid,a.集团编号,a.集团名称,集团类型,a.subsid,a.servnumber,a.加入集团时间,
nvl(case when upper(substr(集团类型,1,1))=‘‘A’’ then ‘‘A’’
when upper(substr(集团类型,1,1))=‘‘B’’ then ‘‘B’’
when upper(substr(集团类型,1,1))=‘‘D’’ then ‘‘D’’ end,upper(substr(集团类型,1,2))) 集团类型结果,
row_number() over (partition by a.subsid order by a.加入集团时间 desc ) 排名
from zhyw.qcy_busscustsubs_‘||v_monsr||’ a,
(select distinct b.subsid from shzc.sm_gzl_gzl_hmmx_xzhm b ) b
where a.subsid=b.subsid) a
where a.排名=1’;
EXECUTE IMMEDIATE (SQL_STRING);

SQL_STRING:=‘delete shzc.sm_gzl_gzl_hmmx_xrgw_jthf_bd a where a.cycle=’‘’||v_monsr||‘’’ ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

SQL_STRING:='insert into shzc.sm_gzl_gzl_hmmx_xrgw_jthf_bd
select * from shzc.sm_gzl_gzl_hmmx_xrgw_jthf a
where not exists (select 1 from shzc.sm_gzl_gzl_hmmx_xrgw_jthf_bd t where t.cycle=a.cycle and t.subsid=a.subsid) ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

----取号码最后一个月最后一次的记录

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xrgw_jthfa’),‘SHZC’);
SQL_STRING:='create table shzc.sm_gzl_gzl_hmmx_xrgw_jthfa as
select * from
(select a.*,row_number() over (partition by a.subsid order by a.cycle desc,a.加入集团时间 desc ) z排名
from shzc.sm_gzl_gzl_hmmx_xrgw_jthf_bd a) a
where z排名=1 ';
EXECUTE IMMEDIATE (SQL_STRING);

----发展客户 ARPU 指 12 月发展客户在 1 月实际收入，流失客户 Arpu 指 2 月流失客户在 1 月实际收入；
----更新网龄天数，增加上月arpu,携入的按照当月现在时间计算，不然后续不好环比

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhmx’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhmx as
select a.*,b.open_date3,
ceil(nvl(case when a.status=’‘US10’’ then to_date(to_char(a.starttime,‘‘yyyymm’’)||substr(‘’‘||P_DAY||’‘’,7,2),‘‘yyyymmdd’’) + 1 end,a.statusdate)-
nvl(case when a.类型=‘‘携入’‘then nvl(b.open_date3,a.createdate) end,a.createdate)) 新网龄天数,
nvl(cs.arpu,0) arpu_sy,
nvl(c.arpu,0) arpu,
nvl(d.集团类型结果,’‘0’’) 集团类型结果,d.集团编号,d.集团名称,d.加入集团时间
from shzc.sm_gzl_gzl_hmmx_xzhm a,
shzc.sm_gzl_gzl_hmmx_xrgw_daya b,
zibo.Dw_product_‘||v_monsrq2||’ cs,
zibo.Dw_product_‘||v_monsrq||’ c,
shzc.sm_gzl_gzl_hmmx_xrgw_jthfa d
where a.telnum=b.comp_product_no(+)
and a.subsid=cs.user_id(+)
and a.subsid=c.user_id(+)
and a.subsid=d.subsid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

----增加区县编码与业务时间主要基础表+++++

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhma’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhma as
select a.*,nvl(t.jf_county_id,’‘1008’‘) jf_county_id,nvl(t.name,’‘线上’‘) name,
to_char(a.starttime,’‘yyyymmdd’') startday
from shzc.sm_gzl_gzl_hmmx_xzhmx a,
zhyw.rpt_county t
where a.qx=t.county_id(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

----第一个表生成数据

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xz_hz’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xz_hz as
select ‘’’||p_day||‘’’ cycle,a.qx,a.jf_county_id,a.name,
count(distinct case when a.类型=‘‘携入’’ and a.startday=‘’‘||p_day||’‘’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 日携入量,
count(distinct case when a.类型=‘‘携入’’ and a.startday=‘’‘||p_day||’‘’ and a.携出运营商=‘‘联通’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 日携入量_联通,
count(distinct case when a.类型=‘‘携入’’ and a.startday=‘’‘||p_day||’‘’ and a.携出运营商=‘‘电信’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 日携入量_电信,

count(distinct case when a.类型=‘‘携出’’ and a.startday=‘’‘||p_day||’‘’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 日携出量,
count(distinct case when a.类型=‘‘携出’’ and a.startday=‘’‘||p_day||’‘’ and a.携入运营商=‘‘联通’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 日携出量_联通,
count(distinct case when a.类型=‘‘携出’’ and a.startday=‘’‘||p_day||’‘’ and a.携入运营商=‘‘电信’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 日携出量_电信,

count(distinct case when a.类型=‘‘携入’’ and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 月携入量,
count(distinct case when a.类型=‘‘携入’’ and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.携出运营商=‘‘联通’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 月携入量_联通,
count(distinct case when a.类型=‘‘携入’’ and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.携出运营商=‘‘电信’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 月携入量_电信,

count(distinct case when a.类型=‘‘携出’’ and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 月携出量,
count(distinct case when a.类型=‘‘携出’’ and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.携入运营商=‘‘联通’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 月携出量_联通,
count(distinct case when a.类型=‘‘携出’’ and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.携入运营商=‘‘电信’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 月携出量_电信,

count(distinct case when a.类型=‘‘携入’’ and substr(a.startday,1,4)=‘’‘||v_yesr||’‘’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 年携入量,
count(distinct case when a.类型=‘‘携入’’ and substr(a.startday,1,4)=‘’‘||v_yesr||’‘’ and a.携出运营商=‘‘联通’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 年携入量_联通,
count(distinct case when a.类型=‘‘携入’’ and substr(a.startday,1,4)=‘’‘||v_yesr||’‘’ and a.携出运营商=‘‘电信’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 年携入量_电信,

count(distinct case when a.类型=‘‘携出’’ and substr(a.startday,1,4)=‘’‘||v_yesr||’‘’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 年携出量,
count(distinct case when a.类型=‘‘携出’’ and substr(a.startday,1,4)=‘’‘||v_yesr||’‘’ and a.携入运营商=‘‘联通’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 年携出量_联通,
count(distinct case when a.类型=‘‘携出’’ and substr(a.startday,1,4)=‘’‘||v_yesr||’‘’ and a.携入运营商=‘‘电信’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 年携出量_电信,
‘‘分割线’’ fen,-----------------------------------------------------------------------------
count(distinct case when a.类型=‘‘携入’’ and a.新网龄天数>=31 and a.startday=‘’‘||p_day||’‘’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_日携入量,
count(distinct case when a.类型=‘‘携入’’ and a.新网龄天数>=31 and a.startday=‘’‘||p_day||’‘’ and a.携出运营商=‘‘联通’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_日携入量_联通,
count(distinct case when a.类型=‘‘携入’’ and a.新网龄天数>=31 and a.startday=‘’‘||p_day||’‘’ and a.携出运营商=‘‘电信’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_日携入量_电信,

count(distinct case when a.类型=‘‘携出’’ and a.新网龄天数>=31 and a.startday=‘’‘||p_day||’‘’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_日携出量,
count(distinct case when a.类型=‘‘携出’’ and a.新网龄天数>=31 and a.startday=‘’‘||p_day||’‘’ and a.携入运营商=‘‘联通’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_日携出量_联通,
count(distinct case when a.类型=‘‘携出’’ and a.新网龄天数>=31 and a.startday=‘’‘||p_day||’‘’ and a.携入运营商=‘‘电信’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_日携出量_电信,

count(distinct case when a.类型=‘‘携入’’ and a.新网龄天数>=31 and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_月携入量,
count(distinct case when a.类型=‘‘携入’’ and a.新网龄天数>=31 and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.携出运营商=‘‘联通’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_月携入量_联通,
count(distinct case when a.类型=‘‘携入’’ and a.新网龄天数>=31 and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.携出运营商=‘‘电信’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_月携入量_电信,

count(distinct case when a.类型=‘‘携出’’ and a.新网龄天数>=31 and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_月携出量,
count(distinct case when a.类型=‘‘携出’’ and a.新网龄天数>=31 and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.携入运营商=‘‘联通’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_月携出量_联通,
count(distinct case when a.类型=‘‘携出’’ and a.新网龄天数>=31 and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.携入运营商=‘‘电信’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_月携出量_电信,

count(distinct case when a.类型=‘‘携入’’ and a.新网龄天数>=31 and substr(a.startday,1,4)=‘’‘||v_yesr||’‘’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_年携入量,
count(distinct case when a.类型=‘‘携入’’ and a.新网龄天数>=31 and substr(a.startday,1,4)=‘’‘||v_yesr||’‘’ and a.携出运营商=‘‘联通’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_年携入量_联通,
count(distinct case when a.类型=‘‘携入’’ and a.新网龄天数>=31 and substr(a.startday,1,4)=‘’‘||v_yesr||’‘’ and a.携出运营商=‘‘电信’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_年携入量_电信,

count(distinct case when a.类型=‘‘携出’’ and a.新网龄天数>=31 and substr(a.startday,1,4)=‘’‘||v_yesr||’‘’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_年携出量,
count(distinct case when a.类型=‘‘携出’’ and a.新网龄天数>=31 and substr(a.startday,1,4)=‘’‘||v_yesr||’‘’ and a.携入运营商=‘‘联通’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_年携出量_联通,
count(distinct case when a.类型=‘‘携出’’ and a.新网龄天数>=31 and substr(a.startday,1,4)=‘’‘||v_yesr||’‘’ and a.携入运营商=‘‘电信’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 非低网龄_年携出量_电信
from shzc.sm_gzl_gzl_hmmx_xzhma a
group by a.qx,a.jf_county_id,a.name
union all
select ‘’‘||p_day||’‘’ cycle,‘‘q’’ qx,‘‘999’’ jf_county_id,‘‘全市’’ name,
count(distinct case when a.类型=‘‘携入’’ and a.startday=‘’‘||p_day||’‘’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 日携入量,
count(distinct case when a.类型=‘‘携入’’ and a.startday=‘’‘||p_day||’‘’ and a.携出运营商=‘‘联通’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 日携入量_联通,
count(distinct case when a.类型=‘‘携入’’ and a.startday=‘’‘||p_day||’‘’ and a.携出运营商=‘‘电信’’ then a.telnum||a.subsid||a.类型||a.携出运营商||a.携入运营商||a.starttime end) 日携入量_电信,

-------结果1

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xz_qxhz’),‘SHZC’);
SQL_STRING:='create table shzc.sm_gzl_gzl_hmmx_xz_qxhz as
select a.cycle,a.qx,a.jf_county_id,a.name,
a.非低网龄_日携入量,a.非低网龄_日携出量,round(a.非低网龄_日携入量/decode(a.非低网龄_日携出量,0,1,a.非低网龄_日携出量),2) 非低网龄_日入出比,
a.非低网龄_日携入量-a.非低网龄_日携出量非低网龄_日净发展,
a.非低网龄_月携入量,a.非低网龄_月携出量,round(a.非低网龄_月携入量/decode(a.非低网龄_月携出量,0,1,a.非低网龄_月携出量),2) 非低网龄_月入出比,
a.非低网龄_月携入量-a.非低网龄_月携出量非低网龄_月净发展,
a.日携入量_联通,a.日携出量_联通,a.日携入量_联通-a.日携出量_联通日净发展_联通,
a.月携入量_联通,a.月携出量_联通,a.月携入量_联通-a.月携出量_联通月净发展_联通,
a.日携入量_电信,a.日携出量_电信,a.日携入量_电信-a.日携出量_电信日净发展_电信,
a.月携入量_电信,a.月携出量_电信,a.月携入量_电信-a.月携出量_电信月净发展_电信,

a.日携入量,a.日携出量,round(a.日携入量/decode(a.日携出量,0,1,a.日携出量),2) 日入出比,
a.日携入量-a.日携出量日净发展,
a.月携入量,a.月携出量,round(a.月携入量/decode(a.月携出量,0,1,a.月携出量),2) 月入出比,
a.月携入量-a.月携出量月净发展,
a.年携入量,a.年携出量,round(a.年携入量/decode(a.年携出量,0,1,a.年携出量),2) 年入出比,
a.年携入量-a.年携出量年净发展
from shzc.sm_gzl_gzl_hmmx_xz_hz a ';
EXECUTE IMMEDIATE (SQL_STRING);

---- 三、发展客户精准运营

–A类：dw_A_dm_user_othernet_mm

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xz_a_mx’),‘SHZC’);
SQL_STRING:='create table shzc.sm_gzl_gzl_hmmx_xz_a_mx as
select distinct a.op_time,a.product_no from zibo.DW_A_DM_USER_OTHERNET_YH_DS a
where a.op_time in (select max(a.op_time) from zibo.DW_A_DM_USER_OTHERNET_YH_DS a) ';
EXECUTE IMMEDIATE (SQL_STRING);

–B类：dw_B_broadband_other_user_mm

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xz_b_mx’),‘SHZC’);
SQL_STRING:='create table shzc.sm_gzl_gzl_hmmx_xz_b_mx as
select max(a.op_time) op_time,a.product_no from zibo.DW_B_BROADBAND_OTHER_USER_MM a
group by a.product_no ';
EXECUTE IMMEDIATE (SQL_STRING);

–C类： dw_notbroadband_user_ds

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xz_c_mx’),‘SHZC’);
SQL_STRING:='create table shzc.sm_gzl_gzl_hmmx_xz_c_mx as
select distinct a.op_time,a.user_id,a.product_no from zibo.D_NOTBROADBAND_USER_DS a
where a.op_time in (select max(a.op_time) from zibo.D_NOTBROADBAND_USER_DS a) ';
EXECUTE IMMEDIATE (SQL_STRING);

–D类：dw_d_comp_user_mm

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xz_d_mx’),‘SHZC’);
SQL_STRING:='create table shzc.sm_gzl_gzl_hmmx_xz_d_mx as
select distinct a.op_time,a.comp_product_no from zibo.D_COMP_USER_MM a
where a.op_time in (select max(a.op_time) from zibo.D_COMP_USER_MM a) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_fc’),‘SHZC’);
SQL_STRING:='create table shzc.sm_gzl_gzl_hmmx_xzhm_fc as
select distinct a.*,
nvl(case when b1.product_no is not null then 1 end,0) A类,
nvl(case when b2.product_no is not null then 1 end,0) B类,
nvl(case when b3.user_id is not null then 1 end,0) C类,
nvl(case when b4.comp_product_no is not null then 1 end,0) D类
from shzc.sm_gzl_gzl_hmmx_xzhma a,
(select * from shzc.sm_gzl_gzl_hmmx_xz_a_mx b ) b1,
(select * from shzc.sm_gzl_gzl_hmmx_xz_b_mx b ) b2,
(select * from shzc.sm_gzl_gzl_hmmx_xz_c_mx b ) b3,
(select * from shzc.sm_gzl_gzl_hmmx_xz_d_mx b ) b4
where a.telnum=b1.product_no(+)
and a.telnum=b2.product_no(+)
and a.subsid=b3.user_id(+)
and a.telnum=b4.comp_product_no(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_fca’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhm_fca as
select ‘’’||p_day||‘’’ cycle,a.qx,a.jf_county_id,a.name,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsrq||’‘’ then a.telnum||a.starttime||a.subsid end) 上月发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsr||’‘’ then a.telnum||a.starttime||a.subsid end) 本月发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsrq||’‘’ and a.a类+a.b类+a.c类+a.d类>0 then a.telnum||a.starttime||a.subsid end) 上月四类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsr||’‘’ and a.a类+a.b类+a.c类+a.d类>0 then a.telnum||a.starttime||a.subsid end) 本月四类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsrq||’‘’ and a.a类>0 then a.telnum||a.starttime||a.subsid end) 上月A类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsr||’‘’ and a.a类>0 then a.telnum||a.starttime||a.subsid end) 本月A类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsrq||’‘’ and a.b类>0 then a.telnum||a.starttime||a.subsid end) 上月B类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsr||’‘’ and a.b类>0 then a.telnum||a.starttime||a.subsid end) 本月B类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsrq||’‘’ and a.c类>0 then a.telnum||a.starttime||a.subsid end) 上月C类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsr||’‘’ and a.c类>0 then a.telnum||a.starttime||a.subsid end) 本月C类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsrq||’‘’ and a.d类>0 then a.telnum||a.starttime||a.subsid end) 上月D类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsr||’‘’ and a.d类>0 then a.telnum||a.starttime||a.subsid end) 本月D类发展量
from shzc.sm_gzl_gzl_hmmx_xzhm_fc a where a.类型 =‘‘携入’’ and a.新网龄天数>=31
and to_number(substr(a.startday,7,2))<=to_number(substr(‘’‘||p_day||’‘’,7,2))
group by a.qx,a.jf_county_id,a.name
union all
select ‘’‘||p_day||’‘’ cycle,‘‘q’’ qx,‘‘999’‘jf_county_id,’‘全市’’ name,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsrq||’‘’ then a.telnum||a.starttime||a.subsid end) 上月发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsr||’‘’ then a.telnum||a.starttime||a.subsid end) 本月发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsrq||’‘’ and a.a类+a.b类+a.c类+a.d类>0 then a.telnum||a.starttime||a.subsid end) 上月四类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsr||’‘’ and a.a类+a.b类+a.c类+a.d类>0 then a.telnum||a.starttime||a.subsid end) 本月四类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsrq||’‘’ and a.a类>0 then a.telnum||a.starttime||a.subsid end) 上月A类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsr||’‘’ and a.a类>0 then a.telnum||a.starttime||a.subsid end) 本月A类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsrq||’‘’ and a.b类>0 then a.telnum||a.starttime||a.subsid end) 上月B类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsr||’‘’ and a.b类>0 then a.telnum||a.starttime||a.subsid end) 本月B类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsrq||’‘’ and a.c类>0 then a.telnum||a.starttime||a.subsid end) 上月C类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsr||’‘’ and a.c类>0 then a.telnum||a.starttime||a.subsid end) 本月C类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsrq||’‘’ and a.d类>0 then a.telnum||a.starttime||a.subsid end) 上月D类发展量,
count(distinct case when substr(a.startday,1,6) =‘’‘||v_monsr||’‘’ and a.d类>0 then a.telnum||a.starttime||a.subsid end) 本月D类发展量
from shzc.sm_gzl_gzl_hmmx_xzhm_fc a where a.类型 =‘‘携入’’ and a.新网龄天数>=31
and to_number(substr(a.startday,7,2))<=to_number(substr(‘’‘||p_day||’‘’,7,2)) ';
EXECUTE IMMEDIATE (SQL_STRING);

—结果3

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_fcb’),‘SHZC’);
SQL_STRING:='create table shzc.sm_gzl_gzl_hmmx_xzhm_fcb as
select a.cycle,a.qx,a.jf_county_id,a.name,
a.上月发展量,a.本月发展量,
round((a.本月发展量-a.上月发展量)/decode(a.上月发展量,0,1,a.上月发展量)*100,2) 本月发展量环比,
a.上月四类发展量,round(a.上月四类发展量/decode(a.上月发展量,0,1,a.上月发展量)*100,2) 上月四类占比,
a.本月四类发展量,round(a.本月四类发展量/decode(a.本月发展量,0,1,a.本月发展量)*100,2) 本月四类占比,
round((a.本月四类发展量-a.上月四类发展量)/decode(a.上月四类发展量,0,1,a.上月四类发展量)*100,2) 本月四类发展量环比,

   a.上月a类发展量,round(a.上月a类发展量/decode(a.上月发展量,0,1,a.上月发展量)*100,2) 上月a类占比,
   a.本月a类发展量,round(a.本月a类发展量/decode(a.本月发展量,0,1,a.本月发展量)*100,2) 本月a类占比,
   round((a.本月a类发展量-a.上月a类发展量)/decode(a.上月a类发展量,0,1,a.上月a类发展量)*100,2) 本月a类发展量环比,
   
   a.上月b类发展量,round(a.上月b类发展量/decode(a.上月发展量,0,1,a.上月发展量)*100,2) 上月b类占比,
   a.本月b类发展量,round(a.本月b类发展量/decode(a.本月发展量,0,1,a.本月发展量)*100,2) 本月b类占比,
   round((a.本月b类发展量-a.上月b类发展量)/decode(a.上月b类发展量,0,1,a.上月b类发展量)*100,2) 本月b类发展量环比,
   
   a.上月c类发展量,round(a.上月c类发展量/decode(a.上月发展量,0,1,a.上月发展量)*100,2) 上月c类占比,
   a.本月c类发展量,round(a.本月c类发展量/decode(a.本月发展量,0,1,a.本月发展量)*100,2) 本月c类占比,
   round((a.本月c类发展量-a.上月c类发展量)/decode(a.上月c类发展量,0,1,a.上月c类发展量)*100,2) 本月c类发展量环比,
   
   a.上月d类发展量,round(a.上月d类发展量/decode(a.上月发展量,0,1,a.上月发展量)*100,2) 上月d类占比,
   a.本月d类发展量,round(a.本月d类发展量/decode(a.本月发展量,0,1,a.本月发展量)*100,2) 本月d类占比,
   round((a.本月d类发展量-a.上月d类发展量)/decode(a.上月d类发展量,0,1,a.上月d类发展量)*100,2) 本月d类发展量环比

from shzc.sm_gzl_gzl_hmmx_xzhm_fca a ';
EXECUTE IMMEDIATE (SQL_STRING);

-----四、发展渠道跟踪 2023-0210 写入位置

SQL_STRING:=‘insert into shzc.sm_gzl_gzl_fanghao_bd
select a.registerorgid,a.subsid,a.servnumber,a.prodid,a.prodname,a.jiazhi,a.recopid,a.oid,a.formnum,
a.recdate,a.recorgid,a.折后价值,a.是否折后48,a.是否69以上,a.等效69,a.等效48
from zhyw.shc_fanghao_’||v_monsr||’ a
where not exists (select 1 from shzc.sm_gzl_gzl_fanghao_bd b where b.subsid=a.subsid and b.oid=a.oid) ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

—select * from zhyw.hc_hangye_shangqi_operid 客户经理目标库

----2023-0209 带政企、重客、集客三类字眼归政企

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_qd’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhm_qd as
select a.*,c.recopid,d.经理类型,c.oid,c.formnum,c.recdate,
b.orgname,b.status status_qd,b.是否自办渠道,b.单位类型,b.渠道类型,b.渠道类别,b.是否核心渠道,b.是否自办营业厅,
nvl(case when d.经理类型 is not null then ‘‘政企’’
when nvl(b.orgname,’‘0’‘) like ‘’%政企%’’ then ‘‘政企’’
when nvl(b.orgname,‘‘0’’) like ‘’%重客%‘’ then ‘‘政企’’
when nvl(b.orgname,‘‘0’’) like ‘’%集客%‘’ then ‘‘政企’’
when nvl(b.orgname,‘‘0’’) like ‘’%集团客户%‘’ then ‘‘政企’’
when nvl(b.orgname,‘‘0’’) like ‘’%大客户管理%‘’ then ‘‘政企’’ end,‘‘非’’) 是否政企
from shzc.sm_gzl_gzl_hmmx_xzhma a,
zhyw.shc_organization b,
shzc.sm_gzl_gzl_fanghao_bd c,
zhyw.hc_hangye_shangqi_operid d
where a.registerorgid=b.orgid(+)
and a.subsid=c.subsid(+)
and c.recopid=d.客户经理(+)
and a.类型 =‘‘携入’’ ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_qda’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhm_qda as
select substr(a.startday,1,6) cycle,a.qx,a.name,a.registerorgid,a.orgname,a.是否政企,
a.是否自办渠道, 单位类型, 渠道类型, 渠道类别, 是否核心渠道, 是否自办营业厅,count(distinct a.telnum||a.starttime||a.subsid) 号码数
from shzc.sm_gzl_gzl_hmmx_xzhm_qd a
where to_number(substr(a.startday,7,2))<=to_number(substr(’‘’||p_day||‘’‘,7,2))
and substr(a.startday,1,6)>=’‘’||v_monsrq||‘’’
group by substr(a.startday,1,6),a.qx,a.name,a.registerorgid,a.orgname,a.是否政企,
a.是否自办渠道, 单位类型, 渠道类型, 渠道类别, 是否核心渠道, 是否自办营业厅 ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_qdb’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhm_qdb as
select a.*,nvl(case when nvl(a.是否政企,’‘0’‘)=’‘政企’’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%随销%‘’ then ‘‘随销’’
when nvl(a.单位类型,‘‘0’’)=‘‘直销渠道’’ then ‘‘直销’’
when nvl(a.是否自办渠道,‘‘0’’)=‘‘自办渠道’’ then ‘‘自办’‘end,’‘代办’’) 类型
from shzc.sm_gzl_gzl_hmmx_xzhm_qda a ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_qdc’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhm_qdc as
select ‘’’||p_day||‘’’ cycle, a.qx,a.name,
count(case when a.cycle=‘’‘||v_monsrq||’‘’ then a.registerorgid end) 上月渠道数,
sum(nvl(case when a.cycle=‘’‘||v_monsrq||’‘‘then a.号码数 end,0)) 上月放号数,
count(case when a.cycle=’’‘||v_monsr||’‘’ then a.registerorgid end) 本月渠道数,
sum(nvl(case when a.cycle=‘’‘||v_monsr||’‘‘then a.号码数 end,0)) 本月放号数,
—自办
count(case when a.类型=’‘自办’‘and a.cycle=’’‘||v_monsrq||’‘’ then a.registerorgid end) 上月自办渠道数,
sum(nvl(case when a.类型=‘‘自办’’ and a.cycle=‘’‘||v_monsrq||’‘‘then a.号码数 end,0)) 上月自办放号数,
count(case when a.类型=’‘自办’‘and a.cycle=’’‘||v_monsr||’‘’ then a.registerorgid end) 本月自办渠道数,
sum(nvl(case when a.类型=‘‘自办’’ and a.cycle=‘’‘||v_monsr||’‘’ then a.号码数 end,0)) 本月自办放号数,
—代办
count(case when a.类型=‘‘代办’‘and a.cycle=’’‘||v_monsrq||’‘’ then a.registerorgid end) 上月代办渠道数,
sum(nvl(case when a.类型=‘‘代办’’ and a.cycle=‘’‘||v_monsrq||’‘‘then a.号码数 end,0)) 上月代办放号数,
count(case when a.类型=’‘代办’‘and a.cycle=’’‘||v_monsr||’‘’ then a.registerorgid end) 本月代办渠道数,
sum(nvl(case when a.类型=‘‘代办’’ and a.cycle=‘’‘||v_monsr||’‘’ then a.号码数 end,0)) 本月代办放号数,
—随销
count(case when a.类型=‘‘随销’‘and a.cycle=’’‘||v_monsrq||’‘’ then a.registerorgid end) 上月随销渠道数,
sum(nvl(case when a.类型=‘‘随销’’ and a.cycle=‘’‘||v_monsrq||’‘‘then a.号码数 end,0)) 上月随销放号数,
count(case when a.类型=’‘随销’‘and a.cycle=’’‘||v_monsr||’‘’ then a.registerorgid end) 本月随销渠道数,
sum(nvl(case when a.类型=‘‘随销’’ and a.cycle=‘’‘||v_monsr||’‘’ then a.号码数 end,0)) 本月随销放号数,
—直销
count(case when a.类型=‘‘直销’‘and a.cycle=’’‘||v_monsrq||’‘’ then a.registerorgid end) 上月直销渠道数,
sum(nvl(case when a.类型=‘‘直销’’ and a.cycle=‘’‘||v_monsrq||’‘‘then a.号码数 end,0)) 上月直销放号数,
count(case when a.类型=’‘直销’‘and a.cycle=’’‘||v_monsr||’‘’ then a.registerorgid end) 本月直销渠道数,
sum(nvl(case when a.类型=‘‘直销’’ and a.cycle=‘’‘||v_monsr||’‘’ then a.号码数 end,0)) 本月直销放号数,
—政企
count(case when a.类型=‘‘政企’‘and a.cycle=’’‘||v_monsrq||’‘’ then a.registerorgid end) 上月政企渠道数,
sum(nvl(case when a.类型=‘‘政企’’ and a.cycle=‘’‘||v_monsrq||’‘‘then a.号码数 end,0)) 上月政企放号数,
count(case when a.类型=’‘政企’‘and a.cycle=’’‘||v_monsr||’‘’ then a.registerorgid end) 本月政企渠道数,
sum(nvl(case when a.类型=‘‘政企’’ and a.cycle=‘’‘||v_monsr||’‘’ then a.号码数 end,0)) 本月政企放号数

from shzc.sm_gzl_gzl_hmmx_xzhm_qdb a
group by a.qx,a.name
union all
select ‘’‘||p_day||’‘’ cycle, ‘‘q’’ qx,‘‘全市’’ name,
count(case when a.cycle=‘’‘||v_monsrq||’‘’ then a.registerorgid end) 上月渠道数,
sum(nvl(case when a.cycle=‘’‘||v_monsrq||’‘‘then a.号码数 end,0)) 上月放号数,
count(case when a.cycle=’’‘||v_monsr||’‘’ then a.registerorgid end) 本月渠道数,
sum(nvl(case when a.cycle=‘’‘||v_monsr||’‘‘then a.号码数 end,0)) 本月放号数,
—自办
count(case when a.类型=’‘自办’‘and a.cycle=’’‘||v_monsrq||’‘’ then a.registerorgid end) 上月自办渠道数,
sum(nvl(case when a.类型=‘‘自办’’ and a.cycle=‘’‘||v_monsrq||’‘‘then a.号码数 end,0)) 上月自办放号数,
count(case when a.类型=’‘自办’‘and a.cycle=’’‘||v_monsr||’‘’ then a.registerorgid end) 本月自办渠道数,
sum(nvl(case when a.类型=‘‘自办’’ and a.cycle=‘’‘||v_monsr||’‘’ then a.号码数 end,0)) 本月自办放号数,
—代办
count(case when a.类型=‘‘代办’‘and a.cycle=’’‘||v_monsrq||’‘’ then a.registerorgid end) 上月代办渠道数,
sum(nvl(case when a.类型=‘‘代办’’ and a.cycle=‘’‘||v_monsrq||’‘‘then a.号码数 end,0)) 上月代办放号数,
count(case when a.类型=’‘代办’‘and a.cycle=’’‘||v_monsr||’‘’ then a.registerorgid end) 本月代办渠道数,
sum(nvl(case when a.类型=‘‘代办’’ and a.cycle=‘’‘||v_monsr||’‘’ then a.号码数 end,0)) 本月代办放号数,
—随销
count(case when a.类型=‘‘随销’‘and a.cycle=’’‘||v_monsrq||’‘’ then a.registerorgid end) 上月随销渠道数,
sum(nvl(case when a.类型=‘‘随销’’ and a.cycle=‘’‘||v_monsrq||’‘‘then a.号码数 end,0)) 上月随销放号数,
count(case when a.类型=’‘随销’‘and a.cycle=’’‘||v_monsr||’‘’ then a.registerorgid end) 本月随销渠道数,
sum(nvl(case when a.类型=‘‘随销’’ and a.cycle=‘’‘||v_monsr||’‘’ then a.号码数 end,0)) 本月随销放号数,
—直销
count(case when a.类型=‘‘直销’‘and a.cycle=’’‘||v_monsrq||’‘’ then a.registerorgid end) 上月直销渠道数,
sum(nvl(case when a.类型=‘‘直销’’ and a.cycle=‘’‘||v_monsrq||’‘‘then a.号码数 end,0)) 上月直销放号数,
count(case when a.类型=’‘直销’‘and a.cycle=’’‘||v_monsr||’‘’ then a.registerorgid end) 本月直销渠道数,
sum(nvl(case when a.类型=‘‘直销’’ and a.cycle=‘’‘||v_monsr||’‘’ then a.号码数 end,0)) 本月直销放号数,
—政企
count(case when a.类型=‘‘政企’‘and a.cycle=’’‘||v_monsrq||’‘’ then a.registerorgid end) 上月政企渠道数,
sum(nvl(case when a.类型=‘‘政企’’ and a.cycle=‘’‘||v_monsrq||’‘‘then a.号码数 end,0)) 上月政企放号数,
count(case when a.类型=’‘政企’‘and a.cycle=’’‘||v_monsr||’‘’ then a.registerorgid end) 本月政企渠道数,
sum(nvl(case when a.类型=‘‘政企’’ and a.cycle=‘’‘||v_monsr||’‘’ then a.号码数 end,0)) 本月政企放号数
from shzc.sm_gzl_gzl_hmmx_xzhm_qdb a ';
EXECUTE IMMEDIATE (SQL_STRING);

----结果4

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_qdd’),‘SHZC’);
SQL_STRING:='create table shzc.sm_gzl_gzl_hmmx_xzhm_qdd as
select a.cycle,a.qx,a.name,
a.上月放号数,
a.本月放号数,
a.上月自办放号数,
a.本月自办放号数,
round(a.上月自办放号数/decode(a.上月放号数,0,1,a.上月放号数)*100,2) 上月自办产能占比,
round(a.本月自办放号数/decode(a.本月放号数,0,1,a.本月放号数)*100,2) 本月自办产能占比,
round((a.本月自办放号数-a.上月自办放号数)/decode(a.上月自办放号数,0,1,a.上月自办放号数)*100,2) 本月自办产能月环比,
a.上月代办放号数,
a.本月代办放号数,
round(a.上月代办放号数/decode(a.上月放号数,0,1,a.上月放号数)*100,2) 上月代办产能占比,
round(a.本月代办放号数/decode(a.本月放号数,0,1,a.本月放号数)*100,2) 本月代办产能占比,
round((a.本月代办放号数-a.上月代办放号数)/decode(a.上月代办放号数,0,1,a.上月代办放号数)*100,2) 本月代办产能月环比,
a.上月随销放号数,
a.本月随销放号数,
round(a.上月随销放号数/decode(a.上月放号数,0,1,a.上月放号数)*100,2) 上月随销产能占比,
round(a.本月随销放号数/decode(a.本月放号数,0,1,a.本月放号数)*100,2) 本月随销产能占比,
round((a.本月随销放号数-a.上月随销放号数)/decode(a.上月随销放号数,0,1,a.上月随销放号数)*100,2) 本月随销产能月环比,
a.上月直销放号数,
a.本月直销放号数,
round(a.上月直销放号数/decode(a.上月放号数,0,1,a.上月放号数)*100,2) 上月直销产能占比,
round(a.本月直销放号数/decode(a.本月放号数,0,1,a.本月放号数)*100,2) 本月直销产能占比,
round((a.本月直销放号数-a.上月直销放号数)/decode(a.上月直销放号数,0,1,a.上月直销放号数)*100,2) 本月直销产能月环比,
a.上月政企放号数,
a.本月政企放号数,
round(a.上月政企放号数/decode(a.上月放号数,0,1,a.上月放号数)*100,2) 上月政企产能占比,
round(a.本月政企放号数/decode(a.本月放号数,0,1,a.本月放号数)*100,2) 本月政企产能占比,
round((a.本月政企放号数-a.上月政企放号数)/decode(a.上月政企放号数,0,1,a.上月政企放号数)*100,2) 本月政企产能月环比
from shzc.sm_gzl_gzl_hmmx_xzhm_qdc a ';
EXECUTE IMMEDIATE (SQL_STRING);

-----渠道月破零率
—select * from zhyw.hc_hangye_shangqi_operid 客户经理目标库

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_pl’),‘SHZC’);
SQL_STRING:='create table shzc.sm_gzl_gzl_hmmx_xzhm_pl as
select a.*,b.orgid from zhyw.hc_hangye_shangqi_operid a,
tbcs.operator@bcv b
where a.客户经理=b.operid ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_pla’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhm_pla as
select ‘’’||v_monsrq||‘’’ cycle,a.orgid,a.orgname,a.status,a.是否自办渠道,a.单位类型,a.渠道类型,a.渠道类别,
nvl(case when t.orgid is not null then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%政企%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%重客%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%集客%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%集团客户%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%大客户管理%‘’ then ‘‘政企’’ end,‘‘非’’) 是否政企,
nvl(case when nvl(case when t.orgid is not null then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%政企%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%重客%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%集客%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%集团客户%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%大客户管理%‘’ then ‘‘政企’’ end,‘‘非’’)=‘‘政企’’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%随销%‘’ then ‘‘随销’’
when nvl(a.单位类型,‘‘0’’)=‘‘直销渠道’’ then ‘‘直销’’
when nvl(a.是否自办渠道,‘‘0’’)=‘‘自办渠道’’ then ‘‘自办’’ end,‘‘代办’’) 类型
from zhyw.shc_organization_‘||v_monsrq||’ a,
(select distinct t.orgid from shzc.sm_gzl_gzl_hmmx_xzhm_pl t ) t
where a.status=‘‘1’’
and a.orgid=t.orgid(+)
union all
select ‘’‘||v_monsr||’‘’ cycle,a.orgid,a.orgname,a.status,a.是否自办渠道,a.单位类型,a.渠道类型,a.渠道类别,
nvl(case when t.orgid is not null then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%政企%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%重客%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%集客%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%集团客户%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%大客户管理%‘’ then ‘‘政企’’ end,‘‘非’’) 是否政企,
nvl(case when nvl(case when t.orgid is not null then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%政企%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%重客%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%集客%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%集团客户%‘’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%大客户管理%‘’ then ‘‘政企’’ end,‘‘非’’)=‘‘政企’’ then ‘‘政企’’
when nvl(a.orgname,‘‘0’’) like ‘’%随销%‘’ then ‘‘随销’’
when nvl(a.单位类型,‘‘0’’)=‘‘直销渠道’’ then ‘‘直销’’
when nvl(a.是否自办渠道,‘‘0’’)=‘‘自办渠道’’ then ‘‘自办’’ end,‘‘代办’’) 类型
from zhyw.shc_organization_‘||v_monsr||’ a,
(select distinct t.orgid from shzc.sm_gzl_gzl_hmmx_xzhm_pl t ) t
where a.status=‘‘1’’
and a.orgid=t.orgid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_plaa’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhm_plaa as
select a.*,nvl(case when nvl(substr(a.orgid,8,1),’‘0’‘) not in (select t.county_id from zhyw.rpt_county t
where t.county_id not in (’‘0’‘,’‘A’‘,’‘zbtyqd’')) then ‘‘x’’ end,substr(a.orgid,8,1)) qx
from shzc.sm_gzl_gzl_hmmx_xzhm_pla a ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_plab’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhm_plab as
select a.*,nvl(t.jf_county_id,’‘1008’‘) jf_county_id,nvl(t.name,’‘线上’') name
from shzc.sm_gzl_gzl_hmmx_xzhm_plaa a,
zhyw.rpt_county t
where a.qx=t.county_id(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_plb’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhm_plb as
select a.cycle,a.qx,a.jf_county_id,a.name,a.类型,count(distinct a.orgid) orgids
from shzc.sm_gzl_gzl_hmmx_xzhm_plab a
where a.orgid in (select b.orgid from tbcs.operator@bcv b where STATUS=1 and ORGID like ‘‘SD.LC.%’’ group by b.orgid)
group by a.cycle,a.qx,a.jf_county_id,a.name,a.类型
union all
select a.cycle,’‘q’’ qx,'‘999’‘jf_county_id,’‘全市’'name,a.类型,count(distinct a.orgid) orgids
from shzc.sm_gzl_gzl_hmmx_xzhm_plab a
where a.orgid in (select b.orgid from tbcs.operator@bcv b where STATUS=1 and ORGID like ‘‘SD.LC.%’’ group by b.orgid)
group by a.cycle,a.类型 ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_plc’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhm_plc as
select a.cycle,a.qx,a.jf_county_id,a.name,sum(a.orgids) orgids,
max(nvl(case when a.类型=’‘自办’‘then a.orgids end,0)) 自办,
max(nvl(case when a.类型=’‘代办’‘then a.orgids end,0)) 代办,
max(nvl(case when a.类型=’‘随销’‘then a.orgids end,0)) 随销,
max(nvl(case when a.类型=’‘直销’‘then a.orgids end,0)) 直销,
max(nvl(case when a.类型=’‘政企’'then a.orgids end,0)) 政企
from shzc.sm_gzl_gzl_hmmx_xzhm_plb a
group by a.cycle,a.qx,a.jf_county_id,a.name ';
EXECUTE IMMEDIATE (SQL_STRING);

----结果5

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_pld’),‘SHZC’);
SQL_STRING:='create table shzc.sm_gzl_gzl_hmmx_xzhm_pld as
select a.cycle,a.qx,a.name,
b1.orgids 上月总渠道数,a.上月渠道数,round(a.上月渠道数/decode(b1.orgids,0,1,b1.orgids)*100,2) 上月发展渠道占比,
b2.orgids 本月总渠道数,a.本月渠道数,round(a.本月渠道数/decode(b2.orgids,0,1,b2.orgids)*100,2) 本月发展渠道占比,

b1.自办上月自办总渠道数,a.上月自办渠道数,round(a.上月自办渠道数/decode(b1.自办,0,1,b1.自办)*100,2) 上月自办发展渠道占比,
b2.自办本月自办总渠道数,a.本月自办渠道数,round(a.本月自办渠道数/decode(b2.自办,0,1,b2.自办)*100,2) 本月自办发展渠道占比,

b1.代办上月代办总渠道数,a.上月代办渠道数,round(a.上月代办渠道数/decode(b1.代办,0,1,b1.代办)*100,2) 上月代办发展渠道占比,
b2.代办本月代办总渠道数,a.本月代办渠道数,round(a.本月代办渠道数/decode(b2.代办,0,1,b2.代办)*100,2) 本月代办发展渠道占比,

b1.随销上月随销总渠道数,a.上月随销渠道数,round(a.上月随销渠道数/decode(b1.随销,0,1,b1.随销)*100,2) 上月随销发展渠道占比,
b2.随销本月随销总渠道数,a.本月随销渠道数,round(a.本月随销渠道数/decode(b2.随销,0,1,b2.随销)*100,2) 本月随销发展渠道占比,

b1.直销上月直销总渠道数,a.上月直销渠道数,round(a.上月直销渠道数/decode(b1.直销,0,1,b1.直销)*100,2) 上月直销发展渠道占比,
b2.直销本月直销总渠道数,a.本月直销渠道数,round(a.本月直销渠道数/decode(b2.直销,0,1,b2.直销)*100,2) 本月直销发展渠道占比,

b1.政企上月政企总渠道数,a.上月政企渠道数,round(a.上月政企渠道数/decode(b1.政企,0,1,b1.政企)*100,2) 上月政企发展渠道占比,
b2.政企本月政企总渠道数,a.本月政企渠道数,round(a.本月政企渠道数/decode(b2.政企,0,1,b2.政企)*100,2) 本月政企发展渠道占比
from shzc.sm_gzl_gzl_hmmx_xzhm_qdc a,
(select * from shzc.sm_gzl_gzl_hmmx_xzhm_plc b where b.cycle=‘’‘||v_monsrq||’‘’) b1,
(select * from shzc.sm_gzl_gzl_hmmx_xzhm_plc b where b.cycle=‘’‘||v_monsr||’‘’) b2
where a.qx=b1.qx(+)
and a.qx=b2.qx(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

—六、运营健康度
—发展客户 ARPU 指 12 月发展客户在 1 月实际收入，流失客户 Arpu 指 2 月流失客户在 1 月实际收入；
—发展低网龄指发展时间距离首次过网时间≤ 30 天，流失低网龄指流失时间距离开户时间≤30 天。
—ABC1 类政企客户发展口径为近 30 天内发展客户中在本月维护为 ABC1 集团成员的客户

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_yy’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhm_yy as
select a.*,
nvl(case when a.类型=’‘携入’’ and substr(a.startday,1,6)=‘’‘||v_monsrq3||’‘’ then ‘‘上月发展’’
when a.类型=‘‘携出’’ and substr(a.startday,1,6)=‘’‘||v_monsrq||’‘’ then ‘‘上月流失’’
when a.类型=‘‘携入’’ and substr(a.startday,1,6)=‘’‘||v_monsrq2||’‘’ then ‘‘本月发展’’
when a.类型=‘‘携出’’ and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ then ‘‘本月流失’’ end,‘‘非’’) 发展流失,

nvl(case when a.类型=‘‘携入’’ and substr(a.startday,1,6)=‘’‘||v_monsrq||’‘’ and a.新网龄天数<31 then ‘‘上月发展低网龄’’
when a.类型=‘‘携出’’ and substr(a.startday,1,6)=‘’‘||v_monsrq||’‘’ and a.新网龄天数<31 then ‘‘上月流失低网龄’’
when a.类型=‘‘携入’’ and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.新网龄天数<31 then ‘‘本月发展低网龄’’
when a.类型=‘‘携出’’ and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.新网龄天数<31 then ‘‘本月流失低网龄’’ end,‘‘非’’) 低网龄,
nvl(case when a.集团类型结果 in (‘‘A’’,‘‘B’’,‘‘C1’’) then ‘‘1’’ when a.集团类型结果 in (‘‘C2’’,‘‘D’’) then ‘‘2’’ end,‘‘0’’) 集团划分
from shzc.sm_gzl_gzl_hmmx_xzhma a ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_yya’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhm_yya as
select a.qx,a.jf_county_id,a.name,
sum(nvl(case when a.发展流失=’‘上月发展’’ then a.arpu_sy end,0)) 上月发展arpu,
count(case when a.发展流失=‘‘上月发展’’ then a.telnum||a.starttime||a.subsid end) 上月发展用户,
sum(nvl(case when a.发展流失=‘‘本月发展’’ then a.arpu end,0)) 本月发展arpu,
count(case when a.发展流失=‘‘本月发展’’ then a.telnum||a.starttime||a.subsid end) 本月发展用户,

sum(nvl(case when a.发展流失=‘‘上月流失’’ then a.arpu_sy end,0)) 上月流失arpu,
count(case when a.发展流失=‘‘上月流失’’ then a.telnum||a.starttime||a.subsid end) 上月流失用户,
sum(nvl(case when a.发展流失=‘‘本月流失’’ then a.arpu end,0)) 本月流失arpu,
count(case when a.发展流失=‘‘本月流失’’ then a.telnum||a.starttime||a.subsid end) 本月流失用户,

count(case when a.类型=‘‘携入’’ and substr(a.startday,1,6)=‘’‘||v_monsrq||’‘’ and a.startday<=‘’‘||S_DAY||’‘’ then a.telnum||a.starttime||a.subsid end) 上月同期发展用户,
count(case when a.类型=‘‘携入’’ and a.低网龄=‘‘上月发展低网龄’’ and a.startday<=‘’‘||S_DAY||’‘’ then a.telnum||a.starttime||a.subsid end) 上月发展低网龄,
count(case when a.类型=‘‘携入’’ and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.startday<=‘’‘||p_day||’‘’ then a.telnum||a.starttime||a.subsid end) 本月同期发展用户,
count(case when a.类型=‘‘携入’’ and a.低网龄=‘‘本月发展低网龄’’ and a.startday<=‘’‘||p_day||’‘’ then a.telnum||a.starttime||a.subsid end) 本月发展低网龄,

count(case when a.类型=‘‘携出’’ and substr(a.startday,1,6)=‘’‘||v_monsrq||’‘’ and a.startday<=‘’‘||S_DAY||’‘’ then a.telnum||a.starttime||a.subsid end) 上月同期流失用户,
count(case when a.类型=‘‘携出’’ and a.低网龄=‘‘上月流失低网龄’’ and a.startday<=‘’‘||S_DAY||’‘’ then a.telnum||a.starttime||a.subsid end) 上月流失低网龄,
count(case when a.类型=‘‘携出’’ and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.startday<=‘’‘||p_day||’‘’ then a.telnum||a.starttime||a.subsid end) 本月同期流失用户,
count(case when a.类型=‘‘携出’’ and a.低网龄=‘‘本月流失低网龄’’ and a.startday<=‘’‘||p_day||’‘’ then a.telnum||a.starttime||a.subsid end) 本月流失低网龄,

count(case when a.类型=‘‘携入’’ and a.startday=‘’‘||S_DAY||’‘’ and a.集团划分=‘‘1’’ then a.telnum||a.starttime||a.subsid end) 上月日发展abc1用户,
count(case when a.类型=‘‘携入’’ and substr(a.startday,1,6)=‘’‘||v_monsrq||’‘’ and a.startday<=‘’‘||S_DAY||’‘’ and a.集团划分=‘‘1’’ then a.telnum||a.starttime||a.subsid end) 上月发展abc1用户,
count(case when a.类型=‘‘携入’’ and a.startday=‘’‘||p_day||’‘’ and a.集团划分=‘‘1’’ then a.telnum||a.starttime||a.subsid end) 本月日发展abc1用户,
count(case when a.类型=‘‘携入’’ and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.startday<=‘’‘||p_day||’‘’ and a.集团划分=‘‘1’’ then a.telnum||a.starttime||a.subsid end) 本月发展abc1用户,

count(case when a.类型=‘‘携出’’ and a.startday=‘’‘||S_DAY||’‘’ and a.集团划分=‘‘1’’ then a.telnum||a.starttime||a.subsid end) 上月日流失abc1用户,
count(case when a.类型=‘‘携出’’ and substr(a.startday,1,6)=‘’‘||v_monsrq||’‘’ and a.startday<=‘’‘||S_DAY||’‘’ and a.集团划分=‘‘1’’ then a.telnum||a.starttime||a.subsid end) 上月流失abc1用户,
count(case when a.类型=‘‘携出’’ and a.startday=‘’‘||p_day||’‘’ and a.集团划分=‘‘1’’ then a.telnum||a.starttime||a.subsid end) 本月日流失abc1用户,
count(case when a.类型=‘‘携出’’ and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.startday<=‘’‘||p_day||’‘’ and a.集团划分=‘‘1’’ then a.telnum||a.starttime||a.subsid end) 本月流失abc1用户,

count(case when a.类型=‘‘携入’’ and a.startday=‘’‘||S_DAY||’‘’ and a.集团划分=‘‘2’’ then a.telnum||a.starttime||a.subsid end) 上月日发展c2d用户,
count(case when a.类型=‘‘携入’’ and substr(a.startday,1,6)=‘’‘||v_monsrq||’‘’ and a.startday<=‘’‘||S_DAY||’‘’ and a.集团划分=‘‘2’’ then a.telnum||a.starttime||a.subsid end) 上月发展c2d用户,
count(case when a.类型=‘‘携入’’ and a.startday=‘’‘||p_day||’‘’ and a.集团划分=‘‘2’’ then a.telnum||a.starttime||a.subsid end) 本月日发展c2d用户,
count(case when a.类型=‘‘携入’’ and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.startday<=‘’‘||p_day||’‘’ and a.集团划分=‘‘2’’ then a.telnum||a.starttime||a.subsid end) 本月发展c2d用户,

count(case when a.类型=‘‘携出’’ and a.startday=‘’‘||S_DAY||’‘’ and a.集团划分=‘‘2’’ then a.telnum||a.starttime||a.subsid end) 上月日流失c2d用户,
count(case when a.类型=‘‘携出’’ and substr(a.startday,1,6)=‘’‘||v_monsrq||’‘’ and a.startday<=‘’‘||S_DAY||’‘’ and a.集团划分=‘‘2’’ then a.telnum||a.starttime||a.subsid end) 上月流失c2d用户,
count(case when a.类型=‘‘携出’’ and a.startday=‘’‘||p_day||’‘’ and a.集团划分=‘‘2’’ then a.telnum||a.starttime||a.subsid end) 本月日流失c2d用户,
count(case when a.类型=‘‘携出’’ and substr(a.startday,1,6)=‘’‘||v_monsr||’‘’ and a.startday<=‘’‘||p_day||’‘’ and a.集团划分=‘‘2’’ then a.telnum||a.starttime||a.subsid end) 本月流失c2d用户

from shzc.sm_gzl_gzl_hmmx_xzhm_yy a
group by a.qx,a.jf_county_id,a.name
union all
select ‘‘q’‘qx,’‘999’‘jf_county_id,’‘全市’‘name,
sum(nvl(case when a.发展流失=’‘上月发展’’ then a.arpu_sy end,0)) 上月发展arpu,
count(case when a.发展流失=‘‘上月发展’’ then a.telnum||a.starttime||a.subsid end) 上月发展用户,
sum(nvl(case when a.发展流失=‘‘本月发展’’ then a.arpu end,0)) 本月发展arpu,
count(case when a.发展流失=‘‘本月发展’’ then a.telnum||a.starttime||a.subsid end) 本月发展用户,

from shzc.sm_gzl_gzl_hmmx_xzhm_yy a ';
EXECUTE IMMEDIATE (SQL_STRING);

----结果6

zhyw.shc_drop_retable(upper(‘sm_gzl_gzl_hmmx_xzhm_yyb’),‘SHZC’);
SQL_STRING:=‘create table shzc.sm_gzl_gzl_hmmx_xzhm_yyb as
select ‘’’||p_day||‘’’ cycle,a.qx,a.jf_county_id,a.name,
a.上月发展arpu,a.上月发展用户,round(a.上月发展arpu/decode(a.上月发展用户,0,1,a.上月发展用户),2) 上月发展人均arpu,
a.本月发展arpu,a.本月发展用户,round(a.本月发展arpu/decode(a.本月发展用户,0,1,a.本月发展用户),2) 本月发展人均arpu,
a.上月流失arpu,a.上月流失用户,round(a.上月流失arpu/decode(a.上月流失用户,0,1,a.上月流失用户),2) 上月流失人均arpu,
a.本月流失arpu,a.本月流失用户,round(a.本月流失arpu/decode(a.本月流失用户,0,1,a.本月流失用户),2) 本月流失人均arpu,
a.上月同期发展用户,a.上月发展低网龄,round(a.上月发展低网龄/decode(a.上月同期发展用户,0,1,a.上月同期发展用户)*100,2) 上月发展低网龄占比,
a.本月同期发展用户,a.本月发展低网龄,round(a.本月发展低网龄/decode(a.本月同期发展用户,0,1,a.本月同期发展用户)*100,2) 本月发展低网龄占比,
a.上月同期流失用户,a.上月流失低网龄,round(a.上月流失低网龄/decode(a.上月同期流失用户,0,1,a.上月同期流失用户)*100,2) 上月流失低网龄占比,
a.本月同期流失用户,a.本月流失低网龄,round(a.本月流失低网龄/decode(a.本月同期流失用户,0,1,a.本月同期流失用户)*100,2) 本月流失低网龄占比,
a.上月日发展abc1用户,
a.上月发展abc1用户,
a.本月日发展abc1用户,
a.本月发展abc1用户,
a.上月日流失abc1用户,
a.上月流失abc1用户,
a.本月日流失abc1用户,
a.本月流失abc1用户,
a.上月日发展c2d用户,
a.上月发展c2d用户,
a.本月日发展c2d用户,
a.本月发展c2d用户,
a.上月日流失c2d用户,
a.上月流失c2d用户,
a.本月日流失c2d用户,
a.本月流失c2d用户,

   a.本月日发展abc1用户-a.本月日流失abc1用户 本月日净发展abc1用户,
   a.本月发展abc1用户-a.本月流失abc1用户 本月净发展abc1用户,
   a.本月日发展c2d用户-a.本月日流失c2d用户 本月日净发展c2d用户,
   a.本月发展c2d用户-a.本月流失c2d用户 本月净发展c2d用户

from shzc.sm_gzl_gzl_hmmx_xzhm_yya a ';
EXECUTE IMMEDIATE (SQL_STRING);

—结果表
–select * from shzc.sm_gzl_gzl_hmmx_xz_qxhz;
–select * from shzc.sm_gzl_gzl_hmmx_xzhm_fcb;
–select * from shzc.sm_gzl_gzl_hmmx_xzhm_qdd;
–select * from shzc.sm_gzl_gzl_hmmx_xzhm_pld;
–select * from shzc.sm_gzl_gzl_hmmx_xzhm_yyb;