Timescale查询报错：counter_agg(double precision, double precision)函数不存在

背景 #

• table1：存储5个传感器的时间序列数据，包含字段t（时间戳）、var1（传感器读数）、id（传感器唯一标识）
• table2：存储另一组传感器的时间序列数据，包含字段t（时间戳）、var2（传感器读数）、id（传感器唯一标识）
• 两张表的采样时间点不统一，采样频率约1秒/次，但时间随机分布

需求 #

1. 提取最近10秒的数据，通过time_bucket将var1和var2重采样至整秒时间点
2. 关联两张表重采样后的时间序列，计算二者的相关性

原查询及问题 #

最初编写的查询语句：

WITH my_selection AS (
    SELECT
        time_bucket('1 seconds', a.t) as bucket,
        counter_agg(a.t_average, b.a_mag) as summary
    FROM table1 a
    LEFT JOIN table2 b
    ON time_bucket('1 seconds', a.t) = time_bucket('1 seconds', b.t)
    WHERE a.t > now () - INTERVAL '10 seconds'
    AND   (b.t > now () - INTERVAL '10 seconds'
    AND a.id = '1'
    AND b.id = '2')
)
SELECT
    bucket,
    corr(summary)
FROM my_selection
ORDER BY bucket

执行时报错：function counter_agg(double precision, double precision) does not exist，已确认安装Timescale Toolkit且能运行简单的counter_agg示例。

后续发现corr(summary)仅返回单个值，调整后的简化查询：

WITH my_selection AS (
    SELECT
        stats_agg(a.t_average, b.a_mag) as summary
    FROM table1 a
    LEFT JOIN table2 b
    ON time_bucket('1 second', a.t) = time_bucket('1 second', b.t)
    WHERE a.t > now () - INTERVAL '10 seconds'
    AND   (b.t > now () - INTERVAL '10 seconds'
    AND a.id = '1'
    AND b.id = '2')
)
SELECT
    corr(summary)
FROM  my_selection

解决建议 #

1. 纠正聚合函数使用错误 #

counter_agg用于处理单调递增的计数器数据，参数格式为(time, value)，不适用于你的相关性计算场景。你后续改用stats_agg是正确的，该函数专门用于收集成对数值以计算统计指标（如相关性）。

2. 修复表关联逻辑 #

原查询中LEFT JOIN配合b.t > now() - INTERVAL '10 seconds'的条件，会自动将左连接转为内连接（过滤掉左表中无右表匹配的行）。若要保留所有重采样后的时间桶，应先分别对两张表做重采样聚合，再进行关联。

3. 正确的查询实现 #

WITH resampled_table1 AS (
    SELECT
        time_bucket('1 second', t) AS bucket,
        AVG(var1) AS var1_avg  -- 可根据需求替换为last(var1, t)等聚合方式
    FROM table1
    WHERE id = '1' AND t > now() - INTERVAL '10 seconds'
    GROUP BY bucket
),
resampled_table2 AS (
    SELECT
        time_bucket('1 second', t) AS bucket,
        AVG(var2) AS var2_avg
    FROM table2
    WHERE id = '2' AND t > now() - INTERVAL '10 seconds'
    GROUP BY bucket
)
SELECT corr(stats_agg(r1.var1_avg, r2.var2_avg)) AS correlation
FROM resampled_table1 r1
JOIN resampled_table2 r2 ON r1.bucket = r2.bucket;

4. 关键说明 #

• 分别重采样再关联，可避免同时间桶内多数据行导致的笛卡尔积，确保每个时间桶仅一条数据
• 若需保留无匹配的时间桶，可将JOIN改为FULL JOIN，但计算相关性时会自动忽略NULL值
• 聚合函数AVG可替换为last(var1, t)（需Toolkit支持），用于获取每个时间桶的最后一条读数，更贴合时间序列特性

内容的提问来源于stack exchange，提问作者pook