数据仓库中的数据清洗

数据仓库中的数据清洗刘玉① 陈金雄②①福州大学物理与信息工程学院，350002，福州市工业路523号②南京军区福州总医院，350025，福州市西二环北路156号关键词 数据清洗 二次清洗 数据仓库摘 要 以病种分析为例，介绍了在数据仓库中数据清洗的方法——二次清洗法，二次清洗完成的工作是不同的，第一次的清洗主要负责清洗源数据中的“脏数据”，第二次清洗则负责维度的提取。

1 引言随着时间的发展，医院信息系统中积累了大量的业务数据，越来越多的医院选择建立数据仓库以提取其中有用的信息，用于分析和决策。

病种分析就是当前比较热门的主题，可以通过病种分析主题考察单病种的治愈质量、平均费用、平均住院日及单病种的病人构成情况，有利于单病种的合理限价，提高医院的竞争力。

病种分析的星型结构见图1。

病种分析中涉及到众多的数据，数据的准确与否直接关系着决策质量的好坏。

为了能够准确的决策，必须对进入数据仓库的数据进行清洗。

图1 病种分析主题的星型结构（事实表中红色的字段为其度量）由于数据的清洗需要占用系统较多的资源，为了不影响“军卫一号”日常的处理速度，同时保证数据尽可能的准确，我们采用了“二次清洗”的方法：将源数据抽取至数据缓冲区时进行第一次的数据清洗；将数据缓冲区的数据送入数据仓库时进行第二次的清洗，两次清洗的作用范围是不同的[1]。

清洗的过程见图2。

事实表 SYM_IDAGE_IDADD_IDSEX_IDCHARGE_IDCHARGE_DEPTDISCHARGE_DETPDOCTOR_ID数量平均住院日平均费用科室维 DEPT ID 地理维 ADD ID 病种维SYM ID 费别维 CHARGE ID 医生维 DOCTOR ID 性别维 SEX ID 年龄维 AGE ID图2 数据清洗的过程2 第一次清洗病种分析涉及到“军卫一号”中的5张相互关联的业务数据表，14张公用字典表，第一次清洗主要是负责清洗源表中的“脏数据”，本次清洗在数据缓冲区中进行。

根据“脏数据”种类的不同，有下面四种清洗的途径。

2.1 业务数据表间关联的清洗[2] 病种分析主题中所需要的源数据来自“军卫一号”中的不同的五张表：诊断分类记录DIAGNOSTIC_CATEGORY、诊断对照记录DIAG_COMPARING、诊断记录DIAGNOSIS、住院病人主记录PAT_VISIT、门诊诊断记录CLINIC_DIAGNOSIS，这五张表可以通过相应的字段相互关联。

这时，数据清洗要做的就是检查这些表间是否能够一对一的关联起来；若不能关联，则必须找出不能关联的记录，对这些记录中的相关字段进行清洗。

由于可以通过诊断分类记录中的键值与其余四张表中相同的字段相关联。

因此，我们选定诊断分类记录作为主表，其余的四张表作为辅表，利用sql语句中的left outer join 来确定不能与主表建立关联的辅表中的记录。

诊断记录通过病人标识PATIENT_ID、病人本次住院标识VISIT_ID和诊断序号DIAGNOSIS_NO与诊断记录中相对应的字段相关联，以获取病种的治疗天数、诊断质量、诊断类型。

两表间的关联程度，可通过如下sql语句来实现：通过上述的sql语句，可以查看诊断分类记录中不能与住院病人主记录相关联的记录。

select * from(select a.*,b.patient_id as pid,b.visit_id as vid,b.diagnosis_no as diagno frommedrec.diagnostic_category a left outer join medrec.diagnosis b ona.patient_id=b.patient_id and a.visit_id=b.visit_id and a.diagsis_no=b.diagnosis_no) tt数据缓冲区数据仓库 军卫一号对这些不能关联的记录，不能马上将其判断为“脏数据”，还必须做具体的考虑。

由于诊断记录是用来记录住院病人的诊断情况的，而诊断分类记录中的记录既包含住院病人又包含门诊病人的诊断分类情况，因此，诊断分类记录中不能与诊断记录关联的记录，有可能是与门诊诊断记录CLINIC_DIAGNOSIS相关联的。

这种情况下，应再次利用left outer join 语句，将上面的查询结果作为左表，门诊诊断记录作为右表，在删除诊断分类记录中与门诊诊断记录相关联的记录后的记录才是真正的“脏数据”。

对于这些“脏数据”的处理，我们在与诊断记录中添加了一条“默认记录”，当诊断分类记录不能关联到诊断记录时，则自动关联“默认记录”中的数据。

“默认记录”中的具体数，采用极值法获得，即将合法的诊断记录中各字段出现频率最高的值作为“默认记录”中相应字段的默认值。

2.2 业务数据表与公共数据字典间的关联 理论上，“军卫一号”中业务数据表凡是涉及到公用数据字典的字段，都必须将数据业务表中的相应字段作为外键与数据字典表关联，如病人住院主记录中的出院科室DEPT_DISCHARGE_FROM作为外键关联科室字典DEPT_DICT 中的科室代码DEPT_CODE。

但系统为了性能的考虑，在一定程度上舍弃外键约束，导致了数据的不一致。

对于这种类型的数据清洗，首先应从业务表中提取出不能与公共字典对应的记录，再制定相应的规则对其进行转换。

