如何在SQLite3与PostgreSQL中用LAG函数创建存储生成列

核心限制说明 #

无论是PostgreSQL的STORED生成列，还是SQLite的GENERATED ALWAYS AS ... STORED，均不支持引用其他行的数据（比如LAG()这类窗口函数）。生成列的计算仅能依赖当前行字段或常量，因此要实现基于上一行数据的自动计算，必须通过触发器完成。

PostgreSQL 实现步骤 #

1. 创建基础监控表 #

CREATE TABLE system_monitor (
    id SERIAL PRIMARY KEY,
    ts TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    cputil NUMERIC(5,2),
    memfree NUMERIC(10,2),
    util_diff NUMERIC(5,2),  -- 存储cputil与上一行的差值
    mem_diff NUMERIC(10,2),  -- 存储memfree与上一行的差值
    change TEXT              -- 存储CPU使用率变化方向（Up/Down/No Change）
);

2. 编写触发器计算函数 #

CREATE OR REPLACE FUNCTION calc_monitor_diffs()
RETURNS TRIGGER AS $$
BEGIN
    -- 计算cputil差值：当前值 - 上一行值
    SELECT NEW.cputil - LAG(cputil) OVER (ORDER BY ts)
    INTO NEW.util_diff
    FROM system_monitor
    ORDER BY ts DESC
    LIMIT 1;

    -- 计算memfree差值：当前值 - 上一行值
    SELECT NEW.memfree - LAG(memfree) OVER (ORDER BY ts)
    INTO NEW.mem_diff
    FROM system_monitor
    ORDER BY ts DESC
    LIMIT 1;

    -- 判断CPU使用率变化方向
    NEW.change := CASE
        WHEN NEW.util_diff > 0 THEN 'Up'
        WHEN NEW.util_diff < 0 THEN 'Down'
        ELSE 'No Change'
    END;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 绑定触发器到插入事件 #

CREATE TRIGGER trigger_calc_monitor_diffs
BEFORE INSERT ON system_monitor
FOR EACH ROW EXECUTE FUNCTION calc_monitor_diffs();

SQLite3 实现步骤 #

1. 创建基础监控表 #

CREATE TABLE system_monitor (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ts DATETIME DEFAULT CURRENT_TIMESTAMP,
    cputil REAL,
    memfree REAL,
    util_diff REAL,
    mem_diff REAL,
    change TEXT
);

2. 创建触发器（SQLite直接内置逻辑） #

CREATE TRIGGER trigger_calc_monitor_diffs
BEFORE INSERT ON system_monitor
FOR EACH ROW
BEGIN
    -- 计算cputil与上一行的差值
    SELECT NEW.cputil - cputil
    INTO NEW.util_diff
    FROM system_monitor
    ORDER BY ts DESC
    LIMIT 1;

    -- 计算memfree与上一行的差值
    SELECT NEW.memfree - memfree
    INTO NEW.mem_diff
    FROM system_monitor
    ORDER BY ts DESC
    LIMIT 1;

    -- 设置变化方向标识
    SET NEW.change = CASE
        WHEN NEW.util_diff > 0 THEN 'Up'
        WHEN NEW.util_diff < 0 THEN 'Down'
        ELSE 'No Change'
    END;
END;

边界情况处理 #

当插入第一行数据时，util_diff和mem_diff会返回NULL，change会显示No Change。如果需要默认值（比如差值设为0），可以在触发器中添加COALESCE处理：

• PostgreSQL示例：NEW.util_diff := COALESCE(NEW.cputil - LAG(cputil) OVER (...), 0);
• SQLite示例：SELECT COALESCE(NEW.cputil - cputil, 0) INTO NEW.util_diff FROM ...;

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