宋 杰 郝舒欣 徐東群 陳鳳格 劉 悅△

?

MySQL在健康相關(guān)數(shù)據(jù)清理中的應(yīng)用*

宋 杰1郝舒欣2徐東群2陳鳳格1劉 悅2△

目的 探討MySQL技術(shù)在醫(yī)院門診、住院等醫(yī)療數(shù)據(jù)的快速清理和分類統(tǒng)計(jì)匯總中的應(yīng)用。方法 根據(jù)數(shù)據(jù)特征制定清理規(guī)則，通過MySQL軟件實(shí)現(xiàn)醫(yī)療數(shù)據(jù)的清理(刪重、合并、校正)并重新匹配ICD-10編碼，最終匯總計(jì)算分病種日接診量等統(tǒng)計(jì)信息。結(jié)果 MySQL可以對(duì)醫(yī)療數(shù)據(jù)進(jìn)行高效的數(shù)據(jù)清理，刪除不符合要求的記錄、校正錯(cuò)誤信息及匹配正確ICD-10編碼，自動(dòng)生成統(tǒng)計(jì)報(bào)表。結(jié)論 該方法可以高效快捷地處理健康數(shù)據(jù)，尤其是醫(yī)院門診、急救、死因等大數(shù)量級(jí)數(shù)據(jù)，生成準(zhǔn)確統(tǒng)計(jì)報(bào)表，在環(huán)境與健康研究中具有極強(qiáng)科學(xué)意義和使用價(jià)值。

MySQL 健康數(shù)據(jù) 數(shù)據(jù)清理 統(tǒng)計(jì)匯總

由于我國的信息化起步較晚、數(shù)據(jù)標(biāo)準(zhǔn)不統(tǒng)一以及錄入不規(guī)范等問題，導(dǎo)致相關(guān)數(shù)據(jù)尤其是健康數(shù)據(jù)的質(zhì)量參差不齊，存在大量缺失、重復(fù)、異常值及邏輯錯(cuò)誤等問題[1-2]。數(shù)據(jù)質(zhì)量是統(tǒng)計(jì)分析結(jié)果的重要保障，數(shù)據(jù)質(zhì)量的好壞嚴(yán)重影響結(jié)果的科學(xué)性和準(zhǔn)確性；因此，如何快速清理大量的醫(yī)療數(shù)據(jù)，獲得高質(zhì)量的數(shù)據(jù)，已成為環(huán)境與健康研究領(lǐng)域亟待解決的問題之一。

本研究以某兒童醫(yī)院2012-2015年門診數(shù)據(jù)為例，根據(jù)數(shù)據(jù)特征建立清洗規(guī)則，利用MySQL實(shí)現(xiàn)醫(yī)院門診個(gè)案數(shù)據(jù)的快速清洗、匹配標(biāo)準(zhǔn)ICD-10編碼并生成分病種日門診量統(tǒng)計(jì)報(bào)表，為后續(xù)環(huán)境與健康影響分析奠定基礎(chǔ)。

資料與方法

1.數(shù)據(jù)來源

數(shù)據(jù)來自某三級(jí)甲等兒童醫(yī)院2012年1月1日-2015年12月31日全部門診個(gè)案記錄，存儲(chǔ)為excel格式文件，主要包括病人姓名、性別、年齡、家庭住址、出生日期、就診日期、就診科室、接診醫(yī)生、疾病診斷及ICD-10編碼等內(nèi)容。

2.方法

(1)制定清洗程序

根據(jù)數(shù)據(jù)特征，依據(jù)規(guī)范性、完整性、有效性及正確性四大原則，制定數(shù)據(jù)清洗程序(見圖1)。①統(tǒng)一填寫標(biāo)準(zhǔn)，刪除字段前后的空格、數(shù)字或標(biāo)點(diǎn)符號(hào)；②重復(fù)數(shù)據(jù)保留一條，其余刪除；③合并不完整數(shù)據(jù)，針對(duì)本文案例中由于醫(yī)院管理信息系統(tǒng)問題造成的同一病人一次就診出現(xiàn)兩條或兩條以上不完全相同的記錄，將不一致字段進(jìn)行合并，融合成一條記錄；④刪除外地市前來就診的記錄，由于環(huán)境對(duì)人體健康的影響具有時(shí)空差異性，因此刪除外地市病歷記錄；⑤計(jì)算年齡，修正記錄中的異常值記錄及錯(cuò)誤記錄；⑥按照ICD-10編碼進(jìn)行重匹配；⑦修正不一致記錄。

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

