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

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