计算 TimescaleDB 中值相对于上次读数的变化

我在 TimescaleDB 中存储了有关一组电池的时间序列数据，这些数据记录了每个电池每次的“充电状态”。我没有测量流入和流出的流量，只有瞬时充电状态。

从这些数据中，我想找到每次充电状态的变化，稍后我将把它存储在几个小时内的消耗中（在做了一些特定于电池的数学计算之后）。

我写了一个 SQL 查询来实现我的目标：

SELECT time, charge - LAG(charge) OVER (ORDER BY time) AS delta_soc FROM charge_data;

把它放在一个Postgres 生成的列 https://www.postgresql.org/docs/12/ddl-generated-columns.html:

ADD COLUMN delta_soc smallint GENERATED ALWAYS AS (charge - LAG(charge) OVER (ORDER BY time)) STORED

正如文档中所承诺的那样，失败了，因为它引用了另一行。

所以，我（成功）做出了一个物化视图：

CREATE MATERIALIZED VIEW delta_soc AS
SELECT
  time,
  batt_uid,
  charge,
  (charge-LAG(charge) OVER (ORDER BY time)) as delta_charge,
  EXTRACT(EPOCH FROM time-LAG(time) OVER (ORDER BY time)) as delta_time
FROM charge_data 
ORDER BY time;

但如果能近乎实时地获得这些数据那就太好了。毕竟，仅提供上次值的更改是一个“简单”的操作。所以，我查看了 Timescale 的连续聚集体 https://docs.timescale.com/api/latest/continuous-aggregates/create_materialized_view/#create-materialized-view-continuous-aggregate。但是，如文档中所示，连续聚合中不允许使用窗口函数，因此连续聚合无效。

然后，只是把东西扔到墙上，看看什么粘住了，我想知道我是否可以在插入过程中引用前一行

INSERT INTO charge_data VALUES (..., ([$chargevalue]-LAG(charge) OVER (ORDER BY time)), ...);
HINT:  There is a column named "charge" in table "mx_data", but it cannot be referenced from this part of the query.

我知道我可以计算增量

• 插入前
• 插入后通过修改每个 charge_data 行及其增量
• 在 SQL 查询中
• 在查询程序中

但让数据库在插入时/前后计算一次值似乎更简单、更整洁，这让我怀疑我遗漏了一些东西。有什么方法可以在时间尺度上近实时地计算和存储每一行​​的 charge[battery][n]-charge[battery][n-1] ？

我认为插入前触发器效果很好。您可以创建 before_insert 触发器并在使用先前的引用插入时更新增量 soc。

CREATE TABLE batteries ( time timestamp not null, batt_uid varchar, charge int, delta int);
SELECT create_hypertable('batteries', 'time');

CREATE OR REPLACE FUNCTION update_delta() RETURNS trigger AS
$BODY$
DECLARE
    previous_charge integer; 
BEGIN
   select charge
   into previous_charge
   from batteries where batt_uid = NEW.batt_uid
   order by time desc limit 1;

  IF NEW.charge IS NOT NULL THEN
    IF previous_charge IS NOT NULL THEN
       NEW.delta = NEW.charge - previous_charge;
    ELSE
      NEW.delta = 0;

    END IF;
  END IF;

  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_delta_on_insert
               BEFORE INSERT
               ON batteries
               FOR EACH ROW
               EXECUTE PROCEDURE update_delta();

Testing

INSERT INTO batteries VALUES 
('2021-08-26 10:09:00'::timestamp, 'battery-1', 32),
('2021-08-26 10:09:01'::timestamp, 'battery-1', 34),
('2021-08-26 10:09:02'::timestamp, 'battery-1', 38);

INSERT INTO batteries VALUES 
('2021-08-26 10:09:00'::timestamp, 'battery-2', 0),
('2021-08-26 10:09:01'::timestamp, 'battery-2', 4),
('2021-08-26 10:09:02'::timestamp, 'battery-2', 28),
('2021-08-26 10:09:03'::timestamp, 'battery-2', 32),
('2021-08-26 10:09:04'::timestamp, 'battery-2', 28);

输出来自：

SELECT * FROM batteries;

┌─────────────────────┬───────────┬────────┬───────┐
│        time         │ batt_uid  │ charge │ delta │
├─────────────────────┼───────────┼────────┼───────┤
│ 2021-08-26 10:09:00 │ battery-1 │     32 │     0 │
│ 2021-08-26 10:09:01 │ battery-1 │     34 │     2 │
│ 2021-08-26 10:09:02 │ battery-1 │     38 │     4 │
│ 2021-08-26 10:09:00 │ battery-2 │      0 │     0 │
│ 2021-08-26 10:09:01 │ battery-2 │      4 │     4 │
│ 2021-08-26 10:09:02 │ battery-2 │     28 │    24 │
│ 2021-08-26 10:09:03 │ battery-2 │     32 │     4 │
│ 2021-08-26 10:09:04 │ battery-2 │     28 │    -4 │
└─────────────────────┴───────────┴────────┴───────┘