如何用Oracle SQL从生效日期表中找出从佛罗里达迁出的员工？

需求：找出所有从佛罗里达州（FL）迁出的员工相关记录。

原始表数据 #

预期结果 #

我尝试的SQL #

select * 
from ps_addresses 
where effdt = (
  select max(effdt)
    from (
    select 
      emplid, effdt, STATE, over(order by effdt desc) as prev
      from ps_addresses
    ) x  
    where prev is not null and STATE <> prev
);

要搞定这个需求，核心是找出员工最后一条在FL的记录，以及之后第一次切换到非FL的那条记录。用窗口函数LAG或LEAD就能获取同一员工的相邻状态，具体实现如下：

正确SQL示例 #

WITH address_changes AS (
    SELECT
        emplid,
        effdt,
        state,
        -- 获取当前记录的下一条状态（按生效日期升序）
        LEAD(state) OVER (PARTITION BY emplid ORDER BY effdt) AS next_state,
        -- 获取当前记录的上一条状态
        LAG(state) OVER (PARTITION BY emplid ORDER BY effdt) AS prev_state
    FROM ps_addresses
)
SELECT emplid, effdt, state
FROM address_changes
WHERE
    -- 匹配最后一条FL记录：当前是FL，且下一条状态不是FL
    (state = 'FL' AND next_state IS NOT NULL AND next_state != 'FL')
    -- 匹配迁出后的第一条记录：当前不是FL，且上一条状态是FL
    OR (state != 'FL' AND prev_state = 'FL')
ORDER BY emplid, effdt;

代码说明 #

• PARTITION BY emplid：确保窗口函数只在同一员工的记录范围内计算，不会跨员工干扰；
• ORDER BY effdt：保证按生效日期的先后顺序，正确获取相邻记录的状态；
• 筛选条件精准定位到从FL迁出的两个关键节点，完全符合预期结果的要求。

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