金融交易风控场景案例

场景描述

某金融平台需实时统计交易数据，进行交易风控规则计算。

业务需求

实时统计交易金额、笔数及异常交易检测。

解决方案

通过物化视图的增量刷新功能实现实时计算。

操作示例

创建交易记录表。

     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);
 
 
  

创建物化视图存储实时风控指标。

     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);
 
 
  

创建物化视图进行实时异常交易检测。

     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';
 
 
  