在PostgreSQL中插入后计算平均值

Question

我试图获取传感器在过去 24 小时内引入的所有值的字段pm10_ug_m3平均值，但使用我当前的代码，平均值不包括插入的行的值。

目前，由于触发器是在插入之前完成的，因此它不考虑在字段pm10_ug_m3中插入的最后一个值。因此，在该领域引入的平均值是 3，而不是从 (10+3(/2 获得的平均值 6.5

1(创建表格并添加一些日期：

(
  ID bigint NOT NULL,
  SensorID character(10),
  pm10_ug_m3 numeric(10,2),
  tense timestamp without time zone,
  average float,
  CONSTRAINT id_pk PRIMARY KEY (ID)
);
INSERT INTO sensor (ID,SensorID,pm10_ug_m3,tense) VALUES
(1,'S16',1,'2019-07-10 04:25:59'),
(2,'S20',3,'2017-07-10 02:25:59');

2( 创建触发器以计算过去 24 小时内从同一传感器捕获的 pm10_ug_m3 条记录的平均值：

CREATE OR REPLACE FUNCTION calculate_avg() RETURNS TRIGGER AS $BODY$
  BEGIN
  NEW.average := ( SELECT AVG(pm10_ug_m3)
                        FROM sensor
                        WHERE SensorID=NEW.SensorID 
                        AND tense>= NEW.tense - INTERVAL '24 HOURS');
  RETURN NEW;
  END;
  $BODY$
 LANGUAGE plpgsql;

CREATE TRIGGER calculate_avg_trigger BEFORE INSERT OR UPDATE 
    ON sensor FOR EACH ROW 
    EXECUTE PROCEDURE calculate_avg();

3(插入一个新行，它将在其中填充字段平均值：

INSERT INTO sensor (ID,SensorID,pm10_ug_m3,tense) VALUES
(3,'S20',10,'2017-07-10 04:25:59')

Answer 1

这

不起作用，因为AVG()函数只考虑仍在插入的数据，而不考虑将要插入的新数据。

将触发点从BEFORE更改为AFTER确实会提供正确的结果，但不会设置，因为此时INSERT已经完成。

因此，获得结果的一种方法是在触发函数中手动计算平均值：

SELECT (SUM(pm10_ug_M3) + NEW.pm10_ug_m3) / (COUNT(pm10_ug_m3) + 1)
FROM ...

SUM()当前值 + 新值除以当前值的COUNT() + 新值。

演示：数据库<>小提琴