How to get average value in the last 6 months
<p>I have a problem with calculation of a variable. Lets imagine that we have a table with ID, Date and Salary. And now my task is to find out average salary in the last 6 months for each Date. Is there any other possibility how to get this value instead of Cartesian product?</p>

<p>My proposed resolution:</p>

<pre><code>select ID, Date, AVG(Salary2)
from
(
select tab1.ID as ID, tab1.Date as Date, tab2.Date as Date2, tab2.Salary as Salary2
from table tab1
**JOIN**
(select ID, Date, Salary from table) tab2
on tab1.ID = tab2.ID
and tab1.Date &gt;= tab2.Date
and add_months(tab2.Date, 5) &gt;= tab1.Date
) group by ID, Date;
</code></pre>

<p>I assume that this approach is not effective. I think it is too time-consuming in terms of big tables. </p>

<p>Is there any other approach? Or how should I optimize this script?</p>

<p>Thank you</p>