创建含移动平均线的计算列遇Error Code3593报错求助

错误原因 #

MySQL的**生成列（Generated Column）**不支持使用窗口函数（比如AVG() OVER()），这是语法层面的限制，所以直接通过ALTER TABLE添加包含窗口函数的计算列会触发Error Code: 3593。

另外注意：你原代码里ROWS BETWEEN 9 PRECEDING AND CURRENT ROW计算的是最近10条数据的平均值（MA10），如果要实现MA100，需要改成ROWS BETWEEN 99 PRECEDING AND CURRENT ROW。

解决方案 #

1. 创建视图（推荐，无需持久化数据）

如果不需要把MA100存储在表中，而是查询时动态获取，创建视图是最优方案：

CREATE VIEW ibstocks.stocks_with_ma AS
SELECT 
    *,
    CAST(AVG(close) OVER(PARTITION BY Symbol ORDER BY date ROWS BETWEEN 99 PRECEDING AND CURRENT ROW) AS DECIMAL(8,2)) AS MA100
FROM ibstocks.stocks;

使用时直接查询视图：SELECT * FROM ibstocks.stocks_with_ma;

2. 手动维护持久化列

如果必须把MA100存储在表中，可以通过触发器或定时任务来维护：

• 第一步：添加普通列

ALTER TABLE ibstocks.stocks ADD COLUMN MA100 DECIMAL(8,2);

• 第二步：创建触发器（插入/更新时自动更新MA100）
  假设表有主键id，如果没有可以用Symbol+date作为唯一关联键：

DELIMITER //
CREATE TRIGGER update_stock_ma100 AFTER INSERT ON ibstocks.stocks
FOR EACH ROW
BEGIN
    UPDATE ibstocks.stocks s
    JOIN (
        SELECT 
            id,
            CAST(AVG(close) OVER(PARTITION BY Symbol ORDER BY date ROWS BETWEEN 99 PRECEDING AND CURRENT ROW) AS DECIMAL(8,2)) AS new_ma100
        FROM ibstocks.stocks
        WHERE Symbol = NEW.Symbol
    ) ma ON s.id = ma.id
    SET s.MA100 = ma.new_ma100;
END //
DELIMITER ;

注意：如果数据量较大，触发器可能影响写入性能，此时建议用MySQL事件调度器定时批量更新MA100。

3. 查询时动态计算

如果只是偶尔需要MA100数据，直接在查询语句中计算即可：

SELECT 
    *,
    CAST(AVG(close) OVER(PARTITION BY Symbol ORDER BY date ROWS BETWEEN 99 PRECEDING AND CURRENT ROW) AS DECIMAL(8,2)) AS MA100
FROM ibstocks.stocks;

内容的提问来源于stack exchange，提问作者tomaskunes