How do I get the aggregate of last n values in SQL using lead/lag

Question

I am looking at aggregating the last over the three rows ( i.e trying the max value of the column in last 3 three rows). Is there a way we can do using LAG and MAX together. I was able to achieve by creating a function and using it, but it is not efficient. What is the better way.

select symbol, td_timestamp, open, vol_range,  fn_getmaxvalue(high, hihi, hihi2) as highest
 from 
(select     symbol, td_timestamp, open, high, low,  
            volume-lag(volume,2)  over (partition by symbol order by td_timestamp ) vol_chg,
            lag(high,1)  over (partition by symbol order by td_timestamp ) hihi,
            lag(high,2)  over (partition by symbol order by td_timestamp ) hihi2
            from tb_nfbnf where trade_date='2020-02-28' and processed_flg is null
           order by symbol, td_timestamp)a

Answer 1

If I understand correctly, you want a running max. That would use a window frame clause in the window function:

select t.*,
       max(high) over (partition by symbol
                       order by td_timestamp
                       rows between 2 preceding and current row
                      ) as max_hi_3rows
from tb_nfbnf t
where trade_date = '2020-02-28' and processed_flg is null
order by symbol, td_timestamp;

** Changed from 2 rows preceding to 2 preceding **