Calculated fields on timescale

I’m trying to add calculated fields to my hypertable with sensor data, e.g., I would like to add the derivative of the volume readings to get the volume flow.

I have tried to do this with a continuous aggregate, but I get invalid SELECT query for continuous aggregate.

create view public.readings_raw_with_calc_fields
WITH (timescaledb.continuous)
as
select 
serial,
time,
type,
value
from public.readings_raw
union all
select 
serial,
time,
'Volume flow calc.' as type,
(lead(value) over (partition by serial,devicetype,manufacturer order by time))-value as value
from 
public.readings_raw
where type='Volume'

Is this because of the lead function or is it because there has to be an aggregation function in a continuous aggregate? What would be best practice for this? I could make a job that inserts the data in the original table every minute, but then I wouldn’t catch if new data is inserted back in time (it’s a rather large table, so I can’t run through the whole table every minute).

Answer

Both the windowing function, and the UNION will prevent continuous aggregates from working.

In this case, since the computation being performed per-row is so low, an index on time and a regular view may provide the needed performance.