Azure SQL Server中基于滚动计算的3个月滑动平均值预测问题

我有一张用于跟踪Conversion的表，历史数据是经外部源验证的实际值，现在需要基于前3个月的数据预测未来的Conversion值（实际场景中预测周期远超示例的12个月）。使用Azure SQL Server，尝试过各类窗口函数及递归CTE，但均未得到预期结果，推测原因是需要基于已计算出的值进行后续计算。

源数据 #

预期数据 #

我的尝试 #

我尝试用递归CTE从首条记录开始关联源数据，当Conversion为NULL时计算前3个月的平均值，但数据库返回NULL，怀疑是计算时用了NULL而非已计算出的值。过程中试过左连接CTE但无法重复使用，也试过子查询关联主CTE。

代码如下：

;WITH cte AS (
  SELECT
    CalendarYear,
    CalendarMonth,
    ConversionRate,
    ROW_NUMBER() OVER (ORDER BY CalendarYear, CalendarMonth) AS rn
  FROM Data
), recursive_cte AS (
  SELECT
    cte.CalendarYear,
    cte.CalendarMonth,
    ConversionRate,
    cte.rn
  FROM cte
  WHERE rn = 1
  UNION ALL
  SELECT
    cte.CalendarYear,
    cte.CalendarMonth,
    CASE
      WHEN cte.ConversionRate IS NULL THEN
        CAST(avg(recursive_cte.ConversionRate) OVER (ORDER BY cte.[CalendarYear], cte.[CalendarMonth] ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS DECIMAL(5, 4))
      ELSE
        cte.ConversionRate
    END AS ConversionRate,
    cte.rn
  FROM cte
  JOIN recursive_cte ON cte.rn = recursive_cte.rn + 1
)
SELECT
  CalendarYear,
  CalendarMonth,
  ConversionRate
FROM recursive_cte;

解决方案 #

原递归CTE的问题在于：窗口函数无法访问递归过程中生成的前3条已计算记录，只能拿到当前递归层级的前一条数据。要实现基于已计算值的滚动平均，需要在递归过程中维护最近3个月的Conversion值，再用这些值计算平均值。

修正后的代码如下：

WITH cte AS (
    SELECT 
        CalendarYear,
        CalendarMonth,
        Conversion,
        ROW_NUMBER() OVER (ORDER BY CalendarYear, CalendarMonth) AS rn
    FROM Data
), recursive_cte AS (
    -- 初始化第一条记录，维护最近3个值的存储字段
    SELECT 
        CalendarYear,
        CalendarMonth,
        Conversion,
        rn,
        Conversion AS prev1,
        CAST(NULL AS DECIMAL(5,4)) AS prev2,
        CAST(NULL AS DECIMAL(5,4)) AS prev3
    FROM cte
    WHERE rn = 1

    UNION ALL

    SELECT
        c.CalendarYear,
        c.CalendarMonth,
        -- 有实际值则用实际值，否则计算前3个月的平均值
        CASE 
            WHEN c.Conversion IS NOT NULL THEN c.Conversion
            ELSE CAST((rc.prev1 + rc.prev2 + rc.prev3)/3 AS DECIMAL(5,4))
        END AS Conversion,
        c.rn,
        -- 更新最近3个值：当前值成为最新的prev1，原prev1、prev2依次后移
        CASE 
            WHEN c.Conversion IS NOT NULL THEN c.Conversion
            ELSE CAST((rc.prev1 + rc.prev2 + rc.prev3)/3 AS DECIMAL(5,4))
        END AS prev1,
        rc.prev1 AS prev2,
        rc.prev2 AS prev3
    FROM cte c
    JOIN recursive_cte rc ON c.rn = rc.rn + 1
)
SELECT 
    CalendarYear,
    CalendarMonth,
    Conversion
FROM recursive_cte
ORDER BY rn;

核心说明 #

• 递归过程中通过prev1、prev2、prev3三个字段，分别存储最近3个月的Conversion值（prev1为上月值，prev2为上上月值，prev3为上三月值）
• 当当前月份无实际值时，直接用这三个值的平均值作为预测结果
• 每次递归都会更新这三个字段，确保后续计算能使用最新的已生成预测值

内容的提问来源于stack exchange，提问作者Stephen E.