first order by then partition by x < a fixed value.

[jian he <jian.universality@xxxxxxxxx>]

This question (https://stackoverflow.com/q/72975669/15603477) is fun.

DB fiddle link: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=36d685ad463831877ae70361be2cfa3b

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

Then expected output: 
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 

Idea is fixed order by id then cumulative sum. if  <=600 then grouped together using the same row_number.  

But I imagine this kind question has been solved many times.

Current posted solution uses a recursive query, which is really hard for me.

Is there any simple or more intuitive way to solve this kind of problem?

I can write some customized aggregate function to aggregate stops at 600.

I can get the following result, So what's the next step?

+------+----+--------+---------------+-----------------+
| size | id |  name  | capped_at_600 | large_or_eq_600 |
+------+----+--------+---------------+-----------------+
|  100 |  1 | John   |           100 | f               |
|  200 |  2 | Mary   |           300 | f               |
|  300 |  3 | Jane   |           600 | t               |
|  400 |  4 | Anne   |           400 | f               |
|  100 |  5 | Mike   |           500 | f               |
|  600 |  6 | Joanne |          1100 | t               |

+------+----+--------+---------------+-----------------+

--

 I recommend David Deutsch's <<The Beginning of Infinity>>

  Jian