带特殊条件的小计计算：汇总每笔IN交易后的OUT交易金额

原始交易数据表 #

需求说明 #

对每一笔IN交易，汇总其发生之后的所有OUT交易的金额，期望输出结果如下：

目标输出表 #

实现方案（SQL示例） #

以下是两种常用的实现方法，适用于主流关系型数据库：

方法1：关联子查询 #

逻辑直观，针对每一条IN交易，直接查询其之后所有OUT交易的金额总和：

SELECT 
    t1.id,
    t1.flow,
    t1.amount,
    t1.date,
    (SELECT SUM(t2.amount) 
     FROM transactions t2 
     WHERE t2.flow = 'OUT' 
       AND t2.date > t1.date) AS out_amount
FROM transactions t1
WHERE t1.flow = 'IN';

方法2：窗口函数（适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

先预计算OUT交易的倒序累计金额，再关联匹配到对应IN交易，数据量大时性能更优：

WITH out_transactions AS (
    SELECT 
        date,
        SUM(amount) OVER (ORDER BY date DESC) AS cumulative_out
    FROM transactions
    WHERE flow = 'OUT'
    ORDER BY date DESC
)
SELECT 
    t.id,
    t.flow,
    t.amount,
    t.date,
    COALESCE(MAX(ot.cumulative_out), 0) AS out_amount
FROM transactions t
LEFT JOIN out_transactions ot ON ot.date > t.date
WHERE t.flow = 'IN'
GROUP BY t.id, t.flow, t.amount, t.date
ORDER BY t.date;

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