第4章金融核心事实表设计（交易与风控）

4. 金融核心事实表设计（交易与风控）

好，咱们进入第四章。这一章我打算聊聊金融数据仓库里最核心、也最让人头疼的部分——事实表设计。说白了，就是怎么把交易和风控这两大块数据，装进我们的模型里，还得跑得快、查得准。

我个人习惯把事实表分成三类：累积快照、周期快照，还有无事实的事实表。嗯，别被名字吓到，咱们一个一个拆开讲。

4.1 交易事实表：累积快照的实战

先说说交易事实表。你想想看，一笔交易从下单、支付、清算到最终结算，中间要经历好几个状态。如果用普通的明细事实表，每次状态变化都得插一条新记录，查最新状态还得做子查询，性能直接拉胯。

累积快照就是专门解决这个问题的。它把一笔交易的完整生命周期，压缩到一行记录里。每个关键节点都用一个字段来存时间戳或状态码。

核心思路： 一行记录 = 一笔交易的完整旅程

我在项目中遇到过这样一个场景：某支付平台每天处理上亿笔交易，业务方要查「今天下单但3天内未支付」的交易。如果用明细表，得关联好几张表，跑一次查询要几分钟。改成累积快照后，直接在交易事实表里加一个「支付时间」字段，查询秒级返回。

来看一个简化的表结构设计：

-- 交易累积快照事实表
CREATE TABLE fact_trade_accum_snapshot (
    trade_id          BIGINT COMMENT '交易ID',
    account_id        BIGINT COMMENT '账户ID',
    product_id        BIGINT COMMENT '产品ID',
    -- 时间维度
    create_dt         STRING COMMENT '下单日期',
    pay_dt            STRING COMMENT '支付日期',
    settle_dt         STRING COMMENT '结算日期',
    -- 金额度量
    trade_amount      DECIMAL(18,2) COMMENT '交易金额',
    fee_amount        DECIMAL(18,2) COMMENT '手续费',
    -- 状态退化维度
    trade_status      STRING COMMENT '交易状态: 0-待支付,1-已支付,2-已结算',
    pay_channel       STRING COMMENT '支付渠道: 01-微信,02-支付宝',
    -- 技术字段
    etl_time          TIMESTAMP COMMENT 'ETL更新时间'
)
PARTITIONED BY (dt STRING);

我的经验： 累积快照表里，时间字段建议用STRING类型存日期（如'2024-01-15'），不要用TIMESTAMP。为什么？分区裁剪更高效，而且避免时区转换的坑。

4.2 风险事件事实表：周期快照的艺术

风险事件和交易不一样。交易有明确的开始和结束，但风险是持续监控的。比如反洗钱监控，每天都要看账户的累计交易额是否超过阈值。这时候，周期快照就派上用场了。

周期快照的核心思想是：按固定时间间隔（比如每天），把当前状态拍一张「照片」存下来。不管这期间有没有变化，每天都存一条记录。

我曾经帮一家银行做反欺诈系统，他们原来的做法是只记录触发规则的事件。结果业务方问：「昨天有多少账户处于高风险状态？」——查不出来，因为没有历史状态。后来改成周期快照，每天凌晨跑一次全量快照，所有问题迎刃而解。

设计要点：

快照粒度： 通常是「账户+日期」，每天一条
度量指标： 累计交易额、当日交易笔数、风险评分等
退化维度： 风险等级、预警状态等直接挂在事实表上

-- 风险事件周期快照事实表
CREATE TABLE fact_risk_daily_snapshot (
    account_id        BIGINT COMMENT '账户ID',
    snapshot_dt       STRING COMMENT '快照日期',
    -- 累积度量
    total_trade_amt   DECIMAL(18,2) COMMENT '累计交易金额(近30天)',
    trade_count_30d   BIGINT COMMENT '近30天交易笔数',
    -- 风险指标
    risk_score        DECIMAL(5,2) COMMENT '风险评分(0-100)',
    risk_level        STRING COMMENT '风险等级: L1-低, L2-中, L3-高',
    -- 预警信息
    alert_flag        STRING COMMENT '是否触发预警: Y/N',
    alert_reason      STRING COMMENT '预警原因编码',
    -- 技术字段
    etl_time          TIMESTAMP COMMENT 'ETL更新时间'
)
PARTITIONED BY (dt STRING);

