丽水异常分析处理

异常分析处理

1.先统计应报量

表：xmldb.t_sb_dbxx

--t_sb_dbxx的数据来源与登记的SH%表
--835条应报数据
--isdbcg 0未登簿或者登簿失败：699   1登簿成功：130    2除了登簿其他操作成功：6
select xx.isdbcg,count(*) 
from xmldb.t_sb_dbxx xx 
where xx.qxdm='330183' and to_char(xx.dbsj,'yyyy-mm')='2021-08'and xx.qszt>0 
and xx.djshbsm is not null and xx.qlbsm is not null
group by xx.isdbcg;

附记：xmldb.t_sb_dbxx.tszt 统计推送省厅的t_sb_dbl量

--tszt  0未推送:1  1已经推送省厅:833 3需要重推:1    这个代表是推送省厅T_sb_dbl表的推送状态
select xx.tszt,count(*) from xmldb.t_sb_dbxx xx where xx.qxdm='330183' and to_char(xx.dbsj,'yyyy-mm')='2021-08'
and xx.qszt>0 and xx.djshbsm is not null and xx.qlbsm is not null 
group by xx.tszt;

2.统计已经从xmldb.t_sb_dbxx推送到索引表的数据推送状态

处理过的数据

--索引表数据 150条没有推送成功 1条推送成功 
select sy.sybtszt,count(*) from xmldb.t_sb_syb sy,xmldb.t_sb_dbxx xx where xx.djshbsm=sy.djshbsm and
xx.qxdm='330183' and to_char(xx.dbsj,'yyyy-mm')='2021-08'
and xx.qszt>0 and xx.djshbsm is not null and xx.qlbsm is not null  and sy.receiverbsm=2
group by sy.sybtszt;

没有推送成功的量

select count(*) from xmldb.t_sb_syb sy,xmldb.t_sb_dbxx xx ,xmldb.log_exception e
where xx.djshbsm=sy.djshbsm and sy.logecpbsm=e.bsm(+) /*and sy.logecpbsm>0 */and sy.sybtszt='0' and
xx.qxdm='330183' and to_char(xx.dbsj,'yyyy-mm')='2021-08'
and xx.qszt>0 and xx.djshbsm is not null and xx.qlbsm is not null  and sy.receiverbsm=2

没有推送成功的明细

select to_char(e.loginfo) as loginfo,sy.pushtype,xx.* from xmldb.t_sb_syb sy,xmldb.t_sb_dbxx xx ,xmldb.log_exception e
where xx.djshbsm=sy.djshbsm and sy.logecpbsm=e.bsm(+) /*and sy.logecpbsm>0 */and sy.sybtszt='0' and
xx.qxdm='330183' and to_char(xx.dbsj,'yyyy-mm')='2021-08'
and xx.qszt>0 and xx.djshbsm is not null and xx.qlbsm is not null  and sy.receiverbsm=2

数据还在xmldb.t_sb_dbxx，没推送到t_sb_syb的数据量

--还有684条信息没有推送到到t_sb_syb 数据还在xmldb.t_sb_dbxx
select count(*) from xmldb.t_sb_dbxx xx where 
xx.qxdm='330183' and to_char(xx.dbsj,'yyyy-mm')='2021-08'
and xx.qszt>0 and xx.djshbsm is not null and xx.qlbsm is not null 
and not exists(
select * from xmldb.t_sb_syb sy where xx.djshbsm=sy.djshbsm  and sy.receiverbsm=2)

3.统计xml数据推送到登记簿/省厅的数据量

3.1省厅receiverbsm=2 、登记簿receiverbsm=1

附记：t_sb_result记录的是推送登记簿和省厅的成功状态

-- 148失败 3成功 推送省厅的    iscg是2的话 代表省厅异步接受xml成功，是否解析入库不知道
--iscg是1的话代表同步接收解析是确定成功入库了的
select r.iscg,count(*) from xmldb.t_sb_dbxx xx,xmldb.t_sb_result r
where xx.djshbsm=r.djshbsm and xx.qxdm='330183' and to_char(xx.dbsj,'yyyy-mm')='2021-08'and xx.qszt>0
and r.receiverbsm=2 group by r.iscg;

推送失败明细

--148条失败的原因的明细  （xml数据包推送登记簿或者省厅）
select xx.qlbm,xx.qlbsm,xx.djshbsm,xx.ywh,xx.lcdm,xx.djlx,xx.qxdm,xx.dbsj,
e.logtype,to_char(substr(e.loginfo,1,3000))/*,count(*)*/
 from xmldb.t_sb_dbxx xx,xmldb.t_sb_result r,xmldb.log_exception e
where xx.djshbsm=r.djshbsm and xx.qxdm='330183' and to_char(xx.dbsj,'yyyy-mm')='2021-08'and xx.qszt>0
and r.receiverbsm=2 and r.iscg=0
and r.logecpbsm=e.bsm(+)
/*group by xx.qlbm*/

还在t_sb_ywxx的数据量

select count(*) from xmldb.t_sb_dbxx xx where  xx.qxdm='330183' 
and to_char(xx.dbsj,'yyyy-mm')='2021-08' and xx.qszt>0
and not exists (
select * from xmldb.t_sb_result r where xx.djshbsm=r.djshbsm and r.receiverbsm=2);