AI 数据清洗模板、同义词与行统计表.md 23 KB

AI 数据清洗模板、同义词与行统计表

本文引用的文件

  • ai_clean.sql
  • ai_clean_field_short_names.sql
  • ai_clean_row_stats.sql
  • ai_clean_synonym_init.sql

目录

  1. 引言
  2. 项目结构
  3. 核心表模型
  4. 架构总览
  5. 详细组件分析
  6. 依赖关系分析
  7. 清洗流程数据流
  8. 性能优化与幂等设计
  9. 典型 SQL 示例
  10. 故障排查指南
  11. 结论

引言

本设计文档聚焦 AI 数据清洗链路中的数据库层,围绕以下目标展开:

  • 说明 ai_clean_template(AI 清洗模板)、ai_clean_field(模板字段)、ai_clean_rule(清洗规则快照)的建模方式。
  • 说明 ai_clean_field_short_names 脚本对动态列名统一规则的演进。
  • 说明 ai_clean_job(清洗任务)、ai_clean_batch(清洗批次)的行级统计字段。
  • 说明 ai_clean_synonym(同义词库)及其初始化脚本的设计约束。
  • 给出数据来源、模板匹配、同义词替换、字段映射、结果落库的数据流说明。
  • 提供清洗任务查询、统计聚合、模板维护的典型 SQL。
  • 讨论批量清洗的性能优化、重复执行去重与数据回滚方案。

项目结构

本次分析涉及的数据库定义全部位于 sql 目录下,采用“按业务域拆分 SQL 脚本”的组织方式:

  • ai_clean.sql:定义 AI 清洗元数据和运行态主表。
  • ai_clean_field_short_names.sql:迁移 ai_clean_field 的物理列名规则。
  • ai_clean_row_stats.sql:为 job/batch 补充行级统计列。
  • ai_clean_synonym_init.sql:创建并初始化同义词库。

    graph TB
    A["sql/ai_clean.sql<br/>AI 清洗主表"] --> B["ai_clean_template<br/>AI 清洗模板"]
    A --> C["ai_clean_field<br/>模板字段"]
    A --> D["ai_clean_rule<br/>清洗规则快照"]
    A --> E["ai_clean_job<br/>清洗任务"]
    A --> F["ai_clean_batch<br/>清洗批次"]
    A --> G["ai_clean_synonym<br/>同义词库"]
    
    H["sql/ai_clean_field_short_names.sql<br/>字段短名迁移"] --> C
    I["sql/ai_clean_row_stats.sql<br/>行级统计扩展"] --> E
    I --> F
    J["sql/ai_clean_synonym_init.sql<br/>同义词建表+初始化"] --> G
    

图表来源

  • ai_clean.sql:1-200
  • ai_clean_field_short_names.sql:1-16
  • ai_clean_row_stats.sql:1-21
  • ai_clean_synonym_init.sql:1-42

章节来源

  • ai_clean.sql:1-200
  • ai_clean_field_short_names.sql:1-16
  • ai_clean_row_stats.sql:1-21
  • ai_clean_synonym_init.sql:1-42

核心表模型

本节从数据类型、主外键、索引和业务约束角度,梳理 AI 清洗相关表。

