省厅数据量统计分析SQl脚本

1.摆渡机转移率（政务外网转移到政务内网 -39006）

1.1从外网迁移数据包到内网的转移率sql脚本

备注：

SFBB(是否报部): 0代表不报部，1代表需要报部，2代表转移成功，3代表转移失败

-- 从外网迁移数据包到内网的转移率sql脚本
select count(*) as `应转移总量`,
sum(if(x.sfbb=2,1,0)) as `转移成功总量`,
sum(if(x.sfbb=3,1,0)) as `转移失败总量`,
sum(if(x.sfbb=1,1,0)) as `未转移量`,
sum(if(x.sfbb=0,1,0)) as `不报簿转移量`,
-- 转移成功率：转移成功总量/(应转移总量-未转移量-不报簿转移量)
sum(if(x.sfbb=2,1,0))/(count(*)-sum(if(x.sfbb=1,1,0))-sum(if(x.sfbb=0,1,0)))*100 as `转移成功率`,
-- 转移失败率：转移失败总量/(应转移总量-未转移量-不报簿转移量)
sum(if(x.sfbb=3,1,0))/(count(*)-sum(if(x.sfbb=1,1,0))-sum(if(x.sfbb=0,1,0)))*100 as `转移失败率`
from bdcdjjg.t_jx_xml x,bdcdjjg.t_jx_log l 
where x.logbsm=l.bsm and x.dbsj is not null 
and date_format(x.dbsj,'%Y-%m-%d')>='2016-01-01'
and date_format(x.dbsj,'%Y-%m-%d')<='2022-12-01'

1.2从外网迁移数据包到内网的转移异常明细

-- 从外网迁移数据包到内网的转移异常明细
select l.bbinfo as 转移失败原因,
x.ywh as 业务号,x.qlbsm as 权力标识码,x.lcdm as 流程代码,x.qxdm as 区县代码,
x.qlbm as 权力表名, x.dbsj as 登簿时间, x.qszt 权属状态 ,x.djlx 登记类型
from bdcdjjg.t_jx_xml x,bdcdjjg.t_jx_log l 
where x.logbsm=l.bsm and x.dbsj is not null 
and x.sfbb=3 
and date_format(x.dbsj,'%Y-%m-%d')>='2016-01-01'
and date_format(x.dbsj,'%Y-%m-%d')<='2022-12-01'

