SQL案例分析：银行可疑支付交易监控

问题描述

银行客户交易流水存储在transfer_log表中：

 定义两个可疑支付的规则：

1.找出5天之内累积转账超过100万的账号

2.找出相同收付款人5天内连续转账3次以上的记录

问题1

首先查看转账记录：

 难点：滚动的时间窗口

SELECT * FROM(

SELECT * ,
sum(amount) over(partition by from_user ORDER BY log_ts 
	RANGE BETWEEN INTERVAL 5 DAY PRECEDING AND CURRENT row) AS total
FROM transfer_log
WHERE  TYPE='转账' ) t
WHERE total > 1000000

问题2

SELECT *,
COUNT(amount) OVER(PARTITION BY from_user,to_user ORDER BY log_ts 
	RANGE BETWEEN INTERVAL 5 DAY PRECEDING AND CURRENT ROW) AS num
FROM transfer_log
WHERE  TYPE='转账' ) t
WHERE num>2