如何用SQL筛选任意24小时内交易超2笔的账户

核心需求 #

从包含AccountNumber、TransactionID、DateTime字段的交易表中，找出任意连续24小时窗口内交易次数≥3笔的账户，跨天的时间段也要覆盖（比如1月24日23:58、23:59和次日00:05的三笔交易，该账户需被纳入结果）。

解决思路 #

你之前用LAG()函数仅能对比相邻交易的时间差，导致分组后带出无关交易，核心问题是没先精准定位符合条件的账户，再关联交易记录。下面提供两种实用方案：

方案1：窗口排序+关联校验 #

先给每个账户的交易按时间排序，然后检查当前交易和它往前数第2笔交易的时间差——如果这个差值≤24小时，说明这三笔交易落在同一个24小时窗口里，该账户就符合要求。之后用这个账户列表过滤原表，就能只获取目标账户的交易（或仅账户号）。

示例SQL（以PostgreSQL为例，其他数据库调整时间函数即可）：

-- 标记每个账户的交易顺序
WITH ranked_trans AS (
    SELECT 
        AccountNumber,
        TransactionID,
        DateTime,
        ROW_NUMBER() OVER (PARTITION BY AccountNumber ORDER BY DateTime) AS trans_rank
    FROM transactions
),
-- 筛选存在连续3笔交易在24小时内的账户
valid_accounts AS (
    SELECT DISTINCT rt1.AccountNumber
    FROM ranked_trans rt1
    JOIN ranked_trans rt2 
        ON rt1.AccountNumber = rt2.AccountNumber 
        AND rt1.trans_rank = rt2.trans_rank + 2
    WHERE rt1.DateTime - rt2.DateTime <= INTERVAL '24 hours'
)
-- 获取目标账户的所有交易（仅要账户号直接SELECT * FROM valid_accounts即可）
SELECT t.*
FROM transactions t
JOIN valid_accounts va ON t.AccountNumber = va.AccountNumber;

方案2：滑动窗口聚合（需数据库支持） #

用RANGE类型的窗口函数，直接统计每笔交易时间点往前24小时内的交易次数，只要存在次数≥3的情况，该账户就符合条件。这种方法更直观，但要求数据库支持基于时间间隔的RANGE窗口（比如PostgreSQL 11+、MySQL 8.0+）。

示例SQL：

WITH trans_window_counts AS (
    SELECT 
        AccountNumber,
        TransactionID,
        COUNT(*) OVER (
            PARTITION BY AccountNumber 
            ORDER BY DateTime 
            RANGE BETWEEN INTERVAL '24 hours' PRECEDING AND CURRENT ROW
        ) AS cnt_in_24h
    FROM transactions
)
-- 关联原表获取目标交易，去重避免重复账户
SELECT DISTINCT t.*
FROM transactions t
JOIN trans_window_counts twc 
    ON t.AccountNumber = twc.AccountNumber 
    AND t.TransactionID = twc.TransactionID
WHERE twc.cnt_in_24h >= 3;

注意点 #

• 两种方案都先锁定符合条件的账户，再关联原表，彻底避免带出无关交易。
• 不同数据库的时间函数语法有差异，比如MySQL可用TIMESTAMPDIFF(HOUR, rt2.DateTime, rt1.DateTime) <= 24替代PostgreSQL的时间差写法。

内容的提问来源于stack exchange，提问作者Justin Dhinakar