Lets say I have a table with the schema:

id   value
1    0.3
2    0.6
5    0.1
4    0.7

Provided by

  VALUES (1,0.3::float),(2,0.6),(5,0.1),(4,0.7)
) AS x(id, value)

I want to discretize the value column.

The idea is to rank the values and associate 1 to the first half and 2 to the second.

id   value  normalized
1    0.3    1
2    0.6    2
5    0.1    1
4    0.7    2

I cannot figure out how to do that in SQL, any help?

PS: I am using Postgres, so any solution Postgres dependant would be fine as well


You can use the NTILE() function:

select id, value,
       ntile(2) over (order by value) as normalized
from table_name ;

The 2 means that the values should be split into two buckets, after they are ordered according to the over clause: (ORDER BY value).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.