第一次接到“用SQL做三次指数平滑预测”的需求时我第一反应是这怕不是在为难SQL。后来翻完资料、跑通代码才发现这事不仅能做而且做起来还挺顺手只是很多人在“递归CTE”和“窗口函数”两种路线之间反复横跳最后两边都没吃透。这篇就把一个核心问题彻底拆开在SQL里落地三次指数平滑递归解法和非递归解法到底分别怎么写、怎么选、会踩哪些坑顺便把预测值的计算、误差评估、参数调优一起串起来。1. 三次指数平滑模型拆解三层递推到底在算什么1.1 从移动平均到加权平均指数平滑的直觉指数平滑本质上是一种加权移动平均核心思想是“离当前时刻越近的数据越值得信任”。普通移动平均给窗口内每个点相同的权重指数平滑则让权重按时间距离指数衰减衰减速度由平滑系数α控制。一次指数平滑的公式很干净S_t α * Y_t (1 - α) * S_{t-1}其中S_t是t时刻的一次平滑值Y_t是t时刻的实际观测值α取值通常在0到1之间。这个公式看着简单却有一个很妙的性质任意一期的S_t都可以展开成历史所有观测值的加权和权重按(1-α)的指数幂递减。所以它不像滑动平均那样有一个“窗口边界”而是把整个历史都装进了计算里只是越远的数据影响力越小。如果你觉得这个公式太抽象可以想象成滚雪球每一期的新数据是刚下的新雪老数据是原来的雪球。新雪有多少黏上去取决于你给的力度α雪球滚过多少圈老的雪就在表面只剩下薄薄一层。指数平滑里的“记忆长度”不是固定天数而是一个约等于1/α的等效窗口。1.2 三次平滑一次、二次、三次各管什么事一次指数平滑适合没有趋势、没有周期、均值基本稳定的序列。可实际业务里的数据很少这么老实销量在涨、成本在降、用户数在加速增长。这时候一次平滑值永远慢半拍它追不上趋势更看不出“趋势的变化趋势”。二次指数平滑在S_t的基础上再做一次平滑得到双平滑序列S_2_t。它和一次平滑值之间的差值可以估计出当前的线性趋势。只要你看到的走势基本是“直线往上/往下”二次指数平滑基本够用。但遇到增长在加快、增速本身在变化的数据比如新产品上线后的陡增曲线二次平滑仍然不够。三次指数平滑就是把“平滑之后再做平滑”再重复一遍得到三层平滑值。三次平滑值保留的信息更多足够拟合带“加速度”或者说带曲率的曲线。从数学信号的角度理解一次平滑捕捉水平二次平滑捕捉速度三次平滑捕捉加速度。三层区别可以用一张表说清楚平滑层级捕捉的信息适合的数据形态一次平滑当前水平平稳序列无趋势无周期二次平滑水平 线性趋势近似直线上升/下降三次平滑水平 趋势 曲率/加速度增速在变化的曲线型数据1.3 预测方程与参数含义模型定了预测公式就顺着出来了。Brown三次指数平滑的预测方程是F_{tm} a_t m * b_t (1/2) * m^2 * c_t其中a_t、b_t、c_t由三层平滑值S1_t、S2_t、S3_t组合得到a_t 3 * S1_t - 3 * S2_t S3_t b_t α / (2 * (1 - α)^2) * ((6 - 5α) * S1_t - (10 - 8α) * S2_t (4 - 3α) * S3_t) c_t α^2 / (1 - α)^2 * (S1_t - 2 * S2_t S3_t)m是预测步长F_{tm}就是未来第m期的预测值。这里顺带说明一点很多人会把“三次指数平滑”直接等同于Holt-Winters三参数模型后者还有一个季节分量。如果数据存在明显的年度/月度周期性确实应该优先考虑Holt-Winters但本文后面代码围绕的是Brown三次多项式平滑适合无周期但带加速趋势的数据。两类模型在思想上同源落地SQL时Holt-Winters由于多了季节滞后项非递归展开会痛苦得多所以视角聚焦在Brown模型上更利于一次把问题讲透。2. 两条SQL路线递归与非递归的核心差异2.1 路线一逐行迭代模拟原始递推递归解法很好理解既然公式本来就是一行依赖上一行那SQL里就用递归CTE一行一行推下去。每一行都能拿到上一行的S1、S2、S3然后算出当前行新的S1、S2、S3。这几乎是照着公式翻译思路最简单调试也直观你甚至可以只取前5行在纸上手算一遍和SQL结果对拍。缺点也很明显递归CTE有层级限制数据量一上来要么超限报错要么性能难看而且同一个递归分支里三个平滑值互相依赖写起来要稍微绕一下不能直接写“新S2 α * 新S1 ...”因为同一层SELECT里不能引用本层刚算出来的列别名。2.2 路线二把递推展开成“加权历史之和”非递归解法换了个视角先不做逐行迭代而是把S_t的递推展开成“历史观测值乘以权重再求和”。一次指数平滑的展开式是S_t (1-α)^t * S_0 α * Σ_{i1..t} (1-α)^(t-i) * Y_i这个式子意味着如果我能对每一条历史数据算出一个权重再按行做累计求和就能拿到S_t。窗口函数天然擅长这类“前缀和”计算于是递归就不必要了。二次、三次平滑则是对上一层的平滑序列再套一次同样操作也就是对S1序列求加权前缀和得到S2再对S2序列求加权前缀和得到S3形成一个“列级流水线”。非递归解法的好处是不受递归深度限制SQL结构清晰性能通常更好代价是理解门槛更高而且直接按展开式实现时逆权重r^{-t}在长序列下会溢出需要根据数据量选不同写法。2.3 怎么选性能、稳定性、可维护性对照两种路线各有领地不存在绝对的最佳方案。我实际用下来是这样维度递归解法非递归解法理解门槛低公式直译中高需要理解展开式代码量少三行递推多需要多层CTE递归深度限制有长序列可能爆无计算复杂度O(n)逐行O(n log n)取决于实现数值稳定性稳定短序列没问题长序列要防溢出适用数据量百级到千级千级可优化万级建议别硬算判断标准很简单数据量小、想快速验证效果就递归数据量中等但要求可复现、可读性好就考虑非递归数据量很大时两个方案都该升华为“分批/分组建模”别指望单表硬扫。递归的优势是“贴脸理解”非递归的优势是“一次成列”各有各的舒服位置。3. 递归解法实战单层递归CTE一次算三层3.1 数据准备与初始值策略先看演示数据。假设某产品近12个月的销量如下带明显的加速上升趋势适合用三次指数平滑建模。期数t销量y112021323141415251486160717081829175101901120512212初始值在指数平滑里是个绕不开的话题。最省事的做法是让S1_0、S2_0、S3_0都等于第一期观测值120。如果数据量少初始值对前几期影响很大如果数据量多经过几十轮衰减影响会小到忽略不计。后续文章会在坑位章节再展开说。平滑系数α先取0.3。这个值不高也不低给历史数据留下更多记忆用来演示递推趋势很合适。3.2 递归CTE的完整SQL与逐步解释演示数据放进表sample_dataCREATE TABLE sample_data ( t INT PRIMARY KEY, y DECIMAL(10, 2) ); INSERT INTO sample_data VALUES (1, 120), (2, 132), (3, 141), (4, 152), (5, 148), (6, 160), (7, 170), (8, 182), (9, 175), (10, 190), (11, 205), (12, 212);递归CTE可以这样写WITH RECURSIVE smooth AS ( SELECT t, y, y AS s1, y AS s2, y AS s3 FROM sample_data WHERE t 1 UNION ALL SELECT cur.t, cur.y, 0.3 * cur.y 0.7 * prev.s1 AS s1, 0.3 * (0.3 * cur.y 0.7 * prev.s1) 0.7 * prev.s2 AS s2, 0.3 * ( 0.3 * (0.3 * cur.y 0.7 * prev.s1) 0.7 * prev.s2 ) 0.7 * prev.s3 AS s3 FROM sample_data cur JOIN smooth prev ON cur.t prev.t 1 ) SELECT t, y, ROUND(s1, 4) AS s1, ROUND(s2, 4) AS s2, ROUND(s3, 4) AS s3 FROM smooth ORDER BY t;这里有一个初学者必定踩的坑你不能在同一个SELECT里直接写“新S2 0.3 * 新S1 0.7 * 上一行S2”因为SQL的SELECT里列别名不能在本层互相引用。解法就是把新S1的完整表达式再展开到新S2里新S3再展开到新S2的表达式里看起来像套娃但数据库能老老实实算完。前5行结果如下tys1s2s31120.00120.0000120.0000120.00002132.00123.6000121.0800120.32403141.00128.8200123.4020121.24744152.00135.7740127.1136123.00735148.00139.4418130.8121125.3487这组数字你可以手算核对一遍逻辑对得上说明递归实现没问题。3.3 递归解法的边界递归深度与性能陷阱递归CTE不是无底洞。数据库对递归层级都有上限PostgreSQL默认写死1000层左右可以通过session级参数临时调大但调大有风险数据太长时递归一层层JOIN的性能也会比较糟。演示数据12行当然看不出来换成12000行递归CTE的性能就会明显劣化甚至报错。我踩过的一个实际状态是把一张80万行的订单表直接套递归CTE做逐单平滑数据库跑到一半直接爆内存。这种场景下正确做法是先按产品/店铺维度分组每组数据量控制在几百行以内再用窗口函数配合递归分区或循环建模。递归适合“小批多次”的任务不适合“大批一次梭哈”。4. 非递归解法实战从笛卡尔展开到窗口函数优化4.1 版本一CROSS JOIN展开看得懂的穷举版非递归的第一版思路最朴素既然S_t是历史所有Y_i的加权和那我直接把“目标期t”和“历史期i”全部组合出来按权重求和。下面这段SQL先算一次平滑值S1。WITH params AS ( SELECT 0.3 AS alpha, 0.7 AS r, 120.0 AS s0 ), s1_direct AS ( SELECT base.t, params.s0 * POWER(params.r, base.t) params.alpha * SUM(POWER(params.r, base.t - hist.t) * hist.y) AS s1 FROM sample_data base CROSS JOIN sample_data hist CROSS JOIN params WHERE hist.t base.t GROUP BY base.t, params.s0, params.alpha, params.r ) SELECT * FROM s1_direct ORDER BY t;这个版本的逻辑非常简单外层是目标期base内层是历史期hist只允许hist.t base.t然后按目标期分组求和。权重POWER(r, base.t - hist.t)永远小于或等于1数值上很稳。缺点也直白两表做笛卡尔积再过滤12行数据没事1200行就变成144万组合12000行更是天文数字。这个版本适合数据量小、想验证展开式是否正确或者作为教学推导用。4.2 版本二逆权重前缀和跑得快的优化版优化思路来自指数加权展开里的一个代数技巧。展开式里有r^{t-i}其中t是目标期i是历史期。直接算r^{t-i}需要CROSS JOIN。但换个写法r^{t-i} r^t * r^{-i}r^t只依赖目标期tr^{-i}只依赖历史期i。于是可以先把每一行的r^{-i} * Y_i算出来然后用窗口函数的累计求和SUM一次性拿到“从第1行到当前行”的加权前缀和最后再乘以r^t。这样一次窗口扫描就能算完一整列S1。SQL长这样WITH params AS ( SELECT 0.3 AS alpha, 0.7 AS r, 120.0 AS s1_0, 120.0 AS s2_0, 120.0 AS s3_0 ), base AS ( SELECT t, y, POWER(r, t) AS r_t, POWER(r, -t) AS inv_r_t FROM sample_data CROSS JOIN params ), s1 AS ( SELECT t, y, s1_0 * r_t alpha * r_t * SUM(inv_r_t * y) OVER ( ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS s1 FROM base CROSS JOIN params ) SELECT t, y, ROUND(s1, 4) AS s1 FROM s1 ORDER BY t;这个版本跑出来的结果和递归版完全一致验证前几行就能对上t2时S1123.6t3时S1128.82。4.3 两层非递归的复用S2、S3怎么叠加三次平滑的关键在于把S1当作新的输入序列再做一遍同样的指数加权前缀和得到S2再把S2当作输入序列得到S3。所以代码是流水线式的每个CTE把上一层的输出吞进去。WITH params AS ( SELECT 0.3 AS alpha, 0.7 AS r, 120.0 AS s1_0, 120.0 AS s2_0, 120.0 AS s3_0 ), base AS ( SELECT t, y, POWER(r, t) AS r_t, POWER(r, -t) AS inv_r_t FROM sample_data CROSS JOIN params ), s1 AS ( SELECT t, y, s1_0 * r_t alpha * r_t * SUM(inv_r_t * y) OVER ( ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS s1 FROM base CROSS JOIN params ), s2 AS ( SELECT t, y, s1, s2_0 * r_t alpha * r_t * SUM(inv_r_t * s1) OVER ( ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS s2 FROM s1 CROSS JOIN params ), s3 AS ( SELECT t, y, s1, s2, s3_0 * r_t alpha * r_t * SUM(inv_r_t * s2) OVER ( ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS s3 FROM s2 CROSS JOIN params ) SELECT t, y, ROUND(s1, 4) AS s1, ROUND(s2, 4) AS s2, ROUND(s3, 4) AS s3 FROM s3 ORDER BY t;这个版本的优点是用窗口函数替代了递归不会有递归层级限制执行计划也能被数据库优化得很漂亮。和递归版的输出对比数值完全一致。实际业务里如果能保证数据行数在几百以内我通常优先写这个版本。4.4 数值稳定性问题为什么长序列要小心非递归版本二有一个隐藏炸弹POWER(r, -t)这个逆权重当r0.7、t300时值大约是10的54次方t1000时直接让float类型溢出。数据库numeric精度高能撑一阵但终究有限。我在一次实际测试里把数据加到800行r0.7逆权重前缀和版本在PostgreSQL里跑出来的结果开始出现“Infinity”整列平滑值废掉。换成版本一的笛卡尔展开就不会溢因为POWER(r, base.t - hist.t)的指数是正数权重不超过1。但笛卡尔展开性能差。所以非递归两种写法本质上是“用精度换性能”和“用性能换精度”的关系。如果数据量在100行以内放开用窗口版如果数据量到几百上千行且r偏小优先考虑递归版或者笛卡尔展开版。超长序列的更优策略是分块把数据拆成多个小段分别建模别指望一个SQL锤到底。5. 预测计算、误差评估与参数调优5.1 从三次平滑值推导预测值拿到末行S1、S2、S3之后预测值不是硬拍出来的而是套预测方程。用前面的演示数据跑完末行大约是这样的值S1_12 191.40 S2_12 173.50 S3_12 158.65套进公式a 3*191.40 - 3*173.50 158.65 ≈ 212.35 b 0.3061 * ((6-1.5)*191.40 - (10-2.4)*173.50 (4-0.9)*158.65) ≈ 10.57 c 0.1837 * (191.40 - 2*173.50 158.65) ≈ 0.56所以未来两期预测F_13 ≈ 212.35 10.57 0.28 ≈ 223.20 F_14 ≈ 212.35 21.14 1.12 ≈ 234.61和实际增长趋势对比预测值比较平滑没有把最后一期的波动看得过重。这就是指数平滑的特性它不会因为某期突然大涨就大幅跳变权重衰减让“稳健”成为默认值。5.2 评测指标MAPE、RMSE的SQL写法模型到底行不行不能只看预测值顺不顺眼。最常用的两个误差指标MAPE平均绝对百分比误差和RMSE均方根误差。WITH predictions AS ( SELECT t, y, 0.3 * y 0.7 * LAG(y, 1) OVER (ORDER BY t) AS pred FROM sample_data ) SELECT ROUND(AVG(ABS(y - pred) / y) * 100, 4) AS mape, ROUND(SQRT(AVG(POWER(y - pred, 2))), 4) AS rmse FROM predictions WHERE pred IS NOT NULL;这个简单示例用的是滞后一期预测实际建模时要先算完成整的S1/S2/S3序列再对每个历史时点计算拟合值然后算残差。思路不变只是把预测公式替换进去。5.3 参数搜索用SQL跑α网格α选多少直接决定平滑结果。0.1和0.9跑出来的模型完全像两个模型。手工试几个值不靠谱更规范的做法是做一个α网格搜索让SQL把每个α的误差算出来再挑最小的。WITH params_grid AS ( SELECT alpha, POWER(1 - alpha, t) AS r_t, POWER(1 - alpha, -t) AS inv_r_t FROM ( SELECT 0.1 AS alpha UNION ALL SELECT 0.2 UNION ALL SELECT 0.3 UNION ALL SELECT 0.4 UNION ALL SELECT 0.5 UNION ALL SELECT 0.6 UNION ALL SELECT 0.7 UNION ALL SELECT 0.8 UNION ALL SELECT 0.9 ) a CROSS JOIN sample_data ) -- 后续按前面非递归流水线的方式分别对每个alpha算S1/S2/S3再计算误差实际项目里α网格可以精确到0.05或0.01代价只是SQL跑得久一点。搜索结束后把最小MAPE对应的α挑出来再跑一遍完整模型得到最终预测值。这一步非常值得做因为三次平滑对α比一次平滑更敏感凭感觉选数很容易翻车。6. 实际落地中的五个坑与排查心得6.1 初始值不同前几期结果差异很大初始值S1_0、S2_0、S3_0怎么设对序列前10期的平滑值影响很大尤其数据只有三十几期时。我常用的做法是取前几期平均值比如用前3期或前5期的均值作为初始值比单纯用第一期稳。数据量够多时初始值的影响会随r^t迅速衰减这时候用第一期也问题不大。项目里如果初值敏感建议跑敏感性分析换三组初始值看预测结果差多少。差得大就要谨慎差得小说明模型已经稳健。6.2 递归CTE报错超出递归层级数据行数超过递归上限时数据库会直接报“recursion query iteration limit exceeded”。临时解法是调数据库参数但根治方案是换非递归写法或者把长序列截断成多个短段。我在项目里见过最无语的情况明明只有2000行数据递归CTE却在第102层报错原因是数据里t字段存在空洞递归条件和JOIN条件没对齐导致递归链路断掉又续上。排查方法是先检查时间列是否连续不连续就先补行否则递归链条会被NULL打断。6.3 非递归窗口函数结果偏大或溢出逆权重POWER(r, -t)在长序列里爆炸前面已经说过。另一种“偏大”发生在numeric精度不够、窗口累计项太大时结果不是报错而是悄悄失真。排查技巧很土但有效拿前5行结果手算一遍和SQL输出对拍对不上就说明数值精度出了问题。想偷懒就直接对比递归版输出两个版本结果一致才说明非递归版可信。6.4 数据缺值和异常值平滑前的预处理指数平滑对缺失值非常敏感。某期缺失如果直接跳过等价于把那个点当成0塞进去会把后面整个平滑序列拉歪。正确做法是先用前值填充或线性插值把缺口补齐再做平滑。异常值同理某天销量突降指数平滑虽然会平滑掉一部分但权重再小也有影响。建议在进入模型前先做一次简单的离群值检测超过均值三倍标准差的点做缩尾处理。SQL里可以用窗口函数算均值和标准差再UPDATE标记异常值预处理这一步不值得省。6.5 超过一条产品线/门店怎么办分组建模SQL真实业务不会只有一条序列可能是几百个SKU、几百个门店。这时候先按业务维度分组每个分组单独建模。非递归版天然好做分组窗口函数加PARTITION BY各组独立排序互不干扰。递归版做分组麻烦因为递归CTE的“递归起点”不好按组初始化。所以我的建议是多序列场景优先用非递归窗口版每个组的数据量控制在几百行以内然后并行处理。如果组数太多加一层分区裁剪只处理需要预测的组没必要全量扫描。写到这里回到最初那个问题SQL能不能做三次指数平滑预测能而且递归和非递归都能做只是侧重点不同。如果你的序列短、想快速验证递归CTE最顺手如果你对性能有要求、数据达到几百上千行非递归窗口函数版是更舒服的选择如果数据量大又带分组那就把非递归和分组编码结合起来。我个人在实际项目里最常用的组合是“窗口函数非递归版做主体计算 前几期均值做初始值 α网格搜索选参”这套组合在大部分中短期业务预测场景下都够稳。最后再提醒一句平滑模型的结果一定要结合业务判断不能只看误差指标预测值再漂亮业务逻辑说不通也是白搭。