注意： 周期快照的数据量会随时间线性增长。假设有1000万个活跃账户，每天存一条，一年就是36.5亿条。建议按季度或半年做一次数据归档，把旧数据移到冷存储。

4.3 金融指标聚合设计

事实表设计好了，但业务方不会直接查原始表。他们要的是各种聚合指标：比如「今日交易总额」、「高风险账户占比」、「支付成功率」等等。

我的做法是：分层聚合。不要一把梭，从明细到汇总一步到位。那样ETL跑得慢，而且不好排查问题。

我建议分三层：

DWS层（轻度汇总）： 按小时、按产品维度聚合。比如每小时每个产品的交易额、笔数。
ADS层（应用汇总）： 按天、按业务线聚合。比如每天各业务线的交易指标。
报表层： 直接对接BI工具，按需聚合。

举个例子，计算「支付成功率」这个指标：

-- DWS层：每小时聚合
INSERT OVERWRITE TABLE dws_trade_hourly
PARTITION (dt='2024-01-15')
SELECT 
    product_id,
    hour(create_time) as hour_id,
    COUNT(*) as total_count,
    SUM(CASE WHEN pay_status = 'SUCCESS' THEN 1 ELSE 0 END) as success_count,
    SUM(trade_amount) as total_amount
FROM fact_trade_accum_snapshot
WHERE dt = '2024-01-15'
GROUP BY product_id, hour(create_time);

-- ADS层：每日聚合，直接算成功率
INSERT OVERWRITE TABLE ads_trade_daily
PARTITION (dt='2024-01-15')
SELECT 
    product_id,
    SUM(total_count) as total_trades,
    SUM(success_count) as success_trades,
    ROUND(SUM(success_count) * 1.0 / SUM(total_count), 4) as success_rate
FROM dws_trade_hourly
WHERE dt = '2024-01-15'
GROUP BY product_id;

避坑指南： 我曾经在聚合层直接用明细表算指标，结果跑一次要2小时。后来改成先轻度汇总，再二次聚合，时间缩短到15分钟。记住：能提前聚合的，绝不拖到后面。

4.4 退化维度处理

最后聊聊退化维度。这个概念听着高大上，其实说白了就是：把那些本来应该做成维度的字段，直接塞进事实表里。

什么时候该退化？我总结了三个场景：

字段取值很少： 比如交易状态，就3-5个值。单独建一张维度表，查询时还得JOIN，得不偿失。
字段变化不频繁： 比如支付渠道，一年可能才新增一两个。直接放事实表里，省事。
字段和事实强绑定： 比如交易ID，它本身就是事实表的主键一部分，没必要再建维度表。

我见过最夸张的一个案例：某团队把「性别」字段做成了维度表，然后事实表里存性别ID。每次查交易数据都要关联性别维度表。你说图啥？性别就男、女、未知三个值，直接退化到事实表里，一个字段搞定。

当然，也不是所有字段都适合退化。如果某个字段有几十个属性（比如产品信息：产品名称、类别、风险等级、利率等），那还是老老实实建维度表吧。

下面我用一张图来总结本章的知识体系：

嗯，这一章的内容就这些。总结一下：交易用累积快照，风险用周期快照，指标要分层聚合，小字段直接退化。这些都是我在实际项目中踩过坑、填过坑之后总结出来的经验。你照着做，至少能少走一半弯路。

公众号:蓝海资料掘金营，微信deep3321

📚 课程目录

4. 金融核心事实表设计（交易与风控）

4.1 交易事实表：累积快照的实战

4.2 风险事件事实表：周期快照的艺术

4.3 金融指标聚合设计

4.4 退化维度处理