個(gè)案數(shù)據(jù)導(dǎo)入MySQL Server后，將表單命名為children_hospital，調(diào)整年齡字段的存儲(chǔ)結(jié)構(gòu)為INT(10)，其余各字段調(diào)整為VARCHAR(100)。

①刪除各字段前后的不標(biāo)準(zhǔn)字符，以姓名字段為例，代碼如下：UPDATE children_hospital SET 姓名=REPLACE(姓名,LEFT(姓名,1),″)WHERE LEFT(姓名,1)=‘.’OR LEFT(姓名,1)=‘#’OR LEFT(姓名,1)=‘3’OR LEFT(姓名,1)=‘ ’；姓名后的字符將LEFT改為RIGHT。

②刪除重復(fù)記錄，在children_hospital表中增加自動(dòng)增長且不為空的新字段ID(ID INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID))，定義姓名、性別、出生時(shí)間、就診時(shí)間、家庭地址、就診科室、接診醫(yī)生、疾病診斷完全相同的記錄為重復(fù)記錄，保留ID最小的，刪除其余記錄：DELETE FROM children_hospital WHERE ID NOT IN(SELECT a.id FROM(SELECT MIN(ID)AS id,SUM(1)AS sums FROM children_hospital GROUP BY 姓名,性別,年齡,地址,出生時(shí)間,就診科室,接診醫(yī)生,就診時(shí)間,診斷碼)AS a WHERE a.sums>=1)。

③合并不完全記錄，針對(duì)部分字段存在差異、其余字段一致的記錄

CREATE TABLE child_temp SELECT ID,姓名,性別,年齡,GROUP_CONCAT(DISTINCT 地址)地址,GROUP_CONCAT(DISTINCT 出生時(shí)間)出生時(shí)間,就診時(shí)間,科室,醫(yī)生,診斷碼,診斷名稱 FROM children_hospital GROUP BY 姓名,性別,年齡,就診時(shí)間,診斷碼ORDER BY ID。

圖1 醫(yī)院門診數(shù)據(jù)清洗程序

④刪除外地市居民就診記錄：DELETE FROM child_temp WHERE 地址 LIKE ‘%邢臺(tái)%’OR 地址 LIKE ‘%邯鄲%’OR 地址 LIKE ‘%衡水%’OR 地址 LIKE ‘%張家口%’OR 地址 LIKE ‘%承德%’OR 地址 LIKE ‘%滄州%’OR 地址 LIKE ‘%唐山%’OR 地址 LIKE ‘%保定%’OR 地址 LIKE ‘%廊坊%’OR 地址 LIKE ‘%秦皇島%’。其他區(qū)縣記錄更改相應(yīng)區(qū)域名稱。

⑤計(jì)算年齡：UPDATE child_temp SET 年齡1=DATE_FORMAT(FROM_DAYS(TO_DAYS(就診時(shí)間)-TO_DAYS(出生時(shí)間1)),‘%Y’ )+0；根據(jù)年齡排序，觀察是否有異常值及邏輯錯(cuò)誤值：SELECT * FROM child_temp ORDER BY 年齡1 ASC；如有異常值，與年齡欄比較判斷是否為錄入錯(cuò)誤，進(jìn)行糾正。

⑥重新匹配ICD-10編碼：UPDATE child_temp,icd10 SET newicd=icd WHERE 診斷名稱=disease。由于疾病名稱填寫不規(guī)范(簡稱、錯(cuò)別字、拼音、縮寫等)而導(dǎo)致的未匹配ICD-10編碼的記錄，需要人工查找匹配。

(3)統(tǒng)計(jì)匯總

根據(jù)ICD-10編碼，分別計(jì)算各病種的日就診量，以及進(jìn)行性別、年齡分層匯總，生成統(tǒng)計(jì)報(bào)表。

①創(chuàng)建臨時(shí)表用于存儲(chǔ)日就診人數(shù)：CREATE TEMPORARY TABLE temp(就診時(shí)間 DATETIME,疾病分類 VARCHAR(50),就診人數(shù) INT);

