实战 SQL！金融机构可疑支付交易的监测 | 原力计划

作者 | 不剪发的Tony老师

责编 | 王晓曼

出品 | CSDN博客

今天，我们来谈谈如何利用 SQL 窗口函数发现可疑的银行卡支付交易。2002 年，中国人民银行为了加强对人民币支付交易的监督管理，规范人民币支付交易报告行为，防范利用银行支付结算进行洗钱等违法犯罪活动，制定了《人民币大额和可疑支付交易报告管理办法》。

该办法定义了大额支付交易和可疑交易支付的各种场景和定义。其中大额交易判断比较简单，主要是通过单笔交易额进行监测；可疑交易的情况比较复杂，其中有一些是基于短期交易频率、相同收付款人和交易额度等数据进行监测。针对这种类型的可疑交易，利用 SQL 窗口函数可以非常方便地进行分析。

本文示例经过验证的数据库包括 MySQL、Oracle、SQLServer、PostgreSQL 以及 SQLite，首先给出结论：

上面这些函数包含了OVER子句，都属于窗口函数而不是聚合函数。

 

窗口函数简介

 

窗口函数（Window Function）是专门用于数据分析的函数，它们针对查询中的每一行数据，基于和当前行相关的一组数据计算出一个结果。我们可以通过与聚合函数比较来了解窗口函数的作用：

上图中的 COUNT、SUM 以及 AVG 既可以用做聚合函数，也可以用作窗口函数；聚合函数针对所有的数据只返回一条结果，窗口函数为每行数据都返回一个结果。

从定义上来讲，窗口函数包含了一个OVER子句，用于指定数据分析的窗口：

window_function ( expression, ... ) OVER (
    PARTITION BY ...
    ORDER BY ...
    frame_clause
)

其中，window_function 是窗口函数的名称；expression 是参数，有些函数不需要参数；OVER子句包含三个选项：分区（PARTITION BY）、排序（ORDER BY）以及窗口大小（frame_clause）。

PARTITIONBY 选项用于定义分区，作用类似于 GROUP BY 的分组。如果指定了分区选项，窗口函数将会分别针对每个分区单独进行分析；如果省略分区选项，所有的数据作为一个整体进行分析。

ORDERBY 选项用于指定分区内的排序方式，通常用于数据的排名分析。

窗口选项 frame_clause 用于在当前分区内指定一个可移动的计算窗口；指定了窗口之后，分析函数不再基于分区进行计算，而是基于窗口内的数据进行计算。具体来说，窗口大小的常用选项如下：

{ ROWS | RANGE } frame_start
{ ROWS | RANGE } BETWEEN frame_start AND frame_end

其中，ROWS 表示以行为单位计算窗口的偏移量，RANGE 表示以数值（例如 30 分钟）为单位计算窗口的偏移量，参考下图：

CURRENT ROW 表示当前正在处理的行；其他的行可以使用相对当前行的位置表示；窗口的大小不会超出当前分区的范围。

frame_start用于定义窗口的起始位置，可以指定以下内容之一：

• UNBOUNDEDPRECEDING，窗口从分区的第一行开始，默认值；
• N PRECEDING，窗口从当前行之前的第 N 行或者数值开始；
• CURRENTROW，窗口从当前行开始。

frame_end用于定义窗口的结束位置，可以指定以下内容之一：

• CURRENTROW，窗口到当前行结束，默认值；
• NFOLLOWING，窗口到当前行之后的第 N 行或者数值结束；
• UNBOUNDEDFOLLOWING，窗口到分区的最后一行结束。

常见的窗口函数可以分为以下几类：聚合窗口函数、排名窗口函数（实现产品的分类排名）以及取值窗口函数（实现销量的同比/环比分析）。本文只涉及聚合窗口函数，其他函数下回分解。

接下来我们介绍两个具体的案例，创建一个记录银行卡交易流水的表 transfer_log：

CREATE TABLE transfer_log
( log_id    INTEGER NOT NULL PRIMARY KEY,
  log_ts    TIMESTAMP NOT NULL,
  from_user VARCHAR(50) NOT NULL,
  to_user   VARCHAR(50),
  type      VARCHAR(10) NOT NULL,
  amount    NUMERIC(10) NOT NULL
);

INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (1,'2019-01-02 10:31:40','62221234567890',NULL,'存款',50000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (2,'2019-01-02 10:32:15','62221234567890',NULL,'存款',100000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (3,'2019-01-03 08:14:29','62221234567890','62226666666666','转账',200000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (4,'2019-01-05 13:55:38','62221234567890','62226666666666','转账',150000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (5,'2019-01-07 20:00:31','62221234567890','62227777777777','转账',300000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (6,'2019-01-09 17:28:07','62221234567890','62227777777777','转账',500000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (7,'2019-01-10 07:46:02','62221234567890','62227777777777','转账',100000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (8,'2019-01-11 09:36:53','62221234567890',NULL,'存款',40000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (9,'2019-01-12 07:10:01','62221234567890','62228888888881','转账',10000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (10,'2019-01-12 07:11:12','62221234567890','62228888888882','转账',8000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (11,'2019-01-12 07:12:36','62221234567890','62228888888883','转账',5000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (12,'2019-01-12 07:13:55','62221234567890','62228888888884','转账',6000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (13,'2019-01-12 07:14:24','62221234567890','62228888888885','转账',7000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (14,'2019-01-21 12:11:16','62221234567890','62228888888885','转账',70000);

还是需要说明一下：可疑支付交易并不一定就是有问题的交易；本文只是采用了一个简化的计算模式作为演示，主要目的是为了说明窗口函数的作用。

 

短期累计转账超过一百万元

 

当个人账户在短期（通常是 10 个营业日）内出现累计 100 万元以上转账操作，我们认为这是一个可疑的行为，需要记录并进一步进行分析。以下语句用于查询 5 天之内累积转账超过 100 万的账号：

select *
from (
    select *, 
    sum(amount) over (partition by from_user order by log_ts range interval '5' day preceding) total_amount
    from transfer_log
    where type = '转账'
    ) t
where total_amount > 1000000;
log_id|log_ts             |from_user     |to_user       |type|amount|total_amount|
------|-------------------|--------------|--------------|----|------|------------|
     7|2019-01-10 07:46:02|62221234567890|62227777777777|转账  |100000|     1050000|

该查询主要使用了窗口函数 sum，partition by 用于按照用户进行分析，而不是将所有用户交易混合在一起；order by 按照交易时间进行排序；range 将数据分析的窗口定义为 5 天之内的交易流水。

查询结果显示账号 62221234567890 在 5 天之内累计转账 105 万。

 

相同收付款人短期频繁转账

 

利用 COUNT 窗口函数，可以分析相同收付款人短期内的转账频率，例如：

select *
from (
    select *, 
    count(1) over (partition by from_user,to_user order by log_ts range interval '5' day preceding) times
    from transfer_log
    where type = '转账'
    ) t
where times >= 3;
log_id|log_ts             |from_user     |to_user       |type|amount|times|
------|-------------------|--------------|--------------|----|------|-----|
     7|2019-01-10 07:46:02|62221234567890|62227777777777|转账  |100000|    3|

其中，count 函数用于统计次数；partition by 按照不同的发起方和接收方进行分组；其他参数和上一个示例相同。查询表明账号 62221234567890 在 5 天之内给账号 62227777777777 转账了 3 次以上。

下面我们再来介绍一个 AVG 窗口函数的使用案例。

移动平均法预测产品的销量

 

移动平均法是用一组最近的实际数据值来预测未来一期或几期内公司产品的需求量、公司产能等的一种常用方法。移动平均法适用于近期预测，分为简单移动平均法、加权移动平均法、趋势移动平均法等。

我们以简单移动平均法为例，也就是说未来一期的销量等于前 N 期销量的算术平均值。基于该销售数据，我们预测一下未来的产品销量：

select *, avg(amount) over (partition by product order by ym rows 4 preceding) next_amount
from sales_monthly
order by product,ym desc;
product  |ym    |amount  |next_amount |
---------|------|--------|------------|
桔子     |201906|11524.00|11351.400000|
桔子     |201905|11423.00|11266.400000|
桔子     |201904|11327.00|11179.400000|
桔子     |201903|11302.00|11102.400000|
桔子     |201902|11181.00|11009.600000|
桔子     |201901|11099.00|10931.000000|
桔子     |201812|10988.00|10847.200000|
桔子     |201811|10942.00|10765.200000|
桔子     |201810|10838.00|10677.800000|
桔子     |201809|10788.00|10603.200000|
桔子     |201808|10680.00|10510.600000|
桔子     |201807|10578.00|10423.600000|
...

AVG函数用于计算平均值；Partition by 按照不同产品进行分析；Order by 按照月份进行排序；Rows 指定分析窗口为前 4 个月和当前月（共 5 期数据进行平均）。

查询结果显示“桔子”最新一期（201907）的预期销量为 11351.4；利用已有的销量数据和基于历史的预测值，可以计算出预测的标准误差（需要用到取值窗口函数 LAG），从而可以尝试不同的 N 值并找出更误差最小的值。

总结

 

SQL窗口函数提供了强大的数据分析功能，我们介绍了一些聚合窗口函数的使用。SUM 函数常常用于计算历史累计值，COUNT 函数可以用于计算数据累计出现的次数，AVG 函数可以用于计算移动平均值。

版权声明：本文为CSDN博主「不剪发的Tony老师」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。

原文链接：https://blog.csdn.net/horses/article/details/105855073

【END】

6月2日20:00 ，CSDN 创始人&董事长、极客帮创投创始合伙人蒋涛携手全球顶级开源基金会主席、董事，聚焦中国开源现状，直面开发者在开源技术、商业上的难题，你绝不可错过的开源巅峰对谈！立即免费围观：

更多精彩推荐
☞人工智能时代，AI 并不是为了取代传统教师而生！
☞基础软件，未来只有开源一条路？
☞大佬 Zed 玩转跨界：不会绘画的音乐家不是好程序员
☞基于深度学习和传统算法的人体姿态估计，技术细节都讲清楚了
☞面试中遇到这 3 个SQL问题，最容易掉坑里！
☞好扑科技结合区块链行业发展趋势，重磅推出“好扑区块链合伙人”计划
点击阅读原文，精彩继续。
你点的每个“在看”，我都认真当成了喜欢