2.报部率(省厅的数据包转移到国标）

备注⚠️:

t_bwsb_log表的上报状态sbzt字段：
0表示备份失败，     1表示备份成功，   2表示分发成功，     3表示分发失败，     4表示上报成功，
5表示上报失败,      6表示响应成功，   7表示响应失败

2.1内外数据包上报到国标报部率 (维度2，详细)

select count(*) as `总量`,
sum(if(l.sbzt=0,1,0))+sum(if(l.sbzt=1,1,0))+sum(if(l.sbzt=2,1,0))+sum(if(l.sbzt=3,1,0)) as `未上报量`,
sum(if(l.sbzt=6,1,0)) as `上报国标成功成功量`, -- 响应成功了才是算上报过程
sum(if(l.sbzt=5,1,0))+sum(if(l.sbzt=7,1,0)) as `上报国标失败量`,
-- 上报成功量/(总量-未上报量)
sum(if(l.sbzt=4,1,0))/(count(*)-(sum(if(l.sbzt=0,1,0))+sum(if(l.sbzt=1,1,0))+sum(if(l.sbzt=2,1,0))+sum(if(l.sbzt=3,1,0))))*100 as `上报国标成功率`,
-- 上报失败量/(总量-未上报量)
(sum(if(l.sbzt=5,1,0))+sum(if(l.sbzt=7,1,0)))/(count(*)-(sum(if(l.sbzt=0,1,0))+sum(if(l.sbzt=1,1,0))+sum(if(l.sbzt=2,1,0))+sum(if(l.sbzt=3,1,0))))*100 as `上报国标失败率`
from bdcdjjg1.t_bwsb_xml x,bdcdjjg1.t_bwsb_log l
where x.logbsm=l.bsm and l.dbsj is not null 
and date_format(l.dbsj,'%Y-%m-%d')>='2016-01-01'
and date_format(l.dbsj,'%Y-%m-%d')<='2022-12-01'

2.2内外数据包上报到国标报部失败明细（维度2，详细）

-- 上报失败维度 sbzt=5
select x.ywh as 业务号,x.qlbsm as 权力标识码,x.qxdm as 区县代码,
l.logtype as 上报国标失败类型,l.loginfo as 上报国标失败原因
from bdcdjjg1.t_bwsb_xml x,bdcdjjg1.t_bwsb_log l
where x.logbsm=l.bsm and l.dbsj is not null 
and date_format(l.dbsj,'%Y-%m-%d')>='2016-01-01'
and date_format(l.dbsj,'%Y-%m-%d')<='2022-12-01'
and l.sbzt=5 and --x.fwqbm=1

-- 响应失败维度 sbzt=7
select x.ywh as 业务号,x.qlbsm as 权力标识码,x.qxdm as 区县代码,
l.logtype as 上报国标失败类型,l.loginfo as 上报国标失败原因
from bdcdjjg1.t_bwsb_xml x,bdcdjjg1.t_bwsb_log l
where x.logbsm=l.bsm and l.dbsj is not null 
and date_format(l.dbsj,'%Y-%m-%d')>='2016-01-01'
and date_format(l.dbsj,'%Y-%m-%d')<='2022-12-01'
and l.sbzt=7 and --x.fwqbm=1

-- 数据包转移到内网sftp失败维度 sbzt=0
select x.ywh as 业务号,x.qlbsm as 权力标识码,x.qxdm as 区县代码,
l.logtype as 上报国标失败类型,l.loginfo as 上报国标失败原因
from bdcdjjg1.t_bwsb_xml x,bdcdjjg1.t_bwsb_log l
where x.logbsm=l.bsm and l.dbsj is not null 
and date_format(l.dbsj,'%Y-%m-%d')>='2016-01-01'
and date_format(l.dbsj,'%Y-%m-%d')<='2022-12-01'
and l.sbzt=0 and --x.fwqbm=1

3.解析率(政务外网异步解析-39006)

3.1政务外网异步解析率（维度2，详细）

-- 解析率
select count(*) as 总量,
sum(if(x.jxzt=0,1,0)) as 未解析量,
sum(if(x.jxzt=1,1,0)) as 解析成功量,
sum(if(x.jxzt=2,1,0)) as 解析失败量,
sum(if(x.jxzt=3,1,0)) as 暂不解析量, -- 一般是原权利未的量
-- sum(if(x.jxzt=4,1,0)) as 解析校验失败量,  -- 暂时不考虑，应该没有这种数据
-- 解析成功率： 解析成功量/(总量-未解析量-暂不解析量)
sum(if(x.jxzt=1,1,0))/(count(*)-sum(if(x.jxzt=0,1,0))-sum(if(x.jxzt=3,1,0)))*100 as 解析成功率,
-- 解析失败率： 解析成功量/(总量-未解析量-暂不解析量)
sum(if(x.jxzt=2,1,0))/(count(*)-sum(if(x.jxzt=1,1,0))-sum(if(x.jxzt=3,1,0)))*100 as 解析失败率
from bdcdjjg.t_jx_log l,bdcdjjg.t_jx_xml x
where x.logbsm=l.bsm and x.jxzt>='0'
and date_format(x.dbsj,'%Y-%m-%d')>='2016-01-01'
and date_format(x.dbsj,'%Y-%m-%d')<='2022-12-01'

3.2政务外网异步解析失败明细 （维度2，详细）

-- 解析失败明细
select x.jxzt as 解析状态,l.jxjg as 解析结果,
x.ywh as 业务号,x.qlbsm as 权力标识码,x.qlbm as 权力表名,
x.lcdm as 流程代码,x.qszt as 权属状态,x.qxdm as 区县代码
from bdcdjjg.t_jx_log l,bdcdjjg.t_jx_xml x
where x.logbsm=l.bsm and x.jxzt>='0'
and x.jxzt in (2,3)
and date_format(x.dbsj,'%Y-%m-%d')>='2016-01-01'
and date_format(x.dbsj,'%Y-%m-%d')<='2022-12-01'

3.3总体的解析率：解析的量/省厅接受的总量（维度1，大体）

-- 解析率=解析的总量/省厅配号总量
select  
(select count(*) 
from bdcdjjg.t_jx_log l,bdcdjjg.t_jx_xml x
where x.logbsm=l.bsm and x.jxzt>='0'
and date_format(x.dbsj,'%Y-%m-%d')>='2021-01-03'
and date_format(x.dbsj,'%Y-%m-%d')<='2022-12-01')
/
 (select count(*) from bdcdjjg.bdcdjbh ph
where 
ph.gxsj>='2021-01-03' and ph.gxsj<='2022-12-01')
as 解析率;

4.报省率（市县区上报，省厅39006接受数据包）

4.1报省率(维度1，大体)

-- 报省率=省厅接收的总量/省厅配号总量
select 
(select count(*) 
from bdcdjjg.t_sb_dbl x
where 
date_format(x.dbsj,'%Y-%m-%d')>='2021-08-01'
and date_format(x.dbsj,'%Y-%m-%d')<='2021-09-01')
/
 (select count(*) from bdcdjjg.bdcdjbh ph
where 
ph.gxsj>='2021-08-01' and ph.gxsj<='2021-09-04')
as 报省率；

4.2oracle库查推送登记簿省厅的总量

select count(*) as 总登簿量,
sum(decode(t.isdbcg,1,1,0)) as 登簿成功量,
sum(decode(t.isdbcg,0,1,0))   as  登簿失败量,
sum(decode(t.isdbcg,1,0,0,0,1)) as 未登簿量,
sum(decode(s.iscg,1,1,2,1,0))  as   推送省厅成功量   ,
sum(decode(s.iscg,0,1,0)) as  推送省厅失败量   ,
sum(decode(s.iscg,null,1,0)) as  未推送省厅量      
from   
(select t.bsm,t.ywh,t.dbsj,t.qszt,t.qlbsm,t.qlbm,t.djlx,t.lcdm,t.slsj,t.qxdm from bdcdj.sh t   
where  t.dbsj  is not null and  t.qszt is not  null   
and  to_char(t.dbsj,'yyyy-mm-dd') >= '2021-08-01' and to_char(t.dbsj,'yyyy-mm-dd')<= '2021-08-31'
and  t.dbsj   is   not   null
union all
select t.bsm,t.ywh,t.dbsj,t.qszt,t.qlbsm,t.qlbm,t.djlx,t.lcdm,t.slsj,t.qxdm
frombdcdj.sh_ls t 
where 
t.dbsj is not null--and t.qszt is not null
and to_char(t.dbsj,'yyyy-mm-dd')>='2021-08-01' 
and to_char(t.dbsj,'yyyy-mm-dd')<= '2021-08-31'
and t.dbsj is not null
)sh,
(select*from
XMLDB.T_SB_DBXX t, XMLDB.t_sb_result s 
where  
to_char(t.dbsj,'yyyy-mm-dd')>='2021-08-01' 
and to_char(t.dbsj,'yyyy-mm-dd')<= '2021-08-31'
and t.djshbsm=s.djshbsm(+) and receiverbsm=1)t,
(select  * from
XMLDB.T_SB_DBXX t,XMLDB.t_sb_result s 
where 
to_char(t.dbsj,'yyyy-mm-dd')>='2021-08-01' and to_char(t.dbsj,'yyyy-mm-dd')<= '2021-08-31'
and t.djshbsm=s.djshbsm(+) and receiverbsm=2) s--and t.dbsj is notnull
where 
sh.ywh=T.YWH(+) and sh.qlbsm=t.qlbsm(+)
and sh.ywh=s.ywh(+) and sh.qlbsm=s.qlbsm(+)

4.2oracle查推送登记簿省厅失败明细

select t.ywh as 业务号 ,t.qlbsm 权利标识码,t.qlbm 权利表名,to_char(t.dbsj,'YYYY-MM-DD') 登簿时间 ,t.qxdm 区县代码 ,to_char(substr(le.loginfo,1,3800))  as 异常描述，
logtype as 异常类型,'记载登记簿失败' as 失败类型，'详情' 详情 
from XMLDB.T_SB_DBXX t,xmldb.t_sb_result R,xmldb.LOG_EXCEPTION le 
where to_char(t.dbsj,'yyyy-mm-dd') >= '2020-08--01' 
and  to_char(t.dbsj,'yyyy-mm-dd') <= '2021-08--01'  
and isdbcg=0 and t.djshbsm=R.djshbsm  AND  R.LOGECPBSM=LE.BSM(+) and receiverbsm=1 
union 
select t.ywh as 业务号 ,t.qlbsm 权利标识码,t.qlbm 权利表名,to_char(t.dbsj,'YYYY-MM-DD') 登簿时间,t.qxdm,to_char(substr(le.loginfo,1,3800))  as 异常描述,
logtype as 异常类型,'推送省厅失败' as 失败类型, '详情' 详情   
from XMLDB.T_SB_DBXX t,xmldb.t_sb_result R,xmldb.LOG_EXCEPTION le
where  to_char(t.dbsj,'yyyy-mm-dd') >= '2020-08--01' 
and  to_char(t.dbsj,'yyyy-mm-dd') <= '2021-08--01'  
and iscg=0 and t.djshbsm=R.djshbsm  AND  R.LOGECPBSM=LE.BSM(+) and receiverbsm=2
and  not exists(select 1 from xmldb.t_sb_result su,XMLDB.T_SB_DBXX st 
where st.djshbsm=su.djshbsm  and su.djshbsm=r.djshbsm and su.receiverbsm=1 and su.LOGECPBSM>0
and to_char(st.dbsj,'yyyy-mm-dd') >= '2020-08--01' and  to_char(st.dbsj,'yyyy-mm-dd') <= '2021-08--01' and isdbcg=0)