数据质量通用规则

数据治理

数据质量规则

---

目的

数据治理分析的重要前提是有数据且有正确的数据可以提供分析。数据质量的把控，应是后续工作的重中之重。为数据挖掘、预测、数据分析算法的合理使用、多维查询、即席分析、数据可视化等工作做好支撑，让数据质量不再是空中楼阁。数据质量不高表现为数据以多种格式，杂乱无序的存在于内外部的各个业务应用系统中，无统一数据源，数据分析可用的准确数据无法识别，展示信息不准，很难有效支持领导决策。为形成有效数据资产，要明确数据质量管理目标、控制对象和指标、定义数据质量检验规则、执行数据质量检核，生产数据质量报告。综上数据质量在数据治理过程中既是前提又是目标，通过数据质量问题处理流程及相关功能实现数据质量问题从发现到处理的闭环管理，从而促进数据质量的不断提升。

数据质量校验标准

• 完整性：完整性用于度量哪些数据丢失了或者哪些数据不可用。
• 规范性：规范性用于度量哪些数据未按统一格式存储。
• 一致性：一致性用于度量哪些数据的值在信息含义上是冲突的。
• 准确性：准确性用于度量哪些数据和信息是不正确的，或者数据是超期的。
• 唯一性：唯一性用于度量哪些数据是重复数据或者数据的哪些属性是重复的。
• 关联性：关联性用于度量哪些关联的数据缺失或者未建立索引。

需求分析概述

数据质量通用规则应该做到在不同业务都可以直接使用而不用担心规则的过度判断和遗漏。

1. 规则分组
   通用规则可以放在行业常用规则分组中。
2. 规则命名
   命名需要体现规则的作用范围，不可与规则SQL表达式有冲突。
3. 规则评价分类
   规则评价分类选择系统预定义规则评估分类类型。
4. 规则权重
   通用规则在质量评价中的等级，分为：一般、警告和阻断。
5. 规则SQL表达式
   质量规则的定义，一般不要在前面加where，参数使用${自然数}表示参数。
6. 参数配置
   定义参数类型，有当前模型指标项、字符、数值、时间等。

数据质量规则评审

通过SQL表达式找出不符合规则的数据，通用规则既要考虑结果的准确性还要考虑处理过程的可行性、时间成本、空间成本、不同规则是否满足业务不相关等因素。

评审内容	SQL表达式	描述	参数
非空校验	${1} is null	当前模型字段为空的字段	${1}:字段
值域校验	left outer join ${1} t on ${2} = t.${3} and t.${3} is null WHERE ${2} IS NOT NULL AND t.${3} IS NOT NULL	模型字段不在码表中的数据	${1}:码表 ${2}:当前模型字段 ${3}:码表字段
大小值校验	${1} > ${2} or ${1} < $	模型字段值不某个取值范围	${1}:当前字段 ${2}:最大值 ${3}:最小值
长度校验	length(${1}) <> $	模型字段长度规范值	${1}:字段 ${2}:长度值
唯一性校验	group by ${1} having count([DISTINCT] ${1}) > 1	找出模型重复数据	${1}:当前表主键字段
及时性校验	datediff(${1},${2}) < 0	判断当前模型时间标识字段是否是晚于截止日期	${1}:字段 ${2}:截止时间
取值范围约束	${1} not in (${2})	模型字段是否在有限的取值范围内	${1}:当前字段 ${2}:可能的取值列表（如：'01','02'）
数字精确位数	length(split((case when length(regexp_extract(${1},"([0-9]+\.)([0-9]+)(E-*[0-9]+)",2))=0 then ${1} when length(regexp_extract(${1},"([0-9]+\.)([0-9]+)(E[0-9]+)",2))<=cast(regexp_extract(${1},"(E)([0-9]+)",2) as int) then rpad(regexp_replace(regexp_extract(${1},"([^E]+)",1),"\.",""),cast(regexp_extract(${1},"(E)([0-9]+)",2) as int)+1,"0") when length(regexp_extract(${1},"([0-9]+\.)([0-9]+)(E[0-9]+)",2))>cast(regexp_extract(${1},"(E)([0-9]+)",2) as int) then concat(substr(regexp_replace(regexp_extract(${1},"([^E]+)",1),"\.",""),1,cast(regexp_extract(${1},"(E)([0-9]+)",2) as int)+1),".", substr(regexp_replace(regexp_extract(${1},"([^E]+)",1),"\.",""),cast(regexp_extract(${1},"(E)([0-9]+)",2) as int)+2)) when ${1} regexp "E-" then concat("0.",repeat("0",cast(regexp_extract(${1},"(E)(-)([0-9]+)",3) as int)-1),regexp_replace(regexp_extract(${1},"(.+)(E)",1),"\.","")) else ${1} end), '[.]')[1]) < $	数精度判断（为适应科学计数法sql写法比较长）	${1}:字段 ${2}:最小精度
电话号手机号码校验(11位)码校验	${1} is null or length(${1}) <> 11 or REGEXP_LIKE(${1}, '^[^1]')	校验11位手机号	${1}:当前字段
身份证号校验(18位)	${1} is null or length(${1})<>18 or regexp_like(substr(${1},1,17), '^[[:digit:]]+$') or substr(${1},7,4) < 1800 or (substr(${1},11,2) < 1 or substr(${1},11,2)>12) or (substr(${1},1,1)7+ substr(${1},2,1)9+ substr(${1},3,1)10+ substr(${1},4,1)5+ substr(${1},5,1)8+ substr(${1},6,1)4+ substr(${1},7,1)2+ substr(${1},8,1)1+ substr(${1},9,1)6+ substr(${1},10,1)3+ substr(${1},11,1)7+ substr(${1},12,1)9+ substr(${1},13,1)10+ substr(${1},14,1)5+ substr(${1},15,1)8+ substr(${1},16,1)4+ substr(${1},17,1)*2)%11 <> ( case when substr(${1},18,1)='1' then '0' when substr(${1},18,1)='0' then '1' when substr(${1},18,1) in ('X','x') then '2' when substr(${1},18,1)='9' then '3' when substr(${1},18,1)='8' then '4' when substr(${1},18,1)='7' then '5' when substr(${1},18,1)='6' then '6' when substr(${1},18,1)='5' then '7' when substr(${1},18,1)='4' then '8' when substr(${1},18,1)='3' then '9' when substr(${1},18,1)='2' then '10' end )	二代身份证校验	${1}:当前字段