Oracle SQL NTILE - 平均分配

Question

此查询:

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;

使用此数据

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

有这样的结果:

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

是否还有其他 SQL 可能使 sum(quantity) 分布更均匀？

我知道有很多算法，但我很确定一定有智能SQL(分析函数)解决方案，例如......

Answer 1

尝试这样的事情:

    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;

对于您的输入数据结果将是:

1  8  1659  14 16615 22 17223 30 170