MariaDB/MySQL创建基于前20行求和的存储生成列报错求助

由于存储生成列不允许使用子查询或复杂聚合逻辑，直接通过ALTER TABLE创建目标存储生成列的方法不可行，以下是几种实用替代方案：

方案1：创建含滚动总和的视图 #

视图无需物理存储数据，可直接通过窗口函数计算当前行及前19行的总和，适合Home Assistant直接查询以简化逻辑。

假设表中有用于排序的时间戳列（如timestamp），执行以下SQL创建视图：

CREATE VIEW solar_generation_with_running_total AS
SELECT 
    *,
    SUM(amount) OVER(
        ORDER BY timestamp 
        ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
    ) AS running_total
FROM solar_generation;

后续在Home Assistant中直接查询该视图即可，每次查询都会实时计算最新的滚动总和，无需额外维护。

方案2：用触发器维护普通列 #

若需要将滚动总和物理存储在表中，可先添加普通列，再通过触发器自动维护该列的值：

1. 先添加普通列：

ALTER TABLE solar_generation ADD COLUMN running_total DECIMAL(10,2);

2. 创建触发器（以MySQL 8.0+为例），在数据插入、更新或删除时自动更新受影响行的滚动总和：

DELIMITER //
CREATE TRIGGER update_running_total_after_insert AFTER INSERT ON solar_generation
FOR EACH ROW
BEGIN
    UPDATE solar_generation s
    JOIN (
        SELECT 
            id, -- 假设表中有主键id
            SUM(amount) OVER(
                ORDER BY timestamp 
                ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
            ) AS new_total
        FROM solar_generation
    ) t ON s.id = t.id
    SET s.running_total = t.new_total
    WHERE s.id >= NEW.id - 19 AND s.id <= NEW.id;
END //
DELIMITER ;

注意：需根据实际表结构调整主键、时间列等字段，同时需额外创建UPDATE和DELETE触发器覆盖所有数据变更场景。此方案会增加写入时的性能开销，适合数据量不大的场景。

方案3：使用物化视图（PostgreSQL专属） #

若使用PostgreSQL 12及以上版本，可创建物化视图定期刷新，兼顾物理存储的查询性能和数据的相对新鲜度：

1. 创建物化视图：

CREATE MATERIALIZED VIEW solar_generation_mv AS
SELECT 
    *,
    SUM(amount) OVER(
        ORDER BY timestamp 
        ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
    ) AS running_total
FROM solar_generation;

2. 定期刷新物化视图以更新数据：

REFRESH MATERIALIZED VIEW solar_generation_mv;

可通过定时任务（如PostgreSQL的pg_cron插件）自动执行刷新，适合太阳能数据按固定间隔插入、对实时性要求不高的场景。

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