我用来捕获交易欺诈的 SQL 模式

快速免责声明：我在程序完整性团队中从事数据工作。下面的示例使用通用事务表和虚构场景。这里没有任何内容来自我实际工作或看到的任何内容。观点是我的，不是我雇主的。交易数据中的欺诈检测主要是SQL。不是机器学习，不是图形数据库，也不是 Gartner 今年大肆宣传的任何东西。 SQL，针对正确的表运行，使用正确的连接，寻找正确的形状。我主要从事政府资助的福利计划，但以下模式可以移植到任何带有交易表的事物：信用卡、医疗保健索赔、电子商务、销售点。如果资金移动并被记录，这些查询会在日志中发现奇怪的东西。六种图案。大致按照我在新数据集上构建它们的顺序。 1. 速度 最简单的一种。持有被盗卡的人想要在持卡人注意到之前将其耗尽。所以他们很快就打牌了。 SELECT cardholder_id, date_trunc( 'hour' , timestamp ) AS hour_bucket, count ( * ) AS tx_count, min ( timestamp ) AS first_tx, max ( timestamp ) AS last_tx FROM transactions WHERE timestamp >= current_date - INTERVAL '30 days' GROUP BY 1 , 2 HAVING count ( * ) > 10 ;调整两个旋钮：窗口大小和计数阈值。我通常并行运行 1 分钟、5 分钟和 1 小时的版本并进行比较。不同的欺诈行为以不同的规模出现——卡测试环在几秒钟内就到达了服务器；一个利益贩卖团伙可能需要一个下午的时间。少数持卡人会合理地突破门槛。为自动售货机提供服务的路线运营商。人们批量充值预付卡。你的误报。第一次通过后值得保留白名单。对于滑动窗口速度，这是我使用的形式： SELECT cardholder_id, timestamp , count ( * ) OVER ( PARTITION BY cardholder_id ORDER BY timestamp RANGE BETWEEN INTERVAL '5 分钟' PRECEDING AND CURRENT ROW ) AS tx_in_last_5min FROM transactions QUALIFY tx_in_last_5min >= 5 ORDER BY cardholder_id, timestamp ; QUALIFY 适用于 Snowflake、BigQuery、Databricks、Teradata。对于 Postgres，您将整个事物包装在 CTE 中并在外部进行过滤。轻微疼痛，结果相同。 2. 不可能的旅行 如果一张卡在芝加哥刷卡，七分钟后又在洛杉矶刷卡，那么其中一次刷卡就是假的。该卡已克隆。这是你会发现的最没有争议的欺诈信号——一张卡在七分钟内出现在两个遥远的地方几乎没有合理的理由。 WITHorder_tx AS ( SELECT cardholder_id, timestamp , location , LAG ( timestamp ) OVER ( PARTITION BY cardholder_id ORDER BY timestamp ) AS prev_ts, LAG ( location ) OVER ( PARTITION BY cardholder_id ORDER BY timestamp ) AS prev_loc FROM transactions ) SELECT cardholder_id, prev_ts AS first_tx, timestamp AS secondary_tx, prev_loc AS第一个位置，位置 AS 第二个位置，EXTRACT(EPOCH FROM (时间戳 - prev_ts)) / 60 AS 分钟_间隔，haversine(prev_loc, 位置 ) AS Miles_apart FROMordered_tx，其中 prev_ts 不为空且 prev_loc <> 位置 AND hasrsine(prev_loc, 位置)/ nullif (EXTRACT(EPOCH FROM (时间戳 - prev_ts)), 0 ) * 3600 > 600 ;半正矢是大圆距离函数。大多数仓库都会运送一件。如果你的没有，你自己写大约十行。 600 英里/小时的阈值很粗糙——商用喷气式飞机的巡航速度约为 575 英里/小时，因此这“比飞机可能达到的速度还要快”。如果你也想赶上快得令人怀疑的地面旅行，你可以将速度收紧到 100 英里/小时，但在这个阈值你开始搭载真正的航空旅客、父母开车从营地回家的孩子，诸如此类的事情。同一个系列中的其他一些形状也值得运行：两个遥远的城市，同一州，5 分钟内。本地克隆环。一小时内多个邮政编码。撇油环在一个区域内工作。 10 分钟内过境。国际戒指。 3. 金额异常 有一些金额在欺诈中不成比例地出现，而在正常使用中几乎从未出现过。 SELECT cardholder_id、timestamp、amount、merchant_id FROM transactions WHERE (amount >= 99 . 50 AND amount < 100 . 00 ) OR (amount >= 499 . 50 AND amount < 500 . 00 ) OR amount IN (1 . 00 , 5 . 00 , 10 . 00 ) ORDER BY持卡人 ID、时间戳；发生的情况：小额金额（1.00 美元、5.00 美元、10.00 美元）几乎都是卡测试。有人从垃圾场得到了卡号，他们在转售之前检查它是否有效。真正的持卡人几乎不会以 1 美元的价格购买任何东西。咖啡 4.73 美元，汽油 52.81 美元。圆度就是信号。低于阈值的金额是不同的。 99.99 美元很有趣，因为在很多地方，100 美元是收银员检查身份证的行。 499.99 美元很有趣，因为 500 美元通常是 ATM 每日上限。进行交易的人都知道规则并遵守规则。 （特别是对于福利交易，整数模式没有多大帮助。福利不会以同样的方式进行卡测试。有信号我