模板与字段模型

  • ai_clean_template:AI 清洗模板,替代既有 table_info 在 AI 链路中的作用,避免污染既有匹配器。

    • 主键:id。
    • 唯一键:header_md5,用于同一表头签名直接命中,实现“零模型重放”。
    • 关键业务字段:
    • headers:归一化后的逗号分隔列头。
    • header_line_no:多级表头压平后的表头行号。
    • func_regex:沿用 table_info.func_regex 语义的正则。
    • block_no:单 sheet 多套表头的块序号。
    • struct_ops:结构指令快照 JSON。
    • needs_struct:标记是否需要结构归一;此类模板不参与既有匹配。
    • category/category_l2:业务分类。
    • main_ref_template_id:参考的既有模板 id,用于溯源。
    • match_tier:定档档位 STRONG/WEAK/NONE。
    • status:DRAFT/ONLINE/OFFLINE。
    • ver:版本号。
    • confidence:置信度。
    • 索引:uk_ai_clean_tpl_md5、idx_ai_clean_tpl_status、idx_ai_clean_tpl_category。
  • ai_clean_field:模板字段,既是动态表 DDL 真源,也是前端列表头真源。

    • 主键:id。
    • 外键逻辑:ai_template_id 引用 ai_clean_template.id。
    • 关键业务字段:
    • column_name:物理列名,固定 c0/c1/…,长度 ≤5。
    • field_name_cn:原文件列头中文名,前端展示用。
    • field_name_en:与 column_name 完全一致,已收窄为 varchar(5)。
    • field_type:FieldTypeEnum#name()。
    • required:是否必填。
    • field_len:字段长度。
    • field_sort:显示排序。
    • direction_conf:借贷方向字典 JSON。
    • col_index:原文件列序号。
    • matched:内部证据位,两采样不一致时置 0。
    • 索引:idx_ai_clean_field_tpl。

      erDiagram
      AI_CLEAN_TEMPLATE {
      bigint id PK
      varchar table_name_cn
      varchar table_name_en
      varchar headers
      varchar header_md5 UK
      integer header_line_no
      text func_regex
      integer block_no
      text struct_ops
      integer needs_struct
      varchar category
      varchar category_l2
      integer main_ref_template_id
      varchar match_tier
      varchar status
      integer ver
      numeric confidence
      bigint create_by
      timestamp create_time
      timestamp update_time
      }
      
      AI_CLEAN_FIELD {
      bigint id PK
      bigint ai_template_id FK
      varchar column_name
      varchar field_name_cn
      varchar field_name_en
      varchar field_type
      integer required
      integer field_len
      integer field_sort
      text direction_conf
      integer col_index
      integer matched
      timestamp create_time
      }
      
      AI_CLEAN_TEMPLATE ||--o{ AI_CLEAN_FIELD : "包含"
      

图表来源

  • ai_clean.sql:21-86

章节来源

  • ai_clean.sql:21-86

清洗规则快照

  • ai_clean_rule:扁平枚举 hint 的清洗函数链快照。
    • 主键:id。
    • 外键逻辑:ai_template_id 引用 ai_clean_template.id;ref_template_id 引用既有 table_info.id(出口 A)。
    • 关键字段:
    • col_index:列序号。
    • field_name_en:目标字段英文名。
    • hints:JSON 数组,如 TRIM、UNIT_SCALE:WAN 等枚举 hint。
    • ver:规则版本。
    • 索引:idx_ai_clean_rule_tpl。

章节来源

  • ai_clean.sql:88-117

清洗任务与批次

  • ai_clean_job:清洗任务记录,承担幂等游标、状态线和成本埋点。

    • 主键:id。
    • 唯一键:(case_id, file_id, sheet_id, block_no, ver),保证同一块只处理一次。
    • 关键字段:
    • batch_id:关联批次。
    • status:AiCleanStatusEnum#name()。
    • exit_code:A/A2/A3/B。
    • matched_by:HEADER_CACHE/SCORE/LLM/NONE。
    • match_tier:STRONG/WEAK/NONE。
    • ref_template_id / ai_template_id:命中既有模板或 AI 模板。
    • header_md5/header_row/confidence:模板命中信息。
    • detail/draft/model_name/token_in/token_out/llm_calls/cost_ms:运行与成本明细。
    • row_total/row_loaded/row_rejected/row_warned/row_unprocessed/fail_reason:行级统计与失败原因。
    • 索引:uk_ai_clean_job、idx_ai_clean_job_status、idx_ai_clean_job_file。
  • ai_clean_batch:融合入口状态机与幂等锚点。

    • 主键:id。
    • 唯一键:(case_id, batch_id)。
    • 关键字段:
    • stage:AiCleanStageEnum#name()。
    • file_count/sheet_total/matched_count/ai_judged_count/existing_hit_count/ai_load_count:进度计数。
    • loaded_rows/rejected_rows/unprocessed_rows:批次数目统计。
    • self_built_count/need_review_count/failed_count:自建表、待复核、失败计数。
    • need_review_detail/last_error:失败和复核详情。
    • 索引:uk_ai_clean_batch、idx_ai_clean_batch_stage。

      flowchart TD
      Start(["上传并创建批次"]) --> CreateBatch["插入 ai_clean_batch<br/>stage=JUDGING"]
      CreateBatch --> Judge["判定 sheet 是否命中既有模板"]
      Judge -->|命中| LoadExisting["走既有模板加载"]
      Judge -->|未命中| AiJudge["AI 判定并生成模板"]
      AiJudge --> LoadAI["按出口 B 入库"]
      LoadExisting --> UpdateJob["写入 ai_clean_job<br/>exit_code=A/A2/A3"]
      LoadAI --> UpdateJob
      UpdateJob --> Stats["更新 row_total/loaded/rejected/warned/unprocessed"]
      Stats --> Done["batch.stage=DONE/PARTIAL"]
      

图表来源

  • ai_clean.sql:119-179

章节来源

  • ai_clean.sql:119-179

同义词库

  • ai_clean_synonym:星型词组 alias_word → std_word。
    • 主键:id。
    • 唯一键:(std_word, alias_word)。
    • 关键字段:
    • std_word:标准词。
    • alias_word:别名。
    • remark:备注。
    • status:启用/停用。
    • 索引:uk_ai_clean_synonym。
    • 业务约束:
    • 同义词仅进入评分 HeaderScorer 与绑定域 filterBinds,不进入 HeaderFingerprint 指纹,避免影响存量 header_md5。
    • 代码侧要求同一归一化词全表只出现一次,防止归一歧义;不做链式传递。

章节来源

  • ai_clean.sql:181-200
  • ai_clean_synonym_init.sql:1-42

字段短名与行级统计迁移

  • ai_clean_field_short_names.sql:

    • 将历史 ai_clean_field.field_name_en 统一为 column_name。
    • 将 column_name 与 field_name_en 收窄为 varchar(5),因为物理列名为 c0..c9999。
  • ai_clean_row_stats.sql:

    • 为 ai_clean_job 新增 row_rejected、row_warned、row_unprocessed。
    • 为 ai_clean_batch 新增 loaded_rows、rejected_rows、unprocessed_rows、self_built_count。

章节来源

  • ai_clean_field_short_names.sql:1-16
  • ai_clean_row_stats.sql:1-21

架构总览

AI 清洗链路在数据库层面的职责划分如下:

  • ai_clean_template:承载 AI 生成的模板元数据,与既有 table_info 解耦。
  • ai_clean_field:定义动态表列、类型、排序及前端展示名。
  • ai_clean_rule:存储清洗函数链的枚举 hint 快照,避免开放任意类反序列化风险。
  • ai_clean_job:记录每次清洗任务的输入输出、模板命中、成本和行级统计。
  • ai_clean_batch:封装上传批次的全局进度、成功/失败计数与幂等。
  • ai_clean_synonym:支撑表头评分与绑定域的标准化,不影响模板指纹。

    graph LR
    Upload["上传文件"] --> Batch["ai_clean_batch"]
    Batch --> Job["ai_clean_job"]
    Job --> Template["ai_clean_template"]
    Job --> Rule["ai_clean_rule"]
    Template --> Field["ai_clean_field"]
    Job --> Synonym["ai_clean_synonym"]
    

图表来源

  • ai_clean.sql:21-200

详细组件分析

ai_clean_template:模板定义与指纹

  • 模板通过 header_md5 实现零模型重放:同一张表头签名第二次上传时直接命中,不再调用大模型。
  • needs_struct=1 的模板必须经过结构归一才能理解表头,且永不参与既有匹配,避免侵入现有链路。
  • category/category_l2/main_ref_template_id 支持业务分类与溯源。
  • match_tier 区分 STRONG/WEAK/NONE,配合 matched_by 记录 HEADER_CACHE/SCORE/LLM/NONE。

章节来源

  • ai_clean.sql:21-59

ai_clean_field:字段映射与物理列名

  • column_name 固定为 c0/c1/…,field_name_en 与其完全一致,便于动态表 DDL 生成与后端字段解析。
  • field_name_cn 保留原中文列头,用于前端展示。
  • field_type/field_len 决定 DuckDB 动态表列的类型与长度。
  • direction_conf 沿用既有借贷方向字典格式。
  • matched 作为 AI 内部证据位,两采样不一致时置 0,不作为打分证据。

    classDiagram
    class AiCleanField {
        +long id
        +long aiTemplateId
        +string columnName
        +string fieldNameCn
        +string fieldNameEn
        +string fieldType
        +int required
        +int fieldLen
        +int fieldSort
        +String directionConf
        +int colIndex
        +int matched
        +timestamp createTime
    }
    

图表来源

  • ai_clean.sql:61-86

章节来源

  • ai_clean.sql:61-86
  • ai_clean_field_short_names.sql:1-16

ai_clean_rule:清洗规则快照

  • hints 使用扁平枚举 JSON 数组,避免 Jackson @JsonTypeInfo 的多态反序列化风险。
  • 运行时由 AiRuleCompiler 将 hint 还原为 Fun 对象,再交给 FunProcess 执行。
  • ref_template_id 用于出口 A 命中既有模板时的溯源。

章节来源

  • ai_clean.sql:88-117

ai_clean_job:清洗任务与行级统计

  • 唯一键 (case_id, file_id, sheet_id, block_no, ver) 是幂等锚点:轮询器据此决定是否接管,重跑做 UPSERT,不会累积重复行。
  • status 独立于 FileSheetStateEnum,遵循 AiParseStatusEnum 的设计思路。
  • detail 保存 VerifyReport JSON,draft 保存模型原始草稿,便于回溯 prompt 版本。
  • row_total/row_loaded/row_rejected/row_warned/row_unprocessed 构成行级统计闭环。

章节来源

  • ai_clean.sql:119-179
  • ai_clean_row_stats.sql:1-21

ai_clean_batch:批次状态机

  • 一个上传批次一行,point “智能”后接口立即返回 batch_id,后台推进 JUDGING → CLEANING_EXISTING → LOADING_AI → DONE/PARTIAL。
  • 唯一键 (case_id, batch_id) 使重复点击按钮返回同一批次而非重跑。
  • loaded_rows/rejected_rows/unprocessed_rows/self_built_count/need_review_count/failed_count 提供全局统计。

章节来源

  • ai_clean.sql:153-179
  • ai_clean_row_stats.sql:1-21

ai_clean_synonym:同义词词库

  • 星型词组 alias_word → std_word,支持多条别名映射到同一个标准词。
  • 不进入 HeaderFingerprint 指纹,避免影响存量 header_md5。
  • 初始化脚本内置 17 条词条,覆盖户名、金额、日期、账号、电话、证件等常见领域。

章节来源

  • ai_clean.sql:181-200
  • ai_clean_synonym_init.sql:1-42

依赖关系分析

  • ai_clean_field 依赖 ai_clean_template:字段归属模板。
  • ai_clean_rule 依赖 ai_clean_template:规则属于模板快照;也可引用既有模板 ref_template_id。
  • ai_clean_job 依赖 ai_clean_template 与 ai_clean_rule:记录命中的模板、规则及模板元数据。
  • ai_clean_job 与 ai_clean_batch:job.batch_id 指向批次,批次汇总各 job 的行级统计。
  • ai_clean_synonym 不直接被表间外键约束,但被评分与绑定域逻辑消费。

    graph TB
    Template["ai_clean_template"] --> Field["ai_clean_field"]
    Template --> Rule["ai_clean_rule"]
    Template --> Job["ai_clean_job"]
    Rule --> Job
    Batch["ai_clean_batch"] --> Job
    Synonym["ai_clean_synonym"] -.-> Job
    

图表来源

  • ai_clean.sql:21-200

章节来源

  • ai_clean.sql:21-200

清洗流程数据流

结合表结构与注释,可抽象出以下数据流阶段:

sequenceDiagram
    participant Client as "客户端"
    participant Service as "清洗服务"
    participant DB as "数据库"
    participant Model as "AI 模型"

    Client->>Service: 提交文件并选择智能清洗
    Service->>DB: 创建 ai_clean_batch(stage=JUDGING)
    Service->>DB: 读取 ai_clean_synonym
    Service->>Service: 表头评分与绑定域归一
    Service->>DB: 按 header_md5 查询 ai_clean_template
    alt 命中既有模板
        Service->>DB: 读取 ai_clean_field/ai_clean_rule
        Service->>DB: 写入 ai_clean_job(exit_code=A/A2/A3)
    else 未命中
        Service->>Model: 请求 AI 判定与生成模板
        Model-->>Service: 返回模板、字段、规则
        Service->>DB: 插入 ai_clean_template/field/rule
        Service->>DB: 写入 ai_clean_job(exit_code=B)
    end
    Service->>DB: 更新 row_total/loaded/rejected/warned/unprocessed
    Service->>DB: 更新 ai_clean_batch(stage=DONE/PARTIAL)
    Service-->>Client: 返回 batch_id 与状态

图表来源

  • ai_clean.sql:119-200
  • ai_clean_synonym_init.sql:1-42

性能优化与幂等设计

幂等与去重

  • ai_clean_job 的唯一键 (case_id, file_id, sheet_id, block_no, ver) 保证同一块只处理一次。
  • ai_clean_batch 的唯一键 (case_id, batch_id) 让重复点击返回同一批次,避免重复执行。
  • ai_clean_template 的唯一键 header_md5 支持“零模型重放”,相同表头直接复用模板,降低模型调用成本。

批量清洗优化建议

  • 分批插入:按 sheet/block 维度分批写入 ai_clean_job,减少单事务大小。
  • 预取同义词:启动时加载 ai_clean_synonym 到内存缓存,避免每行查询。
  • 批量更新批次统计:在 job 完成回调中批量汇总 loaded_rows/rejected_rows/unprocessed_rows。
  • 利用索引:基于 idx_ai_clean_job_status、idx_ai_clean_job_file、idx_ai_clean_batch_stage 进行任务调度与进度查询。
  • 控制重试窗口:对失败 job 设置重试上限与退避策略,避免无限重试导致统计失真。

数据回滚方案

  • 以 batch_id 为边界:删除 ai_clean_batch 对应批次时,级联删除其下 ai_clean_job。
  • 模板与字段清理:若出口 B 模板尚未上线(status=DRAFT),可安全删除 ai_clean_template 及其 ai_clean_field、ai_clean_rule。
  • 业务表清理:对于出口 B 产生的动态表 aiclean{id},应在删除模板前确认无外部依赖,否则应改为软下线(status=OFFLINE)。
  • 建议回滚顺序:ai_clean_job → ai_clean_batch → ai_clean_rule → ai_clean_field → ai_clean_template。

[本节为通用实践建议,不直接分析具体文件]

典型 SQL 示例

清洗任务查询

  • 查询某案件下所有清洗任务状态:

    SELECT
    id,
    case_id,
    file_id,
    sheet_id,
    block_no,
    ver,
    status,
    exit_code,
    matched_by,
    match_tier,
    row_total,
    row_loaded,
    row_rejected,
    row_warned,
    row_unprocessed,
    fail_reason,
    create_time,
    update_time
    FROM ai_clean_job
    WHERE case_id = ?
    ORDER BY create_time DESC;
    
  • 查询某个文件的所有清洗任务:

    SELECT *
    FROM ai_clean_job
    WHERE file_id = ?
    ORDER BY create_time DESC;
    

章节来源

  • ai_clean.sql:119-179

统计聚合

  • 按任务状态聚合行数:

    SELECT
    status,
    count(*) AS job_count,
    sum(row_total)   AS total_rows,
    sum(row_loaded)  AS loaded_rows,
    sum(row_rejected) AS rejected_rows,
    sum(row_warned)  AS warned_rows,
    sum(row_unprocessed) AS unprocessed_rows
    FROM ai_clean_job
    GROUP BY status;
    
  • 按批次聚合统计:

    SELECT
    stage,
    file_count,
    sheet_total,
    matched_count,
    ai_judged_count,
    existing_hit_count,
    ai_load_count,
    loaded_rows,
    rejected_rows,
    unprocessed_rows,
    self_built_count,
    need_review_count,
    failed_count
    FROM ai_clean_batch
    WHERE case_id = ?
    ORDER BY create_time DESC;
    

章节来源

  • ai_clean.sql:119-179
  • ai_clean_row_stats.sql:1-21

模板维护

  • 查询在线模板:

    SELECT id, table_name_cn, table_name_en, headers, header_md5,
         status, ver, confidence, category, category_l2
    FROM ai_clean_template
    WHERE status = 'ONLINE'
    ORDER BY update_time DESC;
    
  • 根据表头指纹查找模板:

    SELECT id, table_name_cn, table_name_en, status, ver, confidence
    FROM ai_clean_template
    WHERE header_md5 = ?;
    
  • 查询模板字段:

    SELECT
    id,
    column_name,
    field_name_cn,
    field_name_en,
    field_type,
    required,
    field_len,
    field_sort,
    col_index,
    matched
    FROM ai_clean_field
    WHERE ai_template_id = ?
    ORDER BY field_sort ASC;
    
  • 查询模板规则快照:

    SELECT id, col_index, field_name_en, hints, ref_template_id, ver
    FROM ai_clean_rule
    WHERE ai_template_id = ?
    ORDER BY col_index ASC;
    

章节来源

  • ai_clean.sql:21-117

同义词维护

  • 查询启用的同义词:

    SELECT std_word, alias_word, remark
    FROM ai_clean_synonym
    WHERE status = 1
    ORDER BY std_word, alias_word;
    
  • 新增同义词(忽略冲突):

    INSERT INTO ai_clean_synonym (std_word, alias_word, remark, status)
    VALUES (?, ?, ?, 1)
    ON CONFLICT (std_word, alias_word) DO NOTHING;
    
  • 停用同义词:

    UPDATE ai_clean_synonym
    SET status = 0, update_time = current_timestamp
    WHERE std_word = ? AND alias_word = ?;
    

章节来源

  • ai_clean.sql:181-200
  • ai_clean_synonym_init.sql:1-42

故障排查指南

常见问题定位

  • 模板重复命中异常:检查 header_md5 是否唯一,确认是否存在不同模板具有相同表头指纹。
  • 动态列名不一致:核对 ai_clean_field.column_name 与 field_name_en 是否一致,以及是否为 varchar(5)。
  • 行级统计缺失:确认 ai_clean_row_stats.sql 是否已执行,确保 row_rejected/row_warned/row_unprocessed 存在。
  • 同义词未生效:确认 ai_clean_synonym.status=1,且未被误放入指纹计算路径。
  • 任务重复执行:检查 ai_clean_job 唯一键是否完整传入 (case_id, file_id, sheet_id, block_no, ver)。

章节来源

  • ai_clean.sql:21-200
  • ai_clean_field_short_names.sql:1-16
  • ai_clean_row_stats.sql:1-21
  • ai_clean_synonym_init.sql:1-42

结论

AI 数据清洗链路通过独立的模板、字段、规则、任务、批次与同义词表,实现了与既有匹配器的解耦、可观测的任务状态、可追溯的模型调用成本,以及可扩展的清洗规则。header_md5 的零模型重放、job 幂等唯一键、batch 幂等唯一键共同保障批量清洗的可重复性与稳定性。同义词库在不破坏指纹的前提下提升表头评分准确率。建议在部署时优先执行 ai_clean.sql,再按需执行字段短名与行级统计迁移脚本,并维护 ai_clean_synonym_init.sql 的内置词条。