②計(jì)算日就診人數(shù)：INSERT INTO temp SELECT LEFT(就診時(shí)間,10),‘日就診人數(shù)’,COUNT(*)FROM child_temp GROUP BY LEFT(就診時(shí)間,10);

INSERT INTO temp SELECT LEFT(就診時(shí)間,10),‘總非意外疾病就診人數(shù)’,COUNT(*)FROM child_temp WHERE LEFT(診斷碼,1)REGEXP ‘[a-r]+’AND SUBSTRING(診斷碼,2,2)>=‘00’AND SUBSTRING(診斷碼,2,2)<=‘99’GROUP BY LEFT(就診時(shí)間,10);

結(jié) 果

1.數(shù)據(jù)特征描述

該兒童醫(yī)院2012-2015年總門診記錄為4417789條，姓名前后可能有異常字符，年齡欄描述均為“×歲×月”或“×月×天”；少量記錄中缺失疾病診斷、ICD-10編碼或出生日期；文件中存在大量完全重復(fù)的記錄，以及僅地址和出生時(shí)間缺失的不完全重復(fù)記錄(表1)。

表1 某醫(yī)院不完全重復(fù)記錄及不標(biāo)準(zhǔn)記錄示例

2.數(shù)據(jù)清洗及ICD-10編碼匹配結(jié)果

該兒童醫(yī)院2012-2015年門診總記錄4417789條，去除重復(fù)記錄333137條，合并不完整記錄后剩余3376347條，去除其他省市及主城區(qū)外患者記錄后余1806032條，去除年齡大于15歲記錄80735條，余1725297條；經(jīng)過三次ICD-10編碼匹配，編碼率達(dá)99.1%，余下15718條記錄中1988條缺失疾病診斷，13730條需人工匹配。

各操作步驟處理記錄及時(shí)間如表2所示，可見絕大多數(shù)運(yùn)行時(shí)間消耗在ICD-10匹配環(huán)節(jié)；數(shù)據(jù)結(jié)構(gòu)及質(zhì)量對(duì)運(yùn)行速度關(guān)系最大，數(shù)據(jù)結(jié)構(gòu)越復(fù)雜、質(zhì)量越差、電腦性能越低，則運(yùn)行時(shí)間越長；數(shù)據(jù)量大小對(duì)速度的影響并不明顯，如若電腦性能較差，可通過拆分為幾個(gè)小量級(jí)的表單分別運(yùn)行。

表2 醫(yī)院門診數(shù)據(jù)處理情況及處理時(shí)間

*：時(shí)間格式hh：mm:ss:ms分別代表小時(shí)、分鐘、秒和毫秒。

3.準(zhǔn)確率

從原始數(shù)據(jù)中隨機(jī)抽取一天的記錄(3571條)進(jìn)行人工處理，發(fā)現(xiàn)重復(fù)數(shù)據(jù)查找與合并、外地市數(shù)據(jù)與不合理數(shù)據(jù)刪除、ICD-10編碼匹配、分系統(tǒng)分病種的就診量統(tǒng)計(jì)結(jié)果均與MySQL的完全一致。

討 論

由于各地經(jīng)濟(jì)發(fā)展水平不同、各家醫(yī)院的管理信息系統(tǒng)不同、甚至各位醫(yī)生的填寫習(xí)慣也不同，導(dǎo)致健康數(shù)據(jù)的結(jié)構(gòu)不統(tǒng)一、數(shù)據(jù)質(zhì)量差異較大[3-4]；而且交通的日益便捷，民眾就診范圍擴(kuò)大，導(dǎo)致與本地污染暴露不相關(guān)的外地患者記錄大大增加。如本研究收集的某醫(yī)院門診記錄，完全重復(fù)及部分重復(fù)記錄占總記錄的23.6%，市區(qū)外就診病例達(dá)到總病例數(shù)的46.5%；如不進(jìn)行數(shù)據(jù)清洗而直接進(jìn)行污染暴露的相關(guān)性分析，其結(jié)果必然存在著極大的不準(zhǔn)確性。因此，在環(huán)境與人群健康研究中，必須要關(guān)注健康數(shù)據(jù)的質(zhì)量，進(jìn)行嚴(yán)謹(jǐn)?shù)臄?shù)據(jù)清洗。

