数据规范化规则编写

以下为 orders 表列 order_date 的数据规范化规则。目标是确保该字段在不同来源、时区和格式下被一致地解析、转换并存储为标准业务日期，以支持分区、聚合及报表一致性。

一、语义定义
- 含义：order_date 表示订单的业务日期，即订单在销售渠道所在业务时区的日历日期（不含时间）。
- 来源优先级：若存在多个时间字段，取 created_at（或订单创建时间）为主；次选为 checkout_completed_at 或 paid_at。具体字段映射需在源系统字典中明确定义。
- 业务时区：以订单所属店铺或销售渠道的 IANA 时区作为业务时区（例如 "America/Los_Angeles"）。业务时区应由维表（如 dim_store 或 dim_channel）提供，并支持历史变更的生效区间（SCD）。

二、数据类型与存储标准
- 数据类型：DATE（不含时间与时区）。
- 存储格式：仓库内使用原生 DATE 类型；对外交换采用 ISO 8601 字符串 "YYYY-MM-DD"。
- 分区策略：建议以 order_date 分区以优化查询与增量加载。

三、输入接受与解析规则
- 可接受的原始输入：
  - 带时区的时间戳字符串（ISO 8601）："2025-09-26T23:15:00+09:00"、"2025-09-26T23:15:00Z"。
  - 不带时区的时间戳字符串："2025-09-26 23:15:00"（需结合来源时区）。
  - 仅日期字符串："2025-09-26"（优先直接视为业务日期）。
  - Epoch 时间：秒或毫秒（整数或字符串）；通过阈值判断单位（≥10^12 视为毫秒）。
- 字符串解析要求：
  - 支持分隔符 "-" 或 "/"，月份与日期必须两位；年份必须四位。
  - 明确禁止模糊格式（如 "09/10/25" 未声明地区）；遇到模糊格式需拒绝并入隔离区。
- 时区识别优先级：
  1) 原始事件中显式提供的时区/偏移（offset）。
  2) 渠道或店铺时区（维表映射）。
  3) 若均缺失，则拒绝（不可默认为 UTC，避免跨日错误）。

四、归一化转换流程
- 标准流程（伪代码）：
  1) 确定 source_ts（时间戳）与 source_tz（来源时区）。
  2) 若仅为日期字符串，直接作为业务日期；完成校验后入库。
  3) 若为时间戳：
     - 使用 IANA 时区库将 source_ts 标准化为 ZonedDateTime（或等价对象）。
     - 转换到 business_tz（店铺/渠道时区），得到本地业务时间 local_dt。
     - 提取日期部分 date(local_dt) 作为 order_date。
- DST 处理：
  - 使用 IANA 时区数据库进行转换，避免手工偏移。
  - 对于夏令时切换导致的本地时间重叠或缺失，库默认策略即可；结果以本地日历日期为准。
- 边界与舍入：
  - 仅日期粒度；去除时间部分。
  - 不进行四舍五入；跨日界限以业务时区的 00:00 为准。

五、校验与约束
- 非空约束：order_date 应非空。若无法确定，记录入隔离区并保留错误原因；不允许自动填充当前日期。
- 合理范围：
  - 最小日期：配置化（默认 2000-01-01）。
  - 最大日期：不超过当前业务时区的 today + 2 天（考虑迟到数据与时差）；超出则隔离。
- 有效性校验：
  - 闰年与月份天数校验。
  - 日期合法性（无 2025-02-30 等）。
  - 与订单生命周期一致性：order_date 不得晚于 fulfilled_at 或 refunded_at（如有）超过合理阈值；异常标记。
- 一致性校验：
  - 若同时存在 created_at 与 paid_at，order_date 与 created_at 的本地日期差异超过 2 天需告警。

六、异常与缺失处理
- 缺失时区：
  - 若时间戳无时区且渠道时区不可获取：隔离并标记 reason=missing_timezone。
- 仅日期输入：
  - 若来源仅提供日期（无时间），直接规范化为 DATE；无需时区转换。
- 无法解析：
  - 无法解析的格式或非法日期进入隔离区；不作默认纠正。
- 衍生策略（可选、需业务确认）：
  - 若 created_at 缺失但 checkout_completed_at 存在，可按优先级派生；记录 lineage。

七、幂等性与可追溯性
- 幂等：相同源记录在相同维表时区映射下重复处理应产生相同 order_date。
- 维表变更：
  - 渠道/店铺时区变更需具备生效日期；重跑历史分区时基于变更生效区间重算。
- 审计字段：
  - 建议记录 raw_ts、source_tz、business_tz、parse_status、error_code 以便回溯。

八、示例转换
- 通用 SQL（PostgreSQL）示例（created_at 为 timestamptz，business_tz 来自维表）：
  - SELECT (created_at AT TIME ZONE business_tz)::date AS order_date
- BigQuery 示例：
  - SELECT DATE(DATETIME(created_at, business_tz)) AS order_date
- Spark（Scala/PySpark）示例（使用 IANA 时区）：
  - withColumn("order_date", to_date(from_utc_timestamp(col("created_at_utc"), col("business_tz"))))

九、数据质量监控
- 指标：
  - 解析失败率、缺失时区比例、未来日期比例、跨日异常比例。
- 规则执行点：
  - 在入湖/入仓前的标准化层（Staging→Curated）。
- 处置策略：
  - 隔离表 orders_quarantine，含原始值与错误原因；定期修复回填。

十、输出标准
- 最终存储：orders.order_date 为 DATE，满足上述语义与转换规则。
- 对外接口或导出：使用 "YYYY-MM-DD" 字符串，明确时区不可用且不需要。

以下为表 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 的一致化与可比性。建议在管道的标准化层统一执行，并保留原始值以支持追溯与精确匹配。