very little information provided by you - what do you expect???
check AVG Ordered Analytical / Window Aggregate Functions syntax
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.
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.
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
) +1)). I m not aware of. If someone can educate me too here :).
I hope this functionaility be there.
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 :)
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.