Creating bins in presto sql - programmatically

user_5905425 0
Viewed 4700

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!

trino presto sql
2 Answers

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

You can just use integer division:

select (intcol - 1) / 5 as bin

Presto does integer division, so you shouldn't have to worry about the remainder.

user_11440350
Related