如何在SQL中实现带FixedValue重置的AccountEntry余额累计？

表结构说明 #

AccountEntry表用于HR系统存储各类账户（弹性账户、休假账户、调休账户等）的交易记录，结构及预期的Balance计算列如下（第三行仅用于展示PeriodId的分组作用，区分不同休假年度等）：

现有实现问题 #

此前尝试通过标量UDF结合计算列实现余额计算，代码如下：

create function dbo.UDF_GetAggregateBalance (@id int)
returns
int as
begin
    declare @b int;
    with result_CTE (id, balance) 
    as
    (
        SELECT
        id,
        SUM(e.value) 
          OVER (partition by e.accountperiodid ORDER BY e.useddate desc rows between current 
          row and unbounded following) balance
        FROM accountentry e
    )
    select @b=balance from result_CTE where id = @id    
    
    return (@b)

end

alter table accountentry add Balance as dbo.UDF_GetAggregateBalance(id)

该函数可实现基础余额累计，但无法处理FixedValue的重置逻辑：带有非0FixedValue的记录，其余额应等于该FixedValue值，后续记录的余额需基于此值计算（例如第5条记录余额应为60 + (-200) = -140）。

解决方案：支持重置逻辑的UDF实现 #

修改UDF，通过窗口函数标记每条记录所属的重置分组，再基于分组计算累计余额：

CREATE FUNCTION dbo.UDF_GetAggregateBalance (@id INT)
RETURNS INT
AS
BEGIN
    DECLARE @balance INT;

    -- 标记每条记录对应的最近重置点（FixedValue非0的记录ID）
    WITH ResetGroups AS (
        SELECT 
            id,
            PeriodId,
            Value,
            FixedValue,
            Date,
            MAX(CASE WHEN FixedValue <> 0 THEN id END) OVER (
                PARTITION BY PeriodId 
                ORDER BY Date 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS ResetId
        FROM AccountEntry
    ),
    -- 获取每个重置分组的基准余额
    ResetValues AS (
        SELECT 
            rg.id,
            rg.PeriodId,
            rg.Value,
            rg.Date,
            COALESCE(r.FixedValue, 0) AS BaseBalance
        FROM ResetGroups rg
        LEFT JOIN AccountEntry r ON rg.ResetId = r.id
    ),
    -- 计算分组内的累计余额
    RunningBalances AS (
        SELECT 
            id,
            BaseBalance + SUM(Value) OVER (
                PARTITION BY PeriodId, ResetId 
                ORDER BY Date 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS Balance
        FROM ResetValues
    )
    SELECT @balance = Balance 
    FROM RunningBalances 
    WHERE id = @id;

    RETURN @balance;
END

逻辑说明 #

1. 标记重置分组：通过窗口函数MAX(CASE ...)找到每条记录之前最近的FixedValue非0记录，以此作为分组标识，将重置点后的所有记录归为同一组。
2. 获取基准余额：关联重置点记录，获取其FixedValue作为该组的基准余额；无前置重置点的组，基准余额为0。
3. 计算累计余额：在每个分组内，按日期顺序累计Value，加上基准余额得到当前记录的最终余额。

重新执行ALTER TABLE语句更新计算列后，即可得到符合重置逻辑的余额值。

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