------服务列表
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(+)
20230516_数据库语句_一号双终端
最新推荐文章于 2024-08-26 19:43:03 发布