Am Thu, Jul 14, 2022 at 01:23:55PM +0530 schrieb jian he: > 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 Anne100 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 Anne2 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 My proposal: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=7daef32ae39b2ec7c38a83cf9e19d4ae select id, name, size , sum(size) over (order by id) as size_total , ((sum(size) over (order by id) - 1) / 600) + 1 as size_group from account order by id, name; Best regards Frank
