Search code examples
sqlprestotrino

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!

Solution

  • 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
    ...