Re: Calculating percentiles for each data record
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.
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.