I have a problem with a SQL query and I am pretty sure it can be solved with window functions but I don't get my head around it. So maybe somebody can give me a push in the right direction.

I have a sample table:

I made a db<>fiddle to show you better.

I want to add a column (kpi_yr_avg) where the average of the year (of that record) is calculated but only for the actual and the previous months of that year of that data record.

For excample:

What I mean is: In january the average is calculated only with the january value. In february the average is calculated with the january and febraury value. And so on.

The real table I am working with has different entries in field1 and field 2 and includes months since 2018.

Would be nice if somebody could point me in the right direction.

CodePudding user response：

SELECT T.FIELD1,T.FIELD2,T.YEAR,T.MONTH,T.KPI,
AVG(T.KPI)OVER(PARTITION BY T.FIELD1,T.FIELD2,T.YEAR ORDER BY T.MONTH ASC)AS RUN_AVG 
 FROM test1 AS T ORDER BY T.field1, T.field2, T.year, T.month;

Could you please try if the above query is suitable for you

CodePudding user response：

Use window function with moving average

-- PostgreSQL(v11)
SELECT *
     , AVG(kpi) OVER (PARTITION BY field1, field2, year ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) kpi_yr_avg
FROM test1

Please check from url https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1db8f0f234d0103120876cd95d8f303b