1、求截止到当前行不为空最后一个值
select calendar_day,
if_trade_dt,
mkt_code,
last_value(tmp_trade_dt, true) over(partition by mkt_code order by calendar_day desc) as gt_trade_dt,
last_value(tmp_trade_dt, true) over(partition by mkt_code order by calendar_day) as lt_trade_dt
from(
select calendar_day, mkt_code,if_trade_dt, null as tmp_trade_dt from tmp_03
union all
select calendar_day, mkt_code,if_trade_dt, calendar_day as tmp_trade_dt
from tmp_05 ) t1
2、求上一个值和下一个值
select
calendar_day,
if_trade_dt,
mkt_code,
lead(calendar_day, 1, date '2999-12-31') over (partition by mkt_code order by calendar_day) as gt_trade_dt,
lag(calendar_day, 1, date '1900-01-01') over (partition by mkt_code order by calendar_day) as lt_trade_dt
from tmp_06