王路露

(江蘇省疾病預(yù)防控制中心，江蘇 南京 210000)

0 引言

目前，公共衛(wèi)生的信息化的現(xiàn)狀是百花齊放，如何利用好信息化數(shù)據(jù)是一個(gè)十分重要的課題。受限于軟件系統(tǒng)中的統(tǒng)計(jì)功能僅滿足常規(guī)工作，加上信息系統(tǒng)建設(shè)等各種原因，而業(yè)務(wù)人員無法處理百萬級數(shù)據(jù)，因此，雖有軟件系統(tǒng)，但卻無法滿足科研工作中新的統(tǒng)計(jì)需求。為此，期望利用SQL對數(shù)據(jù)進(jìn)行清洗、統(tǒng)計(jì)，從而獲取需要的統(tǒng)計(jì)結(jié)果以便指導(dǎo)工作。

本文就2008—2016年某幾個(gè)示范區(qū)的體檢數(shù)據(jù)作為數(shù)據(jù)來源，先建立Oracle數(shù)據(jù)庫，再通過PL/SQL工具對數(shù)據(jù)進(jìn)行清洗，然后通過SQL語句建立存儲過程自動(dòng)生成分地區(qū)、分年齡段、分乙肝表面抗原陽性與陰性、分性別的統(tǒng)計(jì)匯總結(jié)果導(dǎo)出到Excel表格中進(jìn)行透視，最后得到數(shù)據(jù)報(bào)表，為下一步體檢人群的再次篩查體檢工作提供依據(jù)[1]。

1 資料與方法

1.1 數(shù)據(jù)來源

2008—2016年3個(gè)示范區(qū)的體檢數(shù)據(jù)作為數(shù)據(jù)來源，主要包括id、姓名、性別、生日、體檢日期、乙肝表面抗原陰性/陽性、所屬地區(qū)編碼、錄入人員姓名、錄入單位編碼等內(nèi)容。

1.2 方法

根據(jù)本次數(shù)據(jù)的特點(diǎn)，考慮規(guī)范性、完整性、有效性以及正確性，確定數(shù)據(jù)清洗步驟：獲取原始數(shù)據(jù)→去除重復(fù)→計(jì)算統(tǒng)計(jì)標(biāo)準(zhǔn)→計(jì)算統(tǒng)計(jì)結(jié)果→報(bào)表展示。

(1)獲取原始數(shù)據(jù)。

預(yù)處理的目的就是將數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫。這個(gè)過程包括：建立服務(wù)器、安裝數(shù)據(jù)庫、配置操作及將數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫。

(2)去除重復(fù)。

首先，對于一個(gè)人多次體檢，取有乙肝表面抗原檢測結(jié)果的最后一次的體檢結(jié)果(結(jié)果放入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；

其次，存在一個(gè)人同一天多次體檢，統(tǒng)計(jì)時(shí)認(rèn)定為一次，認(rèn)為是重復(fù)錄入導(dǎo)致。具體語句為：

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)計(jì)算統(tǒng)計(jì)標(biāo)準(zhǔn)。

首先，計(jì)算年齡。根據(jù)出生日期計(jì)算年齡。具體語句為：

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

其次，地區(qū)編碼。村一級的編碼很亂，有的村編碼是9位，有的是12位。編碼規(guī)則各不相同，無法統(tǒng)一。109行地區(qū)編碼，人工核對有難度。所以，為了簡便取前8位數(shù)字作為地區(qū)分類編碼，也就是到街道鄉(xiāng)鎮(zhèn)一級。具體語句為：

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)計(jì)算統(tǒng)計(jì)結(jié)果。

首先，根據(jù)乙肝表面抗原陰性或陽性、性別、地區(qū)編碼、年齡層次，統(tǒng)計(jì)出有乙肝表面抗原檢測最后一次結(jié)果的人數(shù)。具體處理過程為：

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);

其次，使用游標(biāo)對年齡段、地區(qū)編碼進(jìn)行循環(huán)統(tǒng)計(jì)，具體處理過程為：

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;/*地區(qū)循環(huán)*/

exit when gbcode11%notfound; --地區(qū)編碼最后時(shí)退出

open nianling11;

loop

fetch nianling11 into nianling1,nianling2;--年齡循環(huán)

exit when nianling11%notfound;--年齡編碼最后時(shí)退出

hbsag11:=1;

while hbsag11<=2 loop

hbsag1:=hbsag11; --hbsag陰性、陽性循環(huán)

xb11:=1;

while xb11<=2 loop --性別男女循環(huán)

xb1:=xb11;

hbsag_sex2(hbsag1, xb1, gbcode1, nianling1, nianling2,tongji11); --執(zhí)行統(tǒng)計(jì)

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

commit;--結(jié)果插入表格,注意提交

xb11:=xb11+1;

