如何在SQL Database中获取时间差5-10分钟内的重复交易记录？

针对你提到的需求，我们可以利用SQL的窗口函数（LAG()）来计算同一BillId下相邻交易的时间间隔，进而筛选出符合条件的重复交易，同时也能轻松生成指定日期范围的统计报表。

一、识别5-6分钟内的重复交易 #

假设你的交易表名为Transactions，核心字段为BillId（账单ID）、Status（交易状态，比如Paid）、TransDateTime（交易时间）。下面的查询会标记出每笔交易是否为重复交易，并关联对应的原始有效交易：

WITH TransactionWithPrev AS (
    SELECT 
        BillId,
        Status,
        TransDateTime,
        -- 获取同一BillId的上一笔交易时间
        LAG(TransDateTime) OVER (PARTITION BY BillId ORDER BY TransDateTime) AS PrevTransDateTime,
        -- 计算与上一笔交易的时间差（分钟）
        DATEDIFF(MINUTE, LAG(TransDateTime) OVER (PARTITION BY BillId ORDER BY TransDateTime), TransDateTime) AS MinDiff
    FROM Transactions
    WHERE Status = 'Paid' -- 只筛选已支付的交易
)
SELECT 
    BillId,
    TransDateTime,
    CASE 
        WHEN MinDiff BETWEEN 5 AND 6 THEN '重复交易'
        ELSE '有效交易'
    END AS TransactionType,
    PrevTransDateTime AS OriginalTransTime,
    MinDiff AS IntervalMinutes
FROM TransactionWithPrev
ORDER BY BillId, TransDateTime;

逻辑说明： #

• LAG(TransDateTime) OVER (PARTITION BY BillId ORDER BY TransDateTime)：按BillId分组、交易时间排序，获取当前交易的上一笔交易时间。
• DATEDIFF(MINUTE, ...)：计算当前交易与上一笔交易的时间差（单位：分钟）。
• 通过CASE语句标记交易类型：时间差在5-6分钟区间内的标记为重复交易，其余标记为有效交易。

对应你给出的示例数据，这个查询会准确匹配出符合条件的重复交易（注：示例中第二笔交易与第一笔间隔3分钟，不会被标记为重复，第三笔与第二笔间隔6分钟，会被标记为重复）。

二、生成指定日期范围的报表（统计平均时间差） #

如果你需要统计指定日期范围内，重复交易的平均时间差（比如5-10分钟区间），可以用下面的查询：

DECLARE @StartDate DATE = '2018-04-18';
DECLARE @EndDate DATE = '2018-04-18';

WITH TransactionWithPrev AS (
    SELECT 
        BillId,
        TransDateTime,
        DATEDIFF(MINUTE, LAG(TransDateTime) OVER (PARTITION BY BillId ORDER BY TransDateTime), TransDateTime) AS MinDiff
    FROM Transactions
    WHERE 
        Status = 'Paid'
        AND CAST(TransDateTime AS DATE) BETWEEN @StartDate AND @EndDate
)
SELECT 
    BillId,
    COUNT(*) AS TotalTransactions,
    SUM(CASE WHEN MinDiff BETWEEN 5 AND 10 THEN 1 ELSE 0 END) AS RepeatTransactionsInRange,
    AVG(CASE WHEN MinDiff BETWEEN 5 AND 10 THEN MinDiff ELSE NULL END) AS AvgIntervalMinutes
FROM TransactionWithPrev
GROUP BY BillId
HAVING SUM(CASE WHEN MinDiff BETWEEN 5 AND 10 THEN 1 ELSE 0 END) > 0; -- 只保留有符合条件重复交易的账单

逻辑说明： #

• 通过声明变量@StartDate和@EndDate快速指定报表的日期范围。
• 先筛选出指定日期内的交易，再计算相邻交易的时间差。
• 统计每个BillId的总交易数、符合5-10分钟间隔的重复交易数，以及这些重复交易的平均时间差。

如果需要按日期维度汇总报表，只需将分组字段调整为CAST(TransDateTime AS DATE)即可。

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