Creating bins in presto sql – programmatically

I am new to Presto SQL syntax and and wondering if a function exists that will bin rows into n bins in a certain range.

For example, I have a a table with 1m different integers that range from 1 – 100. What can I do to create 20 bins between 1 and 100 (a bin for 1-5, 6-10, 11-15 … etc. ) without using 20 separate CASE WHEN statements ? Are there any standard SQL functions that do will perform the binning function?

Any advice would be appreciated!

Answer

You can use the standard SQL function width_bucket. For example:

WITH data(value) AS (
    SELECT rand(100)+1 FROM UNNEST(sequence(1,10000))
) 
SELECT value, width_bucket(value, 1, 101, 20) bucket
FROM data

produces:

 value | bucket
-------+--------
   100 |     20
    98 |     20
    38 |      8
    42 |      9
    67 |     14
    74 |     15
     6 |      2
    ...