SQL月度OpenPipe值计算：解决上月值缺失导致的NULL问题

你的需求是递推计算月度OpenPipe值：第一个月用OpenPipe+NewPipe-ClosingDeal计算，后续月份基于上月的MonthlyOpenPipe值，加上NewPipe-ClosingDeal得到当月结果。之前用lag()函数的方法失效，是因为除2022年1月外，其他月份无初始MonthlyOpenPipe值，导致lag()返回NULL无法参与计算。

可以用递归CTE实现递推逻辑，具体SQL如下：

WITH RankedData AS (
    -- 按年月排序并生成序号，确保递归顺序正确
    SELECT 
        Year,
        Month,
        NewPipe,
        ClosingDeal,
        OpenPipe,
        ROW_NUMBER() OVER (ORDER BY Year, Month) AS rn
    FROM TB1
),
RecursiveOpenPipe AS (
    -- 锚点：取第一个月（2022年1月），计算初始MonthlyOpenPipe
    SELECT 
        Year,
        Month,
        NewPipe,
        ClosingDeal,
        OpenPipe,
        CAST(OpenPipe + NewPipe - ClosingDeal AS DECIMAL(18,2)) AS MonthlyOpenPipe,
        rn
    FROM RankedData
    WHERE Year = 2022 AND Month = 1

    UNION ALL

    -- 递归：用上月计算结果推导当月值
    SELECT 
        rd.Year,
        rd.Month,
        rd.NewPipe,
        rd.ClosingDeal,
        rd.OpenPipe,
        CAST(rop.MonthlyOpenPipe + rd.NewPipe - rd.ClosingDeal AS DECIMAL(18,2)) AS MonthlyOpenPipe,
        rd.rn
    FROM RankedData rd
    INNER JOIN RecursiveOpenPipe rop ON rd.rn = rop.rn + 1
)
SELECT Year, Month, NewPipe, ClosingDeal, OpenPipe, MonthlyOpenPipe
FROM RecursiveOpenPipe
ORDER BY Year, Month;

逻辑说明： #

• RankedData：给数据按年月排序并生成序号，保证递归时能按时间顺序逐行处理。
• RecursiveOpenPipe：
  • 锚点部分取初始月份数据，用规则计算第一个月的MonthlyOpenPipe。
  • 递归部分通过序号关联上一行的计算结果，持续递推后续月份的值，避免NULL问题。
• 最终查询递归结果，得到完整的月度OpenPipe计算值。

如果你的数据库支持DATEFROMPARTS函数，也可以用DATEFROMPARTS(Year, Month, 1)生成日期来排序，替代序号，逻辑会更严谨。

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