同样以病人住院主记录中的出院科室DEPT_DISCHARGE_FROM为例说明，这种类型数据清洗的过程：(1)提取病人住院主记录中出院科室不能与科室字典中的科室代码对应的记录，可利用sql语句的not in语句来找出出院科室不符合科室字典要求的记录：select * from pat_visit where dept_discharge_from not in (select dept_code from dept_dict)(2) 制定转换规则，对步骤（1）中得到的数据进行清洗：a.在科室字典中新增一条记录，令科室代码DEPT_CODE＝“FF”，科室名称DEPT_NAME＝“其他科室”；b.病人住院主记录中不符合要求的出院科室全部更新为“FF”，将其归为“其他科室”。

2.3 数据空值的清洗 对于空值的处理是数据仓库中一个常见问题，是将它作为脏数据还是作为特定一种维成员，应根据实际情况进行判断。

一般的做法是视表中空值字段有无分析价值而定。

2.3.1 对于没有分析价值的字段 如病人住院主记录中的尸检标志、联系人姓名、联系人邮编等属性，在病种分析主题中没有分析的价值，可直接忽略，不进行清洗。

2.3.2 对于有分析价值的数据 必须根据实际情况对空值进行判断，转化为特定的值。

如，病人住院主记录中的出入院科室等属性，主要用于构成病种分析主题中科室维度的外键，具有分析价值，必须保留，若出入院科室代码为空，则将其转换为“FF”（对应于科室字典中的“其他科室”）。

对于其他为空的有分析价值的属性，也可以采取类似的转换办法，给空值字段赋予特定的值。

2.4 不符合逻辑要求的数据 所谓不符合逻辑要求的数据，指的是不符合现实规律的数据，这种类型的“脏数据”主要集中在涉及到日期的字段。

如病种分析中的度量之一——某一病种的住院天数。

“军卫”中的表没有直接涉及到住院天数，需从病人住院主记录中提取病人入院和出院时间，将两字段相减而得到。

为了分析的精确，就必须对病人的出入院的日期进行两方面的逻辑检验：病人的出入院时间是否小于系统当前时间、病人的出院时间是否晚于病人的入院时间。

对于不符合要求的数据，采用“均值法”进行替换，即计算出符合逻辑要求的记录的平均住院天数，将此平均住院天数赋给不符合要求的记录。

3 第二次的数据清洗第二次的数据清洗的主要任务是从经过第一次清洗后的源表中抽取所需要的维度信息。

病种分析中，所涉及到的维度除了科室维、费别维可以分别利用科室字典和费别字典直接得到外，其他的维度时间维、病人年龄维、地理维、病种维等维度均要从源表中抽取相应的字段再经过转换而得到。

一般来说，维度都是具有层次结构的。

因此，按维度层次的清洗顺序，可将维度的清洗分为三种方法：正向清洗、反向清洗及两种的综合。

所谓的正向清洗是指对有层次维度，先取得父维度的信息，再根据父维度信息提取子维度的信息，这样做的好处是可以逐步缩小清洗的范围。

反向清洗是指根据子维度的信息，向上查找父维度的信息，适合于子维度已知且子维度仅属于一个父维度的情况。

而综合清洗则是指两者的综合。

3.1 反向清洗 以病种维为例，说明病种维建立的过程。

病种维采用层次结构，按病种大类、病种中类及疾病名称构成父子层次维度，且一种疾病只对应唯一的病种大类和病种中类。

源表中没有病种大类及病种中类的字段，诊断分类记录的诊断代码DIAGNOSIS_CODE，即疾病代码是按ICD9编码的。

因此，为了获取病种大类和中类，必须新建一张按ICD9编码的病种分类字典，将诊断代码DIAGNOSIS_CODE作为外键与病种分类字典相连。

病种分类字典的结构见表1。

表1 病种分类字典的表结构字段名称 类型 长度说明 ID NUMBER 4 主键，自动增量填写 SYM_BIG_TYPE STRING 20 病种大类，按ICD9编码 SYM_MID_TYPE STRING 20 病种中类，按ICD9编码DIGNOSIS_CODE NUMBER 10 与诊断分类记录中的DIAGNOSIS_CODE相关联获取病种大类与中类的办法是，根据诊断代码，到病种分类字典里取得病种大类与病种中类。

同时，通过诊断代码到“军卫”中的诊断字典DIAGNOSIS_DICT中获取疾病名称。

3.2 综合清洗 以地理维的清洗为例，说明维度综合清洗的过程。

地理维用于存储病人所在地的信息，源表中没有属性直接指明病人的地理信息，但可以通过对病人住院主记录中的通信地址MAILING_ADDRESS的清洗而得到。

第一次的清洗中已将病人住院主记录中的通信地址MAILING_ADDRESS为空的记录用特定值代替（该指定值为医院所在地）。

地理维分为省、市、区/县三个层次。

其中省为父维度，市为省的子维度又同时为区/县的父维度，而区/县则为市的子维度，即一个省中包含若干个市，一个市中又包含若干个区/县。

为了从通信地址中提取这三个层次的信息，需要在数据缓冲区增加一张地区层次字典LEVEL_AREA_DICT，表结构见表2。

表2 地区层次字典的表结构字段名称 类型 长度说明ID NUMBER 4 主键，自动增量填写PROVINCE STRING 20 记录各省的信息CITY STRING 20 记录各省中市的信息，一个市只能对应一个省COUNTY NUMBER 10 记录中各市中区/县的信息，一个区/县只对应一个市对地理维度的提取，先采用正向清洗的办法，即对每条记录中的通信地址在地区层次字典中进行三次遍历，依次得到省、市、区/县的层次维度信息。