rolling average in case of missing month

Analytics
Enthusiast

rolling average in case of missing month

I have data in this format

student_id,month1,fees

A1,201612,22

A1,201611,33

A1,201610,44

A1,201609,55

A1,201608,66

A1,201607,77

A1,201606,88

A2,201612,12

A2,201610,24

A2,201609,36

A2,201607,48

 

I want fees of every student considering average of last three month fees means for student A1, for month 201612, fees will be sum(22,33,44)/3 so I used this query

 

SyntaxEditor Code Snippet

(select student_id,month1,fees,(sum(fees) over(partition by
student_id
order by
student_id
,
month1
asc rows between 2 preceding and current row ))/3 as avg1 from table where
month1
>(select trim(Add_Months(cast(trim(maxrepmonth) as DATE Format 'YYYYMM'),-5) (format 'YYYYMM')) from (select max(
month1
) as maxrepmonth from table) z2)  group by 1,2,3) 

 and this works fine for student A1 as it is having all months data but in case of student A2, for month 201612, It is taking fees from these months 201612,201610,201609 which is wrong, instead it should take only from 201612,201610 as 201611 is missing.

Please help.

 

 

Thanks

2 REPLIES
Teradata Employee

Re: rolling average in case of missing month

Instead of SUM() / 3, why not just do AVG() and select only the most recent 3 months instead of 5?  E.g.,

 

(select student_id
    ,month1
    ,fees
    ,(AVG(fees) over(partition by student_id order by student_id, month1 asc)) as avg1
from table
where month1 >(select trim(Add_Months(cast(trim(maxrepmonth) as DATE Format 'YYYYMM'), -3) (format 'YYYYMM'))
    from (select max( month1 ) as maxrepmonth from table) z2)
group by 1, 2, 3)

 

Enthusiast

Re: rolling average in case of missing month

Hi,

 

I need average for last three months so I am taking 5 months data.