MySQL Forums
Forum List  »  General

Re: Calculating percentiles for each data record
Posted by: Steve Lerner
Date: October 30, 2008 12:45PM

Using MySQL 5.0.67-community-nt via TCP/IP, this query would be run like this:

SELECT
a.id ,
ROUND( 100.0 * ( SELECT COUNT(*) FROM tbl AS b WHERE b.val <= a.val ) / total.cnt, 1 )
AS percentile FROM a
CROSS JOIN (
SELECT COUNT(*) AS cnt
FROM tbl
) AS total
ORDER BY percentile DESC;

using "child" will cause it to fail...

beware of running large sets with this... i'd love to know a better way to handle 35,000+ records and generate percentiles

-Steve Lerner
http://www.stevelerner.com



Edited 1 time(s). Last edit at 10/30/2008 01:01PM by Steve Lerner.

Options: ReplyQuote


Subject
Written By
Posted
Re: Calculating percentiles for each data record
October 30, 2008 12:45PM


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.