在SQL Server中对计算列使用LAG函数实现月期初期末金额计算

需求说明 #

需计算每月的期初金额（OpeningAmt）和期末金额（ClosingAmt），业务规则：

• 第一个月的期初金额为YearAmt
• 次月的期初金额等于上月的期末金额
• 每月期末金额 = 当月期初金额 + Increment - Used

原表结构 #

预期输出 #

尝试的查询语句 #

SELECT *,
CASE WHEN year1 = MonthYear THEN NULL ELSE 
LAG(StartMonthClosing,1) OVER (ORDER BY emp_code, col_code, MonthYear) 
END OtherMonthOpening
FROM (
SELECT emp_code, year1,year2,col_code, MonthYear ,YearAmt,  Increment,  Used,
CASE WHEN year1 = MySalaryTable.paidin THEN YearAmt ELSE NULL END StartMonthOpening,
CASE WHEN year1 = MySalaryTable.paidin THEN YearAmt + Increment - Used ELSE NULL END StartMonthClosing
FROM MyreimTable
INNER JOIN MySalaryTable ON MyreimTable.emp_code = MySalaryTable.emp_code
) 
AS P2

问题分析 #

你使用LAG函数未得到预期结果，原因是LAG只能获取上一行的已有值，无法处理递推计算的逻辑——每月的期末金额依赖上月期末，而上月期末本身又是计算出来的值，这种链式依赖用单纯的LAG无法实现，需要用累计窗口函数来处理。

解决方案 #

方案1：递推式计算 #

通过LAG获取上月期末作为本月期初，再计算本月期末：

SELECT 
    emp_code,
    year1,
    year2,
    col_code,
    paidin,
    YearAmt,
    Increment,
    Used,
    -- 期初：首月用YearAmt，其余用上月期末
    COALESCE(
        LAG(ClosingAmt) OVER (PARTITION BY emp_code, col_code ORDER BY paidin),
        YearAmt
    ) AS OpeningAmt,
    -- 期末：期初 + 增量 - 使用量
    COALESCE(
        LAG(ClosingAmt) OVER (PARTITION BY emp_code, col_code ORDER BY paidin),
        YearAmt
    ) + Increment - Used AS ClosingAmt
FROM 
    你的表名 -- 替换为实际表名
ORDER BY 
    emp_code, col_code, paidin;

方案2：累计求和式计算 #

直接计算累计净增加额，逻辑更简洁：

SELECT 
    emp_code,
    year1,
    year2,
    col_code,
    paidin,
    YearAmt,
    Increment,
    Used,
    -- 期初：初始金额 + 之前所有月份的净增加额
    YearAmt + COALESCE(
        SUM(Increment - Used) OVER (
            PARTITION BY emp_code, col_code 
            ORDER BY paidin 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ),
        0
    ) AS OpeningAmt,
    -- 期末：初始金额 + 到当前月的累计净增加额
    YearAmt + SUM(Increment - Used) OVER (
        PARTITION BY emp_code, col_code 
        ORDER BY paidin
    ) AS ClosingAmt
FROM 
    你的表名 -- 替换为实际表名
ORDER BY 
    emp_code, col_code, paidin;

关键说明 #

• PARTITION BY emp_code, col_code：按员工和类别分组，确保每个分组独立计算，避免跨员工/类别的数据干扰。
• ORDER BY paidin：按月份排序，保证计算顺序符合时间逻辑。
• 累计求和方案无需依赖递推，直接通过窗口函数计算累计值，性能和可读性更优。

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