SQL移动平均怎么计算_SQL移动平均聚合计算教程

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

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_avg
FROM
    daily_sales
ORDER 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_avg
FROM
    daily_sales
ORDER 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_avg
FROM
    daily_sales
ORDER BY
    sale_date;

CMA的优点在于它考虑了所有历史数据，对于长期趋势的观察非常有用，但缺点是它对最新数据的变化反应迟钝。

如何选择计算窗口？

这更多是艺术而非纯粹的科学，很大程度上取决于你的业务场景和数据特性。

• 短期波动平滑： 如果你的数据噪音大，但你更关心短期（比如一周内）的趋势，那么3天、5天、7天这样的短窗口SMA会很合适。它能快速响应近期变化，但对噪音的过滤能力有限。
• 中期趋势观察： 10天、20天、30天甚至更长的窗口（如月度平均）可以更好地过滤掉日常噪音，帮助你识别更稳定的中期趋势。例如，股票市场常用的50日、200日均线就是为了观察中长期走势。
• 季节性调整： 如果你的数据有明显的季节性（例如，每周、每月或每年重复的模式），你可以选择一个与季节周期相匹配的窗口。比如，如果你有每周数据，但想消除周内波动，可以计算7天移动平均。
• 对最新数据敏感度： 窗口越小，移动平均对最新数据的变化越敏感；窗口越大，移动平均越平滑，但对最新变化的响应越慢。
• 数据量： 如果数据量较小，过大的窗口可能会导致有效数据点不足，使得结果不够准确。

我个人在做销售数据分析时，常常会同时看7天和30天的移动平均。7天能告诉我最近一周的势头如何，有没有突然的增长或下滑；30天则能给我一个更宏观的月度视角，看看是不是大趋势在变化。这种多维度观察，往往能提供更全面的洞察。

在SQL中处理移动平均计算中的NULL值或缺失数据

在实际数据中，NULL值和数据缺失是常态，处理不好会严重影响移动平均的准确性。在SQL的窗口函数中，

AVG()

算家云

高效、便捷的人工智能算力服务平台

37

查看详情

举个例子：一个3天移动平均的窗口，如果其中一天销售额为NULL，

AVG()

-- 假设 daily_sales 表中某些日期的 amount 是 NULL
SELECT
    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_ignored
FROM
    daily_sales
ORDER 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_zero
FROM
    daily_sales
ORDER BY
    sale_date;

更棘手的是“日期缺失”问题。 我们的

daily_sales

ORDER BY sale_date

解决日期缺失的方法通常有两种：

1. 在计算前填充缺失日期： 这是最推荐的做法，它能确保你的移动平均是基于连续的日历天数计算的。你可以创建一个完整的日期序列（例如，使用递归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_filled
   FROM
       SalesWithMissingDates
   ORDER BY
       sale_date;

   登录后复制

   这种方式确保了窗口的“宽度”是真正的日历天数，而不是跳跃的记录数。

2. 接受“基于记录数”的移动平均： 如果你知道并接受你的移动平均是基于“过去N个有数据的点”而不是“过去N个日历天”计算的，那么你可以直接使用原始数据。但请务必在报告和解释中明确这一点，以免造成误解。

我通常倾向于第一种方法。因为在我看来，当谈论“3天移动平均”时，大多数人直觉上会认为是“过去3个日历日”的平均，而不是“过去3个有数据的日子”的平均。清晰的定义能避免很多沟通成本。

SQL移动平均在实际业务场景中有哪些应用？

移动平均不仅仅是一个统计学概念，它在实际业务分析中有着极其广泛的应用，是数据分析师工具箱中的一把利器。它的核心价值在于平滑数据，揭示趋势，过滤噪音。

1. 金融市场分析（股票、加密货币等）： 这是移动平均最经典的战场。技术分析师会使用不同周期的移动平均线（如5日、10日、20日、50日、200日均线）来判断股票的短期、中期和长期趋势。例如：

   • 金叉/死叉： 短期均线上穿长期均线（金叉）被视为买入信号，反之（死叉）被视为卖出信号。
   • 支撑/阻力位： 移动平均线可以作为动态的支撑位或阻力位，帮助判断价格走势。
   • 趋势识别： 当价格在移动平均线上方运行时，通常被认为是上升趋势；反之则是下降趋势。

2. 销售与营销趋势分析：

   • 销售额趋势： 我们可以计算每日、每周或每月的销售额移动平均，来观察销售额是处于增长、下降还是平稳状态。这比看每天波动的原始销售额要清晰得多。比如，计算7天移动平均，可以消除周末效应，更好地看出周内销售的整体趋势。
   • 广告效果评估： 监测广告投放后，网站访问量、转化率的移动平均变化，评估广告活动的长期效果，而不是被短期的偶然波动所迷惑。
   • 季节性分析： 通过对比不同年份同期的移动平均，可以更好地理解产品的季节性规律。

3. 生产与运营管理：

   • 生产效率： 监测生产线每小时或每天的产量移动平均，及时发现生产效率的提升或下降，以便调整生产计划。
   • 库存管理： 预测未来几天的需求移动平均，帮助优化库存水平，避免积压或缺货。
   • 服务质量： 比如，客服中心平均响应时间的移动平均，可以反映服务水平的稳定性和变化趋势。

4. 网站/应用性能监控：

   • 响应时间： 监测API请求、页面加载时间的5分钟或1小时移动平均，可以平滑掉瞬时的高峰或低谷，更准确地反映系统的整体性能健康状况。当移动平均线持续上升时，可能预示着系统负载过高或存在瓶颈。
   • 错误率： 跟踪错误率的移动平均，可以帮助发现潜在的软件缺陷或基础设施问题。

5. 物联网（IoT）数据分析：

   • 传感器数据平滑： 传感器数据往往包含大量噪音，例如温度、湿度、压力等读数。计算它们的移动平均可以有效地平滑数据，提取出更真实的物理量变化趋势，用于异常检测或预测。

在我自己的经验里，当老板问“我们最近的销售情况怎么样？”的时候，我很少直接给他看每天的销售额。那会让人眼花缭乱。我更倾向于展示一个7天或30天的移动平均图表，因为这能更直观地反映出“势头”——是向上还是向下，趋势是否稳定。这比纯粹的日数据更有说服力，也更能支持决策。移动平均，本质上就是帮助我们从繁杂的数据中，提炼出有意义的“故事线”。

以上就是SQL移动平均怎么计算_SQL移动平均聚合计算教程的详细内容，更多请关注php中文网其它相关文章！