SQL在健康体检队列数据清洗、数据报表中的应用

王路露

(江苏省疾病预防控制中心，江苏 南京 210000)

0 引言

目前，公共卫生的信息化的现状是百花齐放，如何利用好信息化数据是一个十分重要的课题。受限于软件系统中的统计功能仅满足常规工作，加上信息系统建设等各种原因，而业务人员无法处理百万级数据，因此，虽有软件系统，但却无法满足科研工作中新的统计需求。为此，期望利用SQL对数据进行清洗、统计，从而获取需要的统计结果以便指导工作。

本文就2008—2016年某几个示范区的体检数据作为数据来源，先建立Oracle数据库，再通过PL/SQL工具对数据进行清洗，然后通过SQL语句建立存储过程自动生成分地区、分年龄段、分乙肝表面抗原阳性与阴性、分性别的统计汇总结果导出到Excel表格中进行透视，最后得到数据报表，为下一步体检人群的再次筛查体检工作提供依据[1]。

1 资料与方法

1.1 数据来源

2008—2016年3个示范区的体检数据作为数据来源，主要包括id、姓名、性别、生日、体检日期、乙肝表面抗原阴性/阳性、所属地区编码、录入人员姓名、录入单位编码等内容。

1.2 方法

根据本次数据的特点，考虑规范性、完整性、有效性以及正确性，确定数据清洗步骤：获取原始数据→去除重复→计算统计标准→计算统计结果→报表展示。

(1)获取原始数据。

预处理的目的就是将数据导入数据库。这个过程包括：建立服务器、安装数据库、配置操作及将数据导入数据库。

(2)去除重复。

首先，对于一个人多次体检，取有乙肝表面抗原检测结果的最后一次的体检结果(结果放入linshi表)。具体语句为：

create table linshi as select sfzh,max (tjrq) tjrq from js_tj_tjjl_bak t where hbsag=1 or hbsag=2 group by sfzh；

其次，存在一个人同一天多次体检，统计时认定为一次，认为是重复录入导致。具体语句为：

create table linshi_1 as select bb.* from linshi aa left join js_tj_tjjl_bak bb on (aa.sfzh=bb.sfzh and aa.tjrq=bb.tjrq) where bb.hbsag=1 or bb.hbsag=2;

create table linshi_2 as select max(id) maxid,count(id) num3,sfzh from linshi_1 t group by sfzh order by num3 desc;

最后，通过筛查出的病人的id获取病人的其他字段信息，并按照hbsag、xb、xzqh、nianling 排序。具体语句为：

create table guolv_final3 as select substr(xzqh,1,8) gbcode,to_char(sysdate,‘yyyy’)-to_char(bb.csrq,‘yyyy’) nianling,num3,bb.* from linshi_2 aa left join js_tj_tjjl_bak bb on (aa.maxid=bb.id and aa.sfzh=bb.sfzh) order by hbsag,xb,gbcode,nianling;

(3)计算统计标准。

首先，计算年龄。根据出生日期计算年龄。具体语句为：

select id,csrq,to_char(sysdate,‘yyyy’)-to_char(csrq,‘yyyy’) from js_tj_tjjl_bak;

其次，地区编码。村一级的编码很乱，有的村编码是9位，有的是12位。编码规则各不相同，无法统一。109行地区编码，人工核对有难度。所以，为了简便取前8位数字作为地区分类编码，也就是到街道乡镇一级。具体语句为：

create table temp_gbcode as select distinct(substr(xzqh,1,8)) mm from temp order by mm asc;

最后，建立年龄分层表格：

create table tmp_nianling2 (id number primary key, nianling1 number，nianling2 number);

用ultraedit编辑插入语句

insert into tmp_nianling2 values(1,0，5);

insert into tmp_nianling2 values(2,5，10);

……

insert into tmp_nianling2 values(17,80，150);

(4)计算统计结果。

首先，根据乙肝表面抗原阴性或阳性、性别、地区编码、年龄层次，统计出有乙肝表面抗原检测最后一次结果的人数。具体处理过程为：

select count(*) into tongji from guolv_final3 where hbsag=hbsag1 and xb=xb1 and

