Moving avg on 12 months

abderahmenabderahmen Registered User

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,

Comments

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert
    edited June 5

    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)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file