20230516_数据库语句_一号双终端

------服务列表
create table tmp_20230511_ykszd_mxa as
select b.itemname,a.*
from 
 tbcs.subs_service@bcv  a,
(select * from tbcs.service@bcv where itemname like '%一号双终端%') b
where a.serviceid=b.itemid


select a.itemname,count(distinct a.subsid) 
from  shzc.tmp_20230511_ykszd_mxa a where nvl(a.enddate,sysdate+9999)>a.startdate
and nvl(a.enddate,sysdate+9999)>to_date('202304','yyyymm')
group by a.itemname;

create table tmp_20230511_ykszd_mxb as
select * from shzc.tmp_20230511_ykszd_mxa a where nvl(a.enddate,sysdate+9999)>a.startdate
and nvl(a.enddate,sysdate+9999)>to_date('202304','yyyymm')
and a.startdate>=to_date('202304','yyyymm');

create table tmp_20230511_ykszd_mxc as
select a.subsid,a.serviceid,a.itemname,a.prodid,a.startdate,a.applyoid,a.enddate,a.canceloid,
b.formnum,b.entitytype,b.entityid,b.servnumber,b.contacttype,b.recdefid,b.recorgid,b.recopid,b.recdate,b.notes,b.macaddr,b.ipaddress,
t2.dictname 受理方式,t4.itemname  业务类型,t5.dictname 鉴权,
case when b.verfitytype='AuthCheckE' and  t6.orderid is not null then '手输身份证' end  鉴权2
from tmp_20230511_ykszd_mxb a,
 zhyw.shc_reception b,
  (select * from  tbcs.dict_item@bcv  where groupid='bsac')  t2,
    tbcs.reception_define@bcv t4,
    ( select  * from tbcs.dict_item@bcv  where groupid ='AuthCheck' and status='1') t5,
    (select *from tbcs.cs_cust_order_attr@bcv  where  attrid= 'SUBSAUTHGATHERMODE' and  attrvalue='3')  t6
where a.applyoid=b.oid(+)
and b.contacttype =t2.dictid(+) 
    and b.recdefid=t4.itemid(+)
    and b.verfitytype=t5.dictid(+)
    and  b.orderid=to_char(t6.orderid(+));
    
    create table tmp_20230511_ykszd_mxd as
    select a.*  from 
    (select b.*,a.recdate recdate_ykdzd,row_number() over (partition by b.entityid,a.recdate  order by b.recdate desc ) 排名 
    from zhyw.shc_reception b,
    (select distinct a.entityid,a.recdate  from  tmp_20230511_ykszd_mxc a ) a
    where b.entityid=a.entityid
    and b.recdate<a.recdate
    and nvl(b.ISBACKPROCESS,'0')='0') a
    where 排名<3;
    
    create table tmp_20230511_ykszd_mxe as
    select b.排名,b.recdate_ykdzd,b.oid,b.formnum,b.entitytype,b.entityid,b.servnumber,b.contacttype,b.recdefid,b.recorgid,b.recopid,b.recdate,b.notes,b.macaddr,b.ipaddress,
t2.dictname 受理方式,t4.itemname  业务类型,t5.dictname 鉴权,
case when b.verfitytype='AuthCheckE' and  t6.orderid is not null then '手输身份证' end  鉴权2
from tmp_20230511_ykszd_mxd b,
  (select * from  tbcs.dict_item@bcv  where groupid='bsac')  t2,
    tbcs.reception_define@bcv t4,
    ( select  * from tbcs.dict_item@bcv  where groupid ='AuthCheck' and status='1') t5,
    (select *from tbcs.cs_cust_order_attr@bcv  where  attrid= 'SUBSAUTHGATHERMODE' and  attrvalue='3')  t6
