如何为Timescale的interpolated_average函数添加sensor_id分组？

你的核心问题有两个：一是窗口函数lag()/lead()未按sensor_id分区，导致跨传感器取前后聚合值；二是主查询错误添加GROUP BY sensor_id引发语法报错。以下是修正后的查询和关键说明：

修正后的查询 #

WITH s AS (
  SELECT 
    sensor_id,
    time_bucket('30 minutes', timestamp) AS bucket,
    time_weight('LOCF', timestamp, value) AS 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 (PARTITION BY sensor_id ORDER BY bucket),
    lead(agg) OVER (PARTITION BY sensor_id ORDER BY bucket)
  ) AS interpolated_avg
FROM s
ORDER BY sensor_id, bucket;

关键修改点 #

• 窗口函数分区：给lag()和lead()添加PARTITION BY sensor_id，确保仅在同一传感器的时间桶范围内取前/后聚合值，避免跨传感器的数据干扰。
• 移除多余的GROUP BY：主查询不需要GROUP BY sensor_id，因为interpolated_average是针对每个sensor_id+bucket的单行进行计算的，每个行对应一个独立的插值结果。原查询的GROUP BY会强制合并同一传感器的所有行，触发SQL语法规则报错（非聚合列bucket、agg未加入GROUP BY）。

补充说明 #

interpolated_average需要当前时间桶的聚合值、桶时间、桶间隔，以及前后桶的聚合值完成插值计算。通过按sensor_id分区窗口函数，可保证每个传感器的插值计算独立执行，完全匹配你的分组需求。

内容的提问来源于stack exchange，提问作者Gillis Van Ginderachter