PostgreSQL中查找指定代码间时间间隔的SQL解决方案求助

方法一：使用PostgreSQL的MATCH_RECOGNIZE（推荐，PostgreSQL 12+） #

MATCH_RECOGNIZE专门用于处理这类序列模式匹配场景，能精准匹配每个code=8之后的第一个code=9并计算时间差：

SELECT deviceId, (end_ts - start_ts) AS "elapsed time"
FROM logs
MATCH_RECOGNIZE (
    PARTITION BY deviceId
    ORDER BY timestamp
    MEASURES
        START_8.timestamp AS start_ts,
        END_9.timestamp AS end_ts,
        START_8.deviceId AS deviceId
    PATTERN (START_8 .*? END_9)
    DEFINE
        START_8 AS code = 8,
        END_9 AS code = 9
);

说明：

• PARTITION BY deviceId：按设备分组处理，确保仅在同一设备内匹配序列
• ORDER BY timestamp：按时间顺序匹配，保证取到的是8之后的第一个9
• PATTERN (START_8 .*? END_9)：非贪婪模式匹配，避免匹配到后续的多个9
• MEASURES：提取匹配到的8和9的时间戳，计算差值得到耗时

执行后会直接输出你需要的结果：

方法二：兼容旧版本PostgreSQL（无MATCH_RECOGNIZE） #

如果你的PostgreSQL版本低于12，可以用窗口函数+子查询实现：

WITH filtered_logs AS (
    -- 筛选仅包含8和9的记录，减少计算量
    SELECT deviceId, code, timestamp
    FROM logs
    WHERE code IN (8, 9)
    ORDER BY deviceId, timestamp
),
grouped_logs AS (
    -- 给每个8开头的序列分配组ID，后续记录归属于当前组直到下一个8出现
    SELECT 
        deviceId,
        code,
        timestamp,
        SUM(CASE WHEN code = 8 THEN 1 ELSE 0 END) OVER (PARTITION BY deviceId ORDER BY timestamp) AS group_id
    FROM filtered_logs
),
matched_pairs AS (
    -- 每个组内取8的最早时间、9的最早时间（确保是第一个9）
    SELECT
        deviceId,
        group_id,
        MIN(CASE WHEN code = 8 THEN timestamp END) AS start_ts,
        MIN(CASE WHEN code = 9 THEN timestamp END) AS end_ts
    FROM grouped_logs
    GROUP BY deviceId, group_id
    -- 过滤掉只有8没有9的无效组
    HAVING MIN(CASE WHEN code = 9 THEN timestamp END) IS NOT NULL
)
SELECT deviceId, (end_ts - start_ts) AS "elapsed time"
FROM matched_pairs;

说明：

1. filtered_logs：过滤无关记录，只保留目标code的行
2. grouped_logs：通过累加8的出现次数生成组ID，每个8对应一个新组
3. matched_pairs：提取每组内的有效时间对，过滤无匹配的组
4. 最后计算时间差得到结果

核心思路 #

• 必须按设备分组、按时间排序，确保匹配的是同一设备内8之后的第一个9
• 避免重复匹配：无论是非贪婪模式还是组ID分配，都能保证一个8只对应第一个后续的9，一个9不会被多个8匹配
• 自动过滤无效序列：通过模式匹配特性或HAVING条件，忽略只有8或只有9的情况

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