PostgreSQL中项目状态时间线的优先级划分与线性化实现

假设你的工作流状态表结构为workflow_status，包含字段：

• project_id：项目ID
• workflow_id：工作流ID
• status：工作流状态（取值为In Progress/Pause/Completed）
• start_date：状态开始日期
• end_date：状态结束日期（未完成状态可为NULL）

以下是符合你需求的SQL实现：

WITH all_time_points AS (
    -- 提取所有工作流状态的时间边界（开始/结束日期）
    SELECT project_id, start_date AS time_point FROM workflow_status
    UNION
    SELECT project_id, end_date AS time_point FROM workflow_status
),
project_time_intervals AS (
    -- 生成项目的连续时间区间
    SELECT
        project_id,
        time_point AS interval_start,
        LEAD(time_point) OVER (PARTITION BY project_id ORDER BY time_point) AS interval_end
    FROM all_time_points
    WHERE time_point IS NOT NULL
),
interval_workflow_status AS (
    -- 关联每个时间区间内的所有有效工作流状态
    SELECT
        pti.project_id,
        pti.interval_start,
        pti.interval_end,
        ws.status
    FROM project_time_intervals pti
    JOIN workflow_status ws
        ON pti.project_id = ws.project_id
        AND ws.start_date <= pti.interval_start
        AND (ws.end_date >= pti.interval_end OR ws.end_date IS NULL)
    WHERE pti.interval_end IS NOT NULL
),
project_status_timeline AS (
    -- 按优先级规则确定每个区间的项目状态
    SELECT
        project_id,
        interval_start,
        interval_end,
        CASE
            WHEN MAX(CASE status 
                WHEN 'In Progress' THEN 3 
                WHEN 'Pause' THEN 2 
                WHEN 'Completed' THEN 1 
                ELSE 0 
            END) = 3 THEN 'In Progress'
            WHEN MAX(CASE status 
                WHEN 'In Progress' THEN 3 
                WHEN 'Pause' THEN 2 
                WHEN 'Completed' THEN 1 
                ELSE 0 
            END) = 2 THEN 'Pause'
            ELSE 'Completed'
        END AS project_status
    FROM interval_workflow_status
    GROUP BY project_id, interval_start, interval_end
    ORDER BY project_id, interval_start
)
SELECT * FROM project_status_timeline;

逻辑说明 #

1. 提取时间边界：通过all_time_points收集所有工作流状态的开始和结束日期，作为划分项目时间线的节点。
2. 生成连续区间：使用LEAD窗口函数将相邻时间点组合成连续的时间区间，确保每个区间内所有工作流的状态无变化。
3. 关联区间与工作流状态：匹配每个时间区间内所有处于有效状态的工作流，包括未结束（end_date为NULL）的状态。
4. 按优先级计算项目状态：给状态赋予优先级权重（In Progress=3 > Pause=2 > Completed=1），取区间内权重最大值对应的状态，完全符合你定义的规则。

适配调整 #

如果你的表结构或字段命名不同，只需对应修改workflow_status的字段引用即可。若存在未设置end_date的永久完成状态，可在workflow_status关联条件中补充对应判断。

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