在PostgreSQL中基于列值获取连续时间范围的实现方法

这是典型的连续相同值分组问题，我们可以通过PostgreSQL的窗口函数生成分组标识，再聚合得到每个连续状态的起止时间。

完整SQL实现 #

假设你的表名为status_log，包含date（时间列）和flag（状态列），执行以下SQL即可得到目标结果：

WITH grouped AS (
    SELECT 
        date,
        flag,
        -- 生成分组ID：全局行号减去同flag内的行号，连续相同flag的行差值一致
        ROW_NUMBER() OVER (ORDER BY date) - 
        ROW_NUMBER() OVER (PARTITION BY flag ORDER BY date) AS group_id
    FROM status_log
)
SELECT
    MIN(date) AS start,
    MAX(date) AS end,
    flag
FROM grouped
GROUP BY group_id, flag
ORDER BY start;

逻辑说明 #

1. 生成分组ID：

   • ROW_NUMBER() OVER (ORDER BY date)：给所有行按时间顺序生成全局唯一的序号；
   • ROW_NUMBER() OVER (PARTITION BY flag ORDER BY date)：按flag分区，给每个分区内的行按时间生成序号；
   • 两者的差值group_id会为连续相同flag的行分配同一个ID——因为连续同flag的行，全局序号和组内序号同步增长，差值保持不变。

2. 聚合计算起止时间：
   按group_id和flag分组，取每组的最小时间作为start，最大时间作为end，最后按start排序即可得到连续状态的时间段列表。

测试验证 #

用你提供的测试数据执行上述SQL，会输出：

start         |          end           | flag 
------------------------+------------------------+------
 2023-08-06 23:35:00+00 | 2023-08-06 23:37:00+00 | t
 2023-08-06 23:38:00+00 | 2023-08-06 23:41:00+00 | f
 2023-08-06 23:42:00+00 | 2023-08-06 23:44:00+00 | t

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