PL-SQL 查詢查找“Operation_date”低於指定日期的最新記錄

我有一個具有以下結構的表：

create table DOC_AMOUNT_DETAIL
(
 col_id         NUMBER,
 amount         NUMBER,
 operation_date DATE      
)

該表的一些範例數據是：

col_id    |  amount   |  operation_date 
---------------------------------------
 1       |  5000     |  11/1/2020  
 2       |  1000     |  11/1/2020 
 3       |  3000     |  11/1/2020     
 1       |  1000     |  11/14/2020
 2       |   500     |  11/14/2020

如果amount每個列col_id發生更改，new record則將在表中插入一個新的operation_date，這正是更改的日期。我從表中需要的是每個 col_id 的數量，max(operation_date)<= (a specific date for example 11/15/2020)我的意思是我需要記錄的最後更新operation_date of which is lower than the date I'm querying the table。所以我想要的結果是這樣的：

   col_id    |  amount   
   --------------------
     1       |  1000      
     2       |  500    
     3       |  3000       

到目前為止我寫的是這個，它給了我正確的輸出：

select d.col_id, 
      d.amount
 from doc_amount_detail d
inner join (select t.col_id, 
            max(t.operation_date) operation_date
              from doc_amount_detail t
             where t.operation_date <= to_date('11/15/2020', 'mm/dd/yyyy')
             group by t.col_id
            ) d2
   on d.col_id = d2.col_id
  and d.operation_date = d2.operation_date

我想知道是否有更好的方法來編寫這個查詢。提前致謝。

我找到了更好的解決方案。而不是Last_value()function ，我使用First_value()and order by t.operation_date desc：

select distinct 
      t.col_id,
      first_value(t.amount) over(partition by t.col_id order by t.operation_date desc ) amount
from doc_amount_detail t
where t.operation_date <= to_date('11/15/2020', 'mm/dd/yyyy');

分析函式

select
 col_id, amount
from
(
  select
    d.col_id, d.amount,
    rank() over (partition by d.col_id order by d.operation_date desc) as rk
  from doc_amount_detail d
  where.d.operation_date <= to_date('11/15/2020', 'mm/dd/yyyy')
)
where rk = 1; 

引用自：https://dba.stackexchange.com/questions/279717