Cumulative Average where the partition are overlapping (Moving Avg.)

Database
Enthusiast

Cumulative Average where the partition are overlapping (Moving Avg.)

Need help on how to get moving average. Please suggest.

Thanks!

13 REPLIES
Supporter

Re: Cumulative Average where the partition are overlapping (Moving Avg.)

very little information provided by you - what do you expect???

check AVG Ordered Analytical / Window Aggregate Functions syntax

Enthusiast

Re: Cumulative Average where the partition are overlapping (Moving Avg.)

Stahengik,

It can be anything when you say moving average:

It can be something like this example: AVG(x) OVER (ORDER BY y ROWS z PRECEDING)  .

You can highlight what you have and what you want as output.

Enthusiast

Re: Cumulative Average where the partition are overlapping (Moving Avg.)

Month       Amount     Avg amt

Dec-12    50495    50495            

Jan-13    55023    52759            

Feb-13     26241    43920            

Mar-13   59447    47802            

Apr-13   90199    56281            

May-13   6282       47948            

Jun-13   73127    51545            

Jul-13    62183    52875            

Aug-13   63078    54008            

Sep-13   55743    54182     This field is average from Dec 12 to Sep 13

Oct-13   9090        50083     This field is average from Dec 12 to Oct 13

Nov-13   62759    51139     This field is average from Dec 12 to Nov 13

Dec-13   85223    53761     This field is average from Dec 12 to Dec 13**

Jan-14   5997    60610      This field is average from Dec 13 to Jan 14**

Feb-14   13639    44953     This field is average from Dec 13 to Feb 14

In the table above I have tried to give an example of what I want to do. The avg for a month should be avg from Dec previous year to current month. The Avg for Dec 2013 should be an avg of Dec 2012 through Dec 2013. 

The avg for Jan 2014 should be avg of Dec 2013 and Jan 2014. and so on.

Please let me the if the information sufficient.

Thanks.

PS. Please also suggest a way to past talbe in the query field. I had a hard time to paste the table here.

 

Enthusiast

Re: Cumulative Average where the partition are overlapping (Moving Avg.)

You can try with
avg(amount) over(order by amount rows 10 preceding ) from your_table
You can put your where clause alongside.

Next you can do union all for 2014 data.

Even I am not able to see the format bar on the top. Let us hear from others if they are facing the same issue. By default, there is a format bar at top of the posting.
Enthusiast

Re: Cumulative Average where the partition are overlapping (Moving Avg.)

Thanks Raja!

About the solution since it was an example only, The data I have has more than 20 years. and I am trying to use

Avg (amount) over(order by month rows between (extract(month from month) +1) preceding and current row) as Avg_amount

but its not working. 

When i tried with  the value for 

extract(month from month)+1

in a new variable, Its says its expecting something like "Unbounded".

Can you suggest something for this.

Enthusiast

Re: Cumulative Average where the partition are overlapping (Moving Avg.)

You can think of in this direction like partition and restricting with qualify set by set maybe

Paramterizing  for rows between never worked for me too a while back till now (like rows between (extract(month frommonth) +1)). I m not aware of. If someone can educate me too here :).

I hope this functionaility be there.

Enthusiast

Re: Cumulative Average where the partition are overlapping (Moving Avg.)

In fact I also tried

Avg (amount) over(partition by (extract(year from current_Date)*100 + extract(month from current_date)) >= ((extract(year from month)-1)*100+12) order by month rows between unbounded preceding and current row) as Avg_amount

but it did not work too.. :(

and yes I hope someone here has idea about how to work it out :)

Teradata Employee

Re: Cumulative Average where the partition are overlapping (Moving Avg.)

preceeding and following may only specify positive integers, expressions are not allowed.

If you just say ROWS UNBOUNDED PRECEDING it will go from the beginning of the set to and including the current row. However since you wish to do the years separately, I think you will have to do that in separate queries one for each year's data and union the results.

Enthusiast

Re: Cumulative Average where the partition are overlapping (Moving Avg.)

Thanks Walter!