3. TMS 数据模型概览：运费相关核心表结构与字段定义
在制定运费数据清洗方案之前，必须首先理解 TMS（运输管理系统）中与运费计算直接相关的数据模型。本章将拆解典型 TMS 系统的核心表结构，重点定义里程、费用、单价、重量、体积等关键字段的业务含义与数据类型，为后续异常值检测奠定数据理解基础。
3.1 运费核心表结构关系
典型 TMS 的运费数据通常分布在 4 张核心表中：运输订单表、运单表、运费明细表和基础费率表。下图展示了它们之间的主外键关联与数据流转关系。
数据清洗切入点：运费明细表（freight_detail）是异常值检测的主战场。该表通常包含里程、费用、单价、重量、体积等数值字段，且数据量最大、脏数据最集中。清洗工作应优先从该表展开。
3.2 运费明细表关键字段定义
下表列出了运费明细表中与费用计算直接相关的 5 个核心字段，以及它们的标准数据类型、业务含义和常见异常模式。
| 字段名 | 数据类型 | 业务含义 | 常见异常模式 |
|---|---|---|---|
| mileage | DECIMAL(10,2) | 实际运输里程（公里） | 0 值、负值、超过线路理论里程 1.5 倍、空值 |
| freight_amount | DECIMAL(12,2) | 运费总金额（元） | 0 值、负值、与单价×数量严重不匹配、空值 |
| unit_price | DECIMAL(8,4) | 每吨/每方/每车单价（元） | 0 值、负值、超出合同约定范围 3 倍、空值 |
| weight | DECIMAL(10,3) | 货物毛重（吨） | 0 值、负值、超过车辆核定载重 1.2 倍、空值 |
| volume | DECIMAL(10,3) | 货物体积（立方米） | 0 值、负值、与重量换算密度异常、空值 |
3.3 字段间的业务校验关系
运费数据并非孤立存在，字段之间存在强业务逻辑约束。理解这些约束是设计清洗规则的前提。以下 3 条核心校验关系必须掌握：
- 费用 = 单价 × 计费数量：freight_amount = unit_price × (weight 或 volume 或 mileage)，具体计费方式取决于合同约定（按吨、按方或按车）。
- 里程与线路基准对比：实际 mileage 不应超过该线路标准里程的 150%，也不应低于标准里程的 50%（绕路或虚假里程）。
- 重量与体积密度校验：对于普货，weight 与 volume 的比值（密度）应在 0.1~0.5 吨/立方米之间；超出此范围可能为录入错误或货物类型异常。
清洗规则设计依据：上述 3 条校验关系将直接转化为第 5 章的异常检测规则。例如，当 |freight_amount - unit_price × weight| > 阈值时，标记为“费用计算异常”。
3.4 基础费率表与运单表的辅助字段
除了运费明细表，以下两张表的字段在异常值分析中同样重要：
| 表名 | 字段名 | 数据类型 | 在清洗中的作用 |
|---|---|---|---|
| 基础费率表 | rate_id | INT | 关联运费明细表，获取合同约定单价 |
| standard_unit_price | DECIMAL(8,4) | 作为单价异常检测的基准值 | |
| rate_type | VARCHAR(20) | 计费方式（按吨/按方/按车），决定校验公式 | |
| 运单表 | waybill_id | INT | 主键，关联运费明细 |
| route_standard_mileage | DECIMAL(10,2) | 线路标准里程，用于 mileage 异常检测 | |
| vehicle_max_load | DECIMAL(10,3) | 车辆核定载重（吨），用于 weight 异常检测 |
3.5 数据模型在清洗中的典型查询示例
在实际清洗过程中，我们通常通过 SQL 关联查询来定位异常数据。以下是一个典型的异常检测查询模板：
-- 检测运费明细表中里程与费用不匹配的记录
SELECT
f.freight_id,
f.waybill_id,
f.mileage,
f.freight_amount,
f.unit_price,
f.weight,
w.route_standard_mileage,
r.standard_unit_price,
-- 计算理论费用
ROUND(f.unit_price * f.weight, 2) AS expected_amount,
-- 计算偏差比例
ROUND(ABS(f.freight_amount - f.unit_price * f.weight) / NULLIF(f.unit_price * f.weight, 0) * 100, 2) AS deviation_pct
FROM freight_detail f
JOIN waybill w ON f.waybill_id = w.waybill_id
JOIN rate r ON f.rate_id = r.rate_id
WHERE
-- 里程异常：超过标准里程 1.5 倍或低于 0.5 倍
(f.mileage > w.route_standard_mileage * 1.5 OR f.mileage < w.route_standard_mileage * 0.5)
-- 或费用偏差超过 10%
OR ABS(f.freight_amount - f.unit_price * f.weight) > f.unit_price * f.weight * 0.1
ORDER BY deviation_pct DESC;
注意：上述查询中的 NULLIF 函数用于避免除零错误。在实际清洗脚本中，建议先过滤 unit_price 或 weight 为 0 的记录，再执行偏差计算，否则会导致大量误报。
3.6 字段数据质量基线统计
在进入清洗之前，建议先对运费明细表的 5 个核心字段进行基线统计，以了解数据整体健康状况。以下统计指标应作为清洗前必做的探查步骤：
- 空值率：每个字段的 NULL 记录占比，超过 5% 需重点关注。
- 零值率：mileage、freight_amount、unit_price 的 0 值占比，通常应低于 1%。
- 负值记录数：任何数值字段出现负值均为异常，需逐条核查。
- 极值分布：使用百分位数（P1、P99）识别超出正常业务范围的极端值。
- 字段间相关系数：mileage 与 freight_amount 的 Pearson 相关系数应接近 0.7~0.9，过低说明计费逻辑混乱。
3.7 本章小结
本章完整呈现了 TMS 运费数据模型的核心结构：
- 4 张核心表（运输订单、运单、运费明细、基础费率）通过主外键形成星型模型，运费明细表是清洗主表。
- 5 个关键字段（mileage、freight_amount、unit_price、weight、volume）各自有明确的业务含义和异常模式。
- 3 条业务校验关系（费用=单价×数量、里程与基准对比、重量体积密度校验）是设计清洗规则的数学基础。
- 基线统计（空值率、零值率、极值、相关系数）是清洗前的必要探查步骤。
掌握以上数据模型知识后，第 4 章将正式进入异常值检测方法论的讲解，包括统计阈值法、业务规则法和机器学习法在运费数据中的应用。