PostgreSQL ntile() partition

i have a point grid with population values. each point has an id and population value. i also have an state_id which say in what state the point is.

now i want to calculate the percentile ntile(100) for each state.

SELECT  id, population, state_id, 
   ntile(100) OVER(ORDER BY car20) as percentile
   FROM avi_threshold01

when i use this, i think it calculates the ntile over all points and states.

If you want percentiles per state, then use PARTITION BY state_id in the OVER clause.

And that GROUP BY looks spurious at least. I think it needs to be removed if you want percentiles. And group by the PK is a no-operation anyway.

SELECT  
       id, population, state_id, 
       ntile(100) OVER (PARTITION BY state_id ORDER BY car20) AS percentile
   FROM 
       avi_threshold01 ;

StackExchange Database Administrators Q#137922, answer score: 6