Moving avg on 12 months

abderahmen

Hi,
How can i get a moving AVG ?
i've tried this :
AVG(T.LOSS_PERC) OVER(ORDER BY T.DATE_START ROWS BETWEEN 0 PRECEDING AND 12 FOLLOWING
) AS LOSS_PERC_moving
But in my result i want to have values calculated starting on the 12th month not the first.
Can anyone help, please ?
Regards,

Jim_Knicely

I think this works...

dbadmin=> \d moving_avg;
                                          List of Fields by Tables
 Schema |   Table    |    Column    |     Type      | Size | Default | Not Null | Primary Key | Foreign Key
--------+------------+--------------+---------------+------+---------+----------+-------------+-------------
 public | moving_avg | t_date_start | date          |    8 |         | f        | f           |
 public | moving_avg | loss_perc    | numeric(10,2) |    8 |         | f        | f           |
(2 rows)

dbadmin=> SELECT * FROM moving_avg ORDER BY t_date_start DESC;
 t_date_start | loss_perc
--------------+-----------
 2018-12-12   |     30.00
 2018-07-31   |     50.00
 2018-07-05   |     20.00
 2018-06-05   |     50.00
 2018-05-31   |     20.00
 2018-04-30   |     30.00
 2018-02-05   |     10.00
 2018-01-15   |     50.00
 2017-01-05   |     50.00
(9 rows)

dbadmin=> SELECT t_date_start, loss_perc, AVG(loss_perc) OVER(PARTITION BY EXTRACT(YEAR FROM t_date_start) ORDER BY t_date_start DESC) AS LOSS_PERC_moving FROM moving_avg ORDER BY 1 DESC;
 t_date_start | loss_perc | LOSS_PERC_moving
--------------+-----------+------------------
 2018-12-12   |     30.00 |               30
 2018-07-31   |     50.00 |               40
 2018-07-05   |     20.00 | 33.3333333333333
 2018-06-05   |     50.00 |             37.5
 2018-05-31   |     20.00 |               34
 2018-04-30   |     30.00 | 33.3333333333333
 2018-02-05   |     10.00 |               30
 2018-01-15   |     50.00 |             32.5
 2017-01-05   |     50.00 |               50
(9 rows)