gbcode=gbcode1 and (nianling>=nianling1 and nianling

dbms_output.put_line(‘tongji:’||tongji);

其次，使用游标对年龄段、地区编码进行循环统计，具体处理过程为：

create or replace procedure tongji(tongji11 out number) iscursor gbcode11 is select * from temp_gbcode;cursor nianling11 is select nianling1,nianling2 from tmp_nianling2;xb11 number;hbsag11 number;hbsag1 guolv_final3.hbsag%type;xb1 guolv_final3.xb%type;gbcode1 guolv_final3.gbcode%type;nianling1 guolv_final3.nianling%type;nianling2 guolv_final3.nianling%type;

begin

xb11:=1;

hbsag11:=1;

open gbcode11;

loop

fetch gbcode11 into gbcode1;/*地区循环*/

exit when gbcode11%notfound; --地区编码最后时退出

open nianling11;

loop

fetch nianling11 into nianling1,nianling2;--年龄循环

exit when nianling11%notfound;--年龄编码最后时退出

hbsag11:=1;

while hbsag11<=2 loop

hbsag1:=hbsag11; --hbsag阴性、阳性循环

xb11:=1;

while xb11<=2 loop --性别男女循环

xb1:=xb11;

hbsag_sex2(hbsag1, xb1, gbcode1, nianling1, nianling2,tongji11); --执行统计

insert into tongji_0 values(hbsag1,xb1,gbcode1,nianling1,nianling2,tongji11);

commit;--结果插入表格,注意提交

xb11:=xb11+1;

end loop;

hbsag11:=hbsag11+1;

end loop;

end loop;

close nianling11; --关闭游标

end loop;

close gbcode11; --关闭游标

end tongji;

(5)报表展示。

将结果导出为csv格式，使用Excel打开后，选择透视表，将地区、年龄设置为行变量，乙肝表面抗原、性别设置为列变量，统计结果设置为求和、无计算，结果如图1所示。

图1 数据报表展示

2 结果

2.1 数据特征描述

2008—2016年某示范区体检数据共7 075 763条。9 374条重复数据属于同一人同一天多次体检，应属于重复录入，去重后得到最近一次乙肝表面抗原记录体检结果有1 218 375条。

2.2 数据清洗

刚拿到数据时，并不知道该从哪些方面进行数据清洗，只是根据经验来进行尝试性分析，结果发现结果跟经验得来的不一致，所以才开始了数据清洗。这是一个反复的过程。在进行数据清洗时，为了找出数据异常值，本研究把统计涉及的所有数据字段都进行查询，跟数据字典进行一一比较[2]。

数据清洗过程中发现最耗时间的是去重。对一人多次体检的重复数据，先获取某人的最新一天体检信息，然后根据sfzh、tjrq获取系统中某人最新一天的最大id号的体检信息[3]。另外，对数据格式的简单变换，如本研究中出生日期转换成年龄，N位地区编码转换取前8位地区编码；还将linshi_2表与js_tj_tjjl_bak进行连接，通过id和sfzh获取过滤后人的所有信息。

2.3 数据统计

本次统计的特点是统计量较多，有4个变量地区、年龄段、乙肝表面抗原阴性/阳性、性别，共有816个行。

3 结语

数据清洗耗费非常多的人力、精力，而数据清洗的目的就是要解决数据质量问题，因此是否可以未雨绸缪实现数据质量的提升。在设计软件系统时，需要在数据采集时进行尽可能多的有效性约束，在管理上对数据进行审核，在报表统计时也对数据质量进行分析。这也提醒业务管理人员一定要高度重视数据的质量。很多软件系统上线初期，软件推广主要方向是通过培训让更多的用户使用软件系统。随着软件用户的稳定、软件功能的稳定，软件推广的主要方向更应该侧重于数据的质量的提升以及数据价值的发掘。

数据统计的便捷性与灵活性非常重要。如果手工统计的话，非常耗时耗力。因此本次使用游标、循环执行存储过程等来编写专门编写的SQL块，可以一次性获得所有统计数据，特别省时省力。这种方法不仅能解决某个特定的问题，而且当统计条件变化时，也能够灵活应对，只需稍改存储过程就可以实现。这特别在清理过程需要反复进行时，此方法非常高效。