蚂蚁集团大数据面试SQL-反欺诈：异常交易检测

Q: 反欺诈：异常交易检测

蚂蚁集团大数据面试SQL-反欺诈：短时高频异常交易检测

⚠️ 待修正

一、题目背景

这道题来自蚂蚁集团风控部门的数据分析岗面试。支付宝每天处理数十亿笔交易，其中存在大量欺诈行为——盗刷、刷单、洗钱等。"短时高频交易"是最经典的欺诈特征之一：正常用户不会在几分钟内连续支付五六次，但盗刷者会在拿到账号后快速消费。

业务场景：风控系统的实时决策引擎需要在毫秒级判断一笔交易是否可疑。这道题的30分钟滑动窗口计数就是引擎的核心逻辑——统计当前交易前30分钟内的交易次数，超过阈值就触发风控拦截。

二、题目

现有一张交易流水表 t5_transaction_log，记录了用户的每笔支付交易。请识别"短时高频"异常交易：同一用户在30分钟内交易次数超过5次的记录。

交易流水表 t5_transaction_log：

+--------+----------+--------+---------------------+
| txn_id | user_id  | amount |      txn_time       |
+--------+----------+--------+---------------------+
| T001   | u01      | 50.0   | 2023-03-01 10:00:00 |
| T002   | u01      | 80.0   | 2023-03-01 10:05:12 |
| T003   | u01      | 120.0  | 2023-03-01 10:10:30 |
| T004   | u01      | 35.0   | 2023-03-01 10:15:45 |
| T005   | u01      | 200.0  | 2023-03-01 10:20:00 |
| T006   | u01      | 75.0   | 2023-03-01 10:27:30 |
| T007   | u01      | 90.0   | 2023-03-01 11:00:00 |
| T008   | u02      | 100.0  | 2023-03-01 10:00:00 |
| T009   | u02      | 200.0  | 2023-03-01 10:40:00 |
| T010   | u02      | 150.0  | 2023-03-01 11:20:00 |
| T011   | u03      | 30.0   | 2023-03-01 14:00:00 |
| T012   | u03      | 45.0   | 2023-03-01 14:05:00 |
| T013   | u03      | 60.0   | 2023-03-01 14:10:00 |
| T014   | u03      | 50.0   | 2023-03-01 14:15:00 |
| T015   | u03      | 80.0   | 2023-03-01 14:20:00 |
+--------+----------+--------+---------------------+

三、思路分析

这是风控领域经典的"滑动时间窗口计数"，核心难点在于 Spark SQL 中 RANGE 窗口对时间类型的支持有限，需要用自连接模拟：

时间标准化：unix_timestamp(txn_time) 将时间转为Unix秒数，便于做加减1800秒运算
自连接滑动窗口：同一用户的交易表自连接，条件为 B.txn_ts BETWEEN A.txn_ts - 1800 AND A.txn_ts
窗口计数：GROUP BY + COUNT 统计每笔交易前30分钟内的交易次数
阈值筛选：WHERE cnt_30min > 5 标记异常交易

维度	评分
题目难度	⭐️⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

四、逐步推导

步骤1：将交易时间转为Unix时间戳

执行SQL

select txn_id, user_id, amount, txn_time,
       unix_timestamp(txn_time) as txn_ts
from t5_transaction_log

执行结果

+--------+----------+--------+---------------------+-------------+
| txn_id | user_id  | amount |      txn_time       |    txn_ts   |
+--------+----------+--------+---------------------+-------------+
| T001   | u01      | 50.0   | 2023-03-01 10:00:00 | 1677636000  |
| T002   | u01      | 80.0   | 2023-03-01 10:05:12 | 1677636312  |
| ...    | ...      | ...    | ...                 | ...         |
| T006   | u01      | 75.0   | 2023-03-01 10:27:30 | 1677637650  |
| T007   | u01      | 90.0   | 2023-03-01 11:00:00 | 1677639600  |
+--------+----------+--------+---------------------+-------------+

步骤2：自连接实现30分钟滑动窗口计数

对每笔交易，自连接找到同一用户在过去30分钟内的所有交易。

select t1.txn_id, t1.user_id, t1.amount, t1.txn_time,
       count(1) as cnt_30min
from t5_transaction_log t1
left join t5_transaction_log t2
    on t1.user_id = t2.user_id
    and unix_timestamp(t2.txn_time) between unix_timestamp(t1.txn_time) - 1800
        and unix_timestamp(t1.txn_time)
group by t1.txn_id, t1.user_id, t1.amount, t1.txn_time

T006 (10:27:30) 的前30分钟窗口是 09:57:30~~10:27:30，包含了 T001~~T006 共6笔交易，cnt_30min=6。

步骤3：筛选异常交易

执行SQL

