我用于检测交易欺诈的SQL模式

# 我用来捕捉交易欺诈的六个 SQL 模式 来源：https://analytics.fixelsmith.com/posts/sql-fraud-patterns/ 2026 年 5 月 12 日**快速免责声明：**我在一个项目完整性团队做数据工作。下面的例子使用的是通用交易表和虚构场景。这些内容并非来自我实际处理过或见过的东西。观点仅代表我个人，而非我的雇主。 --- 交易数据中的欺诈检测很大程度上依赖 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 minutes' 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. 不可能旅行 如果一张卡在芝加哥刷了一笔，七分钟后又出现在洛杉矶刷了一笔，那么其中一笔是假的。卡被克隆了。这是最没有争议的欺诈信号——几乎没有正当理由能让一张卡在七分钟内出现在相隔很远的两个地方。 `` WITH ordered_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 second_tx, prev_loc AS first_location, location AS second_location, EXTRACT(EPOCH FROM (timestamp - prev_ts)) / 60 AS minutes_apart, haversine(prev_loc, location) AS miles_apart FROM ordered_tx WHERE prev_ts IS NOT NULL AND prev_loc <> location AND haversine(prev_loc, location) / nullif(EXTRACT(EPOCH FROM (timestamp - prev_ts)), 0) * 3600 > 600; `` `haversine` 是大圆距离函数。大多数数据仓库都内置了这个函数。如果没有，自己写大概十行代码就能实现。 600 英里/小时的阈值是粗略的——商业喷气式客机的巡航速度大约为 575 英里/小时，所以这个阈值意味着“比飞机可能的速度还快”。如果你想也捕捉可疑的高速地面交通，可以把阈值收紧到 100 英里/小时，但这样你也会开始抓到真正的航空旅客、父母开车送孩子从营地回家的案例等等。 同一家族的其他几个形态也值得运行： - 同一州内的两个遥远城市，五分钟内出现。本地克隆团伙。 - 一小时内出现多个邮政编码。该地区的盗刷器团伙。 - 十分钟内出现边境穿越。国际团伙。 ## 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 cardholder_id, timestamp; `` 发生了什么： 小数额的整数金额——1.00 美元、5.00 美元、10.00 美元——几乎总是卡片测试。有人从数据库中获得了一个卡号，在转卖之前先测试它是否有效。真正的持卡人几乎从来不会恰好购买 1.00 美元的东西。咖啡是 4.73 美元，加油是 52.81 美元。整数本身就是一个信号。 略低于阈值的金额则不同。99.99 美元之所以有趣，是因为在很多地方，100 美元是收银员需要检查身份证的界限。499.99 美元之所以有趣，是因为 500 美元通常是每日 ATM 取款上限。进行交易的人知道规则，并且故意待在规则之下。 （对于福利交易来说，整数模式帮助不大。福利不像信用卡那样会被测试。这里的信号通常是重复收款人，这需要另一篇文章来讨论。） ## 4. 可疑商户 当盗刷器入侵了一个卡片读取器（比如加油泵上的），你得到的不是一个欺诈案件，而是几十个。接下来几周里在该油泵上刷过的每一张卡都会出现在某人的数据库中。因此，从商户方面的症状是：在短时间内，大量不相关的卡支出比平时更多。 `` SELECT merchant_id, date_trunc('hour', timestamp) AS hour_bucket, count(DISTINCT cardholder_id) AS unique_cards, count(*) AS total_tx, sum(amount) AS total_amount FROM transactions WHERE timestamp >= current_date - INTERVAL '7 days' GROUP BY 1, 2 HAVING count(DISTINCT cardholder_id) > 20 AND sum(amount) > 5000 ORDER BY total_amount DESC; `` 静态阈值（20 张不同卡、5000 美元）的问题在于它们没有考虑商户规模。一家好市多（Costco）在 90 秒内就能达到这个水平。一家二手书店则永远不会。因此，更好的版本是将每个商户与自身进行比较： `` WITH merchant_hourly AS ( SELECT merchant_id, date_trunc('hour', timestamp) AS hour_bucket, count(DISTINCT cardholder_id) AS unique_cards FROM transactions WHERE timestamp >= current_date - INTERVAL '60 days' GROUP BY 1, 2 ), with_baseline AS ( SELECT *, avg(unique_cards) OVER ( PARTITION BY merchant_id ORDER BY hour_bucket ROWS BETWEEN 168 PRECEDING AND 1 PRECEDING ) AS rolling_avg_cards FROM merchant_hourly ) SELECT *, unique_cards / nullif(rolling_avg_cards, 0) AS spike_ratio FROM with_baseline WHERE unique_cards > rolling_avg_cards * 3 ORDER BY spike_ratio DESC; `` 168 是过去七天的小时桶数。我使用一周是因为日周期和周周期很重要——周二下午两点的咖啡店和同一家店周六上午九点的基线不同。一周能捕捉到这两个周期。 三倍于正常值是起始点。这个阈值足够宽松，不会让警报泛滥，但也足够严格，可以标记出真正异常的时段。 ## 5. 非工作时间 大多数人在消费时是有习惯的。朝九晚五的人不会突然在凌晨三点开始加油。如果他们的卡在奇怪的时间使用，要么是别人在用，要么是他们正在旅行——而旅行会产生你可以核实的其他信号。 `` WITH cardholder_hour_pattern AS ( SELECT cardholder_id, EXTRACT(HOUR FROM timestamp) AS hour_of_day, count(*) AS tx_count FROM transactions WHERE timestamp >= current_date - INTERVAL '90 days' GROUP BY 1, 2 ), cardholder_normal AS ( SELECT cardholder_id, min(hour_of_day) FILTER (WHERE tx_count >= 2) AS earliest_hour, max(hour_of_day) FILTER (WHERE tx_count >= 2) AS latest_hour FROM cardholder_hour_pattern GROUP BY 1 ) SELECT t.cardholder_id, t.timestamp, t.amount, t.merchant_id FROM transactions t JOIN cardholder_normal cn USING (cardholder_id) WHERE EXTRACT(HOUR FROM t.timestamp) NOT BETWEEN cn.earliest_hour AND cn.latest_hour ORDER BY t.timestamp DESC; `` 内层查询中的“该小时内有两次或更多”的过滤条件在做重要的工作。如果没有它，三个月前一次深夜加油站的偶然购买就会成为该持卡人的“正常”小时范围，之后你就再也不会标记他们了。要求在 90 天内某个小时至少有两次购买，就把标准设定为“实际上是习惯”而不是“发生过一次”。 缺点：没有历史数据时这个模式不适用。新账户没有基线。对于这些账户，要么退回到全局小时模式，要么干脆跳过这个模式，等到他们有几个月的历史再说。 ## 6. 用于链式信号的窗口函数 这个其实不算一个模式。它是一个设置，可以让其他五个模式组合使用。 `` SELECT cardholder_id, timestamp, amount, merchant_id, timestamp - LAG(timestamp) OVER w AS time_since_last, CASE WHEN merchant_id <> LAG(merchant_id) OVER w THEN 'changed' ELSE 'same' END AS merchant_change, sum(amount) OVER ( PARTITION BY cardholder_id ORDER BY timestamp RANGE BETWEEN INTERVAL '24 hours' PRECEDING AND CURRENT ROW ) AS running_24h_total, ROW_NUMBER() OVER ( PARTITION BY cardholder_id, date(timestamp) ORDER BY timestamp ) AS tx_of_day FROM transactions WINDOW w AS (PARTITION BY cardholder_id ORDER BY timestamp) ORDER BY cardholder_id, timestamp; `` 一旦你物化了这些列，欺诈规则就变成了过滤表达式。比如说你在追查卡片测试团伙，其标志是“大量小额消费，全部在不同的商户，几分钟内完成”。规则就变成了： `` SELECT * FROM tx_with_windows WHERE tx_of_day >= 5 AND time_since_last < INTERVAL '60 seconds' AND merchant_change = 'changed'; `` 三个过滤器。仅此而已。 这一点之所以重要，是因为一旦你的分析师可以将新的欺诈假设表达为 SQL 过滤器，而不是工程工单，你的迭代周期就会从几周缩短到几小时。你就能更快地捕获更多欺诈。 ## 综合运用 这些模式单独使用都不够。速度有误报（售货机操作员）。地理不可达会遗漏单个都市区内的任何情况。金额异常在卡片测试之外的情境中不适用。非工作时间规则需要历史数据。 有效的方法是同时运行所有模式，并根据信号对每笔交易进行评分。一笔交易在三个或四个信号上失败，几乎可以肯定是欺诈。一笔交易只在一个信号上失败，可能只是你的奶奶在度假时用借记卡做了一件奇怪的事。 如果你是欺诈检测的新手，从模式 1 开始。仅它一个就能发现大量有用的欺诈，而且合法活动极少，运行成本也低。 如果你已经有了 1 到 5 模式，那么值得投资的是模式 6——那些窗口函数原语。一旦存在，团队里的每个分析师都会使用它们，而且添加下一个欺诈模式就不再是一个大项目了。 ## 我省略的内容 这篇文章没有涵盖但经常出现的一些内容： NULL 处理。真实的交易表使用 NULL 的方式和 SQL 入门书不同。很多遗留系统使用哨兵值，比如 `9999-12-31` 表示“无结束日期”，`0001-01-01` 表示“无开始日期”。使用 `IS NULL` 过滤会悄悄遗漏这些行。在编写假设 NULL 的 WHERE 子句之前，务必检查特定表中的约定。 误报。上面的每条规则都会标记出合法但行为奇怪的持卡人。你的欺诈工作流程需要人工复核标记的案例，并有一个反馈循环，让你可以根据实际欺诈和不是欺诈的情况来调整阈值。仅凭一条规则就自动封禁是失去客户的方式。 隐私。如果数据包含个人身份信息（PII），你的查询需要遵守适用的数据使用政策。先使用去标识化或采样数据，得到授权后再使用生产数据。 成本。窗口函数在大分区上运行时成本不低。先过滤日期范围，再应用窗口函数，而不是反过来。我曾见过一位初级分析师在添加 WHERE 条件之前，对整个数据集两年的交易数据运行 `LAG()`，结果烧掉了一个数据仓库的信用预算。 --- 接下来我想写的内容，取决于大家的反馈： - LAG 和 ROW_NUMBER 之外的八个窗口函数技巧 - 检测欺诈团伙，本质上这是一个社交图问题 - 欺诈团队的仪表盘上该放什么，不该放什么 - 为什么你的欺诈警报噪音很大，以及如何真正修复（而不是简单地提高阈值） 如果有你特别想了解的内容，可以通过 fixelsmith.com (https://fixelsmith.com/) 给我留言。 --- *Fixel Smith 是一位经验丰富的项目完整性分析师，专注于公共部门数据工作。*