I am using postgres 9.1.11 and have created a function to cumulate a variable (ln_ret) over a partition, eventually returning my desired column (cumret_pf). The parameters start_date and end_date determine how wide I want the relevant partition to be.
My issue is that I only want a result returned if there are enough rows in the partition, e.g. if I specify that the partition starts 2 rows preceding the current variable, I want the first two rows in the partition to be null.
I have been able to achieve this with the code below for the rows preceding the current row but am unable to achieve the desired result when I consider the rows following the the current row. Can I calculate a variable such as the 'inverse' row number, and perform an equivalent restriction for the end_date as I have below? Is there a better way to restrict my results? Many thanks
select allrows.rnum,
allrows.permno,
allrows.date,
allrows.ln_ret,
allrows.sum_ln_ret,
cast(exp(allrows.sum_ln_ret) - 1 as numeric(18,6)) as cumret_pf
from
(select cast(row_number() over (partition by a.permno order by a.date) as bigint) as rnum,
a.permno,
a.date,
a.ln_ret,
sum(a.ln_ret) over (partition by a.permno order by a.date rows between 3 PRECEDING and 3 FOLLOWING) as sum_ln_ret
from returns.msf_mse as a
where a.ln_ret is not null and permno = 10000
order by a.permno, a.date) as allrows
where rnum > 3;
My output currently appears as shown below (when using window of 3 rows pre/post as shown in the amended code). I would like to be able to do set the value of cumret_pf in the final three rows to null (or to delete them alltogether, as I have with the first three rows). I do not want to hard code the row number as my full dataset has many thousand partitions of varying lengths.
rnum;permno;date;ln_ret;sum_ln_ret;cumret_pf
--------------------------------------------------
4;10000;"1986-05-30";-0.251872;-1.386295;-0.750000
5;10000;"1986-06-30";-0.005038;-1.147884;-0.682693
6;10000;"1986-07-31";-0.084260;-1.736951;-0.823944
7;10000;"1986-08-29";-0.955512;-1.574885;-0.792969
8;10000;"1986-09-30";-0.058841;-1.796797;-0.834171
9;10000;"1986-10-31";-0.277631;-2.030170;-0.868687
10;10000;"1986-11-28";0.058269;-1.945910;-0.857143
