with data
as (select y /* generate a sequence ordered by the y*/
,value_pvt
,row_number() over(order by y) as rnk
,count(*) over(partition by 1) as tot_cnt
from t
)
,row_generator
as (select level as lvl /* generate a rows up and till 5*/
from dual
connect by level<=(select tot_cnt from data where rownum=1)
)
, boundary_values
as (select a.lvl /* generate records for levels and specify the max and min values of the boundaries. Eg:for lvl=4 low_val=1 and hi_val=5*/
,b.lvl as low_val
,a.lvl+b.lvl as hi_val
from row_generator a
join row_generator b
on 1=1
where b.lvl + a.lvl <=(select tot_cnt from data where rownum=1)
)
, computed_mmwl
as ( select lvl,max(diff) as diff
from (select m.lvl
,n.value_pvt - o.value_pvt as diff
from boundary_values m
join data n
on m.low_val=n.rnk
join data o
on m.hi_val=o.rnk
) group by lvl
union all
select max(rnk), 0 /* this union all adds the last record ie y('28/07/2018 00:05:00')|0 */
from data
)
select to_char(a1.y,'dd/mm/yyyy hh24:mi:ss') as y
,a1.value_pvt
,b1.diff as new_mmwl
from data a1
join computed_mmwl b1
on a1.rnk=b1.lvl