PostgreSQL中按条件关联员工项目与职位表的实现需求

实现思路 #

要满足需求，需要先汇总所有员工的项目、职位变更时间点，再为每个时间点匹配当时员工的有效项目和职位状态，具体分为三步：

1. 收集所有员工的项目变更、职位变更日期，形成完整的时间点序列
2. 针对每个时间点，查询该员工在该时间点及之前生效的最新项目、职位
3. 合并结果并去重，按员工和日期排序

SQL 代码 #

WITH all_events AS (
    -- 合并两类变更的日期，确保每个时间点都被覆盖
    SELECT employee_id, effective_date FROM Table1
    UNION
    SELECT employee_id, effective_date FROM Table2
),
latest_project AS (
    -- 匹配每个时间点对应的最新生效项目
    SELECT 
        ae.employee_id,
        ae.effective_date,
        FIRST_VALUE(t1.project) OVER (
            PARTITION BY ae.employee_id, ae.effective_date
            ORDER BY t1.effective_date DESC
        ) AS project
    FROM all_events ae
    LEFT JOIN Table1 t1 
        ON ae.employee_id = t1.employee_id
        AND t1.effective_date <= ae.effective_date
),
latest_designation AS (
    -- 匹配每个时间点对应的最新生效职位
    SELECT 
        ae.employee_id,
        ae.effective_date,
        FIRST_VALUE(t2.designation) OVER (
            PARTITION BY ae.employee_id, ae.effective_date
            ORDER BY t2.effective_date DESC
        ) AS designation
    FROM all_events ae
    LEFT JOIN Table2 t2 
        ON ae.employee_id = t2.employee_id
        AND t2.effective_date <= ae.effective_date
)
-- 合并结果并去重
SELECT DISTINCT
    lp.employee_id,
    lp.project,
    ld.designation,
    lp.effective_date
FROM latest_project lp
JOIN latest_designation ld 
    ON lp.employee_id = ld.employee_id
    AND lp.effective_date = ld.effective_date
ORDER BY lp.employee_id, lp.effective_date;

代码说明 #

• all_events：通过UNION去重合并两张表的变更日期，避免重复处理同一时间点
• latest_project/latest_designation：使用FIRST_VALUE窗口函数，按生效日期倒序取每个时间点之前的最新状态，确保变更发生时能关联到当时有效的项目/职位
• 最终关联两个子查询结果，去重后得到符合要求的变更记录

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