Data discretization based on rank position 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

CREATE TABLE foo AS
SELECT * FROM (
  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).