Moving avg on 12 months

Comments

• Jim_Knicely - Select Field - Administrator

  June 2018 edited June 2018

  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)
  

  0