I believe you can do: select avg(scantime) from percentile; select std(scantime) from percentile;
std() is the same as stddev() which finds the standard deviation. If the scantimes are gaussian (pretty good assumption if it's a large dataset), then the 95th percentile will be avg() + 2*stddev(). This should be very fast and quite accurate. Really it's probably better since I would think that you want the value within which 2 standard deviations fall rather than exactly 95%. This is more useful from a statistics point of view. > -----Original Message----- > From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] > Sent: Saturday, February 01, 2003 2:17 AM > To: Karl Dyson > Cc: [EMAIL PROTECTED] > Subject: Re: Percentile calculations > > > Hi. > > On Fri 2003-01-31 at 17:22:37 -0000, [EMAIL PROTECTED] wrote: > [...] > > select count(*) from percentile where <criteria>; > > > > Work out 95% or this value. > > > > create temporary table percentile (id int unsigned auto_increment > > primary key, scantime decimal(20,10)); > > > > insert into percentile (scantime) select processingtime from emails > > where <same criteria> order by processingtime desc limit <value from > > above>; > > > > select min(scantime) from percentile; > > If I am not mistaken, you can pick the row in question without a > separat table: > > Basically you limit the query to the first n rows (in descending > order) and then pick the one with the lowerst value, which should have > been the last in the limited range. So you could pick that row > directly, I think: > > SELECT processingtime FROM emails > WHERE <blah> ORDER BY processingtime DESC LIMIT <value>,1 > > Second, why use 95% of the table, if you can do with 5%? In other > words, calculate the remainder, sort ascending and limit by the > remainder you calculated. The difference should especially in your > case, where you copy those rows and can avoid to do so for 90% of the > columns. > > HTH, > > Benjamin. > > -- > [EMAIL PROTECTED] > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
