数据质量核验流程及规范

• 明确表的适用范围及意义

1. 表的适用范围：

每张表都有一定的适用范围，超出适用范围，统计的结果就会不准确。

应当由数据提供方明确该表的适用范围，在接入过程中，把适用范围也加到表的中文名上。

例如：有一张表中文名为“放射系统检查病人信息” 需要接入到坪山区大数据中心，从表名来看无法确定该表是包含整个坪山区的医院放射系统病人信息，还是只包含某个医院的放射系统病人信息，甚至有可能只是某家医院分院的放射系统病人信息。

此时需要沟通数据提供方，明确该表的适用范围。

如果该表只包含平乐骨伤科医院的放射系统检查病人信息，则接入到大数据中心后，中文表名应当改为“坪山区平乐骨伤科医院_放射系统检查病人信息” 。

1. 表的意义：

先查看表结构，根据表结构推测这个表记录的内容，如何使用，数据的意义。有助于后续对表数据质量的判断。

• 初步分析表的总体概况

1. 数据量

统计数据量可以增强对表的理解。并且有时候在统计数据量时，就可以发现这个表数据是否正常，例如 坪山高铁班次表，这个表曾经统计出来有几千条数据，一个小站是不可能有几千个班次的。

Select count(1) from 表名

1. 字段数

明确该表的字段数

3. 物理主键

确定该表是否具有物理主键。

物理主键通常是没有实际业务意义的主键，仅用来确保行记录的唯一性，通常是表的第一列ID。如果该表第1列的ID字段具有唯一性，则基本可以确定为该表的物理主键。

Select 主键字段, count(1) from 表名 group by 主键字段 having count(1) > 1;

1. 业务主键

不一定每张表都有业务主键，但是能分析出业务主键，会有助于我们更好的理解这个表的用法。

业务主键就是具有实际业务意义的主键，例如法人表，通常我们用企业统一信用代码来确定唯一一家企业，如果该字段具有唯一性，我们就可以说企业统一信用代码是法人表的业务主键，也可以说企业统一信用代码是法人表的最细粒度。

注意：有企业统一信用代码的表，不一定最细粒度就是企业统一信用代码，需要进一步分析。

例如：

白名单企业信息表，这张表主要用来关联企业人员信息表，用于确定每个行业主管部门纳管了哪些企业单位，而这些企业单位又纳管了哪些人员。但是同一个企业的不同部门可能会由不同的行业主管部门纳管。在白名单企业信息表中存在一种情况：深圳技术大学和深圳技术大学医务室的统一信用代码是同一个，但是深圳技术大学的主管部门是教育局，而深圳技术大学医务室的主管部门是卫健局。所以这张白名单企业信息表的1个企业统一信用代码可能会对应2个企业名称（深圳技术大学和深圳技术大学医务室），则企业统一信用代码不具有唯一性，它不是白名单企业信息表的业务主键。

1. 删除标识

确定该表是否有逻辑删除字段，有可能是delete_flag字段，也有可能使用delete_time作为逻辑删除标识。（不一定是删除标识，也有能是无效标识）

注意：分析主键唯一性之前，应先确认有没有删除标识、无效标识字段，如果有，则应当先过滤掉无效数据。

1. 数据字段分类

区分主数据字段、业务数据字段、分析数据字段、审计字段。

字段可以划分为主数据字段、业务数据字段和分析数据字段。

主数据字段：描述的是核心业务实体，如客户、供应商、地点、产品和库存等；

业务数据字段：描述实体发生的业务流程，如客户订单、出库单等，业务数据的实质是多个业务实体之间的关系；

分析数据字段：在业务数据字段之上衍生出的聚合统计类字段。

审计字段：审计字段与表的主数据字段、业务数据字段、分析数据字段都没有直接联系，通常是用来记录一些操作，包括记录数据的创建人、创建时间、修改人、修改时间、数据的刷新时间等字段。

• 数据核验内容

以下检查内容都应该先过滤掉无效数据（根据逻辑删除标识、是否有效标识过滤）

1、数据完整性检查

完整性检查的主要依据是空值率统计的结果。对所有字段进行空值率统计后，观察每个字段的空值率，主要从以下3个维度观察统计结果：

1. 空值率偏高（80%-100%）的字段数量，占字段总数的比例多少；

如果空值率偏高的字段数量，占总字段数的比例偏高，达到50%以上，可以认为该表的数据可能不完整。

例如：

某委办局共享的一张表总共10个字段，里面有5个字段空值率超过80%，此时应当向该委办局提出疑问，要求对方修正数据或作出合理解释。

1. 重要字段是否空值偏高；

在明确表的意义后，我们可以猜测到其中某些字段是核心字段，该字段存在空值会影响数据完整性，如果空值率太高，甚至可以认为该表数据无意义。

