# 金融交易风控场景案例
#### 场景描述
某金融平台需实时统计交易数据，进行交易风控规则计算。
#### 业务需求
实时统计交易金额、笔数及异常交易检测。
#### 解决方案
通过物化视图的增量刷新功能实现实时计算。
#### 操作示例
1. 创建交易记录表。 
   ```
   CREATE TABLE transaction_log (
   transaction_id BIGINT,
   user_id BIGINT,
   merchant_id BIGINT,
   amount DECIMAL(15,2),
   transaction_type VARCHAR(20),
   risk_level VARCHAR(10),
   transaction_time TIMESTAMP
   ) WITH (orientation = COLUMN);
   ```
   
   
2. 创建物化视图存储实时风控指标。 
   ```
   CREATE MATERIALIZED VIEW mv_risk_indicators
   REFRESH FAST EVERY (interval '1 min')
   ENABLE QUERY REWRITE
   WITH (orientation = COLUMN)
   AS
   SELECT
   user_id,
   DATE_TRUNC('hour', transaction_time) as stat_hour,
   COUNT(*) as transaction_count,
   SUM(amount) as total_amount,
   AVG(amount) as avg_amount,
   MAX(amount) as max_amount,
   COUNT(CASE WHEN risk_level = 'HIGH' THEN 1 END) as high_risk_count,
   COUNT(CASE WHEN risk_level = 'MEDIUM' THEN 1 END) as medium_risk_count
   FROM transaction_log
   WHERE transaction_time >= now() - interval '24 hours'
   GROUP BY user_id, DATE_TRUNC('hour', transaction_time);
   ```
   
   
3. 创建物化视图进行实时异常交易检测。 
   ```
   CREATE MATERIALIZED VIEW mv_anomaly_detection
   REFRESH FAST EVERY (interval '5 min')
   ENABLE QUERY REWRITE
   AS
   SELECT
   ri.user_id,
   ri.stat_hour,
   ri.transaction_count,
   ri.total_amount,
   ri.avg_amount,
   ri.high_risk_count,
   CASE
   WHEN ri.transaction_count > 100 THEN '异常: 高频交易'  -- 笔数异常检测规则
   WHEN ri.total_amount > 100000 THEN '异常: 大额交易'    -- 金额异常检测规则
   WHEN ri.high_risk_count > 5 THEN '异常: 高风险交易'    -- 异常交易检测规则
   ELSE '正常'
   END as anomaly_type
   FROM mv_risk_indicators ri
   WHERE ri.stat_hour >= now() - interval '1 hour';
   ```