如何在SQL中使用LAG函数添加计算列计算对数价格变动

MySQL的生成列（Generated Column）有严格的表达式限制：

• 仅允许引用当前行的字段做计算，不能使用子查询、CTE、窗口函数等跨行读取数据的语法
• 也不允许引用其他表、变量等不符合规则的内容
  你试图在生成列定义里写带LAG窗口函数的查询逻辑，本身就不符合语法要求，自然会报1064错误。

方案1：创建视图（无需存储数据，实时计算） #

如果不需要物理存储这个字段，只是想每次查询的时候自动带出计算结果，直接建视图是最简便的方式：

CREATE VIEW v_prices_with_return AS
SELECT 
    *,
    LN(price / LAG(price, 1) OVER (PARTITION BY ticker ORDER BY date)) AS ln_change
FROM sandbox.prices;

后续直接查询视图 SELECT * FROM v_prices_with_return 就能拿到带对数收益率的结果，数据会随原表自动更新。

方案2：普通字段+触发器自动计算（持久化存储） #

如果需要把值物理存在表里，新增普通字段后搭配触发器自动维护值即可：

第一步：新增字段 #

ALTER TABLE sandbox.prices ADD COLUMN ln_change DOUBLE NULL;

第二步：批量刷入历史数据的计算结果 #

UPDATE sandbox.prices p
JOIN (
    SELECT 
        ticker,
        `date`,
        LN(price / LAG(price, 1) OVER (PARTITION BY ticker ORDER BY date)) AS calc_ln_change
    FROM sandbox.prices
) t ON p.ticker = t.ticker AND p.`date` = t.`date`
SET p.ln_change = t.calc_ln_change;

第三步：新增INSERT触发器，自动计算新插入数据的对数收益率 #

DELIMITER //
CREATE TRIGGER trg_prices_before_insert
BEFORE INSERT ON sandbox.prices
FOR EACH ROW
BEGIN
    DECLARE prev_price DOUBLE;
    SELECT price INTO prev_price 
    FROM sandbox.prices 
    WHERE ticker = NEW.ticker AND `date` < NEW.`date`
    ORDER BY `date` DESC LIMIT 1;
    IF prev_price IS NOT NULL THEN
        SET NEW.ln_change = LN(NEW.price / prev_price);
    ELSE
        SET NEW.ln_change = NULL;
    END IF;
END //
DELIMITER ;

可选：新增UPDATE触发器，价格修改时自动重算 #

DELIMITER //
CREATE TRIGGER trg_prices_before_update
BEFORE UPDATE ON sandbox.prices
FOR EACH ROW
BEGIN
    DECLARE prev_price DOUBLE;
    -- 仅价格修改时才触发重算
    IF OLD.price <> NEW.price THEN
        SELECT price INTO prev_price 
        FROM sandbox.prices 
        WHERE ticker = NEW.ticker AND `date` < NEW.`date`
        ORDER BY `date` DESC LIMIT 1;
        IF prev_price IS NOT NULL THEN
            SET NEW.ln_change = LN(NEW.price / prev_price);
        ELSE
            SET NEW.ln_change = NULL;
        END IF;
    END IF;
END //
DELIMITER ;

配置完成后，后续新增、修改数据时ln_change字段都会自动更新，无需手动维护。

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