end loop;

hbsag11:=hbsag11+1;

end loop;

end loop;

close nianling11; --關(guān)閉游標(biāo)

end loop;

close gbcode11; --關(guān)閉游標(biāo)

end tongji;

(5)報(bào)表展示。

將結(jié)果導(dǎo)出為csv格式，使用Excel打開后，選擇透視表，將地區(qū)、年齡設(shè)置為行變量，乙肝表面抗原、性別設(shè)置為列變量，統(tǒng)計(jì)結(jié)果設(shè)置為求和、無計(jì)算，結(jié)果如圖1所示。

圖1 數(shù)據(jù)報(bào)表展示

2 結(jié)果

2.1 數(shù)據(jù)特征描述

2008—2016年某示范區(qū)體檢數(shù)據(jù)共7 075 763條。9 374條重復(fù)數(shù)據(jù)屬于同一人同一天多次體檢，應(yīng)屬于重復(fù)錄入，去重后得到最近一次乙肝表面抗原記錄體檢結(jié)果有1 218 375條。

2.2 數(shù)據(jù)清洗

剛拿到數(shù)據(jù)時(shí)，并不知道該從哪些方面進(jìn)行數(shù)據(jù)清洗，只是根據(jù)經(jīng)驗(yàn)來進(jìn)行嘗試性分析，結(jié)果發(fā)現(xiàn)結(jié)果跟經(jīng)驗(yàn)得來的不一致，所以才開始了數(shù)據(jù)清洗。這是一個(gè)反復(fù)的過程。在進(jìn)行數(shù)據(jù)清洗時(shí)，為了找出數(shù)據(jù)異常值，本研究把統(tǒng)計(jì)涉及的所有數(shù)據(jù)字段都進(jìn)行查詢，跟數(shù)據(jù)字典進(jìn)行一一比較[2]。

數(shù)據(jù)清洗過程中發(fā)現(xiàn)最耗時(shí)間的是去重。對一人多次體檢的重復(fù)數(shù)據(jù)，先獲取某人的最新一天體檢信息，然后根據(jù)sfzh、tjrq獲取系統(tǒng)中某人最新一天的最大id號的體檢信息[3]。另外，對數(shù)據(jù)格式的簡單變換，如本研究中出生日期轉(zhuǎn)換成年齡，N位地區(qū)編碼轉(zhuǎn)換取前8位地區(qū)編碼；還將linshi_2表與js_tj_tjjl_bak進(jìn)行連接，通過id和sfzh獲取過濾后人的所有信息。

2.3 數(shù)據(jù)統(tǒng)計(jì)

本次統(tǒng)計(jì)的特點(diǎn)是統(tǒng)計(jì)量較多，有4個(gè)變量地區(qū)、年齡段、乙肝表面抗原陰性/陽性、性別，共有816個(gè)行。

3 結(jié)語

數(shù)據(jù)清洗耗費(fèi)非常多的人力、精力，而數(shù)據(jù)清洗的目的就是要解決數(shù)據(jù)質(zhì)量問題，因此是否可以未雨綢繆實(shí)現(xiàn)數(shù)據(jù)質(zhì)量的提升。在設(shè)計(jì)軟件系統(tǒng)時(shí)，需要在數(shù)據(jù)采集時(shí)進(jìn)行盡可能多的有效性約束，在管理上對數(shù)據(jù)進(jìn)行審核，在報(bào)表統(tǒng)計(jì)時(shí)也對數(shù)據(jù)質(zhì)量進(jìn)行分析。這也提醒業(yè)務(wù)管理人員一定要高度重視數(shù)據(jù)的質(zhì)量。很多軟件系統(tǒng)上線初期，軟件推廣主要方向是通過培訓(xùn)讓更多的用戶使用軟件系統(tǒng)。隨著軟件用戶的穩(wěn)定、軟件功能的穩(wěn)定，軟件推廣的主要方向更應(yīng)該側(cè)重于數(shù)據(jù)的質(zhì)量的提升以及數(shù)據(jù)價(jià)值的發(fā)掘。

數(shù)據(jù)統(tǒng)計(jì)的便捷性與靈活性非常重要。如果手工統(tǒng)計(jì)的話，非常耗時(shí)耗力。因此本次使用游標(biāo)、循環(huán)執(zhí)行存儲過程等來編寫專門編寫的SQL塊，可以一次性獲得所有統(tǒng)計(jì)數(shù)據(jù)，特別省時(shí)省力。這種方法不僅能解決某個(gè)特定的問題，而且當(dāng)統(tǒng)計(jì)條件變化時(shí)，也能夠靈活應(yīng)對，只需稍改存儲過程就可以實(shí)現(xiàn)。這特別在清理過程需要反復(fù)進(jìn)行時(shí)，此方法非常高效。