一、维度建模核心设计方法论

1.1 账户维度设计（三阶建模法）

核心表结构设计：

-- 主维度表
CREATE TABLE dim_account (
    account_sk INT PRIMARY KEY AUTO_INCREMENT,  -- 代理键
    account_id VARCHAR(32) NOT NULL,            -- 业务键
    open_date DATE,                             -- 开户日期
    status ENUM('ACTIVE','FROZEN','CLOSED'),    -- 账户状态
    product_sk INT,                             -- 产品维度外键
    branch_sk INT                               -- 分支机构维度外键
);

-- 微型维度表（信用评分）
CREATE TABLE micro_credit (
    credit_sk INT PRIMARY KEY,
    score_range VARCHAR(20),                    -- 评分区间
    risk_level VARCHAR(10),                     -- 风险等级
    effective_date DATE                         -- 生效日期
);

设计要点：

1. 使用SCD Type 4处理高频变更属性（如信用评分）
2. 通过代理键（SK）实现与产品、分支机构的解耦
3. 建立有效日期范围索引支持历史状态追溯

业务场景示例：
当客户信用评分从"700-750"变为"750-800"时，只需插入新的micro_credit记录，避免更新主维度表。

---

1.2 客户-家庭关系建模

桥接表设计范式：

CREATE TABLE bridge_account_customer (
    account_sk INT,
    customer_sk INT,
    relationship_type ENUM('PRIMARY','JOINT','GUARDIAN'),
    weight DECIMAL(5,4) CHECK (weight BETWEEN 0 AND 1),
    start_date DATE,
    end_date DATE,
    PRIMARY KEY (account_sk, customer_sk)
);

-- 家庭维度表
CREATE TABLE dim_household (
    household_sk INT PRIMARY KEY,
    household_id VARCHAR(32),
    total_assets DECIMAL(18,2),
    risk_exposure_level VARCHAR(20)
);

典型应用场景：
联名账户资金分配计算：

SELECT 
    a.account_id,
    SUM(t.amount * bc.weight) AS allocated_amount
FROM fact_transaction t
JOIN bridge_account_customer bc ON t.account_sk = bc.account_sk
JOIN dim_account a ON t.account_sk = a.account_sk
GROUP BY a.account_id;

---

二、事实表建模实战技巧

2.1 账户快照事实表设计

按月快照表结构：

CREATE TABLE fact_account_snapshot (
    snapshot_date_key INT,       -- 时间维度外键
    account_sk INT,              -- 账户维度外键
    credit_sk INT,               -- 信用微型维度外键
    current_balance DECIMAL(18,2),
    avg_daily_balance DECIMAL(18,2),
    transaction_count INT,
    PRIMARY KEY (snapshot_date_key, account_sk)
)
PARTITION BY RANGE (snapshot_date_key) (
    PARTITION p_202301 VALUES LESS THAN (20230201),
    PARTITION p_202302 VALUES LESS THAN (20230301)
);

性能优化策略：

• 采用列式存储压缩历史分区
• 建立月度汇总物化视图
• 使用BITMAP索引加速状态条件查询

---

2.2 交易流水实时分析模型

宽表设计模式：

CREATE TABLE fact_transaction (
    transaction_id BIGINT,
    transaction_time DATETIME(6),  -- 精确到微秒
    account_sk INT,
    customer_sk INT,
    amount DECIMAL(18,2),
    channel_code VARCHAR(10),
    status ENUM('SUCCESS','FAILED','PENDING'),
    risk_score DECIMAL(5,3),
    INDEX idx_risk (risk_score) USING BTREE
) ENGINE=InnoDB;

实时风控应用：

1. 建立交易特征矩阵：
   • 短期交易频次（1小时窗口）
   • 地理位置跳跃检测
   • 异常时间交易识别
2. 关联反欺诈规则维度表动态评分

---

三、复杂业务建模解决方案

3.1 金融产品超类建模

产品层次模型设计：

-- 产品超类表
CREATE TABLE dim_product (
    product_sk INT PRIMARY KEY,
    product_type VARCHAR(20),
    base_rate DECIMAL(5,3),
    effective_date DATE
);

-- 信用卡子类表
CREATE TABLE sub_credit_card (
    product_sk INT PRIMARY KEY,
    annual_fee DECIMAL(8,2),
    reward_points_ratio DECIMAL(5,2),
    FOREIGN KEY (product_sk) REFERENCES dim_product(product_sk)
);

-- 贷款子类表
CREATE TABLE sub_loan (
    product_sk INT PRIMARY KEY,
    term_months INT,
    repayment_method VARCHAR(20),
    FOREIGN KEY (product_sk) REFERENCES dim_product(product_sk)
);

统一查询接口：

CREATE VIEW v_product AS
SELECT 
    p.*, 
    cc.annual_fee,
    l.term_months
FROM dim_product p
LEFT JOIN sub_credit_card cc ON p.product_sk = cc.product_sk
LEFT JOIN sub_loan l ON p.product_sk = l.product_sk;

---

四、数据治理体系设计

4.1 一致性维度管理

客户主数据标准化：

CREATE TABLE dim_customer (
    customer_sk BIGINT,
    unified_id VARCHAR(20),      -- 跨系统统一ID
    national_id VARCHAR(30),     -- 脱敏处理
    source_system VARCHAR(20),
    attribute_hash CHAR(64),     -- 属性变更校验
    CONSTRAINT pk_customer PRIMARY KEY (customer_sk)
);

变更捕获流程：

1. CDC捕获源系统变更
2. MDM中心进行身份解析
3. 生成增量更新文件
4. 触发维度表更新作业

---

五、典型业务场景实现

5.1 反洗钱监测方案

指标计算逻辑：

# 大额交易监测模型
def detect_suspicious_trans(transactions):
    alerts = []
    for tx in transactions:
        # 规则1：单笔交易超过阈值
        if tx.amount > get_threshold(tx.country_code):
            alerts.append(AMLAlert(type='AMOUNT_OVER', ...))
        
        # 规则2：高频小额交易
        if is_structured_trans(tx):
            alerts.append(AMLAlert(type='STRUCTURED', ...))
    
    return alerts

技术架构：

Kafka → Flink实时计算 → 规则引擎 → 预警存储 → 监管报送

---

习题解析

问题1：如何处理客户同时属于多个家庭的情况？
答案：

• 在家庭维度中建立层次结构（核心家庭、扩展家庭）
• 使用组维度记录家庭组合关系
• 在事实表中同时记录个人和家庭粒度的代理键

问题2：如何优化历史信用评分的查询效率？
答案：

• 建立信用评分变更历史表
• 使用CLUSTER BY snapshot_date进行物理排序
• 创建(account_sk, effective_date)联合索引

🎯下期预告：《电信服务数仓》
💬互动话题：你在学习遇到过哪些坑？欢迎评论区留言讨论！
🏷️温馨提示：我是[随缘而动，随遇而安], 一个喜欢用生活案例讲技术的开发者。如果觉得有帮助，点赞关注不迷路🌟