I am collecting power data from a set of sensors at uneven intervals. I want to create energy aggregations from the power data. The power data is stored in the following table:
CREATE TABLE power_rows (
id INT GENERATED ALWAYS AS IDENTITY,
sensor_id TEXT,
"timestamp" TIMESTAMPTZ,
"value" INT NOT NULL,
PRIMARY KEY (sensor_id, "timestamp")
) WITH (
timescaledb.hypertable = true,
timescaledb.partition_column = 'timestamp',
timescaledb.segmentby = 'sensor_id'
);
Now, I want run the following query to gather 1 minute aggregations:
SELECT
sensor_id,
time AS from,
time + INTERVAL '1 minute' AS to,
interpolated_integral(
tws,
time,
'1 minute',
LAG(tws) OVER (PARTITION BY sensor_id ORDER BY time),
LEAD(tws) OVER (PARTITION BY sensor_id ORDER BY time),
'hours'
) / 1000 AS value
FROM (
SELECT
sensor_id,
time_bucket('1 minute', timestamp) AS time,
time_weight('Linear', timestamp, value) AS tws
FROM power_rows
GROUP BY sensor_id, time
) t;
which works as intended. However when wrapping the query above in a materialized view
CREATE MATERIALIZED VIEW energy_rows_1min
WITH (
timescaledb.continuous = true,
timescaledb.materialized_only = false
) AS
<query from above>
GROUP BY sensor_id, "from";
I get the following error
ERROR: column "t.tws" must appear in the GROUP BY clause or be used in an aggregate function
How can make the query be a continuous aggregate?
(I have set enabled experimental window function support by setting timescaledb.enable_cagg_window_functions)