1.新增字段
alter table tmp_1 add SIMID varchar(20) null; alter table tmp_1 add FMAddress varchar(20) null;
2.更新数据(fmaddress)
merge into tmp_1 aa using ( select pid, (SUBSTRING(datalog,12,1) + SUBSTRING(datalog,15,1)+SUBSTRING(datalog,18,1)+SUBSTRING(datalog,21,1)+SUBSTRING(datalog,24,1)+SUBSTRING(datalog,27,1) + SUBSTRING(datalog,30,1)+SUBSTRING(datalog,33,1)+SUBSTRING(datalog,36,1)+SUBSTRING(datalog,39,1)+SUBSTRING(datalog,42,1) ) as simid from tmp_1 ) bb on(aa.pid=bb.pid) when matched then update set aa.simid=bb.simid ;
merge into tmp_1 aa using tblfminfo bb on(aa.simid=bb.simid) when matched then update set aa.fmaddress=bb.fmaddress
3.根据fmaddress字段取值确定报文记录(间隔,条数)
SELECT * FROM tmp_1 WHERE fmaddress='1484358' order by CreateTime desc
举个栗子:
(1)查询报文(报文总数)
(2)解析报文(间隔,预估记录数,缺失部分)
4.根据fmaddress 统计tblfmreaddata 表中时间在2017-05-11的记录数(实际记录数)
SELECT count(*) FROM tblfmreaddata WHERE fmaddress='01383618' AND readtime LIKE '2017-05-11%'
5.统计结果
备注:
场景说明:
本次记录是对比两张表的统计结果,确定数据是否完整。为下一步工作做准备。