Oracle – Lead() over (partition by xxx order by xxx )

Lead() over()，主要作用是從某一個集合的當前cursor位置算起，按照某種順序將第下第n個位置的某一個欄位值取出來，預設是往下1個位置。

select incid_id,acty_id,
lead(acty_ts) over(partition by incid_id order by acty_ts asc ) as closed_response
from incid_acty_f
where incid_id in (
select incid_id from incid_acty_f group by incid_id having count(acty_id)>1 ) ;

select incid_id,acty_id,
lead(acty_ts) over(partition by null order by incid_id, acty_ts asc ) as closed_response
from incid_acty_f
where incid_id in (
select incid_id from incid_acty_f group by incid_id having count(acty_id)>1 ) ;

這兩個主要區別，第一個語句，在遇到最後一個相同incid_id的時候，不再往下取數，顯示為Null.

第二個語句，在遇到最後一個相同incid_id的時候，繼續往下取數，取的是不同incid_id的acty_ts值