PostgreSQL计算匹配条件行时间差：统计各名称处于state=1的独立时长

这是SQL中典型的连续状态区间识别问题，可通过PostgreSQL内置窗口函数单查询实现，无需创建临时表，执行效率高于多步临时表方案，逻辑更简洁。

核心实现逻辑 #

仅通过两步窗口计算即可完成独立区间的识别，无需拆分多步写入临时表：

1. 按name分区、按time升序排序，为每一条记录标记所属的连续状态块：
   • 对比当前行的state和同name下前一条记录的state值，若状态发生变化则标记为1，未变化则标记为0
   • 对上述标记值做分区内累计求和，得到的数值就是连续状态块的唯一分组ID：同一段连续不变的状态会被归入同一个分组
2. 过滤出所有state=1的状态块，对每个分组取块内最早时间作为区间起始、最晚时间作为区间结束，两者的时间差即为该段区间的总时长。

可直接运行的SQL实现 #

WITH state_block AS (
    SELECT
        name,
        state,
        time,
        -- 计算连续状态块的分组ID
        SUM(
            CASE WHEN state != lag(state, 1, state) OVER w THEN 1 ELSE 0 END
        ) OVER w AS block_id
    FROM intervals
    WINDOW w AS (PARTITION BY name ORDER BY time)
)
SELECT
    name,
    MIN(time) AS interval_start,
    MAX(time) AS interval_end,
    MAX(time) - MIN(time) AS interval_duration
FROM state_block
WHERE state = 1
GROUP BY name, block_id
ORDER BY interval_start;

样例输出结果 #

运行上述SQL可直接得到符合预期的计算结果：

实用优化提示 #

• 若表数据量较大，建议提前建立(name, time, state)联合索引，窗口函数执行时可直接走索引避免全表排序
• 若需要得到精确的数值型秒级时长，可使用EXTRACT(EPOCH FROM (MAX(time) - MIN(time)))将时间间隔转换为带小数的秒值
• 该方案兼容PostgreSQL 9.1及以上所有版本，无版本兼容问题

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