PostgreSQL时间序列按不活动间隙实现会话窗口分组的方法

PostgreSQL未内置session_window函数，可通过窗口函数组合实现按时间间隙分组的会话逻辑，具体实现如下：

实现逻辑 #

• 第一步：按时间戳排序后，计算每行与上一行的时间间隔
• 第二步：标记间隔超过10分钟的行作为新会话的起点
• 第三步：累加新会话标记生成唯一会话ID，按会话ID分组聚合即可得到结果

完整查询SQL #

WITH lag_cal AS (
    -- 计算上一行的时间戳
    SELECT
        timestamp,
        value,
        LAG(timestamp) OVER (ORDER BY timestamp) AS previous_timestamp
    FROM 你的实际表名 -- 替换为实际表名
),
session_flag AS (
    -- 标记新会话起点
    SELECT
        timestamp,
        value,
        CASE
            WHEN previous_timestamp IS NULL OR timestamp - previous_timestamp > INTERVAL '10 minutes'
            THEN 1
            ELSE 0
        END AS new_session_mark
    FROM lag_cal
),
session_id AS (
    -- 生成唯一会话ID
    SELECT
        timestamp,
        value,
        SUM(new_session_mark) OVER (ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
    FROM session_flag
)
-- 聚合得到会话窗口结果
SELECT
    MAX(timestamp) AS "end",
    MIN(timestamp) AS "start",
    AVG(value) AS "average"
FROM session_id
GROUP BY session_id
ORDER BY "start" DESC;

扩展说明 #

如果你的数据包含多个IoT设备，需要按设备独立计算会话窗口，只需在所有窗口函数的OVER子句中增加PARTITION BY 设备ID字段即可，示例：

LAG(timestamp) OVER (PARTITION BY device_id ORDER BY timestamp) AS previous_timestamp

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