纯属原创如有雷同不是巧合本文为准大家好我是只谈技术、不聊人生的 Tony 老师在实战 SQL 系列文章的上一篇中我们介绍了如何实现微信、微博等社交网络中的友好、粉丝关系分析。今天我们来谈谈另一个话题如何利用 SQL 窗口函数发现可疑的银行卡支付交易。2002 年中国人民银行为了加强对人民币支付交易的监督管理规范人民币支付交易报告行为防范利用银行支付结算进行洗钱等违法犯罪活动制定了《人民币大额和可疑支付交易报告管理办法》。该办法定义了大额支付交易和可疑交易支付的各种场景和定义。其中大额交易判断比较简单主要是通过单笔交易额进行监测可疑交易的情况比较复杂其中有一些是基于短期交易频率、相同收付款人和交易额度等数据进行监测。针对这种类型的可疑交易利用 SQL 窗口函数可以非常方便地进行分析。本文示例经过验证的数据库包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite首先给出结论聚合窗口函数MySQLOracleSQL ServerPostgreSQLSQLiteSUM() OVER()✔️✔️✔️✔️✔️COUNT() OVER()✔️✔️✔️✔️✔️AVG() OVER()✔️✔️✔️✔️✔️MAX() OVER()✔️✔️✔️✔️✔️MIN() OVER()✔️✔️✔️✔️✔️上面这些函数包含了OVER子句都属于窗口函数而不是聚合函数。窗口函数简介窗口函数Window Function是专门用于数据分析的函数它们针对查询中的每一行数据基于和当前行相关的一组数据计算出一个结果。我们可以通过与聚合函数比较来了解窗口函数的作用上图中的 COUNT、SUM 以及 AVG 既可以用做聚合函数也可以用作窗口函数聚合函数针对所有的数据只返回一条结果窗口函数为每行数据都返回一个结果。从定义上来讲窗口函数包含了一个OVER子句用于指定数据分析的窗口window_function(expression,...)OVER(PARTITIONBY...ORDERBY...frame_clause)其中window_function 是窗口函数的名称expression 是参数有些函数不需要参数OVER子句包含三个选项分区PARTITION BY、排序ORDER BY以及窗口大小frame_clause。PARTITION BY选项用于定义分区作用类似于 GROUP BY 的分组。如果指定了分区选项窗口函数将会分别针对每个分区单独进行分析如果省略分区选项所有的数据作为一个整体进行分析。ORDER BY选项用于指定分区内的排序方式通常用于数据的排名分析。窗口选项 frame_clause 用于在当前分区内指定一个可移动的计算窗口指定了窗口之后分析函数不再基于分区进行计算而是基于窗口内的数据进行计算。具体来说窗口大小的常用选项如下{ROWS|RANGE } frame_start {ROWS|RANGE }BETWEENframe_startANDframe_end其中ROWS表示以行为单位计算窗口的偏移量RANGE表示以数值例如 30 分钟为单位计算窗口的偏移量参考下图CURRENT ROW表示当前正在处理的行其他的行可以使用相对当前行的位置表示窗口的大小不会超出当前分区的范围。frame_start 用于定义窗口的起始位置可以指定以下内容之一UNBOUNDED PRECEDING窗口从分区的第一行开始默认值N PRECEDING窗口从当前行之前的第 N 行或者数值开始CURRENT ROW窗口从当前行开始。frame_end 用于定义窗口的结束位置可以指定以下内容之一CURRENT ROW窗口到当前行结束默认值N FOLLOWING窗口到当前行之后的第 N 行或者数值结束UNBOUNDED FOLLOWING窗口到分区的最后一行结束。常见的窗口函数可以分为以下几类聚合窗口函数、排名窗口函数实现产品的分类排名以及取值窗口函数实现销量的同比/环比分析。本文只涉及聚合窗口函数其他函数下回分解。接下来我们介绍两个具体的案例创建一个记录银行卡交易流水的表 transfer_logCREATETABLEtransfer_log(log_idINTEGERNOTNULLPRIMARYKEY,log_tsTIMESTAMPNOTNULL,from_userVARCHAR(50)NOTNULL,to_userVARCHAR(50),typeVARCHAR(10)NOTNULL,amountNUMERIC(10)NOTNULL);INSERTINTOtransfer_log(log_id,log_ts,from_user,to_user,type,amount)VALUES(1,2019-01-02 10:31:40,62221234567890,NULL,存款,50000);INSERTINTOtransfer_log(log_id,log_ts,from_user,to_user,type,amount)VALUES(2,2019-01-02 10:32:15,62221234567890,NULL,存款,100000);INSERTINTOtransfer_log(log_id,log_ts,from_user,to_user,type,amount)VALUES(3,2019-01-03 08:14:29,62221234567890,62226666666666,转账,200000);INSERTINTOtransfer_log(log_id,log_ts,from_user,to_user,type,amount)VALUES(4,2019-01-05 13:55:38,62221234567890,62226666666666,转账,150000);INSERTINTOtransfer_log(log_id,log_ts,from_user,to_user,type,amount)VALUES(5,2019-01-07 20:00:31,62221234567890,62227777777777,转账,300000);INSERTINTOtransfer_log(log_id,log_ts,from_user,to_user,type,amount)VALUES(6,2019-01-09 17:28:07,62221234567890,62227777777777,转账,500000);INSERTINTOtransfer_log(log_id,log_ts,from_user,to_user,type,amount)VALUES(7,2019-01-10 07:46:02,62221234567890,62227777777777,转账,100000);INSERTINTOtransfer_log(log_id,log_ts,from_user,to_user,type,amount)VALUES(8,2019-01-11 09:36:53,62221234567890,NULL,存款,40000);INSERTINTOtransfer_log(log_id,log_ts,from_user,to_user,type,amount)VALUES(9,2019-01-12 07:10:01,62221234567890,62228888888881,转账,10000);INSERTINTOtransfer_log(log_id,log_ts,from_user,to_user,type,amount)VALUES(10,2019-01-12 07:11:12,62221234567890,62228888888882,转账,8000);INSERTINTOtransfer_log(log_id,log_ts,from_user,to_user,type,amount)VALUES(11,2019-01-12 07:12:36,62221234567890,62228888888883,转账,5000);INSERTINTOtransfer_log(log_id,log_ts,from_user,to_user,type,amount)VALUES(12,2019-01-12 07:13:55,62221234567890,62228888888884,转账,6000);INSERTINTOtransfer_log(log_id,log_ts,from_user,to_user,type,amount)VALUES(13,2019-01-12 07:14:24,62221234567890,62228888888885,转账,7000);INSERTINTOtransfer_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(partitionbyfrom_userorderbylog_ts rangeinterval5daypreceding)total_amountfromtransfer_logwheretype转账)twheretotal_amount1000000;log_id|log_ts|from_user|to_user|type|amount|total_amount|------|-------------------|--------------|--------------|----|------|------------|7|2019-01-1007:46:02|62221234567890|62227777777777|转账|100000|1050000|该查询主要使用了窗口函数 sumpartition by 用于按照用户进行分析而不是将所有用户交易混合在一起order by 按照交易时间进行排序range 将数据分析的窗口定义为 5 天之内的交易流水。查询结果显示账号 62221234567890 在 5 天之内累计转账 105 万。相同收付款人短期频繁转账利用 COUNT 窗口函数可以分析相同收付款人短期内的转账频率例如select*from(select*,count(1)over(partitionbyfrom_user,to_userorderbylog_ts rangeinterval5daypreceding)timesfromtransfer_logwheretype转账)twheretimes3;log_id|log_ts|from_user|to_user|type|amount|times|------|-------------------|--------------|--------------|----|------|-----|7|2019-01-1007:46:02|62221234567890|62227777777777|转账|100000|3|其中count 函数用于统计次数partition by 按照不同的发起方和接收方进行分组其他参数和上一个示例相同。查询表明账号 62221234567890 在 5 天之内给账号 62227777777777 转账了 3 次以上。下面我们再来介绍一个 AVG 窗口函数的使用案例。移动平均法预测产品的销量移动平均法是用一组最近的实际数据值来预测未来一期或几期内公司产品的需求量、公司产能等的一种常用方法。移动平均法适用于近期预测分为简单移动平均法、加权移动平均法、趋势移动平均法等。我们以简单移动平均法为例也就是说未来一期的销量等于前 N 期销量的算术平均值。基于该销售数据我们预测一下未来的产品销量select*,avg(amount)over(partitionbyproductorderbyymrows4preceding)next_amountfromsales_monthlyorderbyproduct,ymdesc;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 函数可以用于计算移动平均值。除了上面的几种场景你还遇到过或者知道哪些应用案例欢迎关注❤️、评论、点赞