4. 设备主数据质量剖析实战

设备主数据质量剖析的目标不是“看一眼数据”，而是用可复现的查询把隐藏在字段里的问题变成可量化、可定位、可交接的证据。本节以设备主数据表 EQ_MASTER 为例，围绕抽样、SQL profiling、字段分布、异常值检测和质量报告生成，走完一次完整的剖析闭环。示例中的表名、字段名、阈值和结果均为教学假设，实际执行时应替换为真实环境中的对象与参数。

一、先抽样，再决定剖析范围

设备主数据往往有几十万到上百万行，直接全表扫描既慢又容易掩盖局部问题。抽样不是随便取几行，而是按剖析目的选择策略：

  • 随机抽样：用于估计整体字段完整率、唯一率等全局指标，例如 ORDER BY RAND() LIMIT 5000（MySQL 语法，示例）。
  • 分层抽样：按设备类别、工厂、来源系统分层，每层各取一定比例，避免某类设备被整体忽略。
  • 定向抽样：专门抽取最近 30 天新增或修改的记录，用于发现新录入数据的质量问题。

抽样后先做一次“数据体检”：统计行数、主键重复数、关键字段空值数。例如：

SELECT COUNT(*) AS total_rows,
       COUNT(DISTINCT EQUIP_ID) AS distinct_id,
       SUM(CASE WHEN EQUIP_NAME IS NULL OR TRIM(EQUIP_NAME)='' THEN 1 ELSE 0 END) AS name_null_cnt,
       SUM(CASE WHEN LOCATION_ID IS NULL THEN 1 ELSE 0 END) AS loc_null_cnt
FROM EQ_MASTER_SAMPLE;

若 total_rows=5000、distinct_id=4987，说明样本内已有 13 条主键重复；name_null_cnt=210 表示设备名称缺失率约 4.2%。这些数字就是后续质量报告的第一批证据。

二、SQL profiling：把字段“画像”出来

Profiling 的核心是对每个字段计算一组统计量：非空率、唯一率、最大/最小长度、枚举值分布、格式匹配率。以设备名称 EQUIP_NAME 为例：

SELECT COUNT(*) AS total,
       COUNT(EQUIP_NAME) AS non_null,
       COUNT(DISTINCT EQUIP_NAME) AS distinct_val,
       MIN(LENGTH(EQUIP_NAME)) AS min_len,
       MAX(LENGTH(EQUIP_NAME)) AS max_len,
       AVG(LENGTH(EQUIP_NAME)) AS avg_len
FROM EQ_MASTER_SAMPLE;

假设结果为：total=5000、non_null=4790、distinct_val=4310、min_len=2、max_len=120、avg_len=18.6。这里有两个信号：一是非空率 95.8%，二是最大长度 120 远超平均值，说明存在异常长名称，可能是把描述、备注甚至整段说明误填进了名称字段。Profiling 的价值就在于把“感觉有问题”变成“长度分布右偏、存在离群点”。

三、字段分布分析：看集中度与枚举合理性

对分类字段，分布分析比均值更有意义。例如设备状态 STATUS 的分布：

SELECT STATUS, COUNT(*) AS cnt,
       ROUND(COUNT(*)*100.0/SUM(COUNT(*)) OVER(),2) AS pct
FROM EQ_MASTER_SAMPLE
GROUP BY STATUS
ORDER BY cnt DESC;

假设输出为：RUNNING 3120 (62.4%)、STOPPED 980 (19.6%)、MAINT 610 (12.2%)、UNKNOWN 210 (4.2%)、NULL 80 (1.6%)。这里 UNKNOWN 与 NULL 合计 5.8%，属于需要跟进的“状态不明”设备；如果业务规则规定状态只能取 RUNNING/STOPPED/MAINT 三值，那么 UNKNOWN 和 NULL 就是明确的脏数据。对设备类别、工厂代码等字段同样处理，可以快速发现“某工厂代码出现 37 种取值，但标准字典只有 12 种”这类字典外值问题。

四、异常值检测：规则 + 统计双通道

异常值检测建议同时使用规则通道和统计通道。规则通道针对明确约束，例如：设备编号必须匹配 ^EQ[0-9]{8}$；投运日期不得晚于当前日期；功率字段必须大于 0。统计通道则用分位数和 IQR 识别离群点：

-- 计算功率字段 P_RATING 的 Q1、Q3 与 IQR 边界
SELECT
  MAX(CASE WHEN pct <= 0.25 THEN P_RATING END) AS q1,
  MAX(CASE WHEN pct <= 0.75 THEN P_RATING END) AS q3
FROM (
  SELECT P_RATING,
         PERCENT_RANK() OVER (ORDER BY P_RATING) AS pct
  FROM EQ_MASTER_SAMPLE
  WHERE P_RATING IS NOT NULL
) t;

假设 Q1=15、Q3=75，则 IQR=60，上界为 75+1.5×60=165，下界为 15-1.5×60=-75。任何 P_RATING>165 的记录都进入异常清单。若某条记录功率为 9800，而设备类别是“小型水泵”，则几乎可以判定为录入错误或单位混淆（kW 与 W 混用）。异常值不一定都要删除，但必须逐条给出判定结论：错误、合法极端值、待确认。

五、质量报告生成：把发现变成可执行清单

质量报告不是把查询结果堆在一起，而是按“问题—证据—影响—建议”组织。下面给出一份可直接参考的报告结构示例（字段与数值均为教学假设）：

问题编号字段问题类型证据（样本）影响建议动作
Q-01EQUIP_ID主键重复13 条重复，涉及 6 组关联工单时可能串号按创建时间保留最早记录，其余合并
Q-02EQUIP_NAME缺失 + 超长缺失 210 条；最大长度 120检索与报表不可用补录；超长记录截断并回写备注字段
Q-03STATUS字典外值UNKNOWN 210、NULL 80状态统计失真按最近工单状态回填，无法判定则标记待确认
Q-04P_RATING统计离群17 条超出上界 165容量分析偏差逐条核对单位与量纲，修正后复算

报告生成后，建议把每条问题对应的 SQL 查询一并存档，形成“问题—查询—结果”的可追溯链。这样下一次剖析时，只需替换样本表名或时间范围，就能快速复现并对比质量趋势。

设备主数据质量剖析链路（示例） EQ_MASTER 全表 约 80 万行（假设） 抽样策略 随机 / 分层 / 定向 样本集 5000 行 用于快速剖析 SQL Profiling 非空率 / 唯一率 / 长度 EQUIP_NAME：非空 95.8% 最大长度 120，均值 18.6 → 存在超长异常 字段分布分析 STATUS 枚举分布 RUNNING 62.4% / STOPPED 19.6% MAINT 12.2% / UNKNOWN 4.2% → 字典外值 + 空值 5.8% 异常值检测 规则：编号格式 / 日期范围 统计：IQR 上界 165 P_RATING 离群 17 条 → 单位混淆 / 录入错误 质量报告：问题—证据—影响—建议 Q-01 主键重复 · Q-02 名称缺失/超长 · Q-03 状态字典外值 · Q-04 功率离群 SQL 存档 → 复现 → 对比趋势 → 回填/合并 → 复算验证

整条链路的关键在于：抽样决定剖析的视野，profiling 和分布分析把字段问题量化，异常值检测把离群记录挑出来，质量报告把发现转化为可执行动作。每一步都保留 SQL 和结果快照，下一次剖析就能在同一口径下对比质量变化，而不是每次从零开始“感觉数据有问题”。

← 上一章 📖 返回目录 下一章 →