PostgreSQL按组查询时间排序的连续特定标签值首段数据

测试表结构 #

CREATE TABLE test_data (
    rowid SERIAL PRIMARY KEY,
    entryid INT,
    "TimeStamp" TIMESTAMP,
    labels INT
);

测试插入数据 #

INSERT INTO test_data (entryid, "TimeStamp", labels) VALUES
(1, '2024-01-01 00:00:00', 1),
(2, '2024-01-01 00:01:00', 1),
(3, '2024-01-01 00:02:00', 0),
(4, '2024-01-01 00:03:00', 0),
(5, '2024-01-01 00:04:00', 1),
(6, '2024-01-01 00:00:00', 0),
(7, '2024-01-01 00:01:00', 1),
(8, '2024-01-01 00:02:00', 1),
(9, '2024-01-01 00:03:00', 0),
(10, '2024-01-01 00:04:00', 0);

查询方案 #

核心思路是通过窗口函数识别连续相同label的分组，再筛选每个entryid下首次出现的label=0和label=1的连续块：

WITH labeled_blocks AS (
    SELECT
        rowid,
        entryid,
        "TimeStamp",
        labels,
        -- 标记连续相同label的分组：当前label与前一行不同时，分组编号+1
        SUM(CASE WHEN labels = LAG(labels) OVER (PARTITION BY entryid ORDER BY "TimeStamp") THEN 0 ELSE 1 END) 
            OVER (PARTITION BY entryid ORDER BY "TimeStamp") AS block_id
    FROM test_data
),
first_blocks AS (
    SELECT
        entryid,
        labels,
        MIN(block_id) AS first_block_id
    FROM labeled_blocks
    GROUP BY entryid, labels
)
SELECT lb.rowid
FROM labeled_blocks lb
JOIN first_blocks fb 
    ON lb.entryid = fb.entryid 
    AND lb.labels = fb.labels 
    AND lb.block_id = fb.first_block_id
ORDER BY lb.entryid, lb."TimeStamp";

逻辑解释 #

1. labeled_blocks CTE：按entryid分组、TimeStamp排序，用LAG()函数对比当前行与前一行的labels值，生成连续相同label的分组编号block_id。
2. first_blocks CTE：对每个entryid和labels，找到首次出现的连续块编号（最小的block_id）。
3. 最终查询：关联两个CTE，提取属于首次连续块的所有rowid，并按entryid和时间排序。

预期结果 #

针对上述测试数据，返回的rowid为：1,2,3,4,6,7,8

内容的提问来源于stack exchange，提问作者Christian Kim Eschen