有没有办法给toolkit_experimental.interpolated_average函数增加一个额外的GROUP BY？假设我的数据有不同传感器的功率测量；我如何在sensor_id上添加一个组？
with s as (
select sensor_id,
time_bucket('30 minutes', timestamp) bucket,
time_weight('LOCF', timestamp, value) agg
from
measurements m
inner join sensor_definition sd on m.sensor_id = sd.id
where asset_id = '<battery_id>' and sensor_name = 'power' and
timestamp between '2023-01-05 23:30:00' and '2023-01-07 00:30:00'
group by sensor_id, bucket)
select sensor_id,
bucket,
toolkit_experimental.interpolated_average(
agg,
bucket,
'30 minutes'::interval,
lag(agg) over (order by bucket),
lead(agg) over (order by bucket)
)
from s
group by sensor_id;
上面的查询不起作用，因为我还需要添加bucket和agg作为GROUP BY列.
您可以在下面找到相关的架构.
create table measurements
(
sensor_id uuid not null,
timestamp timestamp with time zone not null,
value double precision not null
);
create table sensor_definition
(
id uuid default uuid_generate_v4() not null
primary key,
asset_id uuid not null,
sensor_name varchar(256) not null,
sensor_type varchar(256) not null,
unique (asset_id, sensor_name, sensor_type)
);
有什么建议吗？