cumulative amount to current

base_table

   month     id    sales  cumulative_sales
2021-01-01  33205   10           10
2021-02-01  33205   15           25

Based on the base table above, I would like to add more rows up to the current month, even if there is no sales.

Expected table

   month     id    sales cumulative_sales
2021-01-01  33205   10           10
2021-02-01  33205   15           25
2021-03-01  33205    0           25
2021-04-01  33205    0           25
2021-05-01  33205    0           25
.........
2021-11-01  33205    0           25

My query stops at

select month, id, sales, 
sum(sales) over (partition by id 
                 order by month 
                 rows between unbounded preceding and current row) as cumulative_sales
from base_table

CodePudding user response：

This works. Assumes the month column is constrained to hold only "first of the month" dates. Use the desired hard-coded start date, or use another CTE to get the earliest date from base_table:

with base_table as (
    select *
    from (values
     ('2021-01-01'::date,33205,10)
    ,('2021-02-01'      ,33205,15)
    ,('2021-01-01'      ,12345,99)
    ,('2021-04-01'      ,12345,88)
         ) dat("month",id,sales)
)
select cal.dt::date
      ,list.id
      ,coalesce(dat.sales,0) as sales
      ,coalesce(sum(dat.sales) over (partition by list.id order by cal.dt),0) as cumulative_sales
from Generate_Series('2020-06-01' /* use desired start date here */,current_date,'1 month') cal(dt)
     cross join (select distinct id from base_table) list
     left join base_table dat on dat."month" = cal.dt and dat.id = list.id
;

Results:

| dt         | id    | sales | cumulative_sales |
 ------------ ------- ------- ------------------ 
| 2020-06-01 | 12345 |     0 |                0 |
| 2020-07-01 | 12345 |     0 |                0 |
| 2020-08-01 | 12345 |     0 |                0 |
| 2020-09-01 | 12345 |     0 |                0 |
| 2020-10-01 | 12345 |     0 |                0 |
| 2020-11-01 | 12345 |     0 |                0 |
| 2020-12-01 | 12345 |     0 |                0 |
| 2021-01-01 | 12345 |    99 |               99 |
| 2021-02-01 | 12345 |     0 |               99 |
| 2021-03-01 | 12345 |     0 |               99 |
| 2021-04-01 | 12345 |    88 |              187 |
| 2021-05-01 | 12345 |     0 |              187 |
| 2021-06-01 | 12345 |     0 |              187 |
| 2021-07-01 | 12345 |     0 |              187 |
| 2021-08-01 | 12345 |     0 |              187 |
| 2021-09-01 | 12345 |     0 |              187 |
| 2021-10-01 | 12345 |     0 |              187 |
| 2021-11-01 | 12345 |     0 |              187 |
| 2020-06-01 | 33205 |     0 |                0 |
| 2020-07-01 | 33205 |     0 |                0 |
| 2020-08-01 | 33205 |     0 |                0 |
| 2020-09-01 | 33205 |     0 |                0 |
| 2020-10-01 | 33205 |     0 |                0 |
| 2020-11-01 | 33205 |     0 |                0 |
| 2020-12-01 | 33205 |     0 |                0 |
| 2021-01-01 | 33205 |    10 |               10 |
| 2021-02-01 | 33205 |    15 |               25 |
| 2021-03-01 | 33205 |     0 |               25 |
| 2021-04-01 | 33205 |     0 |               25 |
| 2021-05-01 | 33205 |     0 |               25 |
| 2021-06-01 | 33205 |     0 |               25 |
| 2021-07-01 | 33205 |     0 |               25 |
| 2021-08-01 | 33205 |     0 |               25 |
| 2021-09-01 | 33205 |     0 |               25 |
| 2021-10-01 | 33205 |     0 |               25 |
| 2021-11-01 | 33205 |     0 |               25 |