如何在Timescale连续聚合中获取最大值对应的关联字段？

表结构 #

ts     timestamp
value1 decimal
value2 decimal
ts_v2  timestamp

需求 #

创建一个连续聚合视图，按60秒的time_bucket分组，完成以下计算：

• 统计每组value1的总和
• 找出每组value2的最大值
• 获取该value2最大值对应行的ts_v2字段

用户初始尝试的SQL（存在逻辑问题）：

CREATE MATERIALIZED VIEW IF NOT EXISTS {schemaName}.{viewName}
WITH (timescaledb.continuous) AS
(
    SELECT
        public.time_bucket(INTERVAL '60 second', ts) AS new_ts_bucket,
        sum(value1)                                   AS value1,
        max(value2)                                   AS value2,
        -- 获取value2最大值对应行的ts_v2
        ts_v2

    FROM
        {schemaName}.{tableName}
    GROUP BY new_ts_bucket
    ORDER BY new_ts_bucket
);

示例输入 #

xxx, 10, 20, ts_1
xxx, 20, 30, ts_2
xxx, 30, 40, ts_3 <- value2最大值，需取该行的ts_v2
xxx, 40, 30, ts_4

期望输出 #

xxx, 100, 40, ts_3

解决方案 #

Timescale连续聚合对部分语法有兼容性限制，以下两种方式可实现需求：

方式1：结合窗口函数与DISTINCT ON #

CREATE MATERIALIZED VIEW IF NOT EXISTS {schemaName}.{viewName}
WITH (timescaledb.continuous) AS
(
    SELECT DISTINCT ON (new_ts_bucket)
        public.time_bucket(INTERVAL '60 second', ts) AS new_ts_bucket,
        sum(value1) OVER (PARTITION BY new_ts_bucket) AS value1,
        max(value2) OVER (PARTITION BY new_ts_bucket) AS value2,
        ts_v2
    FROM {schemaName}.{tableName}
    ORDER BY new_ts_bucket, value2 DESC, ts DESC
)
WITH NO DATA;

说明：通过窗口函数计算分组内的总和与最大值，再利用DISTINCT ON+排序逻辑，确保取到value2最大的行的ts_v2；若同分组内存在多个value2等于最大值的行，会按ts降序取最新的一行。

方式2：子查询聚合后关联原表 #

CREATE MATERIALIZED VIEW IF NOT EXISTS {schemaName}.{viewName}
WITH (timescaledb.continuous) AS
(
    SELECT
        agg.new_ts_bucket,
        agg.value1_sum AS value1,
        agg.value2_max AS value2,
        t.ts_v2
    FROM (
        SELECT
            public.time_bucket(INTERVAL '60 second', ts) AS new_ts_bucket,
            sum(value1) AS value1_sum,
            max(value2) AS value2_max
        FROM {schemaName}.{tableName}
        GROUP BY new_ts_bucket
    ) agg
    JOIN {schemaName}.{tableName} t
        ON public.time_bucket(INTERVAL '60 second', t.ts) = agg.new_ts_bucket
        AND t.value2 = agg.value2_max
    ORDER BY agg.new_ts_bucket
)
WITH NO DATA;

说明：先通过子查询计算分组聚合结果，再关联原表匹配value2最大值对应的行；若同分组内存在多个value2等于最大值的行，会返回多条记录，可添加DISTINCT ON或额外排序条件确保结果唯一。

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