我想您需要查看每个项目编号的最后两条记录，无论何时按日期列的降序排列。我准备了这个分组和排序，并使用 DISTINCT 在下面的查询中，我们不知道哪一个成本是belayer发现的公共日期的最新成本。我只是把最大值看作你的结果和通货膨胀的性质：）。
我决定了以前和现在的专栏的贡献 ROW_NUMBER() 最后一步中的函数：

WITH t1 AS
(
 SELECT DISTINCT MAX(Cost) OVER (PARTITION BY Item_Number, Effective_Date) AS Cost,
        Item_Number, Effective_Date
   FROM tab 
  ORDER BY Item_Number, Effective_Date 
), t2 AS
(
 SELECT t1.*, 
        ROW_NUMBER() OVER (PARTITION BY Item_Number ORDER BY Effective_Date DESC) AS rn
   FROM t1  
)
SELECT Item_Number, 
       MAX(CASE WHEN rn = 2 THEN Effective_Date END) AS Previous_Date,
       MAX(CASE WHEN rn = 2 THEN Cost END) AS Previous_Cost,
       MAX(CASE WHEN rn = 1 THEN Effective_Date END) AS Current_Date,
       MAX(CASE WHEN rn = 1 THEN Cost END) AS Current_Cost       
  FROM t2   
 WHERE rn <= 2 
 GROUP BY Item_Number;

演示

我可以为你建议以下方法。

with
    date_t as
    (
        select 1 as item_number, to_date('01/01/2020','mm/dd/yyyy') as effective_date, 8.00 as cost, 11.00 as price from dual union all
        select 1 as item_number, to_date('01/01/2020','mm/dd/yyyy') as effective_date, 8.00 as cost, 10.50 as price from dual union all
        select 2 as item_number, to_date('09/22/2020','mm/dd/yyyy') as effective_date, 6.25  as cost, 6.50 as price from dual union all
        select 1 as item_number, to_date('01/01/2020','mm/dd/yyyy') as effective_date, 8.00 as cost, 10.50 as price from dual union all
        select 1 as item_number, to_date('05/07/2019','mm/dd/yyyy') as effective_date, 7.00 as cost, 10.50 as price from dual union all
        select 1 as item_number, to_date('03/12/2018','mm/dd/yyyy') as effective_date, 6.00 as cost, 10.50 as price from dual union all
        select 2 as item_number, to_date('03/12/2018','mm/dd/yyyy') as effective_date, 6.00  as cost, 6.50 as price from dual union all
        select 2 as item_number, to_date('03/12/2018','mm/dd/yyyy') as effective_date, 6.00  as cost, 6.50 as price from dual union all
        select 1 as item_number, to_date('01/01/2020','mm/dd/yyyy') as effective_date, 7.00 as cost, 10.50 as price from dual union all
        select 1 as item_number, to_date('08/01/2016','mm/dd/yyyy') as effective_date, 5.25 as cost, 10.50 as price from dual 
    )
select 
    item_number,
    lag(effective_date) over (partition by item_number order by effective_date) as previous_date,
    lag(cost) over (partition by item_number order by effective_date) as previous_cost,
    effective_date as current_date,
    cost as current_cost
from    
    (
        select
            item_number,
            effective_date,
            max(cost) as cost
        from
            date_t  
        group by
            item_number,
            effective_date
        order by effective_date desc
    ) t

输出将如下所示。

ITEM_NUMBER PREVIOUS_DATE   PREVIOUS_COST   CURRENT_DATE    CURRENT_COST
----------- -------------   -------------   ------------     ----------- 
1             null          null            01.08.2016       5.25
1             01.08.2016    5.25            12.03.2018       6
1             12.03.2018    6               07.05.2019       7
1             07.05.2019    7               01.01.2020       8
2             null          null            12.03.2018       6
2             12.03.2018    6               22.09.2020       6.25

注：我有其他日期格式在甲骨文是 dd.mm.yyyy .