MySQL Forums
Forum List  »  General

Re: Calculating percentiles for each data record
Posted by: Peter Brawley
Date: July 28, 2006 12:17AM

With 4.1 or later, it's a dynamic value, it changes every time a value changes, and the table is small, so why not just compute it on the fly when needed? Assuming a table tbl(id, val) ...
SELECT 
  a.id ,
  ROUND( 100.0 * ( SELECT COUNT(*) FROM tbl AS b WHERE b.val <= a.val ) / total.cnt, 1 ) 
  AS percentile
FROM child a 
CROSS JOIN ( 
  SELECT COUNT(*) AS cnt 
  FROM tbl 
) AS total
ORDER BY percentile DESC;
Before 4.1, break out the subqueries to temp tables.

PB



Edited 2 time(s). Last edit at 06/05/2008 12:22PM by Peter Brawley.

Options: ReplyQuote


Subject
Written By
Posted
Re: Calculating percentiles for each data record
July 28, 2006 12:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.