Thread: 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