数据规范化规则编写

以下为表 kpi_daily 的列 amount 的数据规范化规则，以确保跨源数据一致、可比和可审计。规则适用于货币型、比例型及其他数值型 KPI。

一、字段定义与目标标准

• 语义：amount 表示每日 KPI 的数值结果，支持货币金额、比例（比率/百分比）及其他计数/度量型数值。
• 数据类型：DECIMAL/NUMERIC，建议精度 scale 6（例如 DECIMAL(20,6)），禁止使用浮点类型（FLOAT/DOUBLE）。
• 存储单位归一：
  • 货币型：统一存储为人民币 CNY，单位“元”。
  • 比例型：统一存储为[0,1]区间的实数（即 12.34% 存 0.1234）。
  • 非货币数值型：统一存储为业务定义的基础单位（如“次”“件”“人”“秒”等），在入库前完成单位换算。

二、输入清洗与标准化流程

1. 原始值解析

   • 去除空白、千分位分隔符（如“,”）、货币符号（如“$”“¥”“USD”）、百分号“%”。
   • 识别负值：支持符号“−”和括号记负（如“(123.45)”→ -123.45）。
   • 非数值字符（除上面合法符号外）视为不可解析，记录错误并拒绝入库。

2. 货币换算

   • 若原始币种非 CNY，按入库日期的指定汇率版本进行换算：amount_cny = amount_src × fx_rate(src→CNY, rate_date, version)。
   • 汇率来源与版本必须可追溯（例如 fx_rates 表含 source、version、effective_date）。
   • 汇率精度：至少 DECIMAL(20,10)，换算后在最终入库时统一为 DECIMAL(20,6)。
   • 汇率选择：每日收盘或固定时点（建议 Asia/Shanghai 当日 23:59 的官方中间价），必须与 kpi_daily 的统计日一致。

3. 比例/百分数处理

   • 解析到小数形式：输入“12.34%”→ 0.1234；“0.1234”视为 12.34%。
   • 禁止将比例以“百分值”存储（如 12.34）；统一为小数 0.1234。

4. 单位换算（非货币类）

   • 所有输入数据在入库前转换到业务定义的唯一基础单位；换算系数来源需可追溯（如 unit_conversion 表）。
   • 禁止入库时混存不同单位导致不可比。

5. 聚合与去重

   • 对同一主键维度（例如 kpi_id, stat_date, 其他维度键）进行幂等聚合：
     • 加性度量（如金额、数量）：SUM。
     • Snapshot/库存类度量：选择最后快照或业务定义的聚合（如 MAX/MIN），不得使用 SUM。
   • 重复记录在入库阶段需按度量类型规则去重聚合，避免行级重复导致指标膨胀。

6. 舍入与精度

   • 计算链路中保持高精度（≥6 位小数），仅在最终入库时统一舍入为 scale=6。
   • 舍入模式：四舍五入（HALF_UP）。货币型对外展示可再格式化为两位小数，但表内仍存 6 位以保留可审计性。
   • 禁止在中间步骤重复舍入，避免累计误差。

三、取值规则与边界

• 合法范围：
  • 货币型：建议 [-1e12, 1e12]（根据业务上限可调整）。
  • 比例型：必须在 [0,1]；超出则拒绝并告警。
  • 非货币型：不得为 NaN/Inf；上限按业务维度设定并校验（如数量不超过 1e12）。
• 负值处理：
  • 允许负值仅限调整/退款/冲销类 KPI；加性非负 KPI（如“新增用户数”）禁止负值。
  • 需基于 KPI 元数据（kpi_type）进行校验：non_negative=true 时，amount < 0 直接拒绝。
• 缺失值：
  • 区分缺失与零：未知或不可计算时存 NULL，业务定义为 0 的才存 0。
  • 禁止以 0 替代缺失，避免统计偏差。

四、时间与口径

• 统计日边界：统一采用 Asia/Shanghai 时区的自然日 [00:00, 23:59:59]。
• 口径一致性：入库口径（含去重、汇率版本、单位换算规则）需与当日统计口径一致，禁止跨日混用。
• 回补与重算：回补历史数据时，使用对应历史日的汇率版本和单位规则，确保可重现性。

五、校验与拒绝策略

