Postgresql: partition by number of rows after the current one

I need to calculate correlation for each say 3 consecutive rows. For example, assume there is a table with the following data. create table a ( asset_id int, time int not null, value decimal not null ); insert into a values (1,1,30),(1,2,45),(1,3,30),(1,4,30),(1,5,30), (2,1,30),(2,2,40),(2,3,30),(2,4,25),(2,5,25); # select row_number() over() as r, a.asset_id, b.asset_id, a.time, a.value, b.value from a join a b on a.time=b.time and a.asset_id