Oracle SQL - distributing into buckets

Question

i'am searching for a smart oracle sql solution to distribute data into a number of buckets. The order of x is important. I know there are a lot of algorithms but iam pretty sure there must be smart sql (analytic function) solution e.g. NTILE(3) but i don't get it.

x|quantity
1|7
2|4
3|9
4|2
5|10 
6|3
8|7
9|7
10|4
11|9
12|2
13|10 
16|3
17|7

The result should look something like this:

x_from|x_to|sum(quantity)
1|4|22

...and so on

Thanks in advance

Tim

score 3 · Accepted Answer · answered Aug 02 '17 at 19:32

This example divides the table into 4 buckets (ntile( 4 )):

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;

| From | To | SUM("QUANTITY") |
|------|----|-----------------|
|    1 |  4 |              22 |
|    5 |  9 |              27 |
|   10 | 12 |              15 |
|   13 | 17 |              20 |

Oracle SQL - distributing into buckets

1 Answers1