where b.contacttype =t2.dictid(+) 
    and b.recdefid=t4.itemid(+)
    and b.verfitytype=t5.dictid(+)
    and  b.orderid=to_char(t6.orderid(+));
    
    create table tmp_20230511_ykszd_mxf as
    select a.*,
    t1.oid 前1oid,t1.recorgid 前1recorgid,t1.recopid 前1recopid,t1.recdate 前1recdate,t1.受理方式 前1受理方式,t1.业务类型 前1业务类型,t1.鉴权 前1鉴权,t1.鉴权2 前1鉴权2,t1.notes 前1notes,
    t2.oid 前2oid,t2.recorgid 前2recorgid,t2.recopid 前2recopid,t2.recdate 前2recdate,t2.受理方式 前2受理方式,t2.业务类型 前2业务类型,t2.鉴权 前2鉴权,t2.鉴权2 前2鉴权2,t2.notes 前2notes
    from tmp_20230511_ykszd_mxc a,
    (select * from tmp_20230511_ykszd_mxe t where t.排名=1) t1,
    (select * from tmp_20230511_ykszd_mxe t where t.排名=2) t2
    where a.subsid=t1.entityid(+)
    and a.recdate=t1.recdate_ykdzd(+)
    and a.subsid=t2.entityid(+)
    and a.recdate=t2.recdate_ykdzd(+);
    
      INSERT INTO zibo.shzc_wzsc_mb
  select distinct a.servnumber from shzc.tmp_20230511_ykszd_mxf a   ;
  commit;
  
  select a.* from zibo.shzc_wzsc_mb a
  
  drop table tmp_20230511_ykszd_mxg ;
  
  create table tmp_20230511_ykszd_mxg as
    select a.*,to_char(a.start_datetime,'yyyymmdd') day from zibo.shzc_wzsc_mbhm_result a ,
    (select distinct t.servnumber from tmp_20230511_ykszd_mxf t ) t
    where a.product_no=t.servnumber;
    
    ---串号
    
    create table tmp_20230511_ykszd_mxg_imeihz as
    select a.product_no,substr(a.imei,1,14) imei14,a.day,count(*) cs 
    from tmp_20230511_ykszd_mxg a where a.imei is not null
    group by a.product_no,substr(a.imei,1,14),a.day;
    
    
    create table tmp_20230511_ykszd_mxg_imeihza as
    select a.product_no,a.day,count(distinct a.imei14) imei14s ,sum(a.cs) cs
     from tmp_20230511_ykszd_mxg_imeihz a
    group by a.product_no,a.day
    having count(distinct a.imei14)>1;
    
    select * from tmp_20230511_ykszd_mxg_imeihz a 
    where PRODUCT_NO='15106413444'
    and  DAY='20230510';