例如：

某委办局共享了一张核酸采样点信息表，但其中街道、社区、地址字段存在大量空值，则可以认为该表完整性受损，没有使用意义。此时应当向该委办局提出疑问，要求对方修正数据或作出合理解释。

1. 核心字段是否存在空值；

核心字段不应当允许空值存在。法人表的核心字段是统一信用代码，人口表的核心字段是证件号码，楼栋表的核心字段是楼栋编码。核心字段为空的记录通常是没有使用意义的，如果存在空值，应当向该委办局提出疑问，要求对方修正数据或作出合理解释。

Sql : select '字段' as 字段名, sum(case 字段 when null then 1 when 'NULL' then 1 when 'null' then 1 when '' then 1 else 0 end)/count(1) as 空值率 from 表名

2、数据唯一性检查

（1）物理主键唯一性

如果ID是表的物理主键，应当确保ID是唯一的；（注意过滤逻辑删除数据和无效数据）

（2）业务主键唯一性

如果表不存在物理主键，但是根据业务理解，应当有一个字段或多个字段构成业务主键，则业务主键应当具有唯一性。

例1：楼栋信息表，如果根据业务理解，该表存储的数据对应是每一行记录一栋楼的信息，则对楼栋编码分组统计记录数，每个分组的记录数都不应该超过1。

例2：核酸匹配结果表，如果根据业务理解，该表存储的数据是近期每个人的核酸检测记录，按照正常逻辑，一个人一天应该是只做一次核酸检测的（也可能有例外），可以初步判定，证件号码和核酸采样日期构成业务主键，根据证件号码和核酸采样日期进行分组统计记录数，每个分组的记录数都不应该大于1。

如果初步判定的业务主键不具有唯一性，应当向数据提供方询问具体的业务主键是由什么字段构成。

Sql : select '字段' as 字段名, 字段 as 字段值, count(1) as 个数 from 表名 group by id having count(1)>1

3、数据有效性检查

（1）日期时间有效性

部分日期时间字段是由系统生成的，比如创建时间、修改时间、更新时间、删除时间等字段，按照常识理解，这些时间的范围应该都是近几年，或近十几年的，不太可能是2000年以前的，更不可能是当前日期以后的。

部分日期时间字段是具有业务意义的，比如出生日期，如果该表记录的是历史人物的出生日期，则不需要检查有效性，但如果该表记录的是近几年的医院就诊病人信息，则出生日期不太可能出现1900年之前的，更不可能出现当前日期以后的。

可以统计出日期时间字段的最大值和最小值，看看是否在合理范围内。

如不合理，应当向该数据提供方提出疑问，要求对方修正数据或作出合理解释。

Sql: select max(BirthDate), min(BirthDate) from 表名 where BirthDate is not null；

select substr(BirthDate, 0, 4) br, count(1) from 表名

where BirthDate is not null

group by substr(BirthDate, 0, 4)

order by br desc;

（2）枚举值有效性

枚举值字段的数据值是有限的，例如性别、民族、状态、街道名称、街道编码。应当对其去重，看看数据是否合理。假如一个表存储的是某个区的数据，则不应该出现其他区的街道名称或编码。

注意：经常会有枚举值字段使用数值表示，例如：XX状态、XX编码。但这些状态、编码的每个数值分别代表什么意思，我们一般是猜不出的。此时应当要求数据提供方，对此类枚举值的中文意义进行备注，同时在数据接入时，也要注意加到字段备注上。

Sql: select xb, count(1) from 表  group by xb;

（3）度量值有效性；

度量值像比如身高、体重、年龄、净重，如果根据业务理解，此类数值应当有一个大致的范围，则应该检查其数值是否超出范围。例如身高不可能超过3米，年龄一般不会超过120岁。

Sql: 参考日期时间有效性

4、数据一致性检查

（1）精度一致性：

对于经纬度数据，为了定位精确，应当要求其精确到小数点后6位。如果有的精确到小数点后5位，有的是6位，则违反精度一致性。

注意：不同的地图厂商会对地图数据做不同的偏移处理，确保国家地图数据安全。因此，在接收到经纬度相关数据时，应当要求数据提供方备注地图坐标系（例如：GPS坐标，腾讯地图坐标系、百度地图坐标系）。

Sql: select length(substring(字段,5)), count(1) from 表名 group by length(substring(字段,5));

（2）字符长度一致性；

编码、ID、证件号码、统一信用代码等字段，一般是由系统生成的或者有固定的长度规则，可以尝试通过长度判断是否存在异常数据。

