3

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.

1
  • I know this is an old post but it's really important to understand for anyone who stumbles across this - WHAT YOU ARE RETURNING IS NOT A PERCENTILE! NTILE cannot be used to accurately calculate a percentile. Your query probably returns the correct results most of the time but it's by coincidence, not because you're math is correct. Note this article: sqlservercentral.com/articles/PERCENT_RANK/141532 specifically the section titled "How NOT to calculate Percentile Rank." The article is about MS SQL Server's NTILE but the math applies to PostgreSQL as well. – Alan Burstein Feb 8 '19 at 19:33
6

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 ;
1
  • thanks, looks good. i kind of hoped group by does the partition ;) – fabvys May 8 '16 at 20:01
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

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.