sql - 在 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() + 新值。

demo:db<>fiddle