SQL视图实现非规范化宽表到逻辑模型的映射

首先需要明确一个前提：视图本身无法解决数据冗余和更新异常问题，这属于物理表结构的范畴。但如果目标是向BI系统、API接口或下游ETL流程提供一个逻辑上符合第三范式（3NF）的访问层，那么视图确实是一种快速见效的过渡方案——无需重构物理表，就能让查询逻辑看起来符合规范。

为什么不能在视图里用 JOIN 拼出“假规范化”模型？

最常见的误区是编写一个视图，将宽表字段拆分为多张逻辑子表，再通过 LEFT JOIN 模拟外键关系。例如从 orders_wide 中 SELECT 出 customer_id、customer_name、product_id、product_name，然后 JOIN 回自身进行“去重”。结果往往适得其反：

• 行数急剧膨胀：每条原始订单行都携带完整的客户和商品信息，JOIN 后仍会产生笛卡尔积式的数据膨胀，无法真正实现实体分离。
• NULL 语义模糊：宽表中某个字段为空时，视图无法准确判断是“暂时缺失值”还是“该实体根本不存在”。
• BI 工具无法识别维度关系：Power BI 或 Tableau 接收到这种视图后，仍会将其视为普通宽表，维度关系建模完全失效。

正确做法：用 UNION ALL 加标识字段构建逻辑维度视图

核心思路非常简单：放弃“一张视图模拟多张表”的错误想法，改为为每个逻辑实体单独创建视图，并使用固定字段标明来源和粒度。例如原始宽表 sales_flat 中包含 order_id、cust_name、cust_city、prod_sku、prod_category 等字段，可以按以下方式拆分：

先创建客户逻辑视图：

CREATE VIEW dim_customer AS
SELECT DISTINCT 
  MD5(cust_name, cust_city) AS customer_key,
  cust_name AS customer_name,
  cust_city AS city,
  'sales_flat' AS source_system,
  CURRENT_TIMESTAMP AS loaded_at
FROM sales_flat
WHERE cust_name IS NOT NULL;

再创建商品逻辑视图：

CREATE VIEW dim_product AS
SELECT DISTINCT 
  MD5(prod_sku) AS product_key,
  prod_sku,
  prod_category,
  'sales_flat' AS source_system,
  CURRENT_TIMESTAMP AS loaded_at
FROM sales_flat
WHERE prod_sku IS NOT NULL;

这里有几个关键要点：

• DISTINCT 配合确定性哈希是标准做法：使用 MD5() 生成稳定主键，避免后续数据变更导致键值漂移。
• 明确标注来源和加载时间：添加 source_system 和 loaded_at 字段，让下游清楚这是派生逻辑表，而非原始源系统。
• WHERE 过滤空值：防止 NULL 参与哈希计算或污染维度的唯一性。

明细事实视图如何关联这些逻辑维度？

不要在事实视图中编写 JOIN dim_customer ON ...——这会导致视图依赖外部对象，破坏可移植性。正确的做法是在宽表内直接反查并映射：

CREATE VIEW fact_sales AS
SELECT 
  order_id,
  MD5(cust_name, cust_city) AS customer_key,
  MD5(prod_sku) AS product_key,
  sale_amount,
  order_date,
  'sales_flat' AS source_system
FROM sales_flat
WHERE cust_name IS NOT NULL AND prod_sku IS NOT NULL;

这种做法的优势非常明显：

• 所有逻辑都在单条 SQL 内完成，不依赖其他视图或函数（除非数据库支持内联标量函数）。
• BI 工具导入时，customer_key 和 product_key 会被识别为字符串型维度字段，可以直接拖拽建模。
• 未来如果物理表结构发生变化（例如新增 cust_region 字段），只需扩展 dim_customer 视图，fact_sales 完全不受影响。

字段类型与 NULL 处理最容易被忽略的细节

还有一个容易被忽略的细节：宽表中常存在混合类型字段，例如 status 是 TINYINT 类型但实际存储 0/1/NULL，直接暴露给 BI 会导致筛选失效：

• 数值型 ID 字段：如果 cust_id 原为 DECIMAL(18,0)，BI 工具可能会自动归类为“度量”，需要在视图中用 CAST(cust_id AS CHAR) 强制转换为字符串。
• 布尔类字段：必须显式转义，例如 CASE WHEN is_active = 1 THEN 'Y' ELSE 'N' END AS is_active_flag，避免保留 TINYINT(1) 这种类型。
• 逻辑键字段：所有用于 JOIN 的键（如 customer_key）必须定义为 NOT NULL，否则 Power BI 会跳过关系自动检测。

话说回来，真正困难的不在于编写这些视图，而是让团队接受它们只是过渡层，不能替代规范化设计方案。一旦业务稳定下来，读写比例转向分析侧，就需要将逻辑视图沉淀为物理维度表——否则每次查询都在重复计算哈希、去重和类型转换，性能上终究不是长久之计。