Interpolated_average() usage with continuous aggregates

TimescaleDB and PostgreSQL
,
1

Is it possible to use interpolated_average() with continuous aggregates somehow? My first attempt fails due to window functions.

My use case is the following. I’m in the energy sector and collect power unit data from the sites we operate in. The measurements come in irregularly sampled and we’d like to use materialized views for standard aggregation periods like 1 second, 1 minute and 15 minutes. If I understand correctly, average(time_weight(...)) would introduce small errors at the bucket bounds and interpolated_average() is the right aggregation function for correct averages, even around the bucket bounds. But the interpolated_average() aggregation function variant of average() isn’t compatible with continuous aggregates due the usage of lag() and lead() window functions. Is there a way around this limitation?

Here is my test schema

CREATE TABLE IF NOT EXISTS series_point (
    series_name VARCHAR(255) NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL,
    value DOUBLE PRECISION NOT NULL,
    PRIMARY KEY (series_name, timestamp)
);

SELECT create_hypertable('series_point', 'timestamp', if_not_exists => TRUE);

create MATERIALIZED VIEW series_point_agg_1min WITH (timescaledb.continuous) as
with t as (
    SELECT
    	series_name,
    	time_bucket('1 minute', timestamp) AS time,
  	    time_weight('Linear', timestamp, value) as tw
	FROM series_point
	group by series_name, time
)
SELECT
    series_name,
    time,
    interpolated_average(
    	tw,
    	time,
    	'1 minute',
    	LAG(tw) OVER (PARTITION BY series_name ORDER by time),
    	LEAD(tw) OVER (PARTITION BY series_name ORDER by time)
    ) AS average
from t
order by series_name, time;
2

Posted as a feature request in the github issue tracker.