例1：ID、编码字段如果是系统生成的，字符长度不太可能出现长度明显不一致的情况， 如果一个表的ID字段，长度从1到16各种情况都有，并且其中长度为16的有1万条数据，而长度为1到15的都是零零散散几条数据，则长度为1到15的数据很有可能是异常数据。应当对其重点核验。

例2：证件号码，新版的身份证号是18位，旧版的身份证号是15位，如果证件号码只包含身份证，则长度只有可能是15或18。如果证件号码包含了护照、港澳通行证、回乡证等证件，则可能存在长度为8、9、10的，但长度肯定不会是1、2、3、4、5、6、7、19或更大。如果有，则很可能是异常数据。

select length(字段), count(1) from 表名  group by length(字段);

（3）枚举值一致性；

枚举值字段的数据应当是统一格式的，像比如性别字段，只会存在3种情况：女性、男性、不确定。有的表是用0、1、2表示，有的表则用中文表示，如果存在混用数字和中文来表示性别的情况，则可以判定违反数据一致性原则。

5、数据及时性检查

根据表的意义理解，如果表的数据是一些近乎实时状态的信息，则应当检查数据的及时性。

例1：有一张核酸采样点状态信息表，该表是一天多次刷新，存储了多个时间节点下，区内各个核酸采样点的状态信息，用来查询每个核酸采样点当前是开放状态还是关闭状态、拥堵状态还是通畅状态。这种数据的时效性要求比较高，如果数据刷新延迟太大，每次查询到表内最新的数据都是几个小时之前的，这种数据就已经没有使用意义了。

6、数据自洽性检查

自洽性定义：自己可以证明自己至少不是矛盾或者错误的。

如果一个字段跟另一个字段是有逻辑关系的，可以由一个字段推算出其他字段的值，则应当对这几个字段做自洽性检查。

例1：一个表同时存在身份证号、年龄、出生日期三个字段，可以根据身份证号推断出年龄和出生日期，则应该通过截取身份证号的方式，判断截取的内容跟年龄字段、出生日期字段的数值是否一致，来判断数据是否违反自洽性原则。

注意：年龄是会随着年份增长而变动的数据，如果第一年记录了一个人的年龄，第二年没有对年龄进行更新，则该数据就不是准确数据。

7、外数据错误检查

8、跨表关联检查

四. 数据质量问题总结

五. 数据核验报告

六.附录

1、主数据定义

主数据（Master Data）是指具有高业务价值的，可以在企业内跨越各个业务部门被重复使用的数据，是单一、准确、权威的数据来源。主数据包含元数据、属性、定义、角色、关联关系、分类方法等内容，被不同的应用所使用，涉及企业多数组织及业务单元。常见的主数据类型有产品、物料、客户、供应商、员工、会计科目、组织机构、项目等。

2、主数据的特征

与业务型数据和分析型数据相比，主数据具有以下几个特征：

特征一致性：由于企业布局的IT应用越来越多，数据散落分布在众多系统中。客户服务部门、生产部门以及采购部门都有各自的系统，彼此之间信息隔离。即使在一个业务部门里,也有众多前端和后端系统。正是构建在各种架构之上的不兼容系统中的这种部门化数据，使得创建和维护主数据的“单一”视图几乎无法实现。由于主数据的特征经常被用作业务流程的判断条件和数据分析的具体维度层次，因此能否保证主数据的关键特征在不同应用、不同系统中的高度一致直接关系企业实现应用集成的成败。

识别唯一性：在一个系统、一个平台，甚至一个企业范围内，同一主数据实体要求具有唯一的数据标识，即数据编码，例如，对于每位客户都有一个唯一的客户编码。根据一定编码规则得到的唯一的数据编码是进行业务活动的基础，在业务流转过程中各业务环节完全依赖业务活动数据中体现的主数据编码识别标志来定位后续的操作和处理，在业务环节结束后，主数据编码又将成为数据分析的主要维度，用来确定分析的范围和方向。

长期有效性：主数据通常贯穿该业务对象的整个生命周期甚至更长，换而言之，只要该主数据所代表的业务对象仍然继续存在或仍具有比较意义，则该主数据就需要在系统中继续保持其有效性。长期有效性的另一表现为主数据失去其效果时，系统采取的措施通常为标记无效或标记删除而非直接物理删除。只有定期对数据进行归档时，才会考虑将该主数据编码信息从系统中彻底删除。

业务稳定性：主数据作为用来描述业务操作对象的关键信息，在业务过程中其识别信息和关键的特征会被业务过程中产生的数据继承、引用和复制。但无论业务过程如何复杂和持久，除非该主数据本身的特征发生变化，否则主数据本身的属性通常不会随业务的过程而被修改。所以当识别主数据时，某些与业务结果密切相关及日效性很强的特征(如员工薪资等)需要同员工的固定属性（如员工姓名等）区别对待。