----一个号码一天多串号
create table tmp_20230511_ykszd_mxg_imeihzb as
    select a.product_no,a.day,listagg (a.imei14,' ,')  within group (order by a.cs desc ) imei14,
     row_number() over (partition by a.product_no  order by a.day desc ) 排名 
    from tmp_20230511_ykszd_mxg_imeihz a 
    where exists (select * from tmp_20230511_ykszd_mxg_imeihza t where t.product_no=a.product_no and t.day=a.day)
    group by a.product_no,a.day;
    
    
    --- 地市
    select * from tmp_20230511_ykszd_mxg a where a.roamcity_name is not null
    
    create table tmp_20230511_ykszd_mxg_dshz as
    select a.product_no,roamcity_name,a.day,count(*) cs 
    from tmp_20230511_ykszd_mxg a where a.roamcity_name is not null
    group by a.product_no,roamcity_name,a.day;
    
        create table tmp_20230511_ykszd_mxg_dshza as
    select a.product_no,a.day,count(distinct a.roamcity_name) roamcity_names ,sum(a.cs) cs
     from tmp_20230511_ykszd_mxg_dshz a
    group by a.product_no,a.day
    having count(distinct a.roamcity_name)>1;
    
    create table tmp_20230511_ykszd_mxg_dshzaz as
    select a.*,c.PROVINCENAME
    from  tmp_20230511_ykszd_mxg_dshz a ,
    (select * from common.SA_DB_NATAREAINFO@bcv c where AREATYPE=1) c
    where a.roamcity_name=c.AREANAME;
    
    create table tmp_20230511_ykszd_mxg_dshzaza as
    select a.product_no,a.day,count(distinct PROVINCENAME) PROVINCENAME,count(distinct a.roamcity_name) roamcity_name
    from tmp_20230511_ykszd_mxg_dshzaz a
    group by a.product_no,a.day
    having count(distinct PROVINCENAME)>1;
    
    create table tmp_20230511_ykszd_mxg_dshzazb as
    select a.product_no,a.day, PROVINCENAME,count(distinct a.roamcity_name) roamcity_name
    from tmp_20230511_ykszd_mxg_dshzaz a
    group by a.product_no,a.day,a.provincename;
    
    create table tmp_20230511_ykszd_mxg_dshzazc as
    select a.product_no,a.day,listagg (a.provincename||'_'||a.roamcity_name,' ,')  within group (order by a.roamcity_name desc ) provincename,
     row_number() over (partition by a.product_no  order by a.day desc ) 排名
    from tmp_20230511_ykszd_mxg_dshzazb a
    where (a.product_no,a.day) in (select t.product_no,t.day from tmp_20230511_ykszd_mxg_dshzaza t )
    group by a.product_no,a.day;
    
    create table tmp_20230511_ykszd_mxg_dshzazd as
    select a.*,b.provincename 省数,b.roamcity_name 地市数 from tmp_20230511_ykszd_mxg_dshzazc a,
    tmp_20230511_ykszd_mxg_dshzaza b
    where a.product_no=b.product_no(+)
    and a.day=b.day(+);
    
    
    create table tmp_20230511_ykszd_mxg_dshzb as
    select a.product_no,a.day,listagg (a.roamcity_name,' ,')  within group (order by a.cs desc ) roamcity_name,
    count(distinct a.roamcity_name) 城市数,
     row_number() over (partition by a.product_no  order by a.day desc ) 排名 
    from tmp_20230511_ykszd_mxg_dshz a 
    where exists (select * from tmp_20230511_ykszd_mxg_dshza t where t.product_no=a.product_no and t.day=a.day)
    group by a.product_no,a.day;
    
    
    select a.*,
    t1.day,t1.imei14,
    t3.day,t3.imei14,
    t2.day,t2.roamcity_name,t5.day,t5.provincename,t5.省数,t5.地市数, 
    t4.day,t4.roamcity_name,t6.day,t6.provincename,t6.省数,t6.地市数,zt.dictname,b.statusdate,b.ownerorgid,c.name,
    row_number() over (partition by a.subsid  order by a.recdate desc ) 排名
    from tmp_20230511_ykszd_mxf a,
    (select * from tmp_20230511_ykszd_mxg_imeihzb t where t.排名=1) t1,
    (select * from tmp_20230511_ykszd_mxg_dshzb t where t.排名=1) t2,
    (select * from tmp_20230511_ykszd_mxg_imeihzb t where t.排名=2) t3,
    (select * from tmp_20230511_ykszd_mxg_dshzb t where t.排名=2) t4,
    (select * from tmp_20230511_ykszd_mxg_dshzazd t where t.排名=1) t5,
    (select * from tmp_20230511_ykszd_mxg_dshzazd t where t.排名=2) t6,
    zhyw.subscriber b,
    zhyw.rpt_county c,
    (select * from tbcs.dict_item@bcv where groupid='US') zt 
    where a.servnumber=t1.product_no(+)
    and a.servnumber=t2.product_no(+)
    and a.servnumber=t3.product_no(+)
    and a.servnumber=t4.product_no(+)
    and a.servnumber=t5.product_no(+)
    and a.servnumber=t6.product_no(+)
    and b.status=zt.dictid(+)
    and a.subsid=b.subsid(+)
    and substr(b.ownerorgid,8,1)=c.county_id(+)