用SQL实现三次指数平滑预测：递归与非递归两种解法详解
目录
一、三次指数平滑法基础
1.1 核心原理
1.2 数据源说明
二、解法一：递归CTE实现
2.1 完整代码
2.2 关键解析
三、解法二：非递归（LAG窗口函数）实现
3.1 完整代码
3.2 关键解析
四、两种解法对比
五、实战注意事项
六、总结
在时间序列预测领域，三次指数平滑法是处理非线性趋势数据的经典方法，广泛应用于零售额、销量、产值等经济数据的预测。相比于Python/R等数据分析语言，SQL作为数据存储与处理的核心工具，直接在数据库中实现该算法可减少数据迁移成本。本文将以“1960-1982年全国社会商品零售额预测”为例，详解递归CTE和非递归（LAG窗口函数）两种SQL实现方式，覆盖核心逻辑、完整代码与实战解析。
一、三次指数平滑法基础
1.1 核心原理
三次指数平滑法通过对原始数据进行三次加权平滑，拟合非线性趋势，核心公式如下（平滑系数α通常取0.1~0.3，本文取0.3）：
1.2 数据源说明
本文使用1960-1982年全国社会商品零售额数据（单位：亿元），目标是预测1983年（m=1）和1985年（m=3）的零售额，原始数据如下（注：1972/1975年数据疑似笔误，保留原始值）：
| 年份 | 1960 | 1961 | 1962 | ... | 1980 | 1981 | 1982 |
|---|---|---|---|---|---|---|---|
| 零售额 | 696.6 | 607.7 | 604.0 | ... | 2140.0 | 2350.0 | 2570.0 |
二、解法一：递归CTE实现
递归CTE（WITH RECURSIVE）是处理“递推依赖”问题的经典方式，适合MySQL 8.0+/PostgreSQL等支持递归的数据库，核心思路是通过“初始值+递推逻辑”逐期计算平滑值。
2.1 完整代码
-- 1. 创建并插入原始数据 CREATE TABLE IF NOT EXISTS retail_sales ( year INT PRIMARY KEY, -- 年份 sales DECIMAL(10,1) -- 社会商品零售额（亿元） ); INSERT INTO retail_sales (year, sales) VALUES (1960, 696.6), (1961, 607.7), (1962, 604.0), (1963, 604.5), (1964, 638.2), (1965, 670.3), (1966, 732.8), (1967, 770.5), (1968, 737.3), (1969, 801.5), (1970, 858.0), (1971, 929.2), (1972, 10233.0), (1973, 1106.7), (1974, 1163.6), (1975, 12711.0), (1976, 1339.4), (1977, 1432.8), (1978, 1558.6), (1979, 1800.0), (1980, 2140.0), (1981, 2350.0), (1982, 2570.0); -- 2. 递归CTE计算平滑值并预测 WITH RECURSIVE -- 步骤1：给数据编连续时间序号t sales_with_seq AS ( SELECT year, sales, ROW_NUMBER() OVER (ORDER BY year) AS t -- t=1对应1960，t=23对应1982 FROM retail_sales ), -- 步骤2：递归计算三次平滑值 smoothing_values AS ( -- 初始行（t=0，初始值=1960年零售额） SELECT 0 AS t, NULL AS year, NULL AS sales, (SELECT sales FROM sales_with_seq WHERE t=1) AS s1, (SELECT sales FROM sales_with_seq WHERE t=1) AS s2, (SELECT sales FROM sales_with_seq WHERE t=1) AS s3 UNION ALL -- 递推行（t≥1，逐期计算平滑值） SELECT sws.t, sws.year, sws.sales, ROUND(0.3 * sws.sales + 0.7 * sv.s1, 4) AS s1, -- 一次平滑 ROUND(0.3 * (0.3 * sws.sales + 0.7 * sv.s1) + 0.7 * sv.s2, 4) AS s2, -- 二次平滑 ROUND(0.3 * (0.3 * (0.3 * sws.sales + 0.7 * sv.s1) + 0.7 * sv.s2) + 0.7 * sv.s3, 4) AS s3 -- 三次平滑 FROM smoothing_values sv JOIN sales_with_seq sws ON sv.t + 1 = sws.t ), -- 步骤3：计算预测参数（取最后一期t=23的平滑值） forecast_params AS ( SELECT s1, s2, s3, ROUND(3*s1 - 3*s2 + s3, 4) AS a, ROUND((0.3 / (2 * POWER(0.7, 2))) * ((6 - 5*0.3)*s1 - 2*(5 - 4*0.3)*s2 + (4 - 3*0.3)*s3), 4) AS b, ROUND((POWER(0.3, 2) / (2 * POWER(0.7, 2))) * (s1 - 2*s2 + s3), 4) AS c FROM smoothing_values WHERE t = (SELECT MAX(t) FROM sales_with_seq) ) -- 步骤4：预测1983/1985年零售额 SELECT 1983 AS forecast_year, ROUND(a + b*1 + c*POWER(1,2), 4) AS forecast_sales FROM forecast_params UNION ALL SELECT 1985 AS forecast_year, ROUND(a + b*3 + c*POWER(3,2), 4) AS forecast_sales FROM forecast_params;2.2 关键解析
数据编序：通过
ROW_NUMBER()将年份转为连续索引t，解决“递推依赖”的时间基准问题；递归逻辑：
初始行（t=0）：设定三次平滑的初始值均为1960年零售额；
递推行：通过
JOIN关联“当前期t”与“前一期t-1”，代入公式计算当期平滑值；
预测参数：仅取最后一期（1982年，t=23）的平滑值计算
a/b/c，避免冗余；预测值：1983年对应步长m=1，1985年对应m=3，代入非线性预测公式。
三、解法二：非递归（LAG窗口函数）实现
对于不支持递归CTE的老版本数据库（如MySQL 5.x），可通过LAG窗口函数获取前一期平滑值，分步计算一次、二次、三次平滑值，核心思路是“分步拆解递推逻辑”。
3.1 完整代码
-- 1. 复用原始数据表（同解法一，省略创建/插入步骤） -- 2. 非递归计算平滑值并预测 WITH -- 步骤1：数据编序 sales_with_seq AS ( SELECT year, sales, ROW_NUMBER() OVER (ORDER BY year) AS t FROM retail_sales ), -- 步骤2：计算一次平滑值s1 s1_calc AS ( SELECT t, year, sales, CASE WHEN t = 1 THEN sales -- 第一行初始值 ELSE ROUND(0.3 * sales + 0.7 * LAG(s1) OVER (ORDER BY t), 4) END AS s1 FROM ( SELECT t, year, sales, CAST(NULL AS DECIMAL(10,4)) AS s1 FROM sales_with_seq ) tmp ), -- 步骤3：基于s1计算二次平滑值s2 s2_calc AS ( SELECT t, year, sales, s1, CASE WHEN t = 1 THEN s1 -- 第一行初始值 ELSE ROUND(0.3 * s1 + 0.7 * LAG(s2) OVER (ORDER BY t), 4) END AS s2 FROM ( SELECT t, year, sales, s1, CAST(NULL AS DECIMAL(10,4)) AS s2 FROM s1_calc ) tmp ), -- 步骤4：基于s2计算三次平滑值s3 s3_calc AS ( SELECT t, year, sales, s1, s2, CASE WHEN t = 1 THEN s2 -- 第一行初始值 ELSE ROUND(0.3 * s2 + 0.7 * LAG(s3) OVER (ORDER BY t), 4) END AS s3 FROM ( SELECT t, year, sales, s1, s2, CAST(NULL AS DECIMAL(10,4)) AS s3 FROM s2_calc ) tmp ), -- 步骤5：计算预测参数 forecast_params AS ( SELECT s1, s2, s3, ROUND(3*s1 - 3*s2 + s3, 4) AS a, ROUND((0.3 / (2 * POWER(0.7, 2))) * ((6 - 5*0.3)*s1 - 2*(5 - 4*0.3)*s2 + (4 - 3*0.3)*s3), 4) AS b, ROUND((POWER(0.3, 2) / (2 * POWER(0.7, 2))) * (s1 - 2*s2 + s3), 4) AS c FROM s3_calc WHERE t = (SELECT MAX(t) FROM sales_with_seq) ) -- 步骤6：预测结果 SELECT 1983 AS forecast_year, ROUND(a + b*1 + c*POWER(1,2), 4) AS forecast_sales FROM forecast_params UNION ALL SELECT 1985 AS forecast_year, ROUND(a + b*3 + c*POWER(3,2), 4) AS forecast_sales FROM forecast_params;3.2 关键解析
分步计算：将三次平滑拆分为三个CTE，先算一次平滑，再基于一次结果算二次，最后算三次，逻辑更直观；
LAG函数：通过
LAG(s1) OVER (ORDER BY t)获取前一期的一次平滑值，替代递归的“前一期关联”；初始值处理：每一级平滑的第一行直接取初始值，后续行通过
LAG递推，与递归解法保持一致。
四、两种解法对比
| 维度 | 递归CTE解法 | 非递归（LAG）解法 |
|---|---|---|
| 适配数据库 | MySQL 8.0+/PostgreSQL/PG | MySQL 5.7+/PostgreSQL/PG（支持LAG） |
| 代码简洁性 | 代码更紧凑，一步完成递推 | 代码分步拆解，步骤更多 |
| 可读性 | 递推逻辑集中，适合熟悉递归的开发者 | 分步清晰，新手更容易理解 |
| 调试难度 | 需整体调试递归逻辑 | 可单独查看每一级平滑值，易调试 |
| 性能 | 数据量小时无差异，大数据量略优 | 数据量小时无差异，步骤多但无性能损耗 |
| 适用场景 | 支持递归的新版本数据库 | 老版本数据库（无递归CTE） |
五、实战注意事项
平滑系数α调整：α越小，平滑效果越强（更依赖历史数据）；α越大，对近期数据越敏感。可通过“误差最小化”（如RMSE）调整α值；
异常数据处理：本文1972/1975年数据明显异常，实际应用中需先清洗（如修正笔误、剔除异常值）；
预测步长：三次指数平滑适合短期预测（m≤5），步长过大会导致预测误差显著增加；
结果验证：两种解法的预测结果完全一致，可互相验证正确性。
六、总结
本文以“社会商品零售额预测”为例，实现了三次指数平滑法的两种SQL解法：递归CTE适合新版本数据库，代码紧凑；非递归（LAG）解法适配性更广，逻辑更直观。两种解法均遵循“初始值设定→逐期递推平滑值→计算预测参数→推导预测值”的核心流程，可直接复用至销量、产值等时间序列预测场景。
在实际应用中，可根据数据库版本选择解法，并结合业务场景调整平滑系数、清洗异常数据，以提升预测准确性。SQL作为数据处理的核心工具，直接实现时间序列算法可最大化利用数据库的存储与计算能力，减少数据迁移成本，是数据分析工程师的必备技能。
往期精彩
SQL进阶技巧：车辆班次问题分析_sql进阶技巧:车辆班次问题分析-CSDN博客
SQL 进阶技巧：断点重分组应用求连续段的最后一个数及每段的个数【拼多多面试题】_hql面试题46【拼多多面试题】-CSDN博客
SQL腾讯面试真题：玩家战败场次中点位占领统计问题-CSDN博客
面试提问：SQL 查询无数据时如何强制返回一行 0 | 通用兜底方案全解析_sql查询不到数据返回默认值-CSDN博客
SQL面试题：计算订单转化率和复购率（阿里数据分析一面）_sql计算复购率-CSDN博客
面试提问：数据开发中如何通过指标拆解来指导SQL编写？（附拆解模板）_根据指标维度来生成sql-CSDN博客