如何检测表中交易日期早于上一行的记录并插入临时表

核心逻辑 #

通过对比按TransID排序后的相邻交易日期，找出TransID更大但交易时间更早的异常记录，将这些记录（含对比的前序交易信息）插入临时表，便于追踪。

实现代码（支持窗口函数的数据库：MySQL 8+、SQL Server、PostgreSQL等） #

1. 创建临时表（如果不存在）：

CREATE TEMPORARY TABLE IF NOT EXISTS ExceptionalTransactions (
    TransID INT,
    TransactionDate DATETIME,
    Amount DECIMAL(10,2),
    PreviousTransID INT,
    PreviousTransactionDate DATETIME
);

2. 插入异常交易记录：

INSERT INTO ExceptionalTransactions (TransID, TransactionDate, Amount, PreviousTransID, PreviousTransactionDate)
SELECT 
    curr.TransID,
    curr.Date AS TransactionDate,
    curr.Amount,
    curr.PreviousTransID,
    curr.PreviousTransactionDate
FROM (
    SELECT 
        TransID,
        Date,
        Amount,
        -- 获取按TransID排序的前一条交易ID和日期
        LAG(TransID) OVER (ORDER BY TransID) AS PreviousTransID,
        LAG(Date) OVER (ORDER BY TransID) AS PreviousTransactionDate
    FROM Transactions -- 替换为你的交易表名
) curr
-- 筛选当前交易日期早于前序交易的异常
WHERE curr.Date < curr.PreviousTransactionDate;

说明 #

• 临时表存储了异常交易的核心信息，同时保留了前序交易的ID和日期，方便直接对比异常原因
• 窗口函数LAG()的方式性能更优，尤其是在数据量较大的场景下

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