如何编写PostgreSQL查询实现TimescaleDB连续聚合，统计设备每小时ON时长？

样本数据 #

核心思路 #

要统计每小时的ON状态时长，需先计算每个状态的持续时间，再将这些时间按小时分段累加。重点处理跨小时的状态事件，确保只统计事件落在当前小时内的部分。

基础查询语句 #

通过CTE关联每个状态的起始和结束时间，再按小时聚合计算时长：

WITH state_durations AS (
    SELECT
        time_stamp AS start_time,
        LEAD(time_stamp) OVER (ORDER BY time_stamp) AS end_time,
        state
    FROM device_state
)
SELECT
    time_bucket('1 hour', start_time) AS hour_bucket,
    SUM(
        EXTRACT(EPOCH FROM 
            LEAST(end_time, time_bucket('1 hour', start_time) + INTERVAL '1 hour') 
            - GREATEST(start_time, time_bucket('1 hour', start_time))
        ) / 60
    ) AS on_duration_minutes
FROM state_durations
WHERE state = 1 AND end_time IS NOT NULL
GROUP BY hour_bucket
ORDER BY hour_bucket;

语句说明 #

• state_durations：用LEAD()函数获取每个状态的结束时间（即下一条记录的时间戳）
• time_bucket('1 hour', start_time)：按小时生成时间桶
• LEAST()和GREATEST()：截取跨小时事件中落在当前小时内的时间区间
• EXTRACT(EPOCH FROM ...)/60：将时间差转换为分钟

TimescaleDB连续聚合实现 #

如果你的表是TimescaleDB超表，可创建连续聚合视图让系统自动维护统计结果：

1. 确保表是超表（若未创建） #

SELECT create_hypertable('device_state', 'time_stamp');

2. 创建连续聚合视图 #

CREATE MATERIALIZED VIEW device_hourly_on_duration
WITH (timescaledb.continuous) AS
WITH state_durations AS (
    SELECT
        time_stamp AS start_time,
        LEAD(time_stamp) OVER (ORDER BY time_stamp) AS end_time,
        state
    FROM device_state
)
SELECT
    time_bucket('1 hour', start_time) AS hour_bucket,
    SUM(
        EXTRACT(EPOCH FROM 
            LEAST(end_time, time_bucket('1 hour', start_time) + INTERVAL '1 hour') 
            - GREATEST(start_time, time_bucket('1 hour', start_time))
        ) / 60
    ) AS on_duration_minutes
FROM state_durations
WHERE state = 1 AND end_time IS NOT NULL
GROUP BY hour_bucket
WITH DATA;

连续聚合说明 #

• WITH (timescaledb.continuous)：标记为连续聚合视图
• WITH DATA：初始化视图，填充已有数据的统计结果
• TimescaleDB会自动定期刷新视图，无需手动重新计算

结果验证 #

针对样本数据，运行查询后会得到：

• 2023-10-04 10:00:00：约30分钟（示例的35分钟为近似值，精确计算为30分2秒）
• 2023-10-04 11:00:00：约44分钟（示例的45分钟为近似值，精确计算为44分31秒）

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