PostgreSQL calculate monthly rate-CodePudding

I Have this table:

CREATE TABLE data 
    (
        Event_Date date,
        approved int, 
        total int
    )
    
    INSERT INTO data (Event_date, approved, total)
    VALUES
        ('20190901', '5', '20'),
        ('20190902', '6', '30'),
        ('20190903', '4', '50'),
        ('20190904', '7', '40'),
        ('20190905', '8', '50'),
        ('20190906', '10', '70'),
        ('20190907', '4', '25')

How to make a loop or something else for calculate monthly rate from selected month start day until month end day and get results(in monthly rate I write how formula need to be use) like this:

Event_date   approved,      total    Rolling monthly rate
------------------------------------------------------------
20190901           5           20           5/20
20190902           6           30           5 6/20 30
20190903           4           50           5 6 4/20 30 50
20190904           7           40           5 6 4 7/20 30 50 40
20190905           8           50           5 6 4 7 8/20 30 50 40 50
20190906           10          70           ....
20190907           4           25           .....

CodePudding user response：

You can use window functions for that:

select event_date, approved, total, 
       sum(approved::numeric) over (order by event_date) / sum(total::numeric) over (order by event_date) as rolling_monthly
from data
order by event_date;

Or a bit shorter by using a single window definition:

select event_date, approved, total, 
       sum(approved::numeric) over w / sum(total::numeric) over w as rolling_monthly
from data
window w as (order by event_date)
order by event_date;

The cast to ::numeric is necessary because otherwise Postgres would do integer division (and then 5/20 yields 0)