我将尝试将部分计算移至行插入阶段。
添加新列：
alter table measurements add consumption real;
更新列：
with m1 as ( select id, power_total, created_at, lag(power_total) over (partition by device_id order by created_at) prev_power_total, lag(created_at) over (partition by device_id order by created_at) prev_created_at from measurements )update measurements m2set consumption = (m1.power_total+ m1.prev_power_total)* extract(epoch from m1.created_at- m1.prev_created_at)from m1where m2.id = m1.id;
创建触发器：
create or replace function before_insert_on_measurements()returns trigger language plpgsqlas $$declare rec record;begin select power_total, created_at into rec from measurements where device_id = new.device_id order by created_at desc limit 1; new.consumption:= (new.power_total+ rec.power_total)* extract(epoch from new.created_at- rec.created_at); return new;end $$;create trigger before_insert_on_measurementsbefore insert on measurementsfor each row execute procedure before_insert_on_measurements();
查询：
select device_id, sum(consumption) total_consumptionfrom measurements-- where conditionsgroup by 1order by 1