select txn_id, user_id, amount, txn_time, cnt_30min
from (
    select t1.txn_id, t1.user_id, t1.amount, t1.txn_time,
           count(1) as cnt_30min
    from t5_transaction_log t1
    left join t5_transaction_log t2
        on t1.user_id = t2.user_id
        and unix_timestamp(t2.txn_time) between unix_timestamp(t1.txn_time) - 1800
            and unix_timestamp(t1.txn_time)
    group by t1.txn_id, t1.user_id, t1.amount, t1.txn_time
) t
where cnt_30min > 5
order by user_id, txn_time

执行结果

+--------+----------+--------+---------------------+-----------+
| txn_id | user_id  | amount |      txn_time       | cnt_30min |
+--------+----------+--------+---------------------+-----------+
| T006   | u01      | 75.0   | 2023-03-01 10:27:30 | 6         |
| T007   | u01      | 90.0   | 2023-03-01 11:00:00 | 7         |
+--------+----------+--------+---------------------+-----------+

u01 从 10:00 到 10:27 的30分钟内交易6次，触发异常告警。u02在30分钟内只有1笔（正常），u03在14:00-14:20有5笔但刚好等于阈值（未被标记，可调整阈值）。

五、常见坑点

坑1：用 INNER JOIN 会丢失正常交易

如果风控需求是"展示所有交易并标注是否异常"，应该用 LEFT JOIN 保留 cnt_30min=1 的正常交易。如果用 INNER JOIN 且加 WHERE cnt_30min > 5，就只能看到异常交易——两种需求不同。

坑2：自连接的性能问题

交易表通常有亿级数据，自连接的复杂度是 O(n²)。实际生产中会用 Spark Streaming 的滑动窗口或 Flink 的 TUMBLE/HOP 窗口替代。面试中展示自连接方案即可，但可以提一下"数据量大时考虑实时流处理框架"表现你的深度。

坑3：BETWEEN 的边界是闭区间

BETWEEN A-1800 AND A 包含了 A 时刻自身（A的txn_ts等于自身txn_ts，所以 cnt_30min 至少为1）。如果你想"严格前30分钟不包括当前交易"，用 >= A-1800 AND < A。

六、举一反三

调整时间窗口和阈值：把 1800秒改成 300秒（5分钟）和阈值改成 3次，检测更激进的盗刷行为
金额维度：在 WHERE 中加 AND t2.amount > 100，只看大额交易的短时高频，过滤小额测试交易
多维度异常评分：结合时间窗口次数 + 金额方差 + 商户类型变化等维度，做综合异常评分
实时流处理：用 Flink SQL 的 HOP(txn_time, INTERVAL '5' MINUTE, INTERVAL '30' MINUTE) 滑动窗口替代自连接，适应实时场景

七、知识点总结

考点	说明
unix_timestamp 时间标准化	将字符串时间转为秒数，便于数值运算
自连接 + BETWEEN 滑动窗口	模拟固定时间窗口，弥补 RANGE 窗口的限制
COUNT + GROUP BY 窗口计数	统计每个事件在时间窗口内的累计次数
WHERE 阈值过滤	筛选超过阈值的异常记录

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t5_transaction_log (
    txn_id   string COMMENT '交易ID',
    user_id  string COMMENT '用户ID',
    amount   decimal(10,2) COMMENT '交易金额',
    txn_time string COMMENT '交易时间'
) COMMENT '交易流水表';

INSERT INTO t5_transaction_log VALUES
('T001', 'u01', 50.0,  '2023-03-01 10:00:00'),
('T002', 'u01', 80.0,  '2023-03-01 10:05:12'),
('T003', 'u01', 120.0, '2023-03-01 10:10:30'),
('T004', 'u01', 35.0,  '2023-03-01 10:15:45'),
('T005', 'u01', 200.0, '2023-03-01 10:20:00'),
('T006', 'u01', 75.0,  '2023-03-01 10:27:30'),
('T007', 'u01', 90.0,  '2023-03-01 11:00:00'),
('T008', 'u02', 100.0, '2023-03-01 10:00:00'),
('T009', 'u02', 200.0, '2023-03-01 10:40:00'),
('T010', 'u02', 150.0, '2023-03-01 11:20:00'),
('T011', 'u03', 30.0,  '2023-03-01 14:00:00'),
('T012', 'u03', 45.0,  '2023-03-01 14:05:00'),
('T013', 'u03', 60.0,  '2023-03-01 14:10:00'),
('T014', 'u03', 50.0,  '2023-03-01 14:15:00'),
('T015', 'u03', 80.0,  '2023-03-01 14:20:00');

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题

一、题目背景​

二、题目​

三、思路分析​

四、逐步推导​

步骤1：将交易时间转为Unix时间戳​

步骤2：自连接实现30分钟滑动窗口计数​

步骤3：筛选异常交易​

五、常见坑点​

六、举一反三​

七、知识点总结​

八、建表语句和数据插入​

你可能还想看