T-SQL中数据集FIFO匹配：将While循环转换为集运算或CTE

当然可以用CTE结合累计求和的集运算方式实现FIFO匹配，完全替代WHILE循环，大幅提升大数据量下的性能。核心思路是利用累计金额区间匹配模拟FIFO的逐行抵扣逻辑，把循环的逐行处理转换成SQL原生的集运算。

实现逻辑 #

• 分别对收款/付款数据集，按AccountID分组，按业务规定的FIFO顺序（比如交易时间）排序，计算每条记录的累计金额，得到每条记录对应的金额覆盖区间（上一条累计值到当前累计值）。
• 对同一AccountID的收款、付款累计区间做关联，筛选出存在重叠的区间。
• 计算每个重叠区间的实际匹配金额，生成最终的FIFO匹配记录。

样例代码 #

假设收款表Receivables和付款表Payables结构如下：

-- 收款表结构
CREATE TABLE Receivables (
    AccountID INT,
    ReceivableID INT PRIMARY KEY,
    Amount DECIMAL(18,2),
    TransactionDate DATETIME
);

-- 付款表结构
CREATE TABLE Payables (
    AccountID INT,
    PayableID INT PRIMARY KEY,
    Amount DECIMAL(18,2),
    TransactionDate DATETIME
);

以下是CTE实现的FIFO匹配代码：

WITH ReceivableRunningTotals AS (
    SELECT 
        AccountID,
        ReceivableID,
        Amount,
        TransactionDate,
        -- 计算累计金额，当前记录的起始累计=上一条的结束累计
        SUM(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate, ReceivableID) AS RunningTotal,
        SUM(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate, ReceivableID) - Amount AS PreviousRunningTotal
    FROM Receivables
),
PayableRunningTotals AS (
    SELECT 
        AccountID,
        PayableID,
        Amount,
        TransactionDate,
        SUM(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate, PayableID) AS RunningTotal,
        SUM(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate, PayableID) - Amount AS PreviousRunningTotal
    FROM Payables
)
-- 关联两个累计数据集，计算匹配金额
SELECT 
    r.AccountID,
    r.ReceivableID,
    p.PayableID,
    -- 取重叠区间的最小差值作为匹配金额
    ABS(
        LEAST(r.RunningTotal, p.RunningTotal) - 
        GREATEST(r.PreviousRunningTotal, p.PreviousRunningTotal)
    ) AS FIFO_MatchedAmount,
    r.TransactionDate AS ReceivableDate,
    p.TransactionDate AS PayableDate
FROM ReceivableRunningTotals r
JOIN PayableRunningTotals p 
    ON r.AccountID = p.AccountID
    -- 匹配条件：两个区间存在重叠
    AND r.PreviousRunningTotal < p.RunningTotal
    AND p.PreviousRunningTotal < r.RunningTotal
ORDER BY r.AccountID, r.TransactionDate, p.TransactionDate;

关键说明 #

• 排序字段：ORDER BY TransactionDate, ReceivableID确保FIFO顺序的唯一性，避免同时间记录的排序歧义。
• 区间匹配逻辑：通过r.PreviousRunningTotal < p.RunningTotal AND p.PreviousRunningTotal < r.RunningTotal筛选出有重叠的累计区间，精准对应FIFO的逐行抵扣逻辑。
• 性能优势：集运算避免了WHILE循环的逐行迭代，SQL引擎可利用AccountID和排序字段的索引优化执行计划，数据量越大，性能提升越明显。

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