Oracle SQL NTILE - equal distribution

This query:

SELECT min( "x" )  as "From",
       max( "x" ) as "To",
       sum("quantity")
FROM (
SELECT t.*,
      ntile( 4 ) over (order by "x" ) as group_no
FROM table1 t)GROUP BY 
              group_no
              ORDER BY 1;

with this data

x   quantity
1   9
2   43
3   21
4   26
6   14
7   38
8   14
9   20
10  20
11  30
12  32
13  37
14  27
15  22
16  34
17  9
18  4
19  24
20  42
21  21
22  16
23  34
24  9
25  49
26  21
27  20
28  28
29  6
30  3

Has this result:

1   6   113
7   11  122
12  16  152
17  21  100
22  26  129
27  30  57

Is there any other SQL possibility that sum(quantity) is distributed more equally?

I know there are a lot of algorithms but I am pretty sure there must be smart SQL (analytic function) solution e.g..

Solution

Try something like this:


    select
      min(b.x) as "From",
      max(b.x) as "To",
      sum(b.quantity) as "Sum"
    from
      (
        select
          a.x,
          a.quantity,
          ceil(sum(a.quantity) 
            over (order by a.x asc rows between unbounded preceding and current row) / a.avg_quantity_for_group) group_no
        from (select t.x, t.quantity, ceil(sum(t.quantity) over () / 4) avg_quantity_for_group from table1 t) a
      ) b
    group by b.group_no
    order by "From" asc;

For your input data result will be: