SQL Server按ID分区根据事件类型条件计算账户期末余额实现方法

实现逻辑说明 #

按照需求，每个ID单独计算余额，按事件发生时间先后顺序处理，事件类型对应计算规则如下：

• Open事件：期末余额等于本次事件的Event_Amount
• Payment事件：以上一期期末余额减去本次Event_Amount
• Reversal事件：以上一期期末余额加上本次Event_Amount
• Closed事件：期末余额直接记为0

实现SQL #

假设你的源表名为Account_Events，存储事件类型的字段为Event_Remark，可根据实际表结构调整字段名：

WITH Event_Adjustment AS (
    SELECT
        ID,
        Date_Opened,
        Opening_Amount,
        Event_Date,
        Event_Amount,
        Event_Remark,
        -- 映射各事件的金额调整值
        CASE Event_Remark
            WHEN 'Open' THEN Event_Amount
            WHEN 'Payment' THEN -1 * Event_Amount
            WHEN 'Reversal' THEN Event_Amount
            ELSE 0
        END AS Adjust_Amount,
        -- 标记销户事件
        CASE WHEN Event_Remark = 'Closed' THEN 1 ELSE 0 END AS Is_Closed
    FROM Account_Events
),
Running_Balance_Calc AS (
    SELECT
        *,
        -- 按ID分区、按事件日期排序计算累计余额
        SUM(Adjust_Amount) OVER (
            PARTITION BY ID 
            ORDER BY Event_Date ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS Unadjusted_Balance
    FROM Event_Adjustment
)
SELECT
    ID,
    Date_Opened,
    Opening_Amount,
    Event_Date,
    Event_Amount,
    Event_Remark,
    -- 销户事件直接返回0，其余返回累计余额
    CASE 
        WHEN Is_Closed = 1 THEN 0 
        ELSE Unadjusted_Balance 
    END AS Closing_Balance
FROM Running_Balance_Calc
-- 输出结果按ID、事件时间排序，方便核对
ORDER BY ID, Event_Date ASC;

注意事项 #

• 如果业务要求Open事件优先取Opening_Amount作为初始余额，仅需将Event_Adjustment中Open事件的返回值改为Opening_Amount即可
• 若同一ID存在多个相同Event_Date的事件，可在OVER子句的ORDER BY后新增额外排序字段（如事件自增ID、录入时间），保证处理顺序符合业务要求
• 该语法支持SQL Server 2012及以上版本，基于窗口函数实现无需自连接，大数据量下性能表现优异

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