在sql中实现移动平均线计算,核心是利用窗口函数 lag() 配合算术运算,或者使用自连接(self-join)来聚合历史数据。推荐使用窗口函数方案,因为它性能更高且代码更简洁;若数据库版本较旧不支持窗口函数,则采用自连接方案。两种方法均通过指定时间范围或行数,将当前行与过去n行的值进行平均计算。

如何使用LAG函数计算固定行数的移动平均
使用 LAG() 函数可以直接获取当前行之前的第N行数据,从而避免复杂的自连接逻辑。这种方法适用于计算基于“最近N条记录”的移动平均,例如计算过去3天的股价均值。关键在于使用 ORDER BY 子句确保数据按时间顺序排列,并使用 AVG() 窗口函数结合 RANGE 或 ROWS 子句来定义滑动窗口的大小。

SELECT
date,
price,
AVG(price) OVER (
ORDER BY date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3
FROM stock_prices
ORDER BY date;
上述代码中,ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 定义了窗口大小为3行(当前行+前2行)。如果某一行之前的数据不足3行,AVG() 会自动忽略空值并计算可用数据的平均值,直到数据充足后才会稳定输出完整的移动平均值。执行结果会返回包含日期、原始价格和3日移动平均值的三列数据。
如何基于时间范围计算动态移动平均
当需要计算基于特定时间跨度(如过去7天)而非固定行数的移动平均时,应使用 RANGE 子句或自连接。自连接通过将主表与自身关联,筛选出时间差在指定范围内的记录进行聚合。这种方法在处理不规则时间间隔的数据时更为灵活,但性能开销较大。关键在于使用 BETWEEN 和 INTERVAL 来精确控制时间窗口。

SELECT
t1.date,
t1.price,
AVG(t2.price) AS moving_avg_7d
FROM stock_prices t1
JOIN stock_prices t2
ON t1.date >= t2.date
AND t1.date <= DATE_ADD(t2.date, INTERVAL 7 DAY)
GROUP BY t1.date, t1.price
ORDER BY t1.date;
该查询通过自连接 t1 和 t2,将每一天的价格与过去7天内所有价格关联起来。 DATE_ADD(t2.date, INTERVAL 7 DAY) 确保了只统计时间窗口内的数据。如果某天没有交易记录,该天不会出现在结果集中,因此需根据业务需求决定是否填充缺失日期。最终输出的 moving_avg_7d 即为基于时间的7日移动平均值。
当数据存在缺失日期时移动平均结果如何处理
在真实场景中,交易日或非交易日会导致数据出现缺失。窗口函数方案中,ROWS 子句仅依赖物理行数,不关心时间间隔,因此缺失日期不会影响窗口大小,但可能导致平均值的语义偏差(如用非交易日数据替代交易日)。 RANGE 子句和自连接方案则严格基于时间戳,缺失日期会被自然跳过,但可能导致窗口内数据量波动。为验证此行为,可构造含缺失日期的测试数据:
-- 测试数据:2023-01-01, 01-03, 01-05(缺失01-02, 01-04)
INSERT INTO stock_prices (date, price) VALUES
('2023-01-01', 100),
('2023-01-03', 102),
('2023-01-05', 105);
-- 运行3日移动平均查询
SELECT
date,
price,
AVG(price) OVER (
ORDER BY date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3
FROM stock_prices;
-- 预期结果:
-- 2023-01-01: 100.0 (仅1行)
-- 2023-01-03: 101.0 ((100+102)/2)
-- 2023-01-05: 102.33 ((100+102+105)/3)
从结果可见,ROWS 子句在数据不足时自动调整分母,确保计算有效。若业务要求严格的时间窗口,应改用 RANGE 或自连接,并在应用层处理缺失日期的插值问题。

















