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..
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:
1 8 165 9 14 166 15 22 172 23 30 170