SQL 反欺诈异常交易检测：时序异常识别（蚂蚁集团面试题）

Q: SQL 反欺诈异常交易检测：时序异常识别（蚂蚁集团面试题）

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

一、题目

现有一张交易流水表 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 支持 count() over (range between ...) 窗口函数，直接按时间范围聚合，比自连接更高效：

时间标准化：unix_timestamp(txn_time) 将时间转为 Unix 秒数作为窗口排序键
滑动窗口计数：count(1) over (partition by user_id order by unix_timestamp(txn_time) range between 1800 preceding and current row) 统计每笔交易前30分钟内的累计次数
阈值筛选：where cnt_30min > 5 标记异常交易

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

三、逐步推导

步骤1：窗口函数计算30分钟滑动计数

先用 unix_timestamp(txn_time) 将时间转为秒数，再用 count(1) over (partition by user_id order by unix_timestamp range between 1800 preceding and current row) 统计每笔交易前30分钟内的交易次数。range between 1800 preceding 自动包含当前行及之前1800秒内的所有行。

执行SQL

select
    txn_id, user_id, amount, txn_time,
    count(1) over (
        partition by user_id
        order by unix_timestamp(txn_time)
        range between 1800 preceding and current row
    ) as cnt_30min
from t5_transaction_log
order by user_id, txn_time

执行结果

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

T006 在 10:27:30 时，前30分钟窗口(09:57:30~~10:27:30)覆盖了 T001~~T006 共6笔，cnt_30min=6。T010 是 u02 在 11:20:00 的交易，30分钟前无交易，cnt_30min=1。不同用户通过 partition by user_id 独立计算，互不影响。

步骤2：筛选异常交易

执行SQL

select txn_id, user_id, amount, txn_time, cnt_30min
from (
    select
        txn_id, user_id, amount, txn_time,
        count(1) over (
            partition by user_id
            order by unix_timestamp(txn_time)
            range between 1800 preceding and current row
        ) as cnt_30min
    from t5_transaction_log
) 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：RANGE BETWEEN 需要 ORDER BY 为数值类型

range between 1800 preceding 要求 order by 的表达式是数值类型。unix_timestamp(txn_time) 返回 Long 秒数，正好满足。如果直接 order by txn_time（string 类型），range 会报错或行为不确定。这是 Spark SQL 窗口函数的关键约束。

坑2：partition by user_id 不能遗漏

必须 partition by user_id，否则所有用户的交易会混在一个时间轴上，u01 的交易可能被 u02 的计入——不同用户之间的交易没有时间关联。加了 partition by 后，每个用户的滑动窗口独立计算。

坑3：RANGE 包含当前行及边界

range between 1800 preceding and current row 包含当前行自身（cnt_30min 至少为1）。如果需要"严格前30分钟不包括当前交易"，应改用 range between 1800 preceding and 1 preceding，但此时当前行没有前序时 cnt 为 0（第一笔交易前无历史）。

五、知识点总结

考点	说明
count(1) over (range between) 滑动窗口	窗口函数按时间范围聚合，统计固定时间窗口内的累计次数
unix_timestamp 时间标准化	将字符串时间转为 Long 秒数，作为 range 窗口的数值排序键
partition by 用户隔离	每个用户独立计算滑动窗口，避免跨用户数据串扰
range between ... preceding and current row	包含当前行及之前指定范围内的所有行，闭区间

六、建表语句和数据插入

点击展开 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：窗口函数计算30分钟滑动计数​

步骤2：筛选异常交易​

四、常见坑点​

五、知识点总结​

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

你可能还想看