PostgreSQL中按Workflow计算响应平均处理时长的SQL实现

这个需求其实是典型的同组内记录配对问题——要在*同一个workflow_id*下，把每条Status = 'Resp'的记录和它之前最近的Status = 'Open'的记录配对，再计算两者的日期间隔。下面给你两种实用的SQL实现方案，适配不同的PostgreSQL版本：

方法一：使用窗口函数分组（兼容所有PostgreSQL版本） #

这种方法通过给同属一对的Open/Resp记录分配相同的分组ID，再通过分组关联配对，兼容性最好：

WITH ranked_records AS (
    SELECT
        id,
        Status,
        Assigned,
        workflow_Id,
        -- 先把字符串格式的日期转成PostgreSQL的date类型（如果原字段已是date可省略）
        TO_DATE(Date, 'DD-MM-YYYY') AS actual_date,
        -- 每次遇到Open就增加分组编号，让后续Resp和最近的Open同组
        SUM(CASE WHEN Status = 'Open' THEN 1 ELSE 0 END) OVER (
            PARTITION BY workflow_Id 
            ORDER BY TO_DATE(Date, 'DD-MM-YYYY') ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM your_table_name -- 替换成你的实际表名
)
SELECT
    r_open.Assigned,
    r_open.workflow_Id,
    -- 把日期转回你需要的字符串格式
    TO_CHAR(r_open.actual_date, 'DD-MM-YYYY') AS "Open Date",
    TO_CHAR(r_resp.actual_date, 'DD-MM-YYYY') AS "Resp Date",
    -- 计算时长并格式化输出（自动处理单复数）
    CONCAT(
        (r_resp.actual_date - r_open.actual_date),
        ' day',
        CASE WHEN (r_resp.actual_date - r_open.actual_date) != 1 THEN 's' ELSE '' END
    ) AS Avg_Handle_Duration
FROM ranked_records r_open
JOIN ranked_records r_resp
    ON r_open.workflow_Id = r_resp.workflow_Id
    AND r_open.group_id = r_resp.group_id
    AND r_open.Status = 'Open'
    AND r_resp.Status = 'Resp';

逻辑说明： #

1. 先用CTEranked_records给每个workflow_id下的记录按日期排序，通过SUM()窗口函数生成group_id——每碰到一条Open记录，分组编号就加1，这样后续的Resp会自动归到最近的Open所在组。
2. 再将CTE中的Open和Resp记录通过workflow_id和group_id关联，确保每对都是正确的对应关系。
3. 最后计算日期差，并格式化出符合要求的时长文本。

方法二：使用MATCH_RECOGNIZE（PostgreSQL 12+） #

如果你的PostgreSQL版本在12及以上，推荐用MATCH_RECOGNIZE——这是专门为模式匹配设计的语法，代码更直观：

SELECT
    Assigned,
    workflow_Id,
    TO_CHAR(open_date, 'DD-MM-YYYY') AS "Open Date",
    TO_CHAR(resp_date, 'DD-MM-YYYY') AS "Resp Date",
    CONCAT(
        (resp_date - open_date),
        ' day',
        CASE WHEN (resp_date - open_date) != 1 THEN 's' ELSE '' END
    ) AS Avg_Handle_Duration
FROM your_table_name
MATCH_RECOGNIZE (
    -- 按工作流和经办人分组，确保只在同一范围内匹配
    PARTITION BY workflow_Id, Assigned
    -- 按日期排序，保证匹配顺序正确
    ORDER BY TO_DATE(Date, 'DD-MM-YYYY') ASC
    -- 提取匹配到的Open和Resp的字段
    MEASURES
        OPEN.actual_date AS open_date,
        RESP.actual_date AS resp_date,
        OPEN.Assigned AS Assigned,
        OPEN.workflow_Id AS workflow_Id
    -- 定义要匹配的模式：一条Open后面紧跟一条Resp
    PATTERN (OPEN RESP)
    -- 定义模式中每个别名对应的条件
    DEFINE
        OPEN AS Status = 'Open',
        RESP AS Status = 'Resp'
) AS mr;

逻辑说明： #

• MATCH_RECOGNIZE会在有序的分组数据里，精准匹配Open后跟Resp的记录对，直接提取你需要的字段即可，不用手动分组关联，代码更简洁易懂。

额外提示： #

• 如果你的Date字段已经是PostgreSQL的date类型，直接去掉TO_DATE和TO_CHAR的转换逻辑就行。
• 如果存在一个Open对应多个Resp的场景，方法一会把每个Resp都和同一个Open配对；如果需要只取每个Open对应的最后一个Resp，可以在CTE里加ROW_NUMBER()窗口函数筛选。

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