All Forums Database
stahengik 15 posts Joined 10/14
05 Nov 2014
Cumulative Average where the partition are overlapping (Moving Avg.)

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

ulrich 816 posts Joined 09/09
05 Nov 2014

very little information provided by you - what do you expect???
check AVG Ordered Analytical / Window Aggregate Functions syntax

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Raja_KT 1246 posts Joined 07/09
05 Nov 2014

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

stahengik 15 posts Joined 10/14
05 Nov 2014

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.
 

Raja_KT 1246 posts Joined 07/09
05 Nov 2014

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

stahengik 15 posts Joined 10/14
06 Nov 2014

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.
 

Raja_KT 1246 posts Joined 07/09
06 Nov 2014

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

stahengik 15 posts Joined 10/14
06 Nov 2014

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 :)

ToddAWalter 316 posts Joined 10/11
06 Nov 2014

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.

stahengik 15 posts Joined 10/14
06 Nov 2014

Thanks Walter!

ToddAWalter 316 posts Joined 10/11
06 Nov 2014

You would not have to Union if the avg was always within the year and not overlapping. Since the unbounded preceding will go to the beginning of the partition, you could partition by the year and it would work fine in a single query. But there is no direct way to get a row included in two partitions the way you have specified.

stahengik 15 posts Joined 10/14
07 Nov 2014

Thanks for this information Walter! Now I know I should not waste anymore time in getting this thing done in a single query.
There is one concernt though... I have to save this query as a macro which will be executed every month by some end user.  Any advise on how to make it future proof without loosing much efficiency ? 
You can laugh off  my question if its silly. I am a naive!! :P

dnoeth 4628 posts Joined 11/04
07 Nov 2014

Of course you can do this in a single query, but you need nested OLAP functions.
Assuming that month is an actual date (1st of month?) this gets the previous december's value:

MAX(CASE WHEN EXTRACT(MONTH FROM month) = 12 THEN amount END) 
OVER (ROWS BETWEEN 12 PRECEDING AND 1 PRECEDING) AS prev_december

Now put this in a Dervied Table and calculate the average as SUM/COUNT:

(SUM(amount) 
 OVER (PARTITION BY EXTRACT(YEAR FROM mth)
       ORDER BY mth ROWS 12 PRECEDING) 
   + COALESCE(prev_december, 0)
) / 
(ROW_NUMBER() 
 OVER (PARTITION BY EXTRACT(YEAR FROM mth)
       ORDER BY mth ) 
   + CASE WHEN prev_december IS NOT NULL THEN 1 ELSE 0 END)

This should correctly implement your logic :-)

Dieter

stahengik 15 posts Joined 10/14
07 Nov 2014

Thanks a lot Dieter!!
Its very helpful!!
 

You must sign in to leave a comment.