SQL移动平均通过窗口函数AVG()结合OVER()子句实现，核心是使用ROWS BETWEEN n PRECEDING AND CURRENT ROW定义动态计算范围，从而平滑数据、揭示趋势。最常见的是简单移动平均（SMA），适用于固定窗口内等权重计算；累积移动平均（CMA）则从序列起始累加至当前行，适合观察长期趋势。选择窗口大小需权衡对噪音的过滤能力与对最新变化的敏感度：短期窗口（如3-7天）响应快但平滑性弱，长期窗口（如30天以上）更稳定，适合识别中长期趋势，且可匹配季节周期。实际应用中需处理NULL值——AVG默认忽略NULL，若需视作0可用COALESCE；更关键的是日期缺失问题，推荐先用日期序列左连接补全缺失日，确保窗口基于连续日历天而非记录数，避免误导。该方法广泛用于销售趋势分析、广告效果评估、生产效率监控、金融技术分析等领域，帮助提炼数据趋势，支持决策。
SQL移动平均的计算核心在于利用窗口函数（Window Functions），特别是
AVG()
结合
OVER()
子句，来定义一个动态的、随着数据行移动而变化的计算范围。它能帮助我们平滑数据，揭示潜在趋势，过滤掉短期的波动和噪音。
解决方案
在数据分析中，我们经常需要观察某个指标在一段时间内的平均表现，而不是孤立的单点数据。这就引出了移动平均（Moving Average）的概念。SQL提供了一种非常优雅且强大的方式来实现这一点，那就是窗口函数。
假设我们有一个销售记录表
daily_sales
，包含
sale_date
（日期）和
amount
（销售额）。我们想计算过去3天的销售额移动平均。
SELECT sale_date, amount, -- 计算过去3天的移动平均，包括当天 -- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 表示当前行和它之前的2行，总共3行 AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS three_day_moving_avgFROM daily_salesORDER BY sale_date;
这段SQL的核心在于
AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
。让我稍微拆解一下：
AVG(amount)
：这是我们想要聚合的函数，计算平均值。
OVER()
：这标志着我们正在使用一个窗口函数。
ORDER BY sale_date
：这定义了窗口内数据的排序方式，对于时间序列数据，这通常是日期或时间戳，确保计算是按时间顺序进行的。
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
：这才是定义“移动”的关键。它指定了当前行计算平均值时，应该包含哪些行。
2 PRECEDING
：表示包含当前行之前的2行。
CURRENT ROW
：表示包含当前行。合起来，就是当前行和它前面的2行，总共3行数据。
如果你想计算一个7天的移动平均，只需将
2 PRECEDING
改为
6 PRECEDING
即可。
SELECT sale_date, amount, AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS seven_day_moving_avgFROM daily_salesORDER BY sale_date;
这种方式的妙处在于，它不需要自连接，也不需要复杂的子查询，代码简洁且通常性能更优。当然，实际场景中，我们可能还会结合
PARTITION BY
子句，比如按产品类别计算各自的移动平均，那就变成
PARTITION BY product_category ORDER BY sale_date ...
了。
SQL移动平均有哪些类型？如何选择合适的计算窗口？
在SQL中实现移动平均，最常见且直接的是简单移动平均（Simple Moving Average, SMA）。但根据你的分析目的，选择合适的“计算窗口”至关重要，它直接影响了结果的平滑程度和对最新数据的敏感度。
首先，我们上面演示的就是最典型的SMA。它的特点是窗口内所有数据点的权重都是相等的。
还有一种常见的变体是累积移动平均（Cumulative Moving Average, CMA）。它不是固定窗口大小，而是从序列的开始一直累积到当前点。在SQL中，实现CMA非常简单，只需将窗口定义为
UNBOUNDED PRECEDING AND CURRENT ROW
：
SELECT sale_date, amount, -- 计算从数据开始到当前日期的累积平均值 AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_avgFROM daily_salesORDER BY sale_date;
CMA的优点在于它考虑了所有历史数据，对于长期趋势的观察非常有用，但缺点是它对最新数据的变化反应迟钝。
如何选择计算窗口？
这更多是艺术而非纯粹的科学，很大程度上取决于你的业务场景和数据特性。
短期波动平滑： 如果你的数据噪音大，但你更关心短期（比如一周内）的趋势，那么3天、5天、7天这样的短窗口SMA会很合适。它能快速响应近期变化，但对噪音的过滤能力有限。中期趋势观察： 10天、20天、30天甚至更长的窗口（如月度平均）可以更好地过滤掉日常噪音，帮助你识别更稳定的中期趋势。例如，股票市场常用的50日、200日均线就是为了观察中长期走势。季节性调整： 如果你的数据有明显的季节性（例如，每周、每月或每年重复的模式），你可以选择一个与季节周期相匹配的窗口。比如，如果你有每周数据，但想消除周内波动，可以计算7天移动平均。对最新数据敏感度： 窗口越小，移动平均对最新数据的变化越敏感；窗口越大，移动平均越平滑，但对最新变化的响应越慢。数据量： 如果数据量较小，过大的窗口可能会导致有效数据点不足，使得结果不够准确。
我个人在做销售数据分析时，常常会同时看7天和30天的移动平均。7天能告诉我最近一周的势头如何，有没有突然的增长或下滑；30天则能给我一个更宏观的月度视角，看看是不是大趋势在变化。这种多维度观察，往往能提供更全面的洞察。
在SQL中处理移动平均计算中的NULL值或缺失数据
在实际数据中，NULL值和数据缺失是常态，处理不好会严重影响移动平均的准确性。在SQL的窗口函数中，
AVG()
聚合函数默认的行为是忽略NULL值。这意味着，如果窗口内有NULL值，它不会被计入总和，也不会计入计算平均值时的行数。
西语写作助手
西语助手旗下的AI智能写作平台，支持西语语法纠错润色、论文批改写作
19 查看详情
举个例子：一个3天移动平均的窗口，如果其中一天销售额为NULL，
AVG()
会用剩下两天的销售额之和除以2，而不是3。
-- 假设 daily_sales 表中某些日期的 amount 是 NULLSELECT sale_date, amount, -- 默认情况下，AVG会忽略NULL值 AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS three_day_moving_avg_with_null_ignoredFROM daily_salesORDER BY sale_date;
这种默认行为在很多情况下是合理的，因为它确保了平均值是基于实际存在的数值计算的。但有时，你可能希望将NULL视为0。这在某些业务场景下有意义，比如，如果NULL意味着当天没有销售记录，而你希望它拉低平均值。这时，你可以使用
COALESCE
函数：
SELECT sale_date, amount, -- 将NULL销售额视为0进行计算 AVG(COALESCE(amount, 0)) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS three_day_moving_avg_with_null_as_zeroFROM daily_salesORDER BY sale_date;
更棘手的是“日期缺失”问题。 我们的
daily_sales
表可能不是每天都有记录。如果某个日期没有记录，那么
ORDER BY sale_date
会直接跳过那一天。这意味着你的“3天移动平均”可能实际上是“过去3个有记录的日期”的平均值，而不是“过去3个日历日”的平均值。这在分析时可能会产生误导。
解决日期缺失的方法通常有两种：
在计算前填充缺失日期：这是最推荐的做法，它能确保你的移动平均是基于连续的日历天数计算的。你可以创建一个完整的日期序列（例如，使用递归CTE或一个日期维度表），然后与你的
daily_sales
表进行
LEFT JOIN
。这样，即使某天没有销售，也会有一行记录，
amount
字段为NULL，你可以选择让
AVG
忽略它，或者用
COALESCE
将其变为0。
-- 假设我们有一个日期表 dates_series，包含所有日期WITH DateSeries AS ( SELECT generate_series('2023-01-01'::date, '2023-01-31'::date, '1 day'::interval)::date AS full_date),SalesWithMissingDates AS ( SELECT ds.full_date AS sale_date, dsales.amount FROM DateSeries ds LEFT JOIN daily_sales dsales ON ds.full_date = dsales.sale_date)SELECT sale_date, amount, -- 现在，即使有日期缺失，窗口也是基于连续日期的 AVG(COALESCE(amount, 0)) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS three_day_moving_avg_filledFROM SalesWithMissingDatesORDER BY sale_date;
这种方式确保了窗口的“宽度”是真正的日历天数，而不是跳跃的记录数。
接受“基于记录数”的移动平均：如果你知道并接受你的移动平均是基于“过去N个有数据的点”而不是“过去N个日历天”计算的，那么你可以直接使用原始数据。但请务必在报告和解释中明确这一点，以免造成误解。
我通常倾向于第一种方法。因为在我看来，当谈论“3天移动平均”时，大多数人直觉上会认为是“过去3个日历日”的平均，而不是“过去3个有数据的日子”的平均。清晰的定义能避免很多沟通成本。
SQL移动平均在实际业务场景中有哪些应用？
移动平均不仅仅是一个统计学概念，它在实际业务分析中有着极其广泛的应用，是数据分析师工具箱中的一把利器。它的核心价值在于平滑数据，揭示趋势，过滤噪音。
金融市场分析（股票、加密货币等）：这是移动平均最经典的战场。技术分析师会使用不同周期的移动平均线（如5日、10日、20日、50日、200日均线）来判断股票的短期、中期和长期趋势。例如：
金叉/死叉： 短期均线上穿长期均线（金叉）被视为买入信号，反之（死叉）被视为卖出信号。支撑/阻力位： 移动平均线可以作为动态的支撑位或阻力位，帮助判断价格走势。趋势识别： 当价格在移动平均线上方运行时，通常被认为是上升趋势；反之则是下降趋势。
销售与营销趋势分析：
销售额趋势： 我们可以计算每日、每周或每月的销售额移动平均，来观察销售额是处于增长、下降还是平稳状态。这比看每天波动的原始销售额要清晰得多。比如，计算7天移动平均，可以消除周末效应，更好地看出周内销售的整体趋势。广告效果评估： 监测广告投放后，网站访问量、转化率的移动平均变化，评估广告活动的长期效果，而不是被短期的偶然波动所迷惑。季节性分析： 通过对比不同年份同期的移动平均，可以更好地理解产品的季节性规律。
生产与运营管理：
生产效率： 监测生产线每小时或每天的产量移动平均，及时发现生产效率的提升或下降，以便调整生产计划。库存管理： 预测未来几天的需求移动平均，帮助优化库存水平，避免积压或缺货。服务质量： 比如，客服中心平均响应时间的移动平均，可以反映服务水平的稳定性和变化趋势。
网站/应用性能监控：
响应时间： 监测API请求、页面加载时间的5分钟或1小时移动平均，可以平滑掉瞬时的高峰或低谷，更准确地反映系统的整体性能健康状况。当移动平均线持续上升时，可能预示着系统负载过高或存在瓶颈。错误率： 跟踪错误率的移动平均，可以帮助发现潜在的软件缺陷或基础设施问题。
物联网（IoT）数据分析：
传感器数据平滑： 传感器数据往往包含大量噪音，例如温度、湿度、压力等读数。计算它们的移动平均可以有效地平滑数据，提取出更真实的物理量变化趋势，用于异常检测或预测。
在我自己的经验里，当老板问“我们最近的销售情况怎么样？”的时候，我很少直接给他看每天的销售额。那会让人眼花缭乱。我更倾向于展示一个7天或30天的移动平均图表，因为这能更直观地反映出“势头”——是向上还是向下，趋势是否稳定。这比纯粹的日数据更有说服力，也更能支持决策。移动平均，本质上就是帮助我们从繁杂的数据中，提炼出有意义的“故事线”。
以上就是SQL移动平均怎么计算_SQL移动平均聚合计算教程的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/912383.html
微信扫一扫 
支付宝扫一扫 
