Oracle lag ()/lead () over () analysis function

With TMP as (
Select ' 1 ' id, ' AA ' name, ' dual ' from
Select ' 2 ' id, ' BB ' name, ' age from dual union ALL '
Select ' 3 ' ID, ' CC ' name, ' age from dual '
)
Select A.*,
       Lead (age,1) through (Order BY id desc) Lag,
  A.age-lead (age,1) over (order by id DESC) LAG1
From TMP a

The lead function is sorted in reverse order by ID The age record for the next line is displayed in the column lag of the current row, and null is displayed as null

With TMP as (
Select ' 1 ' id, ' AA ' name, ' dual ' from
Select ' 2 ' id, ' BB ' name, ' age from dual union ALL '
Select ' 3 ' ID, ' CC ' name, ' age from dual '
)
Select A.*,
       Lag (age,1) over (order BY id desc) Lag,
  A.age-lag (age,1) over (order by id DESC) LAG1
From TMP a

The LAG function is sorted in reverse order by ID The age record on the previous line is displayed in the column lag of the current row, and null is displayed as null

Oracle lag ()/lead () over () analysis function