Split rows in limited size buckets

Question

I have a table with thousands of records, I want to associate each row with a bucket and each bucket has a maximum of rows associated to it.

I'd love something a "bucketize" analytic function similar to ntile but that instead of the number of buckets takes the maximum number of elements of each bucket.

Is there anything like that?

select value, bucketize(3) as bucket
  from table;

VALUE   BUCKET
--------------
foo          1
bar          1
baz          1
qux          2
quux         2
zap          2
xfoo         3
xbar         3
xbaz         3
xqux         4

Answer 1

You can just use row_number():

select value, floor((row_number() over (order by value) - 1) / 3) as bucket
from t;

I don't think a separate window function is really needed in this case.