醫(yī)院門診量往往比較大，excel處理存在效率低下的問題。我國醫(yī)療系統(tǒng)的信息化起步較晚，相關(guān)的數(shù)據(jù)清理研究很少[5]。本文在分析數(shù)據(jù)特征、確立清洗流程后，將收集到的excel格式存儲(chǔ)的門診記錄導(dǎo)入MySQL數(shù)據(jù)庫，使用結(jié)構(gòu)化查詢語句進(jìn)行快速清洗、匯總等數(shù)據(jù)管理；具有以下優(yōu)點(diǎn)：(1)SQL語法簡單易學(xué)，可以快速掌握運(yùn)用；(2)根據(jù)數(shù)據(jù)特征組合不同程序，可處理各種數(shù)據(jù)結(jié)構(gòu)問題；(3)高效準(zhǔn)確，極大提高數(shù)據(jù)清理速度和質(zhì)量；(4)MySQL為開源軟件，方便獲取利用；(5)MySQL體積小、速度快，在普通電腦即可運(yùn)行且不需聯(lián)網(wǎng)操作。需要強(qiáng)調(diào)的是，本研究首次嘗試在科學(xué)研究中利用MySQL進(jìn)行健康相關(guān)大數(shù)據(jù)的清洗和匯總等研究，僅作為數(shù)據(jù)管理的手段，不作數(shù)據(jù)存儲(chǔ)等的應(yīng)用；受控人員在不聯(lián)網(wǎng)的電腦上處理數(shù)據(jù)，在查重操作后刪除原始數(shù)據(jù)中的敏感信息，匯總目的數(shù)據(jù)后將門診記錄信息全部清空，可以在最大程度上保證信息的安全性。

數(shù)據(jù)清洗過程中，清洗流程的選擇最為重要，由于大多數(shù)醫(yī)院門診數(shù)據(jù)都存在信息缺失或錯(cuò)配的情況，不合理的清洗流程往往導(dǎo)致有用信息的丟失，因此必須仔細(xì)研究數(shù)據(jù)特征，挖掘數(shù)據(jù)間的邏輯關(guān)系，查找問題環(huán)節(jié)、出錯(cuò)原因及規(guī)律，統(tǒng)觀全局而不只是單純的技術(shù)堆砌。

總之，本文所用MySQL技術(shù)不僅可以高效準(zhǔn)確地清洗醫(yī)院健康數(shù)據(jù)，而且可以將程序重新排列組合處理其他的大數(shù)量級(jí)數(shù)據(jù)，在環(huán)境與人群健康相關(guān)性研究中起到重要作用。

[1]劉悅，郝舒欣，宋杰，等.空氣污染與疾病關(guān)系的時(shí)間序列分析中門急診數(shù)據(jù)快速清洗及自動(dòng)分類匯總方法的研究.衛(wèi)生研究,2016，45(4)：624-630.

[2]劉悅，郝舒欣，韓京秀，等.門診個(gè)案數(shù)據(jù)快速清理及診斷疾病自動(dòng)編碼方法研究.中國醫(yī)院管理,2015，35(9)：69-71.

[3]杜艷君，陳晨，李湉湉.大規(guī)模數(shù)據(jù)清理的相關(guān)問題與探討.環(huán)境與健康雜志,2014,31(4):353-354.

[4]劉欽普.中國醫(yī)療衛(wèi)生發(fā)展水平區(qū)域差異綜合評(píng)價(jià).中國衛(wèi)生統(tǒng)計(jì),2016,33(2):251-253.

[5]門可,王霞.陜西省公共衛(wèi)生專業(yè)人員配置及其核心能力需求調(diào)查分析,2015,32(6):952-954.

(責(zé)任編輯：郭海強(qiáng))

公益性行業(yè)(衛(wèi)生)科研專項(xiàng)(201402022)，石家莊市科學(xué)技術(shù)研究與發(fā)展指導(dǎo)計(jì)劃(161460873)

1．石家莊市疾病預(yù)防控制中心(050011)

2．中國疾病預(yù)防控制中心環(huán)境與健康相關(guān)產(chǎn)品安全所

△通信作者：劉悅， E-mail：liuyue@sina.com