• 语法校验：不可解析的数值直接拒绝入库，记录错误码 invalid_number_format。
• 业务校验：
  • 比例型超界（<0 或 >1）拒绝，错误码 ratio_out_of_range。
  • 非负 KPI 出现负值拒绝，错误码 negative_not_allowed。
  • 极值/异常波动：与近 7 日中位数相比偏差 >10 倍标记异常（quality_flag=outlier），可入库但需告警。
• 一致性校验：币种缺失且金额含币种符号不一致时拒绝（currency_inconsistent）。

六、示例约束与参考实现

1. 数据库约束（PostgreSQL 示例）

• 列类型： ALTER TABLE kpi_daily ALTER COLUMN amount TYPE NUMERIC(20,6);

• 合法范围检查（示例值，可按业务调整）： ALTER TABLE kpi_daily ADD CONSTRAINT chk_amount_range CHECK (amount IS NULL OR amount BETWEEN -1000000000000 AND 1000000000000);

• 比例型检查建议通过触发器/ETL校验（因同列存多类型不便用静态 CHECK），依据 kpi_type 元数据执行。

2. 规范化 UDF（Spark SQL/PySpark 参考）

• Python 伪实现（用于 ETL 前置清洗与规范化）： from decimal import Decimal, ROUND_HALF_UP

  def normalize_amount(raw_value: str, value_kind: str, # 'currency' | 'ratio' | 'count' currency: str = 'CNY', # 原始币种 fx_rate: Decimal = Decimal('1'), # src->CNY 当日汇率 unit_factor: Decimal = Decimal('1')) -> Decimal: if raw_value is None or str(raw_value).strip() == '': return None

  s = str(raw_value).strip()
  # 括号记负
  negative = s.startswith('(') and s.endswith(')')
  s = s.replace('(', '').replace(')', '')
  
  # 去符号与分隔
  for ch in [',', '¥', '$', 'USD', 'CNY', 'EUR', ' ']:
      s = s.replace(ch, '')
  percent = s.endswith('%')
  if percent:
      s = s[:-1]
  
  # 解析数值
  try:
      v = Decimal(s)
  except:
      raise ValueError('invalid_number_format')
  
  if negative:
      v = -v
  
  if value_kind == 'ratio':
      # 百分号输入转小数
      v = v / Decimal('100') if percent else v
      if v < Decimal('0') or v > Decimal('1'):
          raise ValueError('ratio_out_of_range')
  elif value_kind == 'currency':
      # 汇率换算到 CNY
      v = (v * fx_rate)
  elif value_kind == 'count':
      # 单位换算
      v = (v * unit_factor)
  else:
      raise ValueError('unsupported_value_kind')
  
  # 最终入库舍入到 6 位
  return v.quantize(Decimal('0.000001'), rounding=ROUND_HALF_UP)
  

七、审计与溯源

• 记录来源系统、原始值、解析前后值、币种、汇率版本、单位换算版本、规范化时间与作业 ID，保证可重现。
• 对异常拒绝与告警保留错误码和样本，以便回溯修复。

八、实施注意事项

• 要求配套的 KPI 元数据（kpi_type、allow_negative、is_ratio、unit_base 等）以驱动规则选择。
• 在批处理与流式处理保持同一规范化逻辑与版本，确保口径一致。
• 对历史数据重算需冻结汇率与单位版本，避免随时间变动导致口径漂移。

以上规则旨在为 amount 列提供统一、可审计、可重现的规范化标准。根据具体 KPI 类型与业务边界可进一步细化数值范围与异常检测阈值。

以下为 dim_cust.cust_name 的数据规范化规则。目标是实现一致、可比、可检索的客户名称表示，用于维度建模和主数据管理，同时尽量保留对业务识别有意义的信息。

一、适用范围与目标

• 适用对象：个人客户姓名、企业/机构名称。
• 目标：消除编码差异、空白与标点噪声、大小写差异、常见称谓与冗余信息，统一合法实体后缀，提高匹配与去重效果。
• 原始值建议保留至原始字段（如 src_cust_name），cust_name 存储规范化后的显示名。

二、字段约束

• 编码与标准：UTF-8；Unicode 归一化：NFKC。
• 长度：1–200 字符（超过则截断并记录审计日志）。
• 允许字符集合：
  • 字母类：Unicode Letter 类（L*，含中日韩）。
  • 数字类：Nd。
  • 空白：空格（统一为 ASCII 空格）。
  • 标点：- ' & . , ( ) / · 以及中文对应全角标点（规范化后保留为半角）。
• 禁止字符：控制符、表情符号/图标（Emoji、Symbol）、不可见分隔符（零宽字符）。统一移除。
• 非空性：cust_name 不可为 NULL；空或全被清洗后为空时，设为 "UNKNOWN" 并标记数据质量标志。

三、通用规范化步骤（顺序执行）

1. 编码与归一化

• 统一为 UTF-8。
• Unicode 归一化 NFKC（统一全角/半角、兼容形式）。

2. 空白与控制字符处理

• 移除所有控制字符（\p{Cc}、\p{Cf}）。
• 将制表符、换行等空白统一为单个空格。
• 去除首尾空格。
• 折叠内部连续空格为单个空格。

3. 标点统一

• 中文全角标点统一为半角：，→ ,；（ → (；） → )；／ → /；—/－/–/— → -。
• 去除重复标点与尾随标点（例如末尾的逗号、句号、括号未闭合的残留）。
• 保留对识别有意义的分隔符：- ' & . , ( ) / ·。

4. 数字与字母规范

• 全角数字统一为半角数字 0–9。
• 拉丁字母统一为大写（A–Z），消除大小写差异；若使用 Postgres，可使用 unaccent 去除拉丁变音符（É→E），提升匹配稳定性。

5. 噪声词清洗（谨慎保留法律实体信息）

• 个人称谓与礼貌用语：移除如 “先生/女士/小姐/太太/老师/博士/经理/总监/董事长/教授/同学/吾友”等位于首尾的称谓。
• 联系信息与杂项：移除括注的联系人或电话，如 “(联系人：张三)”、“联系人:李四”、“Tel:…”。
• 渠道/来源标记：移除明显的来源标签如 “[渠道]”、“<导入>” 等非名称内容。

6. 特殊字符处理

• 保留少数民族姓名中常见分隔点 “·”。
• 移除 Emoji 和符号（例如 ✨, ™, ®），如确属品牌法律名称一部分需通过白名单保留。

7. 语义修剪

• 删除连续重复空格、重复的公司名片式前缀/后缀（如 “公司名称 公司名称”）。
• 统一括号内容的空格与标点样式，例如 “(中国)”、“(上海)” 保留但标准化为半角括号。

四、企业/机构名称的附加规则

• 法律实体后缀标准化（词典驱动）：
  • “有限责任公司” → “有限公司”
  • “股份有限公司” → “股份有限公司”（保持不变）
  • “合伙企业（普通/有限）” → “合伙企业”
  • “个体工商户/个体独资企业” → “个体工商户”
  • “分公司/支公司/分店/分处”统一为“分公司”用于分支标识（位于名称末尾或括注中时）。
• 同义词与英文缩写：
  • “Co., Ltd.”、“Limited”、“Ltd.” → “有限公司”
  • “Inc.” → “股份有限公司”（需结合法律登记数据确认；默认仅英译为“公司”，建议保留原文并在辅助字段存放中文标准化名）。
• 地理/部门信息：
  • 位于括号或末尾的行政区域与部门标记可保留并标准化，如 “(上海)”、“上海分公司”；如影响主名匹配，另存至辅助字段（如 cust_branch）以便分层匹配。
• 品牌与商号：
  • 当存在“品牌（法律主体）”结构，保留括号并统一样式：如 “小米（北京小米有限公司）”。

五、个人姓名的附加规则

• 统一少数民族姓名分隔点：多重分隔点折叠为一个 “·”。
• 仅移除称谓，不改动中文姓名结构；不做姓氏/名字的自动拆分。
• 拉丁姓名统一为大写并保留连字符与撇号（如 O'NEIL、ANNE-MARIE）。

六、质量校验与拒收规则

• 最终长度在 1–200；否则截断并记录审计。
• 仅由噪声被清空后为空则设为 “UNKNOWN” 并写入数据质量标志 dq_cust_name_empty = true。
• 禁止字符检测（Emoji、控制符）计数 >0 时写入数据质量标志 dq_cust_name_illegal = true。
• 若企业名称包含合法实体后缀但被清洗掉，回滚至清洗前并记录异常，防止法律主体信息丢失。

七、实现建议与示例

PySpark（推荐在 ETL 规范化阶段执行）

• 依赖：python unicodedata、正则、可选 unidecode 或自定义变音符映射。
• 词典：LEGAL_SUFFIX_MAP、TITLES_SET。

示例代码（精简版）：

• 该代码仅示意关键步骤，企业后缀与称谓词典需根据业务维护。

from pyspark.sql import functions as F from pyspark.sql.types import StringType import re, unicodedata

TITLES = {"先生","女士","小姐","太太","老师","博士","经理","总监","董事长","教授","同学"} LEGAL_SUFFIX_MAP = { "有限责任公司": "有限公司", "Co., Ltd.": "有限公司", "Ltd.": "有限公司", "Limited": "有限公司", "Inc.": "公司" # 如需更严格，保留原文并在辅助字段映射 }

def normalize_text(s): if s is None: return None # Unicode 归一化 s = unicodedata.normalize("NFKC", s) # 去控制字符与零宽 s = re.sub(r'[\u200B-\u200D\uFEFF]', '', s) # 零宽 s = ''.join(ch for ch in s if unicodedata.category(ch)[0] != 'C') # 空白统一 s = re.sub(r'\s+', ' ', s).strip() # 标点全角转半角与标准化 trans = str.maketrans({'（':'(', '）':')', '，':',', '／':'/', '－':'-', '—':'-', '–':'-'}) s = s.translate(trans) # 尾随标点去除 s = re.sub(r'[.,-&/]+$', '', s).strip() # 拉丁字母大写 s = re.sub(r'([A-Za-z])', lambda m: m.group(1).upper(), s) # 去除联系人等噪声 s = re.sub(r'((联系人|TEL|PHONE)[^)]*)', '', s, flags=re.I).strip() # 称谓清洗（首尾） for t in TITLES: s = re.sub(fr'^(?:{t})\s+', '', s) s = re.sub(fr'\s+{t}$', '', s) # 折叠多空格 s = re.sub(r'\s{2,}', ' ', s) return s or "UNKNOWN"

def normalize_enterprise(s): s = normalize_text(s) if s in (None, "UNKNOWN"): return s # 后缀映射（词典驱动） for k, v in LEGAL_SUFFIX_MAP.items(): s = re.sub(fr'{re.escape(k)}$', v, s, flags=re.I) # “分公司/支公司/分店/分处”统一（位于末尾或括注中） s = re.sub(r'(分公司|支公司|分店|分处)$', '分公司', s) s = re.sub(r'((分公司|支公司|分店|分处))', '(分公司)', s) # 再次清理空白 s = re.sub(r'\s{2,}', ' ', s).strip() return s or "UNKNOWN"

@F.udf(StringType()) def normalize_cust_name(name, cust_type): if cust_type and cust_type.lower() in ('enterprise','company','org','b2b'): return normalize_enterprise(name) return normalize_text(name)

df = df.withColumn("cust_name", normalize_cust_name(F.col("cust_name"), F.col("cust_type")))

SQL（PostgreSQL）要点

• 使用 trim、regexp_replace 处理空白与噪声。
• 使用 unaccent 扩展去除拉丁变音符。
• Unicode NFKC 需在应用层实现或通过外部函数；Postgres 原生不提供。

示例（简化）： -- 需先 CREATE EXTENSION unaccent; UPDATE dim_cust SET cust_name = COALESCE(NULLIF( regexp_replace( -- 多空白折叠 regexp_replace( -- 去联系人噪声 regexp_replace( -- 全角标点到半角（示例） unaccent(trim(cust_name)), '（','('), '）', ')' ), '((联系人|TEL|PHONE)[^)]+)','' ), '\s+',' ' ), ''), 'UNKNOWN');

八、运维与治理

• 建立可维护的词典（称谓、法律实体后缀、英文缩写）与白名单/黑名单。
• 版本化规则，变更需回溯与再处理能力。
• 记录清洗审计（原值、规则版本、清洗后值、原因标签）。
• 与工商/官方注册数据对齐时，以注册名称为最高优先级，规则仅做格式规范，不改变法律主体语义。

以上规则旨在在不丢失法律与业务识别信息的前提下，实现 cust_name 的一致化与可比性。建议在管道的标准化层统一执行，并保留原始值以支持追溯与精确匹配。