PostgreSQL cummulative sum max condition

user_3836530 0
Viewed 31

I have the following table:

account 
size   id    name 
100    1     John 
200    2     Mary 
300    3     Jane 
400    4     Anne
100    5     Mike 
600    6     Joanne

I want to partition the rows in groups, where the sum of size <= 600.

Expected result:

account 
group  size   id    name 
1       100    1     John 
1       200    2     Mary 
1       300    3     Jane 
2       400    4     Anne
2       100    5     Mike 
3       600    6     Joanne

I don't know how to do the partition and add the condition.

postgresql
1 Answers

I cannot think of how to do this without recursion. I left the running_total in the result to make it easier to follow:

with recursive rns as ( -- Assign row numbers as rn in case of gaps in id
  select *, row_number() over (order by id) as rn
    from account
), sumgrp as ( -- Start with first row
  select size, id, name, rn, 1 as grp, size as running_total
    from rns
   where rn = 1
  union all
  select n.size, n.id, n.name, n.rn, 
         case  -- Increment the grp when running_total exceeds 600
           when p.running_total + n.size > 600 then p.grp + 1 
           else p.grp 
         end as grp,
         case  -- Reset the running_total when it exceeds 600
           when p.running_total + n.size > 600 then n.size 
           else p.running_total + n.size 
         end as running_total
    from sumgrp p
         join rns n on n.rn = p.rn + 1
)
select grp, size, id, name, running_total
  from sumgrp 
 order by id;

db<>fiddle here

user_138083190
Related