PostgreSQL ntile() partition

Question

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.

Answer 1

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 ;

Answer 2

I know this is an old post but NTILE cannot accurately calculate a percentile. The misguided idea that you can use NTILE(100) for this is a fiction that spans multiple SQL dialects; I see this often in the Microsoft T-SQL world.

Consider this query:

WITH sampleData as (SELECT v FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(2),(2),(2)) as x(v))
SELECT V, NTILE (2) OVER(ORDER BY v) AS nt
FROM sampleData;

According to the laws of mathematics, identical values will always have the same percentile value. For example, if Mary's test score was 76 and Fred's was also 76, and Mary scored in the top 2% percentile - then so did Fred!

In the sample data above there's seven 1's and three two's. Yet the NTILE column returns five 1's and five 2's. This mathmatically proves my above statement about NTILE(100). NTILE(100) is cannot calculate percentiles because NTILE distributes rows evenly.

I've said this before but it's worth repeating: NTILE and Percent_rank are not related in any way, they are not even members of the same family of ANSI SQL functions. NTILE is a window ranking function and does not consider ties; NTILE exists to do one thing only: divide rows up evenly. Percent_Rank, on the other hand, is a rank distribution function and will ONLY assign a duplicate value to two values when they are identical. Huge difference. Yes, there are cases where NTILE(100) will return the correct answer but its coincidental; note this article: Nasty Fast Percent Rank