做数据分析的人几乎都绕不开一个需求移动平均值。无论是盯销售趋势、算库存周转周期还是看服务器负载曲线移动平均都能帮我们把毛刺抹平看清楚数据背后的真实方向。可同一个需求落到不同人手上无非是三种最常见的落地方案SQL、Power BI、Excel各有各的脾气也各有各的坑。有些坑不是你不会写而是你根本没意识到默认值的边界在哪里。这几年我在数据团队里见过太多类似场景业务方甩过来一句“给我算一个移动平均”需求看着简单结果不同人交付的口径对不上——有人按“含当天往前数7天算”有人觉得应该是“最近7个交易日”还有人直接在Excel里拉了个固定区间错位之后数据对不上。问题从来不是谁算错了而是移动平均值在不同工具里的实现方式差异非常大尤其体现在边界处理、缺失日期和筛选联动上。所以这篇文章我想把SQL、Power BI和Excel里做移动平均的完整思路串一遍说清楚每种写法的原理也把那些你多半会踩的坑提前摆出来。1. 为什么移动平均值总是出现在报表需求里1.1 从一条订单曲线开始理解移动平均值假设你现在经营一家小网店每天订单量一会儿300单、一会儿80单呈锯齿状上下跳动。你要给老板汇报“到底卖得怎么样”直接拿昨天单量说事今天跌40%明天涨50%谁都扛不住。这时候把周期拉长算“每周平均每天订单量”趋势感立刻出来了。这个“每周平均”就是移动平均。更严格的说法是对一个有序的时间序列取固定宽度的窗口窗口随时间逐步向后滑动在每个位置计算窗口内数值的平均值。比如7日均值就是从某一天起往前数7天把这7天的值加总除以7然后每天计算一个才叫“移动”平均——它不是全量的总平均而是一段一段碾过去的滚动窗口。为什么这种算法好使因为业务数据里往往同时混着长期趋势、周期波动和随机噪声移动平均本质是一个低通滤波器把高频的随机波动滤掉留下低频趋势。比如和双11效应这种大促周期叠加再看剔除噪声之后的曲线哪里到了拐点、哪里开始回暖一眼就能判断出来。相比单纯看原始值移动平均给决策提供的是一个更稳的参考平面。1.2 简单移动平均与加权移动平均的差别入门阶段大家算的基本都是简单移动平均也就是窗口内每个数值权重相同过去7天每天各占1/7。它实现起来最简单适用性也最广。但有些场景里近期数据显然比远期更重要。比如库存补货预测昨天卖了多少和高三天前卖了多少对明天需求的参考价值完全不一样。于是就有了加权移动平均给每个位置乘上不同的权重越靠近当前日期的权重越大。更讲究一点的做法是指数移动平均EMA它本质上给历史数据分配按时间衰减的权重早期数据虽然不会消失但影响越来越小。文章后面讨论SQL、Power BI和Excel里的实现时大部分场景用的是简单移动平均。因为加权移动平均本质上是把AVG拆成SUM/COUNT、再往里塞权重理解了简单版之后加权只是多加几个乘数和分子分母的问题不存在本质障碍。1.3 为什么是SQL、Power BI和Excel这三兄弟因为这三种工具分别代表了数据处理的三种形态。SQL是后端引擎负责大规模数据的批处理和定时计算做出来的结果可靠、可复用适合跑在数仓任务里Power BI是前端展示层衡量值跟着报表筛选器动态变化最适合做交互式图表和自助分析Excel则是个轻量沙盒业务同学拿到明细后想快速看一眼走势在单元格里拖出来比找人写脚本快得多。三种阵地各有各的逻辑。SQL靠窗口函数把一组行圈成一个滑动的集合Power BI依赖DAX里与日期表配合的时间智能函数Excel则靠区域引用和OFFSET这类引用函数实现“窗口跟着位置移动”的意图。同一个业务定义在不同工具里默认边界的处理方式完全不同这正是接下来要逐个拆开的东西。2. SQL一条窗口函数搞定滚动统计2.1 没有窗口函数时SQL会多么痛苦先说一个结论现在主流数据库无论是PG、MySQL 8.0还是SQL Server 2012以后都支持窗口函数写移动平均已经不需要再用老办法了。但没有窗口函数的年代是怎么做的最常见的是自关联。设想一张每日销售表包含销售日期、销售额。你要算7日均值思路是将每日表和自己关联让每一天匹配“从自己当天向前7天”的每一天然后GROUP BY汇总。-- 老式自连接写法逻辑等价于7日移动平均 SELECT t1.sale_date, AVG(t2.sale_amount) AS mov_avg_7d FROM daily_sales t1 JOIN daily_sales t2 ON t2.sale_date BETWEEN DATEADD(DAY, -6, t1.sale_date) AND t1.sale_date GROUP BY t1.sale_date;这个写法能跑但理解成本很高。一旦数据量到百万行这个自连接的开销非常吓人因为每一天都要去扫描过去7天的记录索引稍微不合适就直接变成笛卡尔爆炸。而且一旦你后面想改成10日均、20日均关联条件里的-6要一个个改出错概率极高。窗口函数把这个问题压缩成了“在排好序的集合上按相对位置取范围”。不用自关联不用GROUP BY性能直线上升逻辑也更贴近业务描述本身——这就是它成为现代SQL标准里的核心工具的原因。2.2 ROWS BETWEEN 的边界语法拆解用窗口函数写移动平均核心语法长这样SELECT sale_date, sale_amount, AVG(sale_amount) OVER ( PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS avg_7d FROM daily_sales;PARTITION BY你肯定熟悉它做的是分组ORDER BY决定窗口内记录的顺序真正需要重点研究的是ROWS BETWEEN这一段。ROWS BETWEEN 6 PRECEDING AND CURRENT ROW表示窗口由当前行的前6行加当前行组成总共7行对应7日均值。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示从分组内第一行到当前行等价于累进平均。ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING表示当前行到分组末尾用于后向窗口。这里有一个极其容易踩的坑ROWS和RANGE的区别。ROWS是按物理行数来定位窗口的你写6 PRECEDING就是实打实往前数6行RANGE是按排序键的值范围来定位的如果ORDER BY的键有重复值会把排序值相同的行全部纳入窗口。举个例子如果同一天有多条销售记录ORDER BY sale_date那么用RANGE BETWEEN 6 PRECEDING时“6天前”这个边界会把那一天所有记录都包含进来哪怕实际超过了7行用ROWS则是严格的行数。做移动平均时我几乎总是推荐ROWS因为它更符合“取N个数据点”的业务直觉除非你明确想要的是“时间范围内所有点”。2.3 可复用的SQL模板与边界处理一份能直接抄作业的SQL模板大概长这样WITH daily_data AS ( SELECT sale_date, SUM(sale_amount) AS daily_amount FROM orders WHERE sale_date BETWEEN 2025-01-01 AND 2025-03-31 GROUP BY sale_date ) SELECT sale_date, daily_amount, AVG(daily_amount) OVER ( ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS moving_avg_7d FROM daily_data ORDER BY sale_date;很多新手会问数据前6行不够7个数据点怎么办窗口函数默认会“有多少算多少”比如第一行只有一个值那它算出来的就是当天的值除以1第二行除以2直到第七天才真正变成除以7。如果你需要严格保证“窗口不满7个点就返回空值”可以加一层判断WITH calced AS ( SELECT sale_date, daily_amount, COUNT(*) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS cnt, AVG(daily_amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d FROM daily_data ) SELECT sale_date, daily_amount, CASE WHEN cnt 7 THEN moving_avg_7d END AS moving_avg_7d FROM calced;SQL里另一种常见变体是取“过去7天但不含今天”这在计算某些运营指标时很有用比如今天要看的指标不该包含还没结束的今天。写法改成ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING即可窗口同样不影响当天数据。这是一个我想特别强调的细节移动平均“含不含当天”在业务上是有明确讲究的在SQL里只差一个PRECEDING的数字但如果你不看需求就顺手写了含当天版本很容易被业务方揪出来。3. Power BIDAX里的移动平均要处理三层上下文3.1 时间智能函数的正确姿势Power BI和SQL最大的不同在于SQL里的数据是一次性算完放在结果表里Power BI里的度量值则是在报表交互过程中动态计算的而且计算会跟随外部筛选器比如切片器选了某个月、某个地区实时变化。所以写DAX移动平均时你不光要表达“窗口取哪几行”还要考虑筛选上下文对窗口的影响。最基础的时间智能函数有三个DATESINPERIOD、DATESBETWEEN和DATEADD。移动平均的常用套路是先定义当前筛选上下文里“唯一的日期”再借助DATESINPERIOD向前扩展出一个日期集合最后对集合求平均值。没有日期表时也可以写但你会经常碰到“某天找不到数据导致平均偏小”的诡异情况。我强烈建议在模型里单独建一张连续的日历表并且和事实表之间建立关系日期字段完全来自日历表。原因后面会说这里先记住Power BI的时间函数都是建立在连续日期表基础上的否则会默认成表对象本身的行。3.2 核心DAX公式的逐行解读最简单、也最容易让人看懂的移动平均度量值是这样7日均值 VAR CurrentDate MAX(日历表[Date]) VAR WindowStart CurrentDate - 6 VAR WindowEnd CurrentDate RETURN CALCULATE( [日销售额], FILTER( ALL(日历表), 日历表[Date] WindowStart 日历表[Date] WindowEnd ) ) / [7日应计天数]注意这个写法包含两个部分分子是窗口内的销售总额分母是多少天。至于分母到底是7还是窗口内的实际日期天数取决于你的业务定义。如果业务定义是“滚动7个自然日”那么分母可以直接写7如果业务定义是“滚动7个业务日”只算有实际业务发生的日期则应该写7日业务日均值 VAR CurrentDate MAX(日历表[Date]) VAR WindowStart CurrentDate - 6 VAR WindowEnd CurrentDate RETURN CALCULATE( AVERAGEX( VALUES(日历表[Date]), [日销售额] ), DATESBETWEEN(日历表[Date], WindowStart, WindowEnd) )这里的AVERAGEX会对窗口内每个有效日期逐一计算[日销售额]再对结果求平均值。它天然回避了“无销售日期”的干扰只要日历表上这个日期在窗口内哪怕当天没订单度量值也会返回空AVERAGEX会忽略空值只对有销售的日期求平均。这在数据颗粒度不一致的报表里非常实用。如果你用的Power BI版本比较新也可以用DAX窗口函数WINDOW7日均值(窗口函数) CALCULATE( [日销售额], WINDOW(-6, DAY, 0, DAY, ORDERBY(日历表[Date], ASC)) ) / 7这个写法和SQL里的窗口函数已经是同一个世界观了指定相对当前行的向前6天和当前行再按日期升序排列。虽然WINDOW函数在部分Power BI版本中还需要特定开启条件但如果你能用语法表达力是最接近SQL的。3.3 图表联动与筛选器带来的隐性坑Power BI里最常见的移动平均翻车现场有三类。第一类是日期表缺失。模型里没有连续的日期表直接用订单表的日期做X轴结果日历上有很多空洞。移动平均的窗口是“按日期往前推”还是“按事实表里上一行往前推”这个歧义会瞬间击穿报表的准确性。规范做法永远是一张完整的日历表然后用时间智能函数保证窗口是基于日历的连续区间。第二类是外部筛选器泄漏。假设你的图表里放了一个“地区”筛选器筛选了华东地区那么移动平均窗口是否也只统计华东的数据多数场景下我们确实希望“筛选到华东就只算华东的7日均值”这反而说明了上下文传递的重要性。但如果你写的公式里用了ALL函数比如ALL(销售表)它会把地区筛选器给强制移除这时移动平均就会变成全国数据曲线在钻取时一下失联让人一脸懵。第三类是半累加性指标的困惑。销售额可以累加但某些指标比如“在线用户数”代表某个瞬间的状态是不可以跨日期简单累加再取平均的。如果业务方要求用户数的移动平均你需要先想清楚口径是“窗口内每秒用户数的平均”还是“窗口内每天最高同时在线的平均”不要盲目套用销售公式。我通常会在模型里给移动平均度量值写注释把“含当天”“按自然日7天”“分母按7天”这几个参数写清楚。Power BI没有“元数据注释”这种强规范但对团队协作来说这些注释能省下后面大量的口径扯皮。4. ExcelAVERAGE、OFFSET与数据分析工具的组合拳4.1 静态区域公式与下拉填充的注意事项Excel是很多人最早接触移动平均的地方也是最容易写出“半个错误结果”的地方。假设A列是日期B列是每日销售额你想在C列算7日均值。最简单粗暴的公式AVERAGE(B2:B8)往下拖一格公式会自动变成AVERAGE(B3:B9)这就已经是移动平均了每一行对应一个以该行开头、往后7天的窗口。如果你把公式放在当前日期那一行想算“过去7天含当天”的均值则应该是AVERAGE(B2:B8) AUTH...其实把公式错位一格就能实现两种方向。要把“含当天”算进去就把公式放在当前行窗口从当前行往前数7行比如在C8单元格写AVERAGE(B2:B8)这里的窗口是第2行到第8行也就是8行等于7天不对2到8是7个值吗2、3、4、5、6、7、8 7个值对包含当前行往前6个历史行。所以如果想在C8表示第8天的7日均值写AVERAGE(B2:B8)在C9写AVERAGE(B3:B9)依次类推。往下拖的时候需要确认公式引用的起点是否同步移动了如果手工输入固定了B2而没有随行变化就会出现整列算出来的都是同一组数的灾难。静态公式最大的问题是你必须自己保证行数足够。数据从第10行开始有空值、或者中间插了两行汇总行公式立刻错位。所以静态公式适合“一次性算一眼”不适合做长期在用、会持续更新的模板。4.2 OFFSET实现的自适应滑动窗口让Excel里的移动平均真正“智能”起来核心函数是OFFSET。OFFSET返回一个基于指定起点、偏移行数列数、高度宽度的引用区域。利用它可以构造一个永远跟着当前行跑的窗口。比如在C8单元格写AVERAGE(OFFSET(B8,-6,0,7,1))意思是以B8作为起点向上偏移6行取高度为7、宽度为1的区域也就是B2到B8再对这个区域求平均。往下拖到C9起点自动变成B9窗口自动变成B3到B9。这样公式不再依赖“2:8”这种硬编码行号中间插入行时公式也会跟着调整鲁棒性好很多。如果你想做一个动态的“最后7天”结果不需要逐行拉公式可以直接用COUNTA来定位数据最后一行AVERAGE(OFFSET(B1, COUNTA(B:B)-7, 0, 7, 1))这个公式会在你每次往B列末尾新增数据时自动给你算出最新7个数据点的均值。我常用它做一个“今日生机”驾驶舱看板在图表旁边随时显示最近交易日移动平均不用每天手动拉区域。OFFSET也有坑它是易失性函数每次工作表里任何单元格变化它都会重算当数据量大到几万行且同时存在几十个OFFSET公式时文件会明显卡顿。这种情况下建议改用Excel 365里更新的TAKE、DROP、FILTER动态数组函数做更稳健的引用或者在Power Query / Power BI里计算后只把结果导入Excel。4.3 把移动平均画成图表的两种做法Excel里呈现移动平均有两种思路一种是和SQL一样生成一列数据再画折线图另一种是直接用Excel图表自带的功能画“趋势线”。如果你已经算好了C列移动平均值直接选中A列日期和C列数据插入折线图和原始值放在同一个图里对比效果很直观。原始值用浅灰色细线移动平均用深色粗线这种图表在汇报时极大降低解释成本。如果不想要额外的辅助列Excel里还可以直接给原始折线添加“移动平均趋势线”。操作路径是选中已有折线图右键“添加趋势线”趋势线选项里选择“移动平均”周期填7。Excel内部会自动按窗口7计算并画线而且不污染你的数据表。但这条线拿到的是一个折线趋势对象不能直接用于后续计算或导出适合随手看一眼不适合做正式报表素材。有一个我反复踩过的小坑给折线图加移动平均趋势线时Excel默认是按“趋势线两侧各取一些点”的方式拟合起点和终点附近的线会和手算值完全对不上。如果要用图表趋势线做正式结论务必和手算的移动平均比对一下别直接拿对象输出的数值当口径。5. 同一份数据跑三个工具结果到底差在哪5.1 边界窗口的默认值差异我拿一份虚构数据做过一个对照测试一张每日销售表连续30天每天都有销售额偶尔缺两天。分别用SQL窗口函数、Power BI DATESINPERIOD、Excel公式三种方式算“7日滚动均值”在数据完整的区域三个结果完全一致。但一旦出现缺失日期分歧就来了。SQL的窗口函数ROWS BETWEEN 6 PRECEDING AND CURRENT ROW按物理行计数缺了日期它无所谓窗口仍然是7行但如果数据里本来就是每天一行它其实是7个连续日期没问题。可一旦订单表里有桥架、有停业日、有补录的合并记录物理行和日期日就不是一回事。这时SQL的结果会变成“最近7条记录的平均”而不是“最近7天的平均”。Power BI的DATESINPERIOD基于连续日历表遇到缺失日期窗口依然按7个自然日推进但AVERAGEX对空值处理方式又会和分母写成7产生两个不同结果。Excel的固定公式只认行列位置你中间删掉一行整个结果链条全部错位。同一组数据跑三个工具数字完全对不上的根源往往就是“行”和“日”这两种语义混在一起了。我的建议是在动手写公式之前先把业务定义写成一个短语比如“每个日期取往前7个自然日中有销量的日均值含当天”然后三端都围绕这一定义去实现。5.2 性能和维护成本对比从性能角度讲SQL窗口函数在百万行级别依然能跑得很快这是后端引擎的优势。Power BI的DAX计算受限于报表渲染和筛选器复杂度数据量大时移动平均度量值会产生大量上下文调用适当的颗粒度预聚合比直接怼明细更明智。Excel则更受单机算力约束好几万行数据配合OFFSET数组公式滑动起来明显发烫这种情况我一般建议用Power Query把明细先按日期汇总好再回填到Excel。从维护角度看三者的差别很明显SQL脚本适合放在定时任务里改一次参数全部生效Power BI度量值的维护成本集中在DAX公式和模型关系上一旦日期表规范后续新增类似指标非常快Excel公式看着简单但它的格式错位问题在多人协作场景中几乎无法控制。我见过太多共享Excel文件里一个人往中间插了三行后面所有移动平均列全偏一格的惨剧。5.3 一个直接可以照抄的选型清单我自己的判断逻辑大致是这样的判断项推荐方案数据量在50万行以上且需要定时跑批SQL窗口函数数据量中等业务要看交互式图表和钻取Power BI临时分析、个人看数不超过几万行Excel需要严格按业务日期含缺失日计算Power BI 日期表最优只要求最近N个记录不关心日期连续性SQL或Excel均可需要把结果带到其他系统去对接SQL结果表最稳定这个清单不是绝对的。如果团队里Excel是唯一大家都熟悉的工具哪怕数据量再大也不建议强行上SQL——你要先解决的是能力和协作问题。技术选型的第一原则永远是“落地后能被维护”而不是“单次运行最快”。移动平均这一个指标算起来不复杂麻烦的是口径、边界和工具差异交织在一起。我的习惯是在每个项目最开始就把“移动平均”的定义白纸黑字写清楚然后才亲手去碰SQL、Power BI或者Excel。只要你掌握了这三套工具各自的核心写法再遇到这类需求基本就是一次参数换行的事不值得为此紧张。