SQL Server中如何筛选扣除退款后的有效交易行？

要实现同一ApplicationID下抵消金额总和为0的交易、保留剩余有效行的需求，可以通过窗口函数计算累计收入和总退款金额，筛选出未被抵消的收入行。以下是具体实现：

WITH transaction_cte AS (
    SELECT
        CustomerCode,
        ApplicationID,
        InvoiceNumber,
        Date,
        Revenue,
        -- 计算当前订单的总剩余金额（收入减退款）
        SUM(Revenue) OVER (PARTITION BY ApplicationID) AS total_remaining,
        -- 按日期排序，累计计算当前及之前的收入总和
        SUM(CASE WHEN Revenue > 0 THEN Revenue ELSE 0 END) OVER (
            PARTITION BY ApplicationID 
            ORDER BY Date, InvoiceNumber 
            ROWS UNBOUNDED PRECEDING
        ) AS running_positive_sum,
        -- 计算当前订单的总退款绝对值
        SUM(CASE WHEN Revenue < 0 THEN ABS(Revenue) ELSE 0 END) OVER (PARTITION BY ApplicationID) AS total_refund_amount
    FROM [Table] -- 替换为你的实际表名
)
SELECT
    CustomerCode,
    ApplicationID,
    InvoiceNumber,
    Date,
    Revenue
FROM transaction_cte
WHERE
    -- 仅保留总剩余金额为正的订单行
    total_remaining > 0
    -- 筛选未被退款抵消的收入行：累计收入超过总退款金额的部分
    AND Revenue > 0
    AND running_positive_sum > total_refund_amount
ORDER BY ApplicationID, Date;

逻辑说明： #

• CTE部分：
  • total_remaining：计算每个ApplicationID的最终剩余金额，若该值≤0则整单无有效交易，直接排除。
  • running_positive_sum：对每个订单下的收入交易（Revenue>0）按日期排序，逐行累计求和，用于判断哪部分收入未被退款抵消。
  • total_refund_amount：统计每个订单的总退款金额（取绝对值），作为抵消阈值。
• 筛选条件：
  • 只保留总剩余为正的订单行。
  • 仅保留收入行中累计收入超过总退款的部分——这部分就是未被退款抵消的有效交易。
  • 无退款的订单（total_refund_amount=0）会自动保留所有收入行，因为running_positive_sum必然大于0。

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