如何通过SQL获取Oracle表中某行的列的首次更改？

2024-05-06 • 问答

维度表类型2记录了一行随时间（SCD）的变化。我想根据另一列的日期（应该是常见的模式）找到该列的第一个值更改。

例如，具有（emp_id，insert_date，状态，地址）的员工表，其中状态指示工作，不工作，休假等（一些固定值）。比方说，这家奇怪的公司每天都在监视员工。现在我想知道员工在过去一年中何时开始上一次假期。以下内容无效

select distinct empid,max(insert_date) where status = 'vacation'
where insert_date > today - 1 year --- know not correct syntax but to simplify
group by empid

因为它将选择状态为休假的最后一条记录，但休假可能会有很多记录，因为记录可能由于其他原因而发生了更改，例如地址更改等，例如：

emp_id insert_dt status     address
1      9/1/2019   working   123
1      9/2/2019   working   1234
1      9/3/2019   vacation  1234
1      9/4/2019   vacation  12345   --- address change
1      9/5/2019   working   12345

因此，我希望从工作状态到休假状态的第一次转换是9/3（而不是9/4），而min也不起作用。

我们不能更改表模式，也不能更改PL / SQL，也不能使用某些编程语言等中嵌入的SQL，只能使用普通SQL。如果您知道Oracle中有任何可以运行lambda或使用编码列等进行模拟的（分析）函数，那将是理想的选择。

谢谢 S

使用lag()：

select t.*
from (select t.*,lag(status) over (partition by emp_id order by insert_dt) as prev_status
      from t
     ) t
where status = 'vacation' and
      (prev_status <> status or prev_status is null);

编辑：

如果您想为雇员提供最近的时间，可以使用汇总：

select emp_id,max(insert_dt)
from (select t.*,lag(status) over (partition by emp_id order by insert_dt) as prev_status
      from t
     ) t
where status = 'vacation' and
      (prev_status <> status or prev_status is null)
group by emp_id;

您还可以在特定时间段内添加过滤条件。

所以我想第一次出现从工作到休假的转变

我将首先使用lag()获取每个emp_id的先前状态，并按insert_dt进行排序。然后，确定状态从工作状态切换到休假状态的记录，并按insert_dt为每位员工排名。最后，过滤每个员工的第一条记录：

select e.*
from (
    select 
        e.*,row_number() over(partition by emp_id order by insert_dt) rn
    from (
        select
            e.*,lag(status) over(partition by emp_id order by insert_dt) lag_status
        from employee e
        where insert_date > add_months(trunc(sysdate),-12)
    ) e
    where lag_status = 'working' and status = 'vacation'
) e
where rn = 1

如何通过SQL获取Oracle表中某行的列的首次更改？

tzyyhjb 回答：如何通过SQL获取Oracle表中某行的列的首次更改？

大家都在问