-----全量客户特征看板报表支撑需求
---涉及编码-权益
---项目 优惠编码 优惠名称 月功能费 一次性进收
--select * from zhyw.jzc_all_priv_qy_type;
--drop table zhyw.jzc_all_priv_qy_type;
--create table zhyw.jzc_all_priv_qy_type
--(项目 varchar2(18),优惠编码 varchar2(99),优惠名称 varchar2(200),月功能费 varchar2(18),一次性进收 varchar2(50) );
--select * from zhyw.jzc_all_priv_qy_type for update ;
SQL_STRING:='update zhyw.jzc_all_priv_qy_type a set a.优惠编码=trim(replace(a.优惠编码,''?'','''')),
a.月功能费=trim(replace(a.月功能费,'','',''.'')),
a.一次性进收=trim(a.一次性进收) ' ;
execute immediate (SQL_STRING);
commit;
---涉及编码-生态
---项目 优惠编码 优惠名称 月功能费 一次性入收标识
--drop table zhyw.jzc_all_priv_st_type;
--create table zhyw.jzc_all_priv_st_type
--(项目 varchar2(18),优惠编码 varchar2(99),优惠名称 varchar2(200),月功能费 varchar2(18),一次性进收 varchar2(50) );
--select * from zhyw.jzc_all_priv_st_type for update ;
SQL_STRING:='update zhyw.jzc_all_priv_st_type a set a.优惠编码=trim(replace(a.优惠编码,''?'','''')),
a.月功能费=trim(replace(a.月功能费,'','',''.'')),
a.一次性进收=trim(a.一次性进收)' ;
execute immediate (SQL_STRING);
commit;
---千兆宽带特殊优惠
--项目 优惠编码 优惠名称 月功能费 一次性进收 TYPE 类型
--智能组网 gl.base.znzw_229.500 269元档（包含1个千兆路由器+更换智能网关） 169 一次性 privid 编码-生态
zhyw.shc_drop_retable(upper('jzc_all_priv_qz_type'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_qz_type as
select a.prod_type,a.privid,a.privname, 0 月功能费,'' '' 一次性进收,''privid'' TYPE,''编码-生态'' 类型
from zhyw.shishi_report_prod a where a.prod_type = ''千兆宽带'' ' ;
execute immediate (SQL_STRING);
-----------优惠目录合并标记类型
zhyw.shc_drop_retable(upper('jzc_all_priv_hb_type'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_hb_type as
select distinct a.项目,a.优惠编码,a.type,a.类型 from
(select a.项目,a.优惠编码,a.优惠名称,to_number(a.月功能费) 月功能费,a.一次性进收,
(case when b.privid is not null then ''privid''
when c.prodid is not null then ''prodid'' end) type,''编码-权益'' 类型
from zhyw.jzc_all_priv_qy_type a,
TBCS.privilege_SCHEME@BCV b,
tbcs.product@bcv c
where a.优惠编码=b.privid(+)
and a.优惠编码=c.prodid(+)
union all
select a.项目,a.优惠编码,a.优惠名称,to_number(a.月功能费) 月功能费,a.一次性进收,
(case when b.privid is not null then ''privid''
when c.prodid is not null then ''prodid'' end) type,''编码-生态'' 类型
from zhyw.jzc_all_priv_st_type a,
TBCS.privilege_SCHEME@BCV b,
tbcs.product@bcv c
where a.优惠编码=b.privid(+)
and a.优惠编码=c.prodid(+)
union all
select * from zhyw.jzc_all_priv_qz_type a) a ' ;
execute immediate (SQL_STRING);
----优惠编码去重
zhyw.shc_drop_retable(upper('jzc_all_priv_hb_type_jg'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_hb_type_jg as
select * from
(select a.*,row_number() over (partition by a.优惠编码,a.项目 order by a.项目 ) 排名
from zhyw.jzc_all_priv_hb_type a
) a
where 排名=1 ' ;
execute immediate (SQL_STRING);
--------------2021年的数据是拍死在 2021-12-31 正是用或停机号码,平时可以不执行，跨年再执行
---省公司 kpi 2020 开户日报表 318.98万
zhyw.shc_drop_retable(upper('jzc_all_priv_hmmx'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_hmmx as
select a.subsid,a.nettype,a.acctid,a.servnumber,a.prodid,a.signtype,
a.createdate,a.registerorgid,a.ownerorgid,a.active,a.status,a.statusdate,pp.prodname,b.last_date3,b.is_active_user
from zhyw.subscriber_'||v_yesrq||'12@zibo a,
zibo.dw_product_mark_ds_'||v_yesrq||'1231 b,
zhyw.product pp
where nvl(pp.prodname,''0'') not like ''%行业应用%''
and nvl(pp.prodname,''0'') not like ''%数据卡%''
--and nvl(b.is_active_user ,''0'')=''1''
and to_char(a.subsid)=b.user_id(+)
and a.prodid=pp.prodid(+)
and a.nettype =''GSM''
----状态正使用或其他状态6个月内
and (a.status in(''US10'',''US30'')
or (a.status not in (''US10'',''US30'') and a.statusdate>=to_date('''||v_yesrq||'''||''09'',''yyyymm'')))
and a.status not in (''US26'',''US28'')
and length(a.servnumber)=11 ' ;
execute immediate (SQL_STRING);
---2022年数据是2022-01-01开户的,当月arpu不出，所以基础数据是到上月底
zhyw.shc_drop_retable(upper('jzc_all_priv_hmmxa'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_hmmxa as
select a.subsid,a.nettype,a.acctid,a.servnumber,a.prodid,a.signtype,
a.createdate,a.registerorgid,a.ownerorgid,a.active,a.status,a.statusdate
from zhyw.subscriber a,
zhyw.product pp
where a.nettype =''GSM''
and nvl(pp.prodname,''0'') not like ''%行业应用%''
and nvl(pp.prodname,''0'') not like ''%数据卡%''
and a.prodid=pp.prodid(+)
and a.status not in (''US26'',''US28'')
and length(a.servnumber)=11
and a.createdate >=to_date('''||v_yesr||'''||''01'',''yyyymm'')
--- v_monsrh 是下月时间
and a.createdate < to_date('''||v_monsrh||''',''yyyymm'') ' ;
execute immediate (SQL_STRING);
-----今年数据与去年数据合并
zhyw.shc_drop_retable(upper('jzc_all_priv_hmmxb'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_hmmxb as
select a.subsid,a.nettype,a.acctid,a.servnumber,a.prodid,a.signtype,
a.createdate,a.registerorgid,a.ownerorgid,a.active,a.status,a.statusdate,'''||v_yesr||''' lx from zhyw.jzc_all_priv_hmmxa a
union all
select a.subsid,a.nettype,a.acctid,a.servnumber,a.prodid,a.signtype,
a.createdate,a.registerorgid,a.ownerorgid,a.active,a.status,a.statusdate,'''||v_yesrq||''' lx from zhyw.jzc_all_priv_hmmx a ' ;
execute immediate (SQL_STRING);
----2021-2022 收入arpu---执行月arpu插入
SQL_STRING:='INSERT INTO zhyw.jzc_all_priv_arpu
select substr(op_time,1,4)||substr(op_time,6,2) cycle ,a.user_id,a.arpu
from zibo.dw_product_'||v_monsr||' a where a.arpu<> 0
and not exists (select 1 from zhyw.jzc_all_priv_arpu t
where substr(a.op_time,1,4)||substr(a.op_time,6,2)=t.cycle and a.user_id=t.user_id) ' ;
execute immediate (SQL_STRING);
commit;
zhyw.shc_drop_retable(upper('jzc_all_priv_arpu_hz'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_arpu_hz as
select a.user_id,
max(case when a.cycle='''||v_yesrq||'''||''01'' then a.arpu end ) q01arpu,
max(case when a.cycle='''||v_yesrq||'''||''02'' then a.arpu end ) q02arpu,
max(case when a.cycle='''||v_yesrq||'''||''03'' then a.arpu end ) q03arpu,
max(case when a.cycle='''||v_yesrq||'''||''04'' then a.arpu end ) q04arpu,
max(case when a.cycle='''||v_yesrq||'''||''05'' then a.arpu end ) q05arpu,
max(case when a.cycle='''||v_yesrq||'''||''06'' then a.arpu end ) q06arpu,
max(case when a.cycle='''||v_yesrq||'''||''07'' then a.arpu end ) q07arpu,
max(case when a.cycle='''||v_yesrq||'''||''08'' then a.arpu end ) q08arpu,
max(case when a.cycle='''||v_yesrq||'''||''09'' then a.arpu end ) q09arpu,
max(case when a.cycle='''||v_yesrq||'''||''10'' then a.arpu end ) q10arpu,
max(case when a.cycle='''||v_yesrq||'''||''11'' then a.arpu end ) q11arpu,
max(case when a.cycle='''||v_yesrq||'''||''12'' then a.arpu end ) q12arpu,
max(case when a.cycle='''||v_yesr||'''||''01'' then a.arpu end ) b01arpu,
max(case when a.cycle='''||v_yesr||'''||''02'' then a.arpu end ) b02arpu,
max(case when a.cycle='''||v_yesr||'''||''03'' then a.arpu end ) b03arpu,
max(case when a.cycle='''||v_yesr||'''||''04'' then a.arpu end ) b04arpu,
max(case when a.cycle='''||v_yesr||'''||''05'' then a.arpu end ) b05arpu,
max(case when a.cycle='''||v_yesr||'''||''06'' then a.arpu end ) b06arpu,
max(case when a.cycle='''||v_yesr||'''||''07'' then a.arpu end ) b07arpu,
max(case when a.cycle='''||v_yesr||'''||''08'' then a.arpu end ) b08arpu,
max(case when a.cycle='''||v_yesr||'''||''09'' then a.arpu end ) b09arpu,
max(case when a.cycle='''||v_yesr||'''||''10'' then a.arpu end ) b10arpu,
max(case when a.cycle='''||v_yesr||'''||''11'' then a.arpu end ) b11arpu,
max(case when a.cycle='''||v_yesr||'''||''12'' then a.arpu end ) b12arpu
from zhyw.jzc_all_priv_arpu a
group by a.user_id ' ;
execute immediate (SQL_STRING);
----2022年每月活跃情况---本月月底活跃情况插入，没有会报错
SQL_STRING:='INSERT INTO zhyw.jzc_all_priv_active
select to_char(a.op_time,''yyyymm'') cycle,a.user_id,a.last_date3,a.is_active_user
from zibo.dw_product_mark_ds_'||v_monsr_last||' a where length(a.product_no)=11
and a.is_active_user=1
and not exists (select 1 from zhyw.jzc_all_priv_active t
where to_char(a.op_time,''yyyymm'')=t.cycle and a.user_id=t.user_id) ' ;
execute immediate (SQL_STRING);
commit;
zhyw.shc_drop_retable(upper('jzc_all_priv_active_hz'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_active_hz as
select a.user_id,
max(case when a.cycle='''||v_yesrq||'''||''12'' then a.is_active_user end ) q12active,
max(case when a.cycle='''||v_yesr||'''||''01'' then a.is_active_user end ) b01active,
max(case when a.cycle='''||v_yesr||'''||''02'' then a.is_active_user end ) b02active,
max(case when a.cycle='''||v_yesr||'''||''03'' then a.is_active_user end ) b03active,
max(case when a.cycle='''||v_yesr||'''||''04'' then a.is_active_user end ) b04active,
max(case when a.cycle='''||v_yesr||'''||''05'' then a.is_active_user end ) b05active,
max(case when a.cycle='''||v_yesr||'''||''06'' then a.is_active_user end ) b06active,
max(case when a.cycle='''||v_yesr||'''||''07'' then a.is_active_user end ) b07active,
max(case when a.cycle='''||v_yesr||'''||''08'' then a.is_active_user end ) b08active,
max(case when a.cycle='''||v_yesr||'''||''09'' then a.is_active_user end ) b09active,
max(case when a.cycle='''||v_yesr||'''||''10'' then a.is_active_user end ) b10active,
max(case when a.cycle='''||v_yesr||'''||''11'' then a.is_active_user end ) b11active,
max(case when a.cycle='''||v_yesr||'''||''12'' then a.is_active_user end ) b12active
from zhyw.jzc_all_priv_active a
group by a.user_id ' ;
execute immediate (SQL_STRING);
-----月底用户状态留存
SQL_STRING:='INSERT INTO zhyw.jzc_all_priv_STATUS
select cycle,b.subsid,b.STATUS
from tbcs.STATB_SUBSCRIBER@bcv b
where b.cycle='''||v_monsr||'''
and STATUS in (''US10'',''US30'')
and length(b.servnumber)=11
and NETTYPE=''GSM''
and not exists (select 1 from zhyw.jzc_all_priv_STATUS t
where b.cycle=t.cycle and b.subsid=t.subsid) ' ;
execute immediate (SQL_STRING);
commit;
SQL_STRING:='INSERT INTO zhyw.jzc_all_priv_STATUS
select cycle,b.subsid,b.STATUS
from newrpt.v_statb_subscriber533@zwbcv b
where cycle='''||v_monsr||'''
and STATUS in (''US10'',''US30'')
and length(b.servnumber)=11
--and NETTYPE=''GSM''
and not exists (select 1 from zhyw.jzc_all_priv_STATUS t
where b.cycle=t.cycle and b.subsid=t.subsid) ' ;
execute immediate (SQL_STRING);
commit;
zhyw.shc_drop_retable(upper('jzc_all_priv_STATUS_hz'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_STATUS_hz as
select a.subsid user_id,
max(case when a.cycle='''||v_yesrq||'''||''12'' then a.status end ) q12status,
max(case when a.cycle='''||v_yesr||'''||''01'' then a.status end ) b01status,
max(case when a.cycle='''||v_yesr||'''||''02'' then a.status end ) b02status,
max(case when a.cycle='''||v_yesr||'''||''03'' then a.status end ) b03status,
max(case when a.cycle='''||v_yesr||'''||''04'' then a.status end ) b04status,
max(case when a.cycle='''||v_yesr||'''||''05'' then a.status end ) b05status,
max(case when a.cycle='''||v_yesr||'''||''06'' then a.status end ) b06status,
max(case when a.cycle='''||v_yesr||'''||''07'' then a.status end ) b07status,
max(case when a.cycle='''||v_yesr||'''||''08'' then a.status end ) b08status,
max(case when a.cycle='''||v_yesr||'''||''09'' then a.status end ) b09status,
max(case when a.cycle='''||v_yesr||'''||''10'' then a.status end ) b10status,
max(case when a.cycle='''||v_yesr||'''||''11'' then a.status end ) b11status,
max(case when a.cycle='''||v_yesr||'''||''12'' then a.status end ) b12status
from zhyw.jzc_all_priv_STATUS a
group by a.subsid ' ;
execute immediate (SQL_STRING);
-------------------携入携出情况
SQL_STRING:='INSERT INTO zhyw.jzc_all_priv_xzzt
select to_char(a.createdate,''yyyymm'') cycle,a.subsid,a.servnumber,
''携入'' type,a.携入类型,a.createdate
from zhyw.shc_fanghao_'||v_monsr||' a
where a.携入类型 is not null
and not exists (select 1 from zhyw.jzc_all_priv_xzzt t
where a.subsid=t.subsid and to_char(a.createdate,''yyyymm'')=t.cycle and t.type=''携入'') ' ;
execute immediate (SQL_STRING);
commit;
SQL_STRING:='INSERT INTO zhyw.jzc_all_priv_xzzt
select to_char(a.报错时间,''yyyymm'') cycle,a.subsid,a.telnum servnumber,''携出'' type,a.校验情况,d.statusdate createdate
from shzc.Cs_Rec_Chgcarriertask a,
(select distinct b.servnumber from zhyw.jzc_all_priv_hmmxb b ) b,
zhyw.subscriber d
where a.telnum=b.servnumber
and a.subsid=d.subsid
and d.statusdate>=to_date('''||v_yesr||'''||''01'',''yyyymm'')
and d.statusdate<to_date('''||v_monsrh||''',''yyyymm'')
and to_char(a.报错时间,''yyyymm'')<'''||v_monsrh||'''
and a.校验情况 in (''已携出'')
and not exists (select 1 from zhyw.jzc_all_priv_xzzt t
where a.subsid=t.subsid and to_char(a.报错时间,''yyyymm'')=t.cycle and t.type=''携出'')' ;
execute immediate (SQL_STRING);
commit;
-------基本组合表
zhyw.shc_drop_retable(upper('jzc_all_priv_hmmxc'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_hmmxc as
select a.subsid,
a.nettype,
a.acctid,
a.servnumber,
t.prodid,
a.signtype,
a.createdate,
a.registerorgid,
t.ownerorgid,
a.lx,
t.active,
t.status,
t.statusdate,
nvl(b.q12arpu,0) q12arpu,
nvl(b.b01arpu,0) b01arpu,
nvl(b.b02arpu,0) b02arpu,
nvl(b.b03arpu,0) b03arpu,
nvl(b.b04arpu,0) b04arpu,
nvl(b.b05arpu,0) b05arpu,
nvl(b.b06arpu,0) b06arpu,
nvl(b.b07arpu,0) b07arpu,
nvl(b.b08arpu,0) b08arpu,
nvl(b.b09arpu,0) b09arpu,
nvl(b.b10arpu,0) b10arpu,
nvl(b.b11arpu,0) b11arpu,
nvl(b.b12arpu,0) b12arpu,
nvl(d.q12active,0) q12active,
nvl(d.b01active,0) b01active,
nvl(d.b02active,0) b02active,
nvl(d.b03active,0) b03active,
nvl(d.b04active,0) b04active,
nvl(d.b05active,0) b05active,
nvl(d.b06active,0) b06active,
nvl(d.b07active,0) b07active,
nvl(d.b08active,0) b08active,
nvl(d.b09active,0) b09active,
nvl(d.b10active,0) b10active,
nvl(d.b11active,0) b11active,
nvl(d.b12active,0) b12active,
nvl(e.q12status,''0'') q12status,
nvl(e.b01status,''0'') b01status,
nvl(e.b02status,''0'') b02status,
nvl(e.b03status,''0'') b03status,
nvl(e.b04status,''0'') b04status,
nvl(e.b05status,''0'') b05status,
nvl(e.b06status,''0'') b06status,
nvl(e.b07status,''0'') b07status,
nvl(e.b08status,''0'') b08status,
nvl(e.b09status,''0'') b09status,
nvl(e.b10status,''0'') b10status,
nvl(e.b11status,''0'') b11status,
nvl(e.b12status,''0'') b12status
from zhyw.jzc_all_priv_hmmxb a,
zhyw.jzc_all_priv_arpu_hz b,
zhyw.jzc_all_priv_active_hz d,
zhyw.jzc_all_priv_STATUS_hz e,
zhyw.subscriber t
where a.subsid=b.user_id(+)
and a.subsid=d.user_id(+)
and a.subsid=e.user_id(+)
and a.subsid=t.subsid(+)' ;
execute immediate (SQL_STRING);
---添加携转情况--中间结果表
zhyw.shc_drop_retable(upper('jzc_all_priv_hmmxd'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_hmmxd as
select a.*,b.type 携入,to_char(b.createdate,''yyyymm'') 携入cycle,c.type 携出,c.cycle 携出cycle
from zhyw.jzc_all_priv_hmmxc a,
(select * from zhyw.jzc_all_priv_xzzt b where b.type=''携入'') b,
(select * from zhyw.jzc_all_priv_xzzt c where c.type=''携出'') c
where a.subsid=b.subsid(+)
and a.subsid=c.subsid(+)' ;
execute immediate (SQL_STRING);
-----涉及用户业务大类有效性
zhyw.shc_drop_retable(upper('jzc_all_priv_hmmxc_yh'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_hmmxc_yh as
select distinct a.subsid,a.prodid,a.privid,a.startdate,a.enddate,a.applyoid,b.项目,b.类型,b.type
from zhyw.subs_privilege a,
(select * from zhyw.jzc_all_priv_hb_type_jg b where b.type=''privid'') b,
zhyw.jzc_all_priv_hmmxb c
where a.subsid=c.subsid
and a.privid=b.优惠编码
and nvl(a.enddate,sysdate+9999)>a.startdate
and nvl(a.enddate,sysdate+9999)>to_date('''||v_yesr||'''||''01'',''yyyymm'') ' ;
execute immediate (SQL_STRING);
zhyw.shc_drop_retable(upper('jzc_all_priv_hmmxc_cp'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_hmmxc_cp as
select distinct a.subsid,a.prodid,a.privid,a.startdate,a.enddate,a.applyoid,b.项目,b.类型,b.type
from zhyw.subs_privilege a,
(select * from zhyw.jzc_all_priv_hb_type_jg b where b.type=''prodid'') b,
zhyw.jzc_all_priv_hmmxb c
where a.subsid=c.subsid
and a.prodid=b.优惠编码
and nvl(a.enddate,sysdate+9999)>a.startdate
and nvl(a.enddate,sysdate+9999)>to_date('''||v_yesr||'''||''01'',''yyyymm'') ' ;
execute immediate (SQL_STRING);
zhyw.shc_drop_retable(upper('jzc_all_priv_hmmxc_hz'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_hmmxc_hz as
select a.subsid,a.类型,a.项目,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''01'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''01'',''yyyymm'') then 1 end,0)) q12yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''02'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''02'',''yyyymm'') then 1 end,0)) b01yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''03'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''03'',''yyyymm'') then 1 end,0)) b02yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''04'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''04'',''yyyymm'') then 1 end,0)) b03yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''05'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''05'',''yyyymm'') then 1 end,0)) b04yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''06'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''06'',''yyyymm'') then 1 end,0)) b05yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''07'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''07'',''yyyymm'') then 1 end,0)) b06yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''08'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''08'',''yyyymm'') then 1 end,0)) b07yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''09'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''09'',''yyyymm'') then 1 end,0)) b08yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''10'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''10'',''yyyymm'') then 1 end,0)) b09yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''11'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''11'',''yyyymm'') then 1 end,0)) b10yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''12'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''12'',''yyyymm'') then 1 end,0)) b11yw,
max(nvl(case when a.startdate<to_date('''||v_yesrm||'''||''01'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesrm||'''||''01'',''yyyymm'') then 1 end,0)) b12yw
from
(select * from zhyw.jzc_all_priv_hmmxc_yh a
union all
select * from zhyw.jzc_all_priv_hmmxc_cp a ) a
group by a.subsid,a.类型,a.项目 ' ;
execute immediate (SQL_STRING);
zhyw.shc_drop_retable(upper('jzc_all_priv_hmmxc_hza'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_hmmxc_hza as
select a.subsid,
max(nvl(case when a.项目=''数字化业务'' then a.q12yw end,0)) q12yw_数字化业务,
max(nvl(case when a.项目=''数字化业务'' then a.b01yw end,0)) b01yw_数字化业务,
max(nvl(case when a.项目=''数字化业务'' then a.b02yw end,0)) b02yw_数字化业务,
max(nvl(case when a.项目=''数字化业务'' then a.b03yw end,0)) b03yw_数字化业务,
max(nvl(case when a.项目=''数字化业务'' then a.b04yw end,0)) b04yw_数字化业务,
max(nvl(case when a.项目=''数字化业务'' then a.b05yw end,0)) b05yw_数字化业务,
max(nvl(case when a.项目=''数字化业务'' then a.b06yw end,0)) b06yw_数字化业务,
max(nvl(case when a.项目=''数字化业务'' then a.b07yw end,0)) b07yw_数字化业务,
max(nvl(case when a.项目=''数字化业务'' then a.b08yw end,0)) b08yw_数字化业务,
max(nvl(case when a.项目=''数字化业务'' then a.b09yw end,0)) b09yw_数字化业务,
max(nvl(case when a.项目=''数字化业务'' then a.b10yw end,0)) b10yw_数字化业务,
max(nvl(case when a.项目=''数字化业务'' then a.b11yw end,0)) b11yw_数字化业务,
max(nvl(case when a.项目=''数字化业务'' then a.b12yw end,0)) b12yw_数字化业务,
max(nvl(case when a.项目=''权益'' then a.q12yw end,0)) q12yw_权益,
max(nvl(case when a.项目=''权益'' then a.b01yw end,0)) b01yw_权益,
max(nvl(case when a.项目=''权益'' then a.b02yw end,0)) b02yw_权益,
max(nvl(case when a.项目=''权益'' then a.b03yw end,0)) b03yw_权益,
max(nvl(case when a.项目=''权益'' then a.b04yw end,0)) b04yw_权益,
max(nvl(case when a.项目=''权益'' then a.b05yw end,0)) b05yw_权益,
max(nvl(case when a.项目=''权益'' then a.b06yw end,0)) b06yw_权益,
max(nvl(case when a.项目=''权益'' then a.b07yw end,0)) b07yw_权益,
max(nvl(case when a.项目=''权益'' then a.b08yw end,0)) b08yw_权益,
max(nvl(case when a.项目=''权益'' then a.b09yw end,0)) b09yw_权益,
max(nvl(case when a.项目=''权益'' then a.b10yw end,0)) b10yw_权益,
max(nvl(case when a.项目=''权益'' then a.b11yw end,0)) b11yw_权益,
max(nvl(case when a.项目=''权益'' then a.b12yw end,0)) b12yw_权益,
max(nvl(case when a.项目=''视频彩铃'' then a.q12yw end,0)) q12yw_视频彩铃,
max(nvl(case when a.项目=''视频彩铃'' then a.b01yw end,0)) b01yw_视频彩铃,
max(nvl(case when a.项目=''视频彩铃'' then a.b02yw end,0)) b02yw_视频彩铃,
max(nvl(case when a.项目=''视频彩铃'' then a.b03yw end,0)) b03yw_视频彩铃,
max(nvl(case when a.项目=''视频彩铃'' then a.b04yw end,0)) b04yw_视频彩铃,
max(nvl(case when a.项目=''视频彩铃'' then a.b05yw end,0)) b05yw_视频彩铃,
max(nvl(case when a.项目=''视频彩铃'' then a.b06yw end,0)) b06yw_视频彩铃,
max(nvl(case when a.项目=''视频彩铃'' then a.b07yw end,0)) b07yw_视频彩铃,
max(nvl(case when a.项目=''视频彩铃'' then a.b08yw end,0)) b08yw_视频彩铃,
max(nvl(case when a.项目=''视频彩铃'' then a.b09yw end,0)) b09yw_视频彩铃,
max(nvl(case when a.项目=''视频彩铃'' then a.b10yw end,0)) b10yw_视频彩铃,
max(nvl(case when a.项目=''视频彩铃'' then a.b11yw end,0)) b11yw_视频彩铃,
max(nvl(case when a.项目=''视频彩铃'' then a.b12yw end,0)) b12yw_视频彩铃,
max(nvl(case when a.项目=''FTTR'' then a.q12yw end,0)) q12yw_FTTR,
max(nvl(case when a.项目=''FTTR'' then a.b01yw end,0)) b01yw_FTTR,
max(nvl(case when a.项目=''FTTR'' then a.b02yw end,0)) b02yw_FTTR,
max(nvl(case when a.项目=''FTTR'' then a.b03yw end,0)) b03yw_FTTR,
max(nvl(case when a.项目=''FTTR'' then a.b04yw end,0)) b04yw_FTTR,
max(nvl(case when a.项目=''FTTR'' then a.b05yw end,0)) b05yw_FTTR,
max(nvl(case when a.项目=''FTTR'' then a.b06yw end,0)) b06yw_FTTR,
max(nvl(case when a.项目=''FTTR'' then a.b07yw end,0)) b07yw_FTTR,
max(nvl(case when a.项目=''FTTR'' then a.b08yw end,0)) b08yw_FTTR,
max(nvl(case when a.项目=''FTTR'' then a.b09yw end,0)) b09yw_FTTR,
max(nvl(case when a.项目=''FTTR'' then a.b10yw end,0)) b10yw_FTTR,
max(nvl(case when a.项目=''FTTR'' then a.b11yw end,0)) b11yw_FTTR,
max(nvl(case when a.项目=''FTTR'' then a.b12yw end,0)) b12yw_FTTR,
max(nvl(case when a.项目=''全光WIFI'' then a.q12yw end,0)) q12yw_全光WIFI,
max(nvl(case when a.项目=''全光WIFI'' then a.b01yw end,0)) b01yw_全光WIFI,
max(nvl(case when a.项目=''全光WIFI'' then a.b02yw end,0)) b02yw_全光WIFI,
max(nvl(case when a.项目=''全光WIFI'' then a.b03yw end,0)) b03yw_全光WIFI,
max(nvl(case when a.项目=''全光WIFI'' then a.b04yw end,0)) b04yw_全光WIFI,
max(nvl(case when a.项目=''全光WIFI'' then a.b05yw end,0)) b05yw_全光WIFI,
max(nvl(case when a.项目=''全光WIFI'' then a.b06yw end,0)) b06yw_全光WIFI,
max(nvl(case when a.项目=''全光WIFI'' then a.b07yw end,0)) b07yw_全光WIFI,
max(nvl(case when a.项目=''全光WIFI'' then a.b08yw end,0)) b08yw_全光WIFI,
max(nvl(case when a.项目=''全光WIFI'' then a.b09yw end,0)) b09yw_全光WIFI,
max(nvl(case when a.项目=''全光WIFI'' then a.b10yw end,0)) b10yw_全光WIFI,
max(nvl(case when a.项目=''全光WIFI'' then a.b11yw end,0)) b11yw_全光WIFI,
max(nvl(case when a.项目=''全光WIFI'' then a.b12yw end,0)) b12yw_全光WIFI,
max(nvl(case when a.项目=''全屋智能'' then a.q12yw end,0)) q12yw_全屋智能,
max(nvl(case when a.项目=''全屋智能'' then a.b01yw end,0)) b01yw_全屋智能,
max(nvl(case when a.项目=''全屋智能'' then a.b02yw end,0)) b02yw_全屋智能,
max(nvl(case when a.项目=''全屋智能'' then a.b03yw end,0)) b03yw_全屋智能,
max(nvl(case when a.项目=''全屋智能'' then a.b04yw end,0)) b04yw_全屋智能,
max(nvl(case when a.项目=''全屋智能'' then a.b05yw end,0)) b05yw_全屋智能,
max(nvl(case when a.项目=''全屋智能'' then a.b06yw end,0)) b06yw_全屋智能,
max(nvl(case when a.项目=''全屋智能'' then a.b07yw end,0)) b07yw_全屋智能,
max(nvl(case when a.项目=''全屋智能'' then a.b08yw end,0)) b08yw_全屋智能,
max(nvl(case when a.项目=''全屋智能'' then a.b09yw end,0)) b09yw_全屋智能,
max(nvl(case when a.项目=''全屋智能'' then a.b10yw end,0)) b10yw_全屋智能,
max(nvl(case when a.项目=''全屋智能'' then a.b11yw end,0)) b11yw_全屋智能,
max(nvl(case when a.项目=''全屋智能'' then a.b12yw end,0)) b12yw_全屋智能,
max(nvl(case when a.项目=''千兆宽带'' then a.q12yw end,0)) q12yw_千兆宽带,
max(nvl(case when a.项目=''千兆宽带'' then a.b01yw end,0)) b01yw_千兆宽带,
max(nvl(case when a.项目=''千兆宽带'' then a.b02yw end,0)) b02yw_千兆宽带,
max(nvl(case when a.项目=''千兆宽带'' then a.b03yw end,0)) b03yw_千兆宽带,
max(nvl(case when a.项目=''千兆宽带'' then a.b04yw end,0)) b04yw_千兆宽带,
max(nvl(case when a.项目=''千兆宽带'' then a.b05yw end,0)) b05yw_千兆宽带,
max(nvl(case when a.项目=''千兆宽带'' then a.b06yw end,0)) b06yw_千兆宽带,
max(nvl(case when a.项目=''千兆宽带'' then a.b07yw end,0)) b07yw_千兆宽带,
max(nvl(case when a.项目=''千兆宽带'' then a.b08yw end,0)) b08yw_千兆宽带,
max(nvl(case when a.项目=''千兆宽带'' then a.b09yw end,0)) b09yw_千兆宽带,
max(nvl(case when a.项目=''千兆宽带'' then a.b10yw end,0)) b10yw_千兆宽带,
max(nvl(case when a.项目=''千兆宽带'' then a.b11yw end,0)) b11yw_千兆宽带,
max(nvl(case when a.项目=''千兆宽带'' then a.b12yw end,0)) b12yw_千兆宽带,
max(nvl(case when a.项目=''家庭安防'' then a.q12yw end,0)) q12yw_家庭安防,
max(nvl(case when a.项目=''家庭安防'' then a.b01yw end,0)) b01yw_家庭安防,
max(nvl(case when a.项目=''家庭安防'' then a.b02yw end,0)) b02yw_家庭安防,
max(nvl(case when a.项目=''家庭安防'' then a.b03yw end,0)) b03yw_家庭安防,
max(nvl(case when a.项目=''家庭安防'' then a.b04yw end,0)) b04yw_家庭安防,
max(nvl(case when a.项目=''家庭安防'' then a.b05yw end,0)) b05yw_家庭安防,
max(nvl(case when a.项目=''家庭安防'' then a.b06yw end,0)) b06yw_家庭安防,
max(nvl(case when a.项目=''家庭安防'' then a.b07yw end,0)) b07yw_家庭安防,
max(nvl(case when a.项目=''家庭安防'' then a.b08yw end,0)) b08yw_家庭安防,
max(nvl(case when a.项目=''家庭安防'' then a.b09yw end,0)) b09yw_家庭安防,
max(nvl(case when a.项目=''家庭安防'' then a.b10yw end,0)) b10yw_家庭安防,
max(nvl(case when a.项目=''家庭安防'' then a.b11yw end,0)) b11yw_家庭安防,
max(nvl(case when a.项目=''家庭安防'' then a.b12yw end,0)) b12yw_家庭安防,
max(nvl(case when a.项目=''宽带提速包'' then a.q12yw end,0)) q12yw_宽带提速包,
max(nvl(case when a.项目=''宽带提速包'' then a.b01yw end,0)) b01yw_宽带提速包,
max(nvl(case when a.项目=''宽带提速包'' then a.b02yw end,0)) b02yw_宽带提速包,
max(nvl(case when a.项目=''宽带提速包'' then a.b03yw end,0)) b03yw_宽带提速包,
max(nvl(case when a.项目=''宽带提速包'' then a.b04yw end,0)) b04yw_宽带提速包,
max(nvl(case when a.项目=''宽带提速包'' then a.b05yw end,0)) b05yw_宽带提速包,
max(nvl(case when a.项目=''宽带提速包'' then a.b06yw end,0)) b06yw_宽带提速包,
max(nvl(case when a.项目=''宽带提速包'' then a.b07yw end,0)) b07yw_宽带提速包,
max(nvl(case when a.项目=''宽带提速包'' then a.b08yw end,0)) b08yw_宽带提速包,
max(nvl(case when a.项目=''宽带提速包'' then a.b09yw end,0)) b09yw_宽带提速包,
max(nvl(case when a.项目=''宽带提速包'' then a.b10yw end,0)) b10yw_宽带提速包,
max(nvl(case when a.项目=''宽带提速包'' then a.b11yw end,0)) b11yw_宽带提速包,
max(nvl(case when a.项目=''宽带提速包'' then a.b12yw end,0)) b12yw_宽带提速包,
max(nvl(case when a.项目=''扩展坞'' then a.q12yw end,0)) q12yw_扩展坞,
max(nvl(case when a.项目=''扩展坞'' then a.b01yw end,0)) b01yw_扩展坞,
max(nvl(case when a.项目=''扩展坞'' then a.b02yw end,0)) b02yw_扩展坞,
max(nvl(case when a.项目=''扩展坞'' then a.b03yw end,0)) b03yw_扩展坞,
max(nvl(case when a.项目=''扩展坞'' then a.b04yw end,0)) b04yw_扩展坞,
max(nvl(case when a.项目=''扩展坞'' then a.b05yw end,0)) b05yw_扩展坞,
max(nvl(case when a.项目=''扩展坞'' then a.b06yw end,0)) b06yw_扩展坞,
max(nvl(case when a.项目=''扩展坞'' then a.b07yw end,0)) b07yw_扩展坞,
max(nvl(case when a.项目=''扩展坞'' then a.b08yw end,0)) b08yw_扩展坞,
max(nvl(case when a.项目=''扩展坞'' then a.b09yw end,0)) b09yw_扩展坞,
max(nvl(case when a.项目=''扩展坞'' then a.b10yw end,0)) b10yw_扩展坞,
max(nvl(case when a.项目=''扩展坞'' then a.b11yw end,0)) b11yw_扩展坞,
max(nvl(case when a.项目=''扩展坞'' then a.b12yw end,0)) b12yw_扩展坞,
max(nvl(case when a.项目=''智能组网'' then a.q12yw end,0)) q12yw_智能组网,
max(nvl(case when a.项目=''智能组网'' then a.b01yw end,0)) b01yw_智能组网,
max(nvl(case when a.项目=''智能组网'' then a.b02yw end,0)) b02yw_智能组网,
max(nvl(case when a.项目=''智能组网'' then a.b03yw end,0)) b03yw_智能组网,
max(nvl(case when a.项目=''智能组网'' then a.b04yw end,0)) b04yw_智能组网,
max(nvl(case when a.项目=''智能组网'' then a.b05yw end,0)) b05yw_智能组网,
max(nvl(case when a.项目=''智能组网'' then a.b06yw end,0)) b06yw_智能组网,
max(nvl(case when a.项目=''智能组网'' then a.b07yw end,0)) b07yw_智能组网,
max(nvl(case when a.项目=''智能组网'' then a.b08yw end,0)) b08yw_智能组网,
max(nvl(case when a.项目=''智能组网'' then a.b09yw end,0)) b09yw_智能组网,
max(nvl(case when a.项目=''智能组网'' then a.b10yw end,0)) b10yw_智能组网,
max(nvl(case when a.项目=''智能组网'' then a.b11yw end,0)) b11yw_智能组网,
max(nvl(case when a.项目=''智能组网'' then a.b12yw end,0)) b12yw_智能组网,
max(nvl(case when a.项目=''点播'' then a.q12yw end,0)) q12yw_点播,
max(nvl(case when a.项目=''点播'' then a.b01yw end,0)) b01yw_点播,
max(nvl(case when a.项目=''点播'' then a.b02yw end,0)) b02yw_点播,
max(nvl(case when a.项目=''点播'' then a.b03yw end,0)) b03yw_点播,
max(nvl(case when a.项目=''点播'' then a.b04yw end,0)) b04yw_点播,
max(nvl(case when a.项目=''点播'' then a.b05yw end,0)) b05yw_点播,
max(nvl(case when a.项目=''点播'' then a.b06yw end,0)) b06yw_点播,
max(nvl(case when a.项目=''点播'' then a.b07yw end,0)) b07yw_点播,
max(nvl(case when a.项目=''点播'' then a.b08yw end,0)) b08yw_点播,
max(nvl(case when a.项目=''点播'' then a.b09yw end,0)) b09yw_点播,
max(nvl(case when a.项目=''点播'' then a.b10yw end,0)) b10yw_点播,
max(nvl(case when a.项目=''点播'' then a.b11yw end,0)) b11yw_点播,
max(nvl(case when a.项目=''点播'' then a.b12yw end,0)) b12yw_点播
from zhyw.jzc_all_priv_hmmxc_hz a
group by a.subsid ' ;
execute immediate (SQL_STRING);
zhyw.shc_drop_retable(upper('jzc_all_priv_hmmxe'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_hmmxe as
select a.*,
nvl(case when substr(a.ownerorgid,8,1) in (select t.county_id from zhyw.rpt_county t where t.note is not null ) then substr(a.ownerorgid,8,1) end,''0'') qx_id,
nvl(case when substr(a.ownerorgid,8,1) in (select t.county_id from zhyw.rpt_county t where t.note is not null ) then t.name end,''市公司'') qx_name,
nvl(b.q12yw_权益,0)+ nvl(b.q12yw_视频彩铃,0)+ nvl(b.q12yw_数字化业务,0) q12yw_编码_权益, nvl(b.q12yw_fttr,0)+ nvl(b.q12yw_点播,0)+ nvl(b.q12yw_家庭安防,0)+ nvl(b.q12yw_宽带提速包,0)+ nvl(b.q12yw_扩展坞,0)+ nvl(b.q12yw_千兆宽带,0)+ nvl(b.q12yw_全光wifi,0)+ nvl(b.q12yw_全屋智能,0)+ nvl(b.q12yw_智能组网,0) q12yw_编码_生态,
nvl(b.b01yw_权益,0)+ nvl(b.b01yw_视频彩铃,0)+ nvl(b.b01yw_数字化业务,0) b01yw_编码_权益, nvl(b.b01yw_fttr,0)+ nvl(b.b01yw_点播,0)+ nvl(b.b01yw_家庭安防,0)+ nvl(b.b01yw_宽带提速包,0)+ nvl(b.b01yw_扩展坞,0)+ nvl(b.b01yw_千兆宽带,0)+ nvl(b.b01yw_全光wifi,0)+ nvl(b.b01yw_全屋智能,0)+ nvl(b.b01yw_智能组网,0) b01yw_编码_生态,
nvl(b.b02yw_权益,0)+ nvl(b.b02yw_视频彩铃,0)+ nvl(b.b02yw_数字化业务,0) b02yw_编码_权益, nvl(b.b02yw_fttr,0)+ nvl(b.b02yw_点播,0)+ nvl(b.b02yw_家庭安防,0)+ nvl(b.b02yw_宽带提速包,0)+ nvl(b.b02yw_扩展坞,0)+ nvl(b.b02yw_千兆宽带,0)+ nvl(b.b02yw_全光wifi,0)+ nvl(b.b02yw_全屋智能,0)+ nvl(b.b02yw_智能组网,0) b02yw_编码_生态,
nvl(b.b03yw_权益,0)+ nvl(b.b03yw_视频彩铃,0)+ nvl(b.b03yw_数字化业务,0) b03yw_编码_权益, nvl(b.b03yw_fttr,0)+ nvl(b.b03yw_点播,0)+ nvl(b.b03yw_家庭安防,0)+ nvl(b.b03yw_宽带提速包,0)+ nvl(b.b03yw_扩展坞,0)+ nvl(b.b03yw_千兆宽带,0)+ nvl(b.b03yw_全光wifi,0)+ nvl(b.b03yw_全屋智能,0)+ nvl(b.b03yw_智能组网,0) b03yw_编码_生态,
nvl(b.b04yw_权益,0)+ nvl(b.b04yw_视频彩铃,0)+ nvl(b.b04yw_数字化业务,0) b04yw_编码_权益, nvl(b.b04yw_fttr,0)+ nvl(b.b04yw_点播,0)+ nvl(b.b04yw_家庭安防,0)+ nvl(b.b04yw_宽带提速包,0)+ nvl(b.b04yw_扩展坞,0)+ nvl(b.b04yw_千兆宽带,0)+ nvl(b.b04yw_全光wifi,0)+ nvl(b.b04yw_全屋智能,0)+ nvl(b.b04yw_智能组网,0) b04yw_编码_生态,
nvl(b.b05yw_权益,0)+ nvl(b.b05yw_视频彩铃,0)+ nvl(b.b05yw_数字化业务,0) b05yw_编码_权益, nvl(b.b05yw_fttr,0)+ nvl(b.b05yw_点播,0)+ nvl(b.b05yw_家庭安防,0)+ nvl(b.b05yw_宽带提速包,0)+ nvl(b.b05yw_扩展坞,0)+ nvl(b.b05yw_千兆宽带,0)+ nvl(b.b05yw_全光wifi,0)+ nvl(b.b05yw_全屋智能,0)+ nvl(b.b05yw_智能组网,0) b05yw_编码_生态,
nvl(b.b06yw_权益,0)+ nvl(b.b06yw_视频彩铃,0)+ nvl(b.b06yw_数字化业务,0) b06yw_编码_权益, nvl(b.b06yw_fttr,0)+ nvl(b.b06yw_点播,0)+ nvl(b.b06yw_家庭安防,0)+ nvl(b.b06yw_宽带提速包,0)+ nvl(b.b06yw_扩展坞,0)+ nvl(b.b06yw_千兆宽带,0)+ nvl(b.b06yw_全光wifi,0)+ nvl(b.b06yw_全屋智能,0)+ nvl(b.b06yw_智能组网,0) b06yw_编码_生态,
nvl(b.b07yw_权益,0)+ nvl(b.b07yw_视频彩铃,0)+ nvl(b.b07yw_数字化业务,0) b07yw_编码_权益, nvl(b.b07yw_fttr,0)+ nvl(b.b07yw_点播,0)+ nvl(b.b07yw_家庭安防,0)+ nvl(b.b07yw_宽带提速包,0)+ nvl(b.b07yw_扩展坞,0)+ nvl(b.b07yw_千兆宽带,0)+ nvl(b.b07yw_全光wifi,0)+ nvl(b.b07yw_全屋智能,0)+ nvl(b.b07yw_智能组网,0) b07yw_编码_生态,
nvl(b.b08yw_权益,0)+ nvl(b.b08yw_视频彩铃,0)+ nvl(b.b08yw_数字化业务,0) b08yw_编码_权益, nvl(b.b08yw_fttr,0)+ nvl(b.b08yw_点播,0)+ nvl(b.b08yw_家庭安防,0)+ nvl(b.b08yw_宽带提速包,0)+ nvl(b.b08yw_扩展坞,0)+ nvl(b.b08yw_千兆宽带,0)+ nvl(b.b08yw_全光wifi,0)+ nvl(b.b08yw_全屋智能,0)+ nvl(b.b08yw_智能组网,0) b08yw_编码_生态,
nvl(b.b09yw_权益,0)+ nvl(b.b09yw_视频彩铃,0)+ nvl(b.b09yw_数字化业务,0) b09yw_编码_权益, nvl(b.b09yw_fttr,0)+ nvl(b.b09yw_点播,0)+ nvl(b.b09yw_家庭安防,0)+ nvl(b.b09yw_宽带提速包,0)+ nvl(b.b09yw_扩展坞,0)+ nvl(b.b09yw_千兆宽带,0)+ nvl(b.b09yw_全光wifi,0)+ nvl(b.b09yw_全屋智能,0)+ nvl(b.b09yw_智能组网,0) b09yw_编码_生态,
nvl(b.b10yw_权益,0)+ nvl(b.b10yw_视频彩铃,0)+ nvl(b.b10yw_数字化业务,0) b10yw_编码_权益, nvl(b.b10yw_fttr,0)+ nvl(b.b10yw_点播,0)+ nvl(b.b10yw_家庭安防,0)+ nvl(b.b10yw_宽带提速包,0)+ nvl(b.b10yw_扩展坞,0)+ nvl(b.b10yw_千兆宽带,0)+ nvl(b.b10yw_全光wifi,0)+ nvl(b.b10yw_全屋智能,0)+ nvl(b.b10yw_智能组网,0) b10yw_编码_生态,
nvl(b.b11yw_权益,0)+ nvl(b.b11yw_视频彩铃,0)+ nvl(b.b11yw_数字化业务,0) b11yw_编码_权益, nvl(b.b11yw_fttr,0)+ nvl(b.b11yw_点播,0)+ nvl(b.b11yw_家庭安防,0)+ nvl(b.b11yw_宽带提速包,0)+ nvl(b.b11yw_扩展坞,0)+ nvl(b.b11yw_千兆宽带,0)+ nvl(b.b11yw_全光wifi,0)+ nvl(b.b11yw_全屋智能,0)+ nvl(b.b11yw_智能组网,0) b11yw_编码_生态,
nvl(b.b12yw_权益,0)+ nvl(b.b12yw_视频彩铃,0)+ nvl(b.b12yw_数字化业务,0) b12yw_编码_权益, nvl(b.b12yw_fttr,0)+ nvl(b.b12yw_点播,0)+ nvl(b.b12yw_家庭安防,0)+ nvl(b.b12yw_宽带提速包,0)+ nvl(b.b12yw_扩展坞,0)+ nvl(b.b12yw_千兆宽带,0)+ nvl(b.b12yw_全光wifi,0)+ nvl(b.b12yw_全屋智能,0)+ nvl(b.b12yw_智能组网,0) b12yw_编码_生态,
nvl(b.q12yw_数字化业务,0) q12yw_数字化业务,
nvl(b.b01yw_数字化业务,0) b01yw_数字化业务,
nvl(b.b02yw_数字化业务,0) b02yw_数字化业务,
nvl(b.b03yw_数字化业务,0) b03yw_数字化业务,
nvl(b.b04yw_数字化业务,0) b04yw_数字化业务,
nvl(b.b05yw_数字化业务,0) b05yw_数字化业务,
nvl(b.b06yw_数字化业务,0) b06yw_数字化业务,
nvl(b.b07yw_数字化业务,0) b07yw_数字化业务,
nvl(b.b08yw_数字化业务,0) b08yw_数字化业务,
nvl(b.b09yw_数字化业务,0) b09yw_数字化业务,
nvl(b.b10yw_数字化业务,0) b10yw_数字化业务,
nvl(b.b11yw_数字化业务,0) b11yw_数字化业务,
nvl(b.b12yw_数字化业务,0) b12yw_数字化业务,
nvl(b.q12yw_权益,0) q12yw_权益,
nvl(b.b01yw_权益,0) b01yw_权益,
nvl(b.b02yw_权益,0) b02yw_权益,
nvl(b.b03yw_权益,0) b03yw_权益,
nvl(b.b04yw_权益,0) b04yw_权益,
nvl(b.b05yw_权益,0) b05yw_权益,
nvl(b.b06yw_权益,0) b06yw_权益,
nvl(b.b07yw_权益,0) b07yw_权益,
nvl(b.b08yw_权益,0) b08yw_权益,
nvl(b.b09yw_权益,0) b09yw_权益,
nvl(b.b10yw_权益,0) b10yw_权益,
nvl(b.b11yw_权益,0) b11yw_权益,
nvl(b.b12yw_权益,0) b12yw_权益,
nvl(b.q12yw_视频彩铃,0) q12yw_视频彩铃,
nvl(b.b01yw_视频彩铃,0) b01yw_视频彩铃,
nvl(b.b02yw_视频彩铃,0) b02yw_视频彩铃,
nvl(b.b03yw_视频彩铃,0) b03yw_视频彩铃,
nvl(b.b04yw_视频彩铃,0) b04yw_视频彩铃,
nvl(b.b05yw_视频彩铃,0) b05yw_视频彩铃,
nvl(b.b06yw_视频彩铃,0) b06yw_视频彩铃,
nvl(b.b07yw_视频彩铃,0) b07yw_视频彩铃,
nvl(b.b08yw_视频彩铃,0) b08yw_视频彩铃,
nvl(b.b09yw_视频彩铃,0) b09yw_视频彩铃,
nvl(b.b10yw_视频彩铃,0) b10yw_视频彩铃,
nvl(b.b11yw_视频彩铃,0) b11yw_视频彩铃,
nvl(b.b12yw_视频彩铃,0) b12yw_视频彩铃,
nvl(b.q12yw_fttr,0) q12yw_fttr,
nvl(b.b01yw_fttr,0) b01yw_fttr,
nvl(b.b02yw_fttr,0) b02yw_fttr,
nvl(b.b03yw_fttr,0) b03yw_fttr,
nvl(b.b04yw_fttr,0) b04yw_fttr,
nvl(b.b05yw_fttr,0) b05yw_fttr,
nvl(b.b06yw_fttr,0) b06yw_fttr,
nvl(b.b07yw_fttr,0) b07yw_fttr,
nvl(b.b08yw_fttr,0) b08yw_fttr,
nvl(b.b09yw_fttr,0) b09yw_fttr,
nvl(b.b10yw_fttr,0) b10yw_fttr,
nvl(b.b11yw_fttr,0) b11yw_fttr,
nvl(b.b12yw_fttr,0) b12yw_fttr,
nvl(b.q12yw_全光wifi,0) q12yw_全光wifi,
nvl(b.b01yw_全光wifi,0) b01yw_全光wifi,
nvl(b.b02yw_全光wifi,0) b02yw_全光wifi,
nvl(b.b03yw_全光wifi,0) b03yw_全光wifi,
nvl(b.b04yw_全光wifi,0) b04yw_全光wifi,
nvl(b.b05yw_全光wifi,0) b05yw_全光wifi,
nvl(b.b06yw_全光wifi,0) b06yw_全光wifi,
nvl(b.b07yw_全光wifi,0) b07yw_全光wifi,
nvl(b.b08yw_全光wifi,0) b08yw_全光wifi,
nvl(b.b09yw_全光wifi,0) b09yw_全光wifi,
nvl(b.b10yw_全光wifi,0) b10yw_全光wifi,
nvl(b.b11yw_全光wifi,0) b11yw_全光wifi,
nvl(b.b12yw_全光wifi,0) b12yw_全光wifi,
nvl(b.q12yw_全屋智能,0) q12yw_全屋智能,
nvl(b.b01yw_全屋智能,0) b01yw_全屋智能,
nvl(b.b02yw_全屋智能,0) b02yw_全屋智能,
nvl(b.b03yw_全屋智能,0) b03yw_全屋智能,
nvl(b.b04yw_全屋智能,0) b04yw_全屋智能,
nvl(b.b05yw_全屋智能,0) b05yw_全屋智能,
nvl(b.b06yw_全屋智能,0) b06yw_全屋智能,
nvl(b.b07yw_全屋智能,0) b07yw_全屋智能,
nvl(b.b08yw_全屋智能,0) b08yw_全屋智能,
nvl(b.b09yw_全屋智能,0) b09yw_全屋智能,
nvl(b.b10yw_全屋智能,0) b10yw_全屋智能,
nvl(b.b11yw_全屋智能,0) b11yw_全屋智能,
nvl(b.b12yw_全屋智能,0) b12yw_全屋智能,
nvl(b.q12yw_千兆宽带,0) q12yw_千兆宽带,
nvl(b.b01yw_千兆宽带,0) b01yw_千兆宽带,
nvl(b.b02yw_千兆宽带,0) b02yw_千兆宽带,
nvl(b.b03yw_千兆宽带,0) b03yw_千兆宽带,
nvl(b.b04yw_千兆宽带,0) b04yw_千兆宽带,
nvl(b.b05yw_千兆宽带,0) b05yw_千兆宽带,
nvl(b.b06yw_千兆宽带,0) b06yw_千兆宽带,
nvl(b.b07yw_千兆宽带,0) b07yw_千兆宽带,
nvl(b.b08yw_千兆宽带,0) b08yw_千兆宽带,
nvl(b.b09yw_千兆宽带,0) b09yw_千兆宽带,
nvl(b.b10yw_千兆宽带,0) b10yw_千兆宽带,
nvl(b.b11yw_千兆宽带,0) b11yw_千兆宽带,
nvl(b.b12yw_千兆宽带,0) b12yw_千兆宽带,
nvl(b.q12yw_家庭安防,0) q12yw_家庭安防,
nvl(b.b01yw_家庭安防,0) b01yw_家庭安防,
nvl(b.b02yw_家庭安防,0) b02yw_家庭安防,
nvl(b.b03yw_家庭安防,0) b03yw_家庭安防,
nvl(b.b04yw_家庭安防,0) b04yw_家庭安防,
nvl(b.b05yw_家庭安防,0) b05yw_家庭安防,
nvl(b.b06yw_家庭安防,0) b06yw_家庭安防,
nvl(b.b07yw_家庭安防,0) b07yw_家庭安防,
nvl(b.b08yw_家庭安防,0) b08yw_家庭安防,
nvl(b.b09yw_家庭安防,0) b09yw_家庭安防,
nvl(b.b10yw_家庭安防,0) b10yw_家庭安防,
nvl(b.b11yw_家庭安防,0) b11yw_家庭安防,
nvl(b.b12yw_家庭安防,0) b12yw_家庭安防,
nvl(b.q12yw_宽带提速包,0) q12yw_宽带提速包,
nvl(b.b01yw_宽带提速包,0) b01yw_宽带提速包,
nvl(b.b02yw_宽带提速包,0) b02yw_宽带提速包,
nvl(b.b03yw_宽带提速包,0) b03yw_宽带提速包,
nvl(b.b04yw_宽带提速包,0) b04yw_宽带提速包,
nvl(b.b05yw_宽带提速包,0) b05yw_宽带提速包,
nvl(b.b06yw_宽带提速包,0) b06yw_宽带提速包,
nvl(b.b07yw_宽带提速包,0) b07yw_宽带提速包,
nvl(b.b08yw_宽带提速包,0) b08yw_宽带提速包,
nvl(b.b09yw_宽带提速包,0) b09yw_宽带提速包,
nvl(b.b10yw_宽带提速包,0) b10yw_宽带提速包,
nvl(b.b11yw_宽带提速包,0) b11yw_宽带提速包,
nvl(b.b12yw_宽带提速包,0) b12yw_宽带提速包,
nvl(b.q12yw_扩展坞,0) q12yw_扩展坞,
nvl(b.b01yw_扩展坞,0) b01yw_扩展坞,
nvl(b.b02yw_扩展坞,0) b02yw_扩展坞,
nvl(b.b03yw_扩展坞,0) b03yw_扩展坞,
nvl(b.b04yw_扩展坞,0) b04yw_扩展坞,
nvl(b.b05yw_扩展坞,0) b05yw_扩展坞,
nvl(b.b06yw_扩展坞,0) b06yw_扩展坞,
nvl(b.b07yw_扩展坞,0) b07yw_扩展坞,
nvl(b.b08yw_扩展坞,0) b08yw_扩展坞,
nvl(b.b09yw_扩展坞,0) b09yw_扩展坞,
nvl(b.b10yw_扩展坞,0) b10yw_扩展坞,
nvl(b.b11yw_扩展坞,0) b11yw_扩展坞,
nvl(b.b12yw_扩展坞,0) b12yw_扩展坞,
nvl(b.q12yw_智能组网,0) q12yw_智能组网,
nvl(b.b01yw_智能组网,0) b01yw_智能组网,
nvl(b.b02yw_智能组网,0) b02yw_智能组网,
nvl(b.b03yw_智能组网,0) b03yw_智能组网,
nvl(b.b04yw_智能组网,0) b04yw_智能组网,
nvl(b.b05yw_智能组网,0) b05yw_智能组网,
nvl(b.b06yw_智能组网,0) b06yw_智能组网,
nvl(b.b07yw_智能组网,0) b07yw_智能组网,
nvl(b.b08yw_智能组网,0) b08yw_智能组网,
nvl(b.b09yw_智能组网,0) b09yw_智能组网,
nvl(b.b10yw_智能组网,0) b10yw_智能组网,
nvl(b.b11yw_智能组网,0) b11yw_智能组网,
nvl(b.b12yw_智能组网,0) b12yw_智能组网,
nvl(b.q12yw_点播,0) q12yw_点播,
nvl(b.b01yw_点播,0) b01yw_点播,
nvl(b.b02yw_点播,0) b02yw_点播,
nvl(b.b03yw_点播,0) b03yw_点播,
nvl(b.b04yw_点播,0) b04yw_点播,
nvl(b.b05yw_点播,0) b05yw_点播,
nvl(b.b06yw_点播,0) b06yw_点播,
nvl(b.b07yw_点播,0) b07yw_点播,
nvl(b.b08yw_点播,0) b08yw_点播,
nvl(b.b09yw_点播,0) b09yw_点播,
nvl(b.b10yw_点播,0) b10yw_点播,
nvl(b.b11yw_点播,0) b11yw_点播,
nvl(b.b12yw_点播,0) b12yw_点播
from zhyw.jzc_all_priv_hmmxd a,
zhyw.jzc_all_priv_hmmxc_hza b,
zhyw.rpt_county t
where a.subsid=b.subsid(+)
and substr(a.ownerorgid,8,1)=t.county_id(+)' ;
execute immediate (SQL_STRING);
-----需要增加每月宽带标记
----有线宽带
zhyw.shc_drop_retable(upper('jzc_all_priv_kdqk'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_kdqk as
select distinct a.subsid,a.startdate,a.enddate,pp.prodname,
nvl(case when nvl(pp.prodname,''0'') like ''%企业宽带%''or nvl(pp.prodname,''0'') like ''%企宽%'' then ''企业''
when nvl(pp.prodname,''0'') like ''%高校%'' then ''学校'' end,''家庭'') 类型
from zhyw.zhai_YXKD_PRIV_SUBS a,
zhyw.jzc_all_priv_hmmxe b,
tbcs.product@bcv pp
where a.subsid=b.subsid
and a.prodid_fw=pp.prodid(+)
and nvl(a.enddate,sysdate+9999)>a.startdate
and a.startdate<to_date('''||v_monsrh||''',''yyyymm'') ' ;
execute immediate (SQL_STRING);
zhyw.shc_drop_retable(upper('jzc_all_priv_kdqk_hz'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_kdqk_hz as
select a.subsid,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''01'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''01'',''yyyymm'') then 1 end,0)) q12yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''02'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''02'',''yyyymm'') then 1 end,0)) b01yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''03'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''03'',''yyyymm'') then 1 end,0)) b02yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''04'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''04'',''yyyymm'') then 1 end,0)) b03yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''05'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''05'',''yyyymm'') then 1 end,0)) b04yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''06'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''06'',''yyyymm'') then 1 end,0)) b05yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''07'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''07'',''yyyymm'') then 1 end,0)) b06yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''08'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''08'',''yyyymm'') then 1 end,0)) b07yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''09'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''09'',''yyyymm'') then 1 end,0)) b08yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''10'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''10'',''yyyymm'') then 1 end,0)) b09yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''11'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''11'',''yyyymm'') then 1 end,0)) b10yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''12'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''12'',''yyyymm'') then 1 end,0)) b11yw,
max(nvl(case when a.startdate<to_date('''||v_yesrm||'''||''01'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesrm||'''||''01'',''yyyymm'') then 1 end,0)) b12yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''01'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''01'',''yyyymm'') and nvl(类型,''0'')=''家庭'' then 1 end,0)) q12yw_jt,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''02'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''02'',''yyyymm'') and nvl(类型,''0'')=''家庭'' then 1 end,0)) b01yw_jt,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''03'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''03'',''yyyymm'') and nvl(类型,''0'')=''家庭'' then 1 end,0)) b02yw_jt,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''04'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''04'',''yyyymm'') and nvl(类型,''0'')=''家庭'' then 1 end,0)) b03yw_jt,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''05'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''05'',''yyyymm'') and nvl(类型,''0'')=''家庭'' then 1 end,0)) b04yw_jt,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''06'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''06'',''yyyymm'') and nvl(类型,''0'')=''家庭'' then 1 end,0)) b05yw_jt,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''07'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''07'',''yyyymm'') and nvl(类型,''0'')=''家庭'' then 1 end,0)) b06yw_jt,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''08'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''08'',''yyyymm'') and nvl(类型,''0'')=''家庭'' then 1 end,0)) b07yw_jt,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''09'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''09'',''yyyymm'') and nvl(类型,''0'')=''家庭'' then 1 end,0)) b08yw_jt,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''10'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''10'',''yyyymm'') and nvl(类型,''0'')=''家庭'' then 1 end,0)) b09yw_jt,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''11'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''11'',''yyyymm'') and nvl(类型,''0'')=''家庭'' then 1 end,0)) b10yw_jt,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''12'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''12'',''yyyymm'') and nvl(类型,''0'')=''家庭'' then 1 end,0)) b11yw_jt,
max(nvl(case when a.startdate<to_date('''||v_yesrm||'''||''01'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesrm||'''||''01'',''yyyymm'') and nvl(类型,''0'')=''家庭'' then 1 end,0)) b12yw_jt,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''01'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''01'',''yyyymm'') and nvl(类型,''0'')=''学校'' then 1 end,0)) q12yw_xx,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''02'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''02'',''yyyymm'') and nvl(类型,''0'')=''学校'' then 1 end,0)) b01yw_xx,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''03'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''03'',''yyyymm'') and nvl(类型,''0'')=''学校'' then 1 end,0)) b02yw_xx,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''04'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''04'',''yyyymm'') and nvl(类型,''0'')=''学校'' then 1 end,0)) b03yw_xx,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''05'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''05'',''yyyymm'') and nvl(类型,''0'')=''学校'' then 1 end,0)) b04yw_xx,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''06'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''06'',''yyyymm'') and nvl(类型,''0'')=''学校'' then 1 end,0)) b05yw_xx,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''07'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''07'',''yyyymm'') and nvl(类型,''0'')=''学校'' then 1 end,0)) b06yw_xx,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''08'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''08'',''yyyymm'') and nvl(类型,''0'')=''学校'' then 1 end,0)) b07yw_xx,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''09'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''09'',''yyyymm'') and nvl(类型,''0'')=''学校'' then 1 end,0)) b08yw_xx,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''10'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''10'',''yyyymm'') and nvl(类型,''0'')=''学校'' then 1 end,0)) b09yw_xx,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''11'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''11'',''yyyymm'') and nvl(类型,''0'')=''学校'' then 1 end,0)) b10yw_xx,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''12'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''12'',''yyyymm'') and nvl(类型,''0'')=''学校'' then 1 end,0)) b11yw_xx,
max(nvl(case when a.startdate<to_date('''||v_yesrm||'''||''01'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesrm||'''||''01'',''yyyymm'') and nvl(类型,''0'')=''学校'' then 1 end,0)) b12yw_xx,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''01'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''01'',''yyyymm'') and nvl(类型,''0'')=''企业'' then 1 end,0)) q12yw_qy,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''02'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''02'',''yyyymm'') and nvl(类型,''0'')=''企业'' then 1 end,0)) b01yw_qy,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''03'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''03'',''yyyymm'') and nvl(类型,''0'')=''企业'' then 1 end,0)) b02yw_qy,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''04'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''04'',''yyyymm'') and nvl(类型,''0'')=''企业'' then 1 end,0)) b03yw_qy,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''05'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''05'',''yyyymm'') and nvl(类型,''0'')=''企业'' then 1 end,0)) b04yw_qy,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''06'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''06'',''yyyymm'') and nvl(类型,''0'')=''企业'' then 1 end,0)) b05yw_qy,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''07'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''07'',''yyyymm'') and nvl(类型,''0'')=''企业'' then 1 end,0)) b06yw_qy,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''08'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''08'',''yyyymm'') and nvl(类型,''0'')=''企业'' then 1 end,0)) b07yw_qy,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''09'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''09'',''yyyymm'') and nvl(类型,''0'')=''企业'' then 1 end,0)) b08yw_qy,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''10'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''10'',''yyyymm'') and nvl(类型,''0'')=''企业'' then 1 end,0)) b09yw_qy,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''11'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''11'',''yyyymm'') and nvl(类型,''0'')=''企业'' then 1 end,0)) b10yw_qy,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''12'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''12'',''yyyymm'') and nvl(类型,''0'')=''企业'' then 1 end,0)) b11yw_qy,
max(nvl(case when a.startdate<to_date('''||v_yesrm||'''||''01'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesrm||'''||''01'',''yyyymm'') and nvl(类型,''0'')=''企业'' then 1 end,0)) b12yw_qy
from zhyw.jzc_all_priv_kdqk a
group by a.subsid' ;
execute immediate (SQL_STRING);
---和TV
zhyw.shc_drop_retable(upper('jzc_all_priv_htvqk'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_htvqk as
select distinct a.subsid,a.startdate,a.enddate
from zhyw.zhai_htv_subsxin a,
zhyw.jzc_all_priv_hmmxe b
where a.subsid=b.subsid
and nvl(a.enddate,sysdate+9999)>a.startdate
and a.startdate<to_date('''||v_monsrh||''',''yyyymm'') ' ;
execute immediate (SQL_STRING);
zhyw.shc_drop_retable(upper('jzc_all_priv_htvqk_hz'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_htvqk_hz as
select a.subsid,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''01'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''01'',''yyyymm'') then 1 end,0)) q12yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''02'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''02'',''yyyymm'') then 1 end,0)) b01yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''03'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''03'',''yyyymm'') then 1 end,0)) b02yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''04'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''04'',''yyyymm'') then 1 end,0)) b03yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''05'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''05'',''yyyymm'') then 1 end,0)) b04yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''06'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''06'',''yyyymm'') then 1 end,0)) b05yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''07'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''07'',''yyyymm'') then 1 end,0)) b06yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''08'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''08'',''yyyymm'') then 1 end,0)) b07yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''09'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''09'',''yyyymm'') then 1 end,0)) b08yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''10'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''10'',''yyyymm'') then 1 end,0)) b09yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''11'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''11'',''yyyymm'') then 1 end,0)) b10yw,
max(nvl(case when a.startdate<to_date('''||v_yesr||'''||''12'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesr||'''||''12'',''yyyymm'') then 1 end,0)) b11yw,
max(nvl(case when a.startdate<to_date('''||v_yesrm||'''||''01'',''yyyymm'') and nvl(a.enddate,sysdate+9999)>=to_date('''||v_yesrm||'''||''01'',''yyyymm'') then 1 end,0)) b12yw
from zhyw.jzc_all_priv_htvqk a
group by a.subsid' ;
execute immediate (SQL_STRING);
----终端合约
zhyw.shc_drop_retable(upper('shc_pk_zhongduan_'||v_monsr),'ZHYW');
SQL_STRING:='create table zhyw.shc_pk_zhongduan_'||v_monsr||' as
select imei,prodname,recdate,sale_channel_id,t1.orgname,
case when ( 是否信用购=''1'' or prodname in (''全省升舱金币优惠购机合约（自办）'',''全省升舱金币优惠购机合约（代办）'')) then ''1'' else ''0'' end 是否信用购 ,recopid ,luru_subsid ,t2.班组类型,t2.unit_id,录入号码套餐价值
from
zhyw.LSHT_zibo_term_sale'||v_monsr||' t1,
zhyw.shc_organization t2
where prodname is not null
and t1.sale_channel_id=t2.orgid(+)
and prodname is not null
and prodname not like ''%路由器%''
and prodname not like ''%中小学%''
and prodname not like ''%集团%'' ' ;
execute immediate (SQL_STRING);
SQL_STRING:='delete zhyw.jzc_all_priv_reward where cycle = '''||v_monsr||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;
SQL_STRING:=' insert into zhyw.jzc_all_priv_reward
select to_char(a.recdate,''yyyymm'') cycle,a.luru_subsid subsid,
count(distinct a.imei) imeis,max(a.prodname) prodname,max(a.imei) imei,
sum(a.是否信用购) 是否信用购
from zhyw.shc_pk_zhongduan_'||v_monsr||' a
group by to_char(a.recdate,''yyyymm''),a.luru_subsid' ;
execute immediate (SQL_STRING);
COMMIT;
zhyw.shc_drop_retable(upper('jzc_all_priv_reward_hz'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_reward_hz as
select a.subsid user_id,
max(case when a.cycle='''||v_yesrq||'''||''12'' then a.imeis end ) q12status,
max(case when a.cycle='''||v_yesr||'''||''01'' then a.imeis end ) b01status,
max(case when a.cycle='''||v_yesr||'''||''02'' then a.imeis end ) b02status,
max(case when a.cycle='''||v_yesr||'''||''03'' then a.imeis end ) b03status,
max(case when a.cycle='''||v_yesr||'''||''04'' then a.imeis end ) b04status,
max(case when a.cycle='''||v_yesr||'''||''05'' then a.imeis end ) b05status,
max(case when a.cycle='''||v_yesr||'''||''06'' then a.imeis end ) b06status,
max(case when a.cycle='''||v_yesr||'''||''07'' then a.imeis end ) b07status,
max(case when a.cycle='''||v_yesr||'''||''08'' then a.imeis end ) b08status,
max(case when a.cycle='''||v_yesr||'''||''09'' then a.imeis end ) b09status,
max(case when a.cycle='''||v_yesr||'''||''10'' then a.imeis end ) b10status,
max(case when a.cycle='''||v_yesr||'''||''11'' then a.imeis end ) b11status,
max(case when a.cycle='''||v_yesr||'''||''12'' then a.imeis end ) b12status
from zhyw.jzc_all_priv_reward a
group by a.subsid ' ;
execute immediate (SQL_STRING);
--（三）客户特征
-- 1.5G机卡套客户渗透率
-- 2.5G终端渗透率
-- 3.5G套包渗透率
-- 4.5G网络渗透率
-- 5.融合客户渗透率
-- 6.非一体化客户数
SQL_STRING:='delete zhyw.jzc_all_priv_khtz where cycle = '''||v_monsr||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;
SQL_STRING:=' insert into zhyw.jzc_all_priv_khtz
select '''||v_monsr||''' cycle,a.subsid,
nvl(b.is_5gtac,0) is_5gtac,
nvl(b.is_5gtaoc,0) is_5gtaoc,
nvl(b.is_5guser,0) is_5guser,
nvl(b.use_flag,0) use_flag,
nvl(case when nvl(b.use_flag,0)=1 and nvl(b.band_flag,0)=5 then 1 end,0) is_5gwang,
nvl(case when nvl(d.typenames,0)>=1 then 1 end,0) is_ronghe,
nvl(case when nvl(e.counts,0)>=1 then 1 end,0) is_oneth
from zhyw.jzc_all_priv_hmmxd a,
zibo.DW_NEWBUSI_PRODUCT_5G_'||v_monsr_last||' b,
(select d.subsid,count(distinct d.typename) typenames
from zhyw.SHC_202202_PK_RONGHE_MX_'||v_monsr_last||' d
group by d.subsid) d,
(select e.subsid,count(*) counts from zhyw.SHC_TYTAOCAN_'||v_monsr||' e
where nvl(enddate,sysdate+9999)>to_date('''||v_monsrh||''',''yyyymm'')
and nvl(startdate,sysdate+9999)<to_date('''||v_monsrh||''',''yyyymm'')
group by e.subsid) e
where a.subsid=b.user_id(+)
and a.subsid=d.subsid(+)
and a.subsid=e.subsid(+)' ;
execute immediate (SQL_STRING);
COMMIT;
zhyw.shc_drop_retable(upper('jzc_all_priv_khtz_hza'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_khtz_hza as
select a.subsid,
max(nvl(case when a.cycle='''||v_yesrq||'''||''12'' and a.is_5gtac=1 and a.is_5guser=1 and a.use_flag=1 then 1 end,0) ) q12tz_jkw,
max(nvl(case when a.cycle='''||v_yesrq||'''||''12'' and a.is_5gtac=1 then 1 end,0) ) q12tz_zd,
max(nvl(case when a.cycle='''||v_yesrq||'''||''12'' and a.is_5guser=1 then 1 end,0) ) q12tz_taob,
max(nvl(case when a.cycle='''||v_yesrq||'''||''12'' and a.is_5gwang=1 then 1 end,0) ) q12tz_wang,
max(nvl(case when a.cycle='''||v_yesrq||'''||''12'' and a.is_ronghe=1 then 1 end,0) ) q12tz_rongh,
max(nvl(case when a.cycle='''||v_yesrq||'''||''12'' and a.is_oneth=1 then 1 end,0) ) q12tz_oneth,
max(nvl(case when a.cycle='''||v_yesr||'''||''01'' and a.is_5gtac=1 and a.is_5guser=1 and a.use_flag=1 then 1 end,0) ) b01tz_jkw,
max(nvl(case when a.cycle='''||v_yesr||'''||''01'' and a.is_5gtac=1 then 1 end,0) ) b01tz_zd,
max(nvl(case when a.cycle='''||v_yesr||'''||''01'' and a.is_5guser=1 then 1 end,0) ) b01tz_taob,
max(nvl(case when a.cycle='''||v_yesr||'''||''01'' and a.is_5gwang=1 then 1 end,0) ) b01tz_wang,
max(nvl(case when a.cycle='''||v_yesr||'''||''01'' and a.is_ronghe=1 then 1 end,0) ) b01tz_rongh,
max(nvl(case when a.cycle='''||v_yesr||'''||''01'' and a.is_oneth=1 then 1 end,0) ) b01tz_oneth,
max(nvl(case when a.cycle='''||v_yesr||'''||''02'' and a.is_5gtac=1 and a.is_5guser=1 and a.use_flag=1 then 1 end,0) ) b02tz_jkw,
max(nvl(case when a.cycle='''||v_yesr||'''||''02'' and a.is_5gtac=1 then 1 end,0) ) b02tz_zd,
max(nvl(case when a.cycle='''||v_yesr||'''||''02'' and a.is_5guser=1 then 1 end,0) ) b02tz_taob,
max(nvl(case when a.cycle='''||v_yesr||'''||''02'' and a.is_5gwang=1 then 1 end,0) ) b02tz_wang,
max(nvl(case when a.cycle='''||v_yesr||'''||''02'' and a.is_ronghe=1 then 1 end,0) ) b02tz_rongh,
max(nvl(case when a.cycle='''||v_yesr||'''||''02'' and a.is_oneth=1 then 1 end,0) ) b02tz_oneth,
max(nvl(case when a.cycle='''||v_yesr||'''||''03'' and a.is_5gtac=1 and a.is_5guser=1 and a.use_flag=1 then 1 end,0) ) b03tz_jkw,
max(nvl(case when a.cycle='''||v_yesr||'''||''03'' and a.is_5gtac=1 then 1 end,0) ) b03tz_zd,
max(nvl(case when a.cycle='''||v_yesr||'''||''03'' and a.is_5guser=1 then 1 end,0) ) b03tz_taob,
max(nvl(case when a.cycle='''||v_yesr||'''||''03'' and a.is_5gwang=1 then 1 end,0) ) b03tz_wang,
max(nvl(case when a.cycle='''||v_yesr||'''||''03'' and a.is_ronghe=1 then 1 end,0) ) b03tz_rongh,
max(nvl(case when a.cycle='''||v_yesr||'''||''03'' and a.is_oneth=1 then 1 end,0) ) b03tz_oneth,
max(nvl(case when a.cycle='''||v_yesr||'''||''04'' and a.is_5gtac=1 and a.is_5guser=1 and a.use_flag=1 then 1 end,0) ) b04tz_jkw,
max(nvl(case when a.cycle='''||v_yesr||'''||''04'' and a.is_5gtac=1 then 1 end,0) ) b04tz_zd,
max(nvl(case when a.cycle='''||v_yesr||'''||''04'' and a.is_5guser=1 then 1 end,0) ) b04tz_taob,
max(nvl(case when a.cycle='''||v_yesr||'''||''04'' and a.is_5gwang=1 then 1 end,0) ) b04tz_wang,
max(nvl(case when a.cycle='''||v_yesr||'''||''04'' and a.is_ronghe=1 then 1 end,0) ) b04tz_rongh,
max(nvl(case when a.cycle='''||v_yesr||'''||''04'' and a.is_oneth=1 then 1 end,0) ) b04tz_oneth,
max(nvl(case when a.cycle='''||v_yesr||'''||''05'' and a.is_5gtac=1 and a.is_5guser=1 and a.use_flag=1 then 1 end,0) ) b05tz_jkw,
max(nvl(case when a.cycle='''||v_yesr||'''||''05'' and a.is_5gtac=1 then 1 end,0) ) b05tz_zd,
max(nvl(case when a.cycle='''||v_yesr||'''||''05'' and a.is_5guser=1 then 1 end,0) ) b05tz_taob,
max(nvl(case when a.cycle='''||v_yesr||'''||''05'' and a.is_5gwang=1 then 1 end,0) ) b05tz_wang,
max(nvl(case when a.cycle='''||v_yesr||'''||''05'' and a.is_ronghe=1 then 1 end,0) ) b05tz_rongh,
max(nvl(case when a.cycle='''||v_yesr||'''||''05'' and a.is_oneth=1 then 1 end,0) ) b05tz_oneth,
max(nvl(case when a.cycle='''||v_yesr||'''||''06'' and a.is_5gtac=1 and a.is_5guser=1 and a.use_flag=1 then 1 end,0) ) b06tz_jkw,
max(nvl(case when a.cycle='''||v_yesr||'''||''06'' and a.is_5gtac=1 then 1 end,0) ) b06tz_zd,
max(nvl(case when a.cycle='''||v_yesr||'''||''06'' and a.is_5guser=1 then 1 end,0) ) b06tz_taob,
max(nvl(case when a.cycle='''||v_yesr||'''||''06'' and a.is_5gwang=1 then 1 end,0) ) b06tz_wang,
max(nvl(case when a.cycle='''||v_yesr||'''||''06'' and a.is_ronghe=1 then 1 end,0) ) b06tz_rongh,
max(nvl(case when a.cycle='''||v_yesr||'''||''06'' and a.is_oneth=1 then 1 end,0) ) b06tz_oneth,
max(nvl(case when a.cycle='''||v_yesr||'''||''07'' and a.is_5gtac=1 and a.is_5guser=1 and a.use_flag=1 then 1 end,0) ) b07tz_jkw,
max(nvl(case when a.cycle='''||v_yesr||'''||''07'' and a.is_5gtac=1 then 1 end,0) ) b07tz_zd,
max(nvl(case when a.cycle='''||v_yesr||'''||''07'' and a.is_5guser=1 then 1 end,0) ) b07tz_taob,
max(nvl(case when a.cycle='''||v_yesr||'''||''07'' and a.is_5gwang=1 then 1 end,0) ) b07tz_wang,
max(nvl(case when a.cycle='''||v_yesr||'''||''07'' and a.is_ronghe=1 then 1 end,0) ) b07tz_rongh,
max(nvl(case when a.cycle='''||v_yesr||'''||''07'' and a.is_oneth=1 then 1 end,0) ) b07tz_oneth,
max(nvl(case when a.cycle='''||v_yesr||'''||''08'' and a.is_5gtac=1 and a.is_5guser=1 and a.use_flag=1 then 1 end,0) ) b08tz_jkw,
max(nvl(case when a.cycle='''||v_yesr||'''||''08'' and a.is_5gtac=1 then 1 end,0) ) b08tz_zd,
max(nvl(case when a.cycle='''||v_yesr||'''||''08'' and a.is_5guser=1 then 1 end,0) ) b08tz_taob,
max(nvl(case when a.cycle='''||v_yesr||'''||''08'' and a.is_5gwang=1 then 1 end,0) ) b08tz_wang,
max(nvl(case when a.cycle='''||v_yesr||'''||''08'' and a.is_ronghe=1 then 1 end,0) ) b08tz_rongh,
max(nvl(case when a.cycle='''||v_yesr||'''||''08'' and a.is_oneth=1 then 1 end,0) ) b08tz_oneth,
max(nvl(case when a.cycle='''||v_yesr||'''||''09'' and a.is_5gtac=1 and a.is_5guser=1 and a.use_flag=1 then 1 end,0) ) b09tz_jkw,
max(nvl(case when a.cycle='''||v_yesr||'''||''09'' and a.is_5gtac=1 then 1 end,0) ) b09tz_zd,
max(nvl(case when a.cycle='''||v_yesr||'''||''09'' and a.is_5guser=1 then 1 end,0) ) b09tz_taob,
max(nvl(case when a.cycle='''||v_yesr||'''||''09'' and a.is_5gwang=1 then 1 end,0) ) b09tz_wang,
max(nvl(case when a.cycle='''||v_yesr||'''||''09'' and a.is_ronghe=1 then 1 end,0) ) b09tz_rongh,
max(nvl(case when a.cycle='''||v_yesr||'''||''09'' and a.is_oneth=1 then 1 end,0) ) b09tz_oneth,
max(nvl(case when a.cycle='''||v_yesr||'''||''10'' and a.is_5gtac=1 and a.is_5guser=1 and a.use_flag=1 then 1 end,0) ) b10tz_jkw,
max(nvl(case when a.cycle='''||v_yesr||'''||''10'' and a.is_5gtac=1 then 1 end,0) ) b10tz_zd,
max(nvl(case when a.cycle='''||v_yesr||'''||''10'' and a.is_5guser=1 then 1 end,0) ) b10tz_taob,
max(nvl(case when a.cycle='''||v_yesr||'''||''10'' and a.is_5gwang=1 then 1 end,0) ) b10tz_wang,
max(nvl(case when a.cycle='''||v_yesr||'''||''10'' and a.is_ronghe=1 then 1 end,0) ) b10tz_rongh,
max(nvl(case when a.cycle='''||v_yesr||'''||''10'' and a.is_oneth=1 then 1 end,0) ) b10tz_oneth,
max(nvl(case when a.cycle='''||v_yesr||'''||''11'' and a.is_5gtac=1 and a.is_5guser=1 and a.use_flag=1 then 1 end,0) ) b11tz_jkw,
max(nvl(case when a.cycle='''||v_yesr||'''||''11'' and a.is_5gtac=1 then 1 end,0) ) b11tz_zd,
max(nvl(case when a.cycle='''||v_yesr||'''||''11'' and a.is_5guser=1 then 1 end,0) ) b11tz_taob,
max(nvl(case when a.cycle='''||v_yesr||'''||''11'' and a.is_5gwang=1 then 1 end,0) ) b11tz_wang,
max(nvl(case when a.cycle='''||v_yesr||'''||''11'' and a.is_ronghe=1 then 1 end,0) ) b11tz_rongh,
max(nvl(case when a.cycle='''||v_yesr||'''||''11'' and a.is_oneth=1 then 1 end,0) ) b11tz_oneth,
max(nvl(case when a.cycle='''||v_yesr||'''||''12'' and a.is_5gtac=1 and a.is_5guser=1 and a.use_flag=1 then 1 end,0) ) b12tz_jkw,
max(nvl(case when a.cycle='''||v_yesr||'''||''12'' and a.is_5gtac=1 then 1 end,0) ) b12tz_zd,
max(nvl(case when a.cycle='''||v_yesr||'''||''12'' and a.is_5guser=1 then 1 end,0) ) b12tz_taob,
max(nvl(case when a.cycle='''||v_yesr||'''||''12'' and a.is_5gwang=1 then 1 end,0) ) b12tz_wang,
max(nvl(case when a.cycle='''||v_yesr||'''||''12'' and a.is_ronghe=1 then 1 end,0) ) b12tz_rongh,
max(nvl(case when a.cycle='''||v_yesr||'''||''12'' and a.is_oneth=1 then 1 end,0) ) b12tz_oneth
from zhyw.jzc_all_priv_khtz a
group by a.subsid ' ;
execute immediate (SQL_STRING);
----有线宽带 带宽限制 2021-12开始
SQL_STRING:='delete zhyw.jzc_all_priv_1000kd where cycle = '''||v_monsr||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;
----有线宽带 带宽限制 2021-12开始
SQL_STRING:=' insert into zhyw.jzc_all_priv_1000kd
select distinct '''||v_monsr||''' cycle,a.servnumber,a.subsid,nvl(b.BANDWIDTH,0) 带宽限制
from zhyw.zhai_yxkd_priv_subs a,
zibo.DW_MOBILEWIRE_USER_'||v_monsr_last||' b
where nvl(b.BANDWIDTH,0)>=1000
and a.subsid=b.user_id(+)
and nvl(a.enddate,sysdate+9999)>a.startdate
and a.startdate<to_date('''||v_monsrh||''',''yyyymm'')
and nvl(a.enddate,sysdate+9999)>=to_date('''||v_monsrh||''',''yyyymm'') ' ;
execute immediate (SQL_STRING);
COMMIT;
zhyw.shc_drop_retable(upper('jzc_all_priv_1000kd_hz'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_1000kd_hz as
select a.subsid user_id,
max(case when a.cycle='''||v_yesrq||'''||''12'' then a.带宽限制 end ) q12status,
max(case when a.cycle='''||v_yesr||'''||''01'' then a.带宽限制 end ) b01status,
max(case when a.cycle='''||v_yesr||'''||''02'' then a.带宽限制 end ) b02status,
max(case when a.cycle='''||v_yesr||'''||''03'' then a.带宽限制 end ) b03status,
max(case when a.cycle='''||v_yesr||'''||''04'' then a.带宽限制 end ) b04status,
max(case when a.cycle='''||v_yesr||'''||''05'' then a.带宽限制 end ) b05status,
max(case when a.cycle='''||v_yesr||'''||''06'' then a.带宽限制 end ) b06status,
max(case when a.cycle='''||v_yesr||'''||''07'' then a.带宽限制 end ) b07status,
max(case when a.cycle='''||v_yesr||'''||''08'' then a.带宽限制 end ) b08status,
max(case when a.cycle='''||v_yesr||'''||''09'' then a.带宽限制 end ) b09status,
max(case when a.cycle='''||v_yesr||'''||''10'' then a.带宽限制 end ) b10status,
max(case when a.cycle='''||v_yesr||'''||''11'' then a.带宽限制 end ) b11status,
max(case when a.cycle='''||v_yesr||'''||''12'' then a.带宽限制 end ) b12status
from zhyw.jzc_all_priv_1000kd a
group by a.subsid ' ;
execute immediate (SQL_STRING);
-----升降档数据从 2022-05开始
SQL_STRING:='delete zhyw.jzc_all_priv_updow where cycle = '''||v_monsr||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;
SQL_STRING:=' insert into zhyw.jzc_all_priv_updow
select distinct '''||v_monsr||''' cycle,a.subsid,a.servnumber,''升档'' type
from zhyw.zxr_capfee_sd_'||v_monsr||' a ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;
SQL_STRING:=' insert into zhyw.jzc_all_priv_updow
select distinct '''||v_monsr||''' cycle,a.subsid,a.servnumber,''降档'' type
from zhyw.zxr_capfee_jd_'||v_monsr||' a ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;
zhyw.shc_drop_retable(upper('jzc_all_priv_updow_hz'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_updow_hz as
select a.subsid user_id,a.type,
max(nvl(case when a.cycle='''||v_yesrq||'''||''12'' then 1 end,0 )) q12status,
max(nvl(case when a.cycle='''||v_yesr||'''||''01'' then 1 end,0 ) ) b01status,
max(nvl(case when a.cycle='''||v_yesr||'''||''02'' then 1 end,0 ) ) b02status,
max(nvl(case when a.cycle='''||v_yesr||'''||''03'' then 1 end,0 ) ) b03status,
max(nvl(case when a.cycle='''||v_yesr||'''||''04'' then 1 end,0 ) ) b04status,
max(nvl(case when a.cycle='''||v_yesr||'''||''05'' then 1 end,0 ) ) b05status,
max(nvl(case when a.cycle='''||v_yesr||'''||''06'' then 1 end,0 ) ) b06status,
max(nvl(case when a.cycle='''||v_yesr||'''||''07'' then 1 end,0 ) ) b07status,
max(nvl(case when a.cycle='''||v_yesr||'''||''08'' then 1 end,0 ) ) b08status,
max(nvl(case when a.cycle='''||v_yesr||'''||''09'' then 1 end,0 ) ) b09status,
max(nvl(case when a.cycle='''||v_yesr||'''||''10'' then 1 end,0 ) ) b10status,
max(nvl(case when a.cycle='''||v_yesr||'''||''11'' then 1 end,0 ) ) b11status,
max(nvl(case when a.cycle='''||v_yesr||'''||''12'' then 1 end,0 ) ) b12status
from zhyw.jzc_all_priv_updow a
group by a.subsid,a.type ' ;
execute immediate (SQL_STRING);
---arpu 分层
---[0,30) [30,50) [50,80) [80,120) 120+
--- 0 1 2 3 4
zhyw.shc_drop_retable(upper('jzc_all_priv_hmmxf'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_hmmxf as
select a.*,
nvl( t.q12yw ,0) q12yw_宽带,
nvl( t.b01yw ,0) b01yw_宽带,
nvl( t.b02yw ,0) b02yw_宽带,
nvl( t.b03yw ,0) b03yw_宽带,
nvl( t.b04yw ,0) b04yw_宽带,
nvl( t.b05yw ,0) b05yw_宽带,
nvl( t.b06yw ,0) b06yw_宽带,
nvl( t.b07yw ,0) b07yw_宽带,
nvl( t.b08yw ,0) b08yw_宽带,
nvl( t.b09yw ,0) b09yw_宽带,
nvl( t.b10yw ,0) b10yw_宽带,
nvl( t.b11yw ,0) b11yw_宽带,
nvl( t.b12yw ,0) b12yw_宽带,
nvl( t.q12yw_jt ,0) q12yw_家庭宽带,
nvl( t.b01yw_jt ,0) b01yw_家庭宽带,
nvl( t.b02yw_jt ,0) b02yw_家庭宽带,
nvl( t.b03yw_jt ,0) b03yw_家庭宽带,
nvl( t.b04yw_jt ,0) b04yw_家庭宽带,
nvl( t.b05yw_jt ,0) b05yw_家庭宽带,
nvl( t.b06yw_jt ,0) b06yw_家庭宽带,
nvl( t.b07yw_jt ,0) b07yw_家庭宽带,
nvl( t.b08yw_jt ,0) b08yw_家庭宽带,
nvl( t.b09yw_jt ,0) b09yw_家庭宽带,
nvl( t.b10yw_jt ,0) b10yw_家庭宽带,
nvl( t.b11yw_jt ,0) b11yw_家庭宽带,
nvl( t.b12yw_jt ,0) b12yw_家庭宽带,
nvl( t.q12yw_qy ,0) q12yw_企业宽带,
nvl( t.b01yw_qy ,0) b01yw_企业宽带,
nvl( t.b02yw_qy ,0) b02yw_企业宽带,
nvl( t.b03yw_qy ,0) b03yw_企业宽带,
nvl( t.b04yw_qy ,0) b04yw_企业宽带,
nvl( t.b05yw_qy ,0) b05yw_企业宽带,
nvl( t.b06yw_qy ,0) b06yw_企业宽带,
nvl( t.b07yw_qy ,0) b07yw_企业宽带,
nvl( t.b08yw_qy ,0) b08yw_企业宽带,
nvl( t.b09yw_qy ,0) b09yw_企业宽带,
nvl( t.b10yw_qy ,0) b10yw_企业宽带,
nvl( t.b11yw_qy ,0) b11yw_企业宽带,
nvl( t.b12yw_qy ,0) b12yw_企业宽带,
nvl( t.q12yw_xx ,0) q12yw_学校宽带,
nvl( t.b01yw_xx ,0) b01yw_学校宽带,
nvl( t.b02yw_xx ,0) b02yw_学校宽带,
nvl( t.b03yw_xx ,0) b03yw_学校宽带,
nvl( t.b04yw_xx ,0) b04yw_学校宽带,
nvl( t.b05yw_xx ,0) b05yw_学校宽带,
nvl( t.b06yw_xx ,0) b06yw_学校宽带,
nvl( t.b07yw_xx ,0) b07yw_学校宽带,
nvl( t.b08yw_xx ,0) b08yw_学校宽带,
nvl( t.b09yw_xx ,0) b09yw_学校宽带,
nvl( t.b10yw_xx ,0) b10yw_学校宽带,
nvl( t.b11yw_xx ,0) b11yw_学校宽带,
nvl( t.b12yw_xx ,0) b12yw_学校宽带,
nvl( h.q12yw ,0) q12yw_htv,
nvl( h.b01yw ,0) b01yw_htv,
nvl( h.b02yw ,0) b02yw_htv,
nvl( h.b03yw ,0) b03yw_htv,
nvl( h.b04yw ,0) b04yw_htv,
nvl( h.b05yw ,0) b05yw_htv,
nvl( h.b06yw ,0) b06yw_htv,
nvl( h.b07yw ,0) b07yw_htv,
nvl( h.b08yw ,0) b08yw_htv,
nvl( h.b09yw ,0) b09yw_htv,
nvl( h.b10yw ,0) b10yw_htv,
nvl( h.b11yw ,0) b11yw_htv,
nvl( h.b12yw ,0) b12yw_htv,
nvl( i.q12status,0) q12yw_zdhy,
nvl( i.b01status,0) b01yw_zdhy,
nvl( i.b02status,0) b02yw_zdhy,
nvl( i.b03status,0) b03yw_zdhy,
nvl( i.b04status,0) b04yw_zdhy,
nvl( i.b05status,0) b05yw_zdhy,
nvl( i.b06status,0) b06yw_zdhy,
nvl( i.b07status,0) b07yw_zdhy,
nvl( i.b08status,0) b08yw_zdhy,
nvl( i.b09status,0) b09yw_zdhy,
nvl( i.b10status,0) b10yw_zdhy,
nvl( i.b11status,0) b11yw_zdhy,
nvl( i.b12status,0) b12yw_zdhy,
nvl(b.q12tz_jkw,0) q12tz_jkw,
nvl(b.q12tz_zd,0) q12tz_zd,
nvl(b.q12tz_taob,0) q12tz_taob,
nvl(b.q12tz_wang,0) q12tz_wang,
nvl(b.q12tz_rongh,0) q12tz_rongh,
nvl(b.q12tz_oneth,0) q12tz_oneth,
nvl(b.b01tz_jkw,0) b01tz_jkw,
nvl(b.b01tz_zd,0) b01tz_zd,
nvl(b.b01tz_taob,0) b01tz_taob,
nvl(b.b01tz_wang,0) b01tz_wang,
nvl(b.b01tz_rongh,0) b01tz_rongh,
nvl(b.b01tz_oneth,0) b01tz_oneth,
nvl(b.b02tz_jkw,0) b02tz_jkw,
nvl(b.b02tz_zd,0) b02tz_zd,
nvl(b.b02tz_taob,0) b02tz_taob,
nvl(b.b02tz_wang,0) b02tz_wang,
nvl(b.b02tz_rongh,0) b02tz_rongh,
nvl(b.b02tz_oneth,0) b02tz_oneth,
nvl(b.b03tz_jkw,0) b03tz_jkw,
nvl(b.b03tz_zd,0) b03tz_zd,
nvl(b.b03tz_taob,0) b03tz_taob,
nvl(b.b03tz_wang,0) b03tz_wang,
nvl(b.b03tz_rongh,0) b03tz_rongh,
nvl(b.b03tz_oneth,0) b03tz_oneth,
nvl(b.b04tz_jkw,0) b04tz_jkw,
nvl(b.b04tz_zd,0) b04tz_zd,
nvl(b.b04tz_taob,0) b04tz_taob,
nvl(b.b04tz_wang,0) b04tz_wang,
nvl(b.b04tz_rongh,0) b04tz_rongh,
nvl(b.b04tz_oneth,0) b04tz_oneth,
nvl(b.b05tz_jkw,0) b05tz_jkw,
nvl(b.b05tz_zd,0) b05tz_zd,
nvl(b.b05tz_taob,0) b05tz_taob,
nvl(b.b05tz_wang,0) b05tz_wang,
nvl(b.b05tz_rongh,0) b05tz_rongh,
nvl(b.b05tz_oneth,0) b05tz_oneth,
nvl(b.b06tz_jkw,0) b06tz_jkw,
nvl(b.b06tz_zd,0) b06tz_zd,
nvl(b.b06tz_taob,0) b06tz_taob,
nvl(b.b06tz_wang,0) b06tz_wang,
nvl(b.b06tz_rongh,0) b06tz_rongh,
nvl(b.b06tz_oneth,0) b06tz_oneth,
nvl(b.b07tz_jkw,0) b07tz_jkw,
nvl(b.b07tz_zd,0) b07tz_zd,
nvl(b.b07tz_taob,0) b07tz_taob,
nvl(b.b07tz_wang,0) b07tz_wang,
nvl(b.b07tz_rongh,0) b07tz_rongh,
nvl(b.b07tz_oneth,0) b07tz_oneth,
nvl(b.b08tz_jkw,0) b08tz_jkw,
nvl(b.b08tz_zd,0) b08tz_zd,
nvl(b.b08tz_taob,0) b08tz_taob,
nvl(b.b08tz_wang,0) b08tz_wang,
nvl(b.b08tz_rongh,0) b08tz_rongh,
nvl(b.b08tz_oneth,0) b08tz_oneth,
nvl(b.b09tz_jkw,0) b09tz_jkw,
nvl(b.b09tz_zd,0) b09tz_zd,
nvl(b.b09tz_taob,0) b09tz_taob,
nvl(b.b09tz_wang,0) b09tz_wang,
nvl(b.b09tz_rongh,0) b09tz_rongh,
nvl(b.b09tz_oneth,0) b09tz_oneth,
nvl(b.b10tz_jkw,0) b10tz_jkw,
nvl(b.b10tz_zd,0) b10tz_zd,
nvl(b.b10tz_taob,0) b10tz_taob,
nvl(b.b10tz_wang,0) b10tz_wang,
nvl(b.b10tz_rongh,0) b10tz_rongh,
nvl(b.b10tz_oneth,0) b10tz_oneth,
nvl(b.b11tz_jkw,0) b11tz_jkw,
nvl(b.b11tz_zd,0) b11tz_zd,
nvl(b.b11tz_taob,0) b11tz_taob,
nvl(b.b11tz_wang,0) b11tz_wang,
nvl(b.b11tz_rongh,0) b11tz_rongh,
nvl(b.b11tz_oneth,0) b11tz_oneth,
nvl(b.b12tz_jkw,0) b12tz_jkw,
nvl(b.b12tz_zd,0) b12tz_zd,
nvl(b.b12tz_taob,0) b12tz_taob,
nvl(b.b12tz_wang,0) b12tz_wang,
nvl(b.b12tz_rongh,0) b12tz_rongh,
nvl(b.b12tz_oneth,0) b12tz_oneth,
---arpu分档
nvl(case when q12arpu >= 0 and q12arpu < 30 then 0 when q12arpu >= 30 and q12arpu < 50 then 1 when q12arpu >= 50 and q12arpu < 80 then 2 when q12arpu >= 80 and q12arpu <120 then 3 when q12arpu >= 120 then 4 end,5) q12_arpu_dc,
nvl(case when b01arpu >= 0 and b01arpu < 30 then 0 when b01arpu >= 30 and b01arpu < 50 then 1 when b01arpu >= 50 and b01arpu < 80 then 2 when b01arpu >= 80 and b01arpu <120 then 3 when b01arpu >= 120 then 4 end,5) b01_arpu_dc,
nvl(case when b02arpu >= 0 and b02arpu < 30 then 0 when b02arpu >= 30 and b02arpu < 50 then 1 when b02arpu >= 50 and b02arpu < 80 then 2 when b02arpu >= 80 and b02arpu <120 then 3 when b02arpu >= 120 then 4 end,5) b02_arpu_dc,
nvl(case when b03arpu >= 0 and b03arpu < 30 then 0 when b03arpu >= 30 and b03arpu < 50 then 1 when b03arpu >= 50 and b03arpu < 80 then 2 when b03arpu >= 80 and b03arpu <120 then 3 when b03arpu >= 120 then 4 end,5) b03_arpu_dc,
nvl(case when b04arpu >= 0 and b04arpu < 30 then 0 when b04arpu >= 30 and b04arpu < 50 then 1 when b04arpu >= 50 and b04arpu < 80 then 2 when b04arpu >= 80 and b04arpu <120 then 3 when b04arpu >= 120 then 4 end,5) b04_arpu_dc,
nvl(case when b05arpu >= 0 and b05arpu < 30 then 0 when b05arpu >= 30 and b05arpu < 50 then 1 when b05arpu >= 50 and b05arpu < 80 then 2 when b05arpu >= 80 and b05arpu <120 then 3 when b05arpu >= 120 then 4 end,5) b05_arpu_dc,
nvl(case when b06arpu >= 0 and b06arpu < 30 then 0 when b06arpu >= 30 and b06arpu < 50 then 1 when b06arpu >= 50 and b06arpu < 80 then 2 when b06arpu >= 80 and b06arpu <120 then 3 when b06arpu >= 120 then 4 end,5) b06_arpu_dc,
nvl(case when b07arpu >= 0 and b07arpu < 30 then 0 when b07arpu >= 30 and b07arpu < 50 then 1 when b07arpu >= 50 and b07arpu < 80 then 2 when b07arpu >= 80 and b07arpu <120 then 3 when b07arpu >= 120 then 4 end,5) b07_arpu_dc,
nvl(case when b08arpu >= 0 and b08arpu < 30 then 0 when b08arpu >= 30 and b08arpu < 50 then 1 when b08arpu >= 50 and b08arpu < 80 then 2 when b08arpu >= 80 and b08arpu <120 then 3 when b08arpu >= 120 then 4 end,5) b08_arpu_dc,
nvl(case when b09arpu >= 0 and b09arpu < 30 then 0 when b09arpu >= 30 and b09arpu < 50 then 1 when b09arpu >= 50 and b09arpu < 80 then 2 when b09arpu >= 80 and b09arpu <120 then 3 when b09arpu >= 120 then 4 end,5) b09_arpu_dc,
nvl(case when b10arpu >= 0 and b10arpu < 30 then 0 when b10arpu >= 30 and b10arpu < 50 then 1 when b10arpu >= 50 and b10arpu < 80 then 2 when b10arpu >= 80 and b10arpu <120 then 3 when b10arpu >= 120 then 4 end,5) b10_arpu_dc,
nvl(case when b11arpu >= 0 and b11arpu < 30 then 0 when b11arpu >= 30 and b11arpu < 50 then 1 when b11arpu >= 50 and b11arpu < 80 then 2 when b11arpu >= 80 and b11arpu <120 then 3 when b11arpu >= 120 then 4 end,5) b11_arpu_dc,
nvl(case when b12arpu >= 0 and b12arpu < 30 then 0 when b12arpu >= 30 and b12arpu < 50 then 1 when b12arpu >= 50 and b12arpu < 80 then 2 when b12arpu >= 80 and b12arpu <120 then 3 when b12arpu >= 120 then 4 end,5) b12_arpu_dc,
---当月正常在用
nvl(case when a.q12status in (''US10'',''US30'') and a.q12active=1 then 1 end,0) q12_zczy,
nvl(case when a.b01status in (''US10'',''US30'') and a.b01active=1 then 1 end,0) b01_zczy,
nvl(case when a.b02status in (''US10'',''US30'') and a.b02active=1 then 1 end,0) b02_zczy,
nvl(case when a.b03status in (''US10'',''US30'') and a.b03active=1 then 1 end,0) b03_zczy,
nvl(case when a.b04status in (''US10'',''US30'') and a.b04active=1 then 1 end,0) b04_zczy,
nvl(case when a.b05status in (''US10'',''US30'') and a.b05active=1 then 1 end,0) b05_zczy,
nvl(case when a.b06status in (''US10'',''US30'') and a.b06active=1 then 1 end,0) b06_zczy,
nvl(case when a.b07status in (''US10'',''US30'') and a.b07active=1 then 1 end,0) b07_zczy,
nvl(case when a.b08status in (''US10'',''US30'') and a.b08active=1 then 1 end,0) b08_zczy,
nvl(case when a.b09status in (''US10'',''US30'') and a.b09active=1 then 1 end,0) b09_zczy,
nvl(case when a.b10status in (''US10'',''US30'') and a.b10active=1 then 1 end,0) b10_zczy,
nvl(case when a.b11status in (''US10'',''US30'') and a.b11active=1 then 1 end,0) b11_zczy,
nvl(case when a.b12status in (''US10'',''US30'') and a.b12active=1 then 1 end,0) b12_zczy,
---当月离网
nvl(case when a.b01status not in (''US10'',''US30'') and a.q12status in (''US10'',''US30'') then 1 end,0) b01_dylw,
nvl(case when a.b02status not in (''US10'',''US30'') and a.b01status in (''US10'',''US30'') then 1 end,0) b02_dylw,
nvl(case when a.b03status not in (''US10'',''US30'') and a.b02status in (''US10'',''US30'') then 1 end,0) b03_dylw,
nvl(case when a.b04status not in (''US10'',''US30'') and a.b03status in (''US10'',''US30'') then 1 end,0) b04_dylw,
nvl(case when a.b05status not in (''US10'',''US30'') and a.b04status in (''US10'',''US30'') then 1 end,0) b05_dylw,
nvl(case when a.b06status not in (''US10'',''US30'') and a.b05status in (''US10'',''US30'') then 1 end,0) b06_dylw,
nvl(case when a.b07status not in (''US10'',''US30'') and a.b06status in (''US10'',''US30'') then 1 end,0) b07_dylw,
nvl(case when a.b08status not in (''US10'',''US30'') and a.b07status in (''US10'',''US30'') then 1 end,0) b08_dylw,
nvl(case when a.b09status not in (''US10'',''US30'') and a.b08status in (''US10'',''US30'') then 1 end,0) b09_dylw,
nvl(case when a.b10status not in (''US10'',''US30'') and a.b09status in (''US10'',''US30'') then 1 end,0) b10_dylw,
nvl(case when a.b11status not in (''US10'',''US30'') and a.b10status in (''US10'',''US30'') then 1 end,0) b11_dylw,
nvl(case when a.b12status not in (''US10'',''US30'') and a.b11status in (''US10'',''US30'') then 1 end,0) b12_dylw,
---千兆带宽
nvl(case when nvl(q.q12status,0) >=1000 then 1 end,0) q12_1000,
nvl(case when nvl(q.b01status,0) >=1000 then 1 end,0) b01_1000,
nvl(case when nvl(q.b02status,0) >=1000 then 1 end,0) b02_1000,
nvl(case when nvl(q.b03status,0) >=1000 then 1 end,0) b03_1000,
nvl(case when nvl(q.b04status,0) >=1000 then 1 end,0) b04_1000,
nvl(case when nvl(q.b05status,0) >=1000 then 1 end,0) b05_1000,
nvl(case when nvl(q.b06status,0) >=1000 then 1 end,0) b06_1000,
nvl(case when nvl(q.b07status,0) >=1000 then 1 end,0) b07_1000,
nvl(case when nvl(q.b08status,0) >=1000 then 1 end,0) b08_1000,
nvl(case when nvl(q.b09status,0) >=1000 then 1 end,0) b09_1000,
nvl(case when nvl(q.b10status,0) >=1000 then 1 end,0) b10_1000,
nvl(case when nvl(q.b11status,0) >=1000 then 1 end,0) b11_1000,
nvl(case when nvl(q.b12status,0) >=1000 then 1 end,0) b12_1000,
---升档
nvl(case when nvl(sd.q12status,0) >=1 then 1 end,0) q12_up,
nvl(case when nvl(sd.b01status,0) >=1 then 1 end,0) b01_up,
nvl(case when nvl(sd.b02status,0) >=1 then 1 end,0) b02_up,
nvl(case when nvl(sd.b03status,0) >=1 then 1 end,0) b03_up,
nvl(case when nvl(sd.b04status,0) >=1 then 1 end,0) b04_up,
nvl(case when nvl(sd.b05status,0) >=1 then 1 end,0) b05_up,
nvl(case when nvl(sd.b06status,0) >=1 then 1 end,0) b06_up,
nvl(case when nvl(sd.b07status,0) >=1 then 1 end,0) b07_up,
nvl(case when nvl(sd.b08status,0) >=1 then 1 end,0) b08_up,
nvl(case when nvl(sd.b09status,0) >=1 then 1 end,0) b09_up,
nvl(case when nvl(sd.b10status,0) >=1 then 1 end,0) b10_up,
nvl(case when nvl(sd.b11status,0) >=1 then 1 end,0) b11_up,
nvl(case when nvl(sd.b12status,0) >=1 then 1 end,0) b12_up,
---降档
nvl(case when nvl(jd.q12status,0) >=1 then 1 end,0) q12_dow,
nvl(case when nvl(jd.b01status,0) >=1 then 1 end,0) b01_dow,
nvl(case when nvl(jd.b02status,0) >=1 then 1 end,0) b02_dow,
nvl(case when nvl(jd.b03status,0) >=1 then 1 end,0) b03_dow,
nvl(case when nvl(jd.b04status,0) >=1 then 1 end,0) b04_dow,
nvl(case when nvl(jd.b05status,0) >=1 then 1 end,0) b05_dow,
nvl(case when nvl(jd.b06status,0) >=1 then 1 end,0) b06_dow,
nvl(case when nvl(jd.b07status,0) >=1 then 1 end,0) b07_dow,
nvl(case when nvl(jd.b08status,0) >=1 then 1 end,0) b08_dow,
nvl(case when nvl(jd.b09status,0) >=1 then 1 end,0) b09_dow,
nvl(case when nvl(jd.b10status,0) >=1 then 1 end,0) b10_dow,
nvl(case when nvl(jd.b11status,0) >=1 then 1 end,0) b11_dow,
nvl(case when nvl(jd.b12status,0) >=1 then 1 end,0) b12_dow
from zhyw.jzc_all_priv_hmmxe a ,
zhyw.jzc_all_priv_khtz_hza b,
zhyw.jzc_all_priv_kdqk_hz t,
zhyw.jzc_all_priv_htvqk_hz h,
zhyw.jzc_all_priv_reward_hz i,
zhyw.JZC_ALL_PRIV_1000KD_HZ q,
(select * from zhyw.JZC_ALL_PRIV_UPDOW_HZ sd where TYPE=''升档'') sd,
(select * from zhyw.JZC_ALL_PRIV_UPDOW_HZ jd where TYPE=''降档'') jd
where a.subsid=b.subsid(+)
and a.subsid=t.subsid(+)
and a.subsid=h.subsid(+)
and a.subsid=i.USER_ID(+)
and a.subsid=q.USER_ID(+)
and a.subsid=sd.USER_ID(+)
and a.subsid=jd.USER_ID(+) ' ;
execute immediate (SQL_STRING);
zhyw.shc_drop_retable(upper('jzc_all_priv_hmmxg'),'ZHYW');
SQL_STRING:='create table zhyw.jzc_all_priv_hmmxg as
select a.*,
---展示ARPU由低档次升入对应分层中的客户数
nvl(case when a.b01_arpu_dc>a.q12_arpu_dc then 1 end,0) b01_arpu_dc_up,
nvl(case when a.b02_arpu_dc>a.b01_arpu_dc then 1 end,0) b02_arpu_dc_up,
nvl(case when a.b03_arpu_dc>a.b02_arpu_dc then 1 end,0) b03_arpu_dc_up,
nvl(case when a.b04_arpu_dc>a.b03_arpu_dc then 1 end,0) b04_arpu_dc_up,
nvl(case when a.b05_arpu_dc>a.b04_arpu_dc then 1 end,0) b05_arpu_dc_up,
nvl(case when a.b06_arpu_dc>a.b05_arpu_dc then 1 end,0) b06_arpu_dc_up,
nvl(case when a.b07_arpu_dc>a.b06_arpu_dc then 1 end,0) b07_arpu_dc_up,
nvl(case when a.b08_arpu_dc>a.b07_arpu_dc then 1 end,0) b08_arpu_dc_up,
nvl(case when a.b09_arpu_dc>a.b08_arpu_dc then 1 end,0) b09_arpu_dc_up,
nvl(case when a.b10_arpu_dc>a.b09_arpu_dc then 1 end,0) b10_arpu_dc_up,
nvl(case when a.b11_arpu_dc>a.b10_arpu_dc then 1 end,0) b11_arpu_dc_up,
nvl(case when a.b12_arpu_dc>a.b11_arpu_dc then 1 end,0) b12_arpu_dc_up,
---展示ARPU由高档次降入对应分层中的客户数
nvl(case when a.b01_arpu_dc<a.q12_arpu_dc then 1 end,0) b01_arpu_dc_dow,
nvl(case when a.b02_arpu_dc<a.b01_arpu_dc then 1 end,0) b02_arpu_dc_dow,
nvl(case when a.b03_arpu_dc<a.b02_arpu_dc then 1 end,0) b03_arpu_dc_dow,
nvl(case when a.b04_arpu_dc<a.b03_arpu_dc then 1 end,0) b04_arpu_dc_dow,
nvl(case when a.b05_arpu_dc<a.b04_arpu_dc then 1 end,0) b05_arpu_dc_dow,
nvl(case when a.b06_arpu_dc<a.b05_arpu_dc then 1 end,0) b06_arpu_dc_dow,
nvl(case when a.b07_arpu_dc<a.b06_arpu_dc then 1 end,0) b07_arpu_dc_dow,
nvl(case when a.b08_arpu_dc<a.b07_arpu_dc then 1 end,0) b08_arpu_dc_dow,
nvl(case when a.b09_arpu_dc<a.b08_arpu_dc then 1 end,0) b09_arpu_dc_dow,
nvl(case when a.b10_arpu_dc<a.b09_arpu_dc then 1 end,0) b10_arpu_dc_dow,
nvl(case when a.b11_arpu_dc<a.b10_arpu_dc then 1 end,0) b11_arpu_dc_dow,
nvl(case when a.b12_arpu_dc<a.b11_arpu_dc then 1 end,0) b12_arpu_dc_dow
from zhyw.jzc_all_priv_hmmxf a ' ;
execute immediate (SQL_STRING);
zhyw.shc_drop_retable(upper('JZC_ALL_PRIV_HMMX'),'ZHYW');
zhyw.shc_drop_retable(upper('JZC_ALL_PRIV_HMMXB'),'ZHYW');
zhyw.shc_drop_retable(upper('JZC_ALL_PRIV_ARPU_HZ'),'ZHYW');
zhyw.shc_drop_retable(upper('JZC_ALL_PRIV_ACTIVE_HZ'),'ZHYW');
zhyw.shc_drop_retable(upper('JZC_ALL_PRIV_STATUS_HZ'),'ZHYW');
zhyw.shc_drop_retable(upper('JZC_ALL_PRIV_HMMXC'),'ZHYW');
zhyw.shc_drop_retable(upper('JZC_ALL_PRIV_HMMXD'),'ZHYW');
zhyw.shc_drop_retable(upper('JZC_ALL_PRIV_HMMXC_YH'),'ZHYW');
zhyw.shc_drop_retable(upper('JZC_ALL_PRIV_HMMXC_CP'),'ZHYW');
zhyw.shc_drop_retable(upper('JZC_ALL_PRIV_HMMXC_HZ'),'ZHYW');
zhyw.shc_drop_retable(upper('JZC_ALL_PRIV_HMMXC_HZA'),'ZHYW');
zhyw.shc_drop_retable(upper('JZC_ALL_PRIV_KHTZ_HZA'),'ZHYW');
zhyw.shc_drop_retable(upper('JZC_ALL_PRIV_HMMXE'),'ZHYW');
zhyw.shc_drop_retable(upper('JZC_ALL_PRIV_HMMXF'),'ZHYW');
---剩余大表压缩处理
SQL_STRING:='alter table zhyw.JZC_ALL_PRIV_HMMXG move tablespace DS_DATA1 compress' ;
execute immediate (SQL_STRING);
扫一扫
04-16
1290
1290
02-27
964
964
09-23
911
911
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
