MySQL在健康相關數據清理中的應用*

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

?

MySQL在健康相關數據清理中的應用*

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

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

MySQL 健康數據 數據清理 統計匯總

由于我國的信息化起步較晚、數據標準不統一以及錄入不規范等問題，導致相關數據尤其是健康數據的質量參差不齊，存在大量缺失、重復、異常值及邏輯錯誤等問題[1-2]。數據質量是統計分析結果的重要保障，數據質量的好壞嚴重影響結果的科學性和準確性；因此，如何快速清理大量的醫療數據，獲得高質量的數據，已成為環境與健康研究領域亟待解決的問題之一。

本研究以某兒童醫院2012-2015年門診數據為例，根據數據特征建立清洗規則，利用MySQL實現醫院門診個案數據的快速清洗、匹配標準ICD-10編碼并生成分病種日門診量統計報表，為后續環境與健康影響分析奠定基礎。

資料與方法

1.數據來源

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

2.方法

(1)制定清洗程序

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

(2)數據清洗

個案數據導入MySQL Server后，將表單命名為children_hospital，調整年齡字段的存儲結構為INT(10)，其余各字段調整為VARCHAR(100)。

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

②刪除重復記錄，在children_hospital表中增加自動增長且不為空的新字段ID(ID INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID))，定義姓名、性別、出生時間、就診時間、家庭地址、就診科室、接診醫生、疾病診斷完全相同的記錄為重復記錄，保留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 姓名,性別,年齡,地址,出生時間,就診科室,接診醫生,就診時間,診斷碼)AS a WHERE a.sums>=1)。

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

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

圖1 醫院門診數據清洗程序

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

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

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

(3)統計匯總

根據ICD-10編碼，分別計算各病種的日就診量，以及進行性別、年齡分層匯總，生成統計報表。

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

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

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

結 果

1.數據特征描述

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

表1 某醫院不完全重復記錄及不標準記錄示例

2.數據清洗及ICD-10編碼匹配結果

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

各操作步驟處理記錄及時間如表2所示，可見絕大多數運行時間消耗在ICD-10匹配環節；數據結構及質量對運行速度關系最大，數據結構越復雜、質量越差、電腦性能越低，則運行時間越長；數據量大小對速度的影響并不明顯，如若電腦性能較差，可通過拆分為幾個小量級的表單分別運行。

表2 醫院門診數據處理情況及處理時間

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

3.準確率

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

討 論

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

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

數據清洗過程中，清洗流程的選擇最為重要，由于大多數醫院門診數據都存在信息缺失或錯配的情況，不合理的清洗流程往往導致有用信息的丟失，因此必須仔細研究數據特征，挖掘數據間的邏輯關系，查找問題環節、出錯原因及規律，統觀全局而不只是單純的技術堆砌。

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

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

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

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

[4]劉欽普.中國醫療衛生發展水平區域差異綜合評價.中國衛生統計,2016,33(2):251-253.

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

(責任編輯：郭海強)

公益性行業(衛生)科研專項(201402022)，石家莊市科學技術研究與發展指導計劃(161460873)

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

2．中國疾病預防控制中心環境與健康相關產品安全所

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