复杂风控场景（反洗钱）下，一些sql解决方案

前言：

        在工作中遇到的一些比较复杂的场景，一直觉得很有记录的价值，但是就是嫌麻烦懒得写，拖延症比较厉害，主要是怕以后忘了，这些问题如果做面试题的话，也很考验人，算是给自己留个备忘录，也不知道还能干几年了，简单记录下。因为我们用的是hive和trino，但是不同公司使用的数据引擎可能不一样，sql语法会有些差别，所以这里就只写解决思路，不写具体sql。如果有缘人看到了问题后，有更好的解决思路可以留言讨论。

项目场景：

工作中遇到的一些复杂的风控场景，主要就是为了反洗钱，然后后续衍生出了很多类似的问题，但是稍微要求变化一些，做法就变了很多，挺有意思的。

---

问题描述：

为了防止洗钱，需要监控最近一小时内三次转入以上（三个不同的人）转出一次（定义为一次异常交易），连续两组异常交易的人，需要把这些转入转出人都查出来给系统告警，进入监控。

---

问题分析：

必须是三个人以上转入后由一次或者多次转出，只要是满足有一次转出就行，这算异常交易，主要是反洗钱。如果一次转入一次转出就不算，且需要有两次这种异常交易。

---

解决方案：

问题拆成两个问题来做，做两个字查询，然后join。

第一个子查询是要求查处连续超过三笔转入，一次转出，两次这种交易行为。

        根据交易金额增加一列交易类型标签，转入为0，转出为1，做开窗，按人做分组，按交易时间做排序，然后做行转列，每个人一行，把交易类型组成一个值，然后用like ‘%0001%0001%’，这样子可以把超过三次转入一次转出两组异常交易行为的人筛选出来。但是不能解决超过三个不同人的问题。

 第二个子查询是有连续超过三笔转入，切转入人是不同的三个人，因为是需要两次异常交易，所以需要给两次异常交易打上标签，不然无法区分是哪次的异常交易中的人。

        增加一列交易类型标签，转入为0，转出为1，做两个开窗row_number排序，第一个是按账户分组按交易时间排序，第二个是按账户和交易类型分组，按交易时间排序，效果大致如下：

账户	交易类型	交易人	按账户分组按交易时间排序	按账户和交易类型分组按交易时间排序	两个排序值相减
a	1	b	1	1	0
a	0	c	2	1	1
a	0	d	3	2	1
a	0	e	4	3	1
a	1	f	5	2	3

        这里一些字段没写，如交易时间，不是很重要。两个排序减完后，我们把转出类型的数据过滤掉，剩下的相同的就是一组交易的人，这时候我们再行转列，主键是账户，把交易人组成array然后去重，我们这时候用的是trino，语法有些不一样，用的array_agg，然后用了array_distinct去重，还有element_at(字段,3) is not null来判断是否大于等于三个人。

        最后两个子查询用账户号join，结果就是满足需求的数据，并且可以把每次出现风险预警的人都查出来。

2024/0619更新

问题描述：

要求查出来一段时间内一次以上转入，一次以上转出，发生这种异常交易三次的账户，且三个异常交易之间间隔不超过1小时。因为有洗钱的人是多个人往一张卡转，然后再往多张卡转出，所以要监控。

---

问题分析：

举个例子，如果转入为1转出为0，需要查出来在一段时间内交易类型是111100110010000的这种账户。如果只是用交易方式标签like %10%10%10的话，不能满足三组异常交易时间间隔一小时内的要求，所以还需要做其它加工判断。

---

解决方案：

对交易类型打标签，转入为1，转出为0，增加一列开窗函数lag，对lag（标签字段，1，标签字段）over（partition by 账号 order by 交易时间），往下挪一位，选择交易类型列小于开窗函数排序列的，然后再对交易时间做lead（交易时间，1，交易时间）over（partition 账号 order by 交易时间），两个交易时间做差值，然后筛选出来时间差值小于1小时的超过三个的，如下：

账号	交易账号	交易类型	交易类型lag	筛选交易类型小于lag列的	交易时间	交易时间lead	时间差值
a	b	1	1		10:01
a	c	1	1		10:02
a	d	1	1		10:03
a	f	0	1	保留	10:04	10:08	4
a	g	0	0		10:05
a	w	0	0		10:06
a	s	1	0		10:07
a	q	0	1	保留	10:08	10:11	3
a	d	1	0		10:09
a	f	1	1		10:10
a	f	0	1	保留	10:11	10:11	0
a	g	0	0		10:12

问题描述：

要求查出一次转入三次以上转出这种异常交易连续发生三次的账号，且要求三次异常交易的人假期来超过12个不同，切每次异常交易超过4个不同的人。

---

问题分析：

举个例子，如果转入为1转出为0，需要查出来在一段时间内交易类型是100011100000111110的这种账户以及交易对手的账号，问题最大的是需要把一次转入和多次转出归位一个交易分组，需要每组交易之间人先去重，然后再把三组的人再去重计算人数大于12个。

---

解决方案：

还是给交易类型打标签转入为1，转出为0，这次要用上sum的开窗函数高阶用法，sum(交易类型标签)(partition  by 账号 order by 交易时间 row between unbunded preceding and current row)，效果如下：

账号	交易对手	交易类型	sum开窗函数的值
a	b	1	1
a	c	0	1
a	d	0	1
a	f	0	1
a	g	1	2
a	h	1	3
a	i	0	3
a	j	0	3
a	k	0	3
。。。	。。。	。。。	。。。

通过sum的开窗函数，我们把一次转入多次转出标记为一组交易，跟第一个问题类似，我们可以再行转列，再去重，去重后筛选出来大4个账号的，然后再把同一个账号的几组交易账号再合并去重，筛选出来人数大于12个的。这个问题最重要的是怎么把一次转入多次转出标记为一组交易。

问题描述：

来自上面问题的拓展，需要把多进多出标记为一组交易，任意次的转入和任意次的转出标记为一组。

---

问题分析：

该问题只是对需求的拓展，实际应用场景未知，不过想了很久问题比较复杂。需要实现的效果如下：

交易类型	分组
1	1
1	1
1	1
1	1
0	1
1	2
1	2
0	2
0	2

---

解决方案：

先对交易类型打标签，转入为1，转出为0。然后对账号分组，对交易时间开窗排序，使用排序列和标签列相乘，然后把相乘的结果通过lag开窗函数往下挪一位。相乘的结果再减去lag的字段，的出结果后，我们在用max的开窗函数，求之前到当前行的最大值，比较抽象，效果如下：

交易时间排序	交易类型	交易时间排序和交易类型相乘	lag	lag一位后相减	max开窗求之前到当前的最大值
1	0	0	0	0	0
2	0	0	0	0	0
3	1	3	0	3	3
4	1	4	3	1	3
5	1	5	4	1	3
6	0	0	5	-5	3
7	1	7	0	7	7
8	0	0	7	-7	7
9	0	0	0	0	7
10	1	10	0	10	10
11	1	11	10	1	10
12	0	0	11	-11	10
13	0	0	0	0	10
14	0	0	0	0	10
15	0	0	0	0	10
16	1	16	0	16	16
17	0	0	16	-16	16
18	0	0	0	0	16
19	0	0	0	0	16
20	1	20	0	20	20

通过这样子就可以把从转入开始到下一笔转入前的所有转入转出都标记为一组交易。