SQL在健康體檢隊列數據清洗、數據報表中的應用

王路露

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

0 引言

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

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

1 資料與方法

1.1 數據來源

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

1.2 方法

根據本次數據的特點，考慮規(guī)范性、完整性、有效性以及正確性，確定數據清洗步驟：獲取原始數據→去除重復→計算統(tǒng)計標準→計算統(tǒng)計結果→報表展示。

(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；

其次，存在一個人同一天多次體檢，統(tǒng)計時認定為一次，認為是重復錄入導致。具體語句為：

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)計算統(tǒng)計標準。

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

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位數字作為地區(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)計算統(tǒng)計結果。

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

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

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

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ū)編碼最后時退出

open nianling11;

loop

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

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

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

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打開后，選擇透視表，將地區(qū)、年齡設置為行變量，乙肝表面抗原、性別設置為列變量，統(tǒng)計結果設置為求和、無計算，結果如圖1所示。

圖1 數據報表展示

2 結果

2.1 數據特征描述

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

2.2 數據清洗

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

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

2.3 數據統(tǒng)計

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

3 結語

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

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