修改SQL查询获取员工最新Union Code首次绑定的起始日期

原始ASG分配表结构及数据 #

NUMBER      START_DATE              END_DATE                POSITION            UNION CODE        MANAGER
10          01-JAN-2006             17-AUG-2017             MANAGER             6790             Cyndy
10          18-AUG-2017             10-JUL-2018             SUPER MANAGER       6790             Cyndy
10          11-JUL-2018             31-12-4712              SUPER MANAGER       6791             Cyndy

10          01-JAN-2006             07-AUG-2017             associate           6790             Bali
10          08-AUG-2017             10-JUL-2019             sr. associate       6790             Bali
10          11-JUL-2019             31-12-4712              MANAGER             6790             Bali

原查询语句 #

用于获取当前生效行的SQL：

select NUMBER, START_DATE, POSITION, UNION_CODE, MANAGER 
from asg
where sysdate between start_Date and end_date

需求说明 #

修改上述查询，按NUMBER和MANAGER分组，找到每组最新的Union Code，并获取该Code首次绑定的起始日期，同时返回对应职位信息。

期望输出 #

NUMBER      START_DATE              POSITION            UNION CODE        MANAGER
10          11-JUL-2018             SUPER MANAGER       6791             Cyndy
10          01-JAN-2006             associate           6790             Bali

修改后的SQL查询 #

WITH latest_union AS (
    -- 获取每组当前生效的最新Union Code
    SELECT NUMBER, MANAGER, UNION_CODE
    FROM asg
    WHERE sysdate BETWEEN start_Date AND end_date
),
first_bind_info AS (
    -- 筛选对应最新Union Code的所有记录，取首次绑定的起始日期和职位
    SELECT 
        a.NUMBER,
        MIN(a.START_DATE) AS START_DATE,
        FIRST_VALUE(a.POSITION) OVER (PARTITION BY a.NUMBER, a.MANAGER, a.UNION_CODE ORDER BY a.START_DATE) AS POSITION,
        a.UNION_CODE,
        a.MANAGER
    FROM asg a
    JOIN latest_union lu 
        ON a.NUMBER = lu.NUMBER 
        AND a.MANAGER = lu.MANAGER 
        AND a.UNION_CODE = lu.UNION_CODE
    GROUP BY a.NUMBER, a.MANAGER, a.UNION_CODE
)
SELECT NUMBER, START_DATE, POSITION, UNION_CODE, MANAGER
FROM first_bind_info;

逻辑说明 #

1. latest_union子查询：先筛选当前生效的记录，得到每个NUMBER+MANAGER组对应的最新Union Code。
2. first_bind_info子查询：关联原始表和latest_union，锁定每组对应最新Union Code的历史记录，通过MIN(START_DATE)找到该Code首次绑定的日期，用FIRST_VALUE获取首次绑定时的职位。
3. 最后从first_bind_info中取出目标字段，得到期望结果。

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