如何用SQL实现递归移动平均预测（支持多art_id）

问题背景 #

现有sells表结构及数据如下：

CREATE TABLE sells
    (date_ DATETIME,
     art_id int, 
     basal_sell int)
;

INSERT INTO sells
    (date_, art_id , basal_sell)
VALUES ('2022-12-1', 1, 2),
       ('2022-12-2', 1, 1),
       ('2022-12-3', 1, 2),
       ('2022-12-4', 1, 3),
       ('2022-12-5', 1, 4);

需求要求：

• 第一次迭代：计算每个art_id最后5天（含当天）的basal_sell移动平均值，作为次日的预测值，例如(2+1+2+3+4)/5=2.4，对应2022-12-06的预测值。
• 后续迭代：每次将上一次的预测值加入数据集，重新计算最后5天的移动平均值作为次日预测，例如(1+2+3+4+2.4)/5=2.48，对应2022-12-07的预测值。
• 递归直到指定未来日期（如2022-12-09），最终输出包含历史数据和预测数据的结果表。

原尝试的递归CTE存在多art_id场景下的逻辑错误，无法为每个art_id独立生成预测值。

正确SQL实现方案 #

以下代码基于SQL Server语法实现，支持多art_id，并能递归生成到指定日期的预测数据：

-- 定义目标预测截止日期
DECLARE @TargetDate DATETIME = '2022-12-09';

WITH RecursiveSales AS (
    -- 锚点成员：原始历史数据
    SELECT 
        date_,
        art_id,
        CAST(basal_sell AS DECIMAL(10,2)) AS basal_sell,
        -- 标记是否为预测数据
        CAST(0 AS BIT) AS is_forecast
    FROM sells

    UNION ALL

    -- 递归成员：生成每个art_id的次日预测值
    SELECT
        DATEADD(DAY, 1, rs.date_) AS date_,
        rs.art_id,
        -- 计算最近5天（含当前）的移动平均值作为预测值
        CAST(AVG(rs2.basal_sell) OVER (PARTITION BY rs2.art_id ORDER BY rs2.date_ ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS DECIMAL(10,2)) AS basal_sell,
        CAST(1 AS BIT) AS is_forecast
    FROM (
        -- 获取每个art_id的最新日期记录
        SELECT 
            art_id,
            MAX(date_) AS latest_date
        FROM RecursiveSales
        GROUP BY art_id
    ) latest
    JOIN RecursiveSales rs ON rs.art_id = latest.art_id AND rs.date_ = latest.latest_date
    -- 关联所有该art_id的历史+已生成的预测数据，用于计算移动平均
    JOIN RecursiveSales rs2 ON rs2.art_id = rs.art_id
    -- 终止条件：生成的日期不超过目标日期
    WHERE DATEADD(DAY, 1, rs.date_) <= @TargetDate
)
-- 按art_id和日期排序输出
SELECT 
    date_,
    art_id,
    basal_sell,
    is_forecast
FROM RecursiveSales
ORDER BY art_id, date_
OPTION (MAXRECURSION 0); -- 允许无限递归（根据实际情况调整）

代码关键点说明 #

1. 锚点成员：将原始数据的basal_sell转为小数类型，避免整数除法精度丢失，同时添加is_forecast标记区分历史数据和预测数据。
2. 递归成员：
   • 先通过子查询获取每个art_id的最新记录日期，确保为每个商品独立生成下一天的预测。
   • 关联该商品的所有历史+已生成的预测数据，使用窗口函数计算最近5天的移动平均值，作为次日的预测值。
   • 添加终止条件，确保生成的日期不超过指定的@TargetDate。
3. 多ART_ID支持：通过GROUP BY art_id和PARTITION BY art_id实现每个商品独立计算预测值，不会出现单top 1导致的只处理一个商品的问题。
4. 精度处理：使用DECIMAL(10,2)保证预测值的小数精度，避免整数运算的误差。

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