SQL触发功能可在插入时更新每日移动平均值

Question

我正在尝试创建一个SQL触发函数，当将数据插入表中时，该函数应该更新列。该更新基于被插入的值中存在的值。

我有下表存储股票的每日OHLC数据。

CREATE TABLE daily_ohlc (
 cdate date,
 open numeric(8,2),
 high numeric(8,2),
 low numeric(8,2),
 close numeric(8,2),
 sma8 numeric(8,2)
);

INSERT命令：

INSERT INTO daily_ohlc (cdate, open, high, low, close) 
 values ('SYMBOL', 101, 110, 95, 108);

当执行此命令时，我想基于当前值“ INSERTed”和表中已有的值来更新“ sma8”列。

到目前为止，我正在使用以下SQL查询来计算每一行的值，然后使用结果使用python更新“ sma8”列。

SELECT sec.date, AVG(sec.close) 
 OVER(ORDER BY sec.date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS 
simple_mov_avg FROM daily_ohlc sec;

上面的查询计算最后8条记录（包括当前行）的简单移动平均线。

使用此过程，每次插入数据时，都会更新“ sma8”列中的每一行数据。我想通过使用触发器仅更新最后一行（即正在插入的行）。这该怎么做？

Answer 1

您可以UPDATE FROM在触发器中使用适当的联接来进行选择查询。

create or replace function update_sma8() RETURNS TRIGGER AS
$$
 BEGIN

UPDATE daily_ohlc d SET sma8 = s.simple_mov_avg 
FROM
(
 SELECT  sec.cdate,AVG(sec.close)  
   OVER(ORDER BY sec.cdate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS 
    simple_mov_avg FROM daily_ohlc sec
)s where s.cdate = NEW.cdate  --The newly inserted cdate
     AND d.cdate = s.cdate;   
RETURN NULL;

END $$ language plpgsql;

使用此方法的唯一警告是，如果有人 删除 行或 更新
close列，则必须重新计算值，而对于现有行则不会发生。只有插入的行会看到正确的重新计算值。

取而代之的是，您可以简单地创建View以在需要时sma8从主表为所有行计算列。