Thank you to each of you who responded with help. Unfortunately, while testing a Python script, I found the drawback to the geometric mean calculation:
If your sample size is in the hundreds of thousands, it's easy to hit the hardware (processor) limitation on the number size while multiplying the sample values together. Now I know why I don't see geometric mean used much. Best of luck, Andrew Gould --- Gordon <[EMAIL PROTECTED]> wrote: > This is probably not the most elegent solution, but > here is something > that works in 3 SQL statements using user variables. > > mysql> select @gmean := 1, @ct := count(*) from > g_mean; > mysql> select @gmean := @gmean * test_val from > g_mean; > mysql> select power(@gmean, (1/@ct)) as Geo_Mean; > > See details below > > Gordon Bruce > Interstate Software > A MySQL US Training Partner > > mysql> create table g_mean (test_val float); > Query OK, 0 rows affected (0.02 sec) > > mysql> insert into g_mean values > (23),(32),(12),(145),(18); > Query OK, 5 rows affected (0.00 sec) > Records: 5 Duplicates: 0 Warnings: 0 > > xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx > > mysql> select avg(test_val) from g_mean; > +---------------+ > | avg(test_val) | > +---------------+ > | 46 | > +---------------+ > 1 row in set (0.00 sec) > > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX > > mysql> select @gmean := 1, @ct := count(*) from > g_mean; > +-------------+-----------------+ > | @gmean := 1 | @ct := count(*) | > +-------------+-----------------+ > | 1 | 5 | > +-------------+-----------------+ > 1 row in set (0.00 sec) > > mysql> select @gmean := @gmean * test_val from > g_mean; > +-----------------------------+ > | @gmean := @gmean * test_val | > +-----------------------------+ > | 23 | > | 736 | > | 8832 | > | 1280640 | > | 23051520 | > +-----------------------------+ > 5 rows in set (0.00 sec) > > mysql> select power(@gmean, (1/@ct)) as Geo_Mean; > +-----------+ > | Geo_Mean | > +-----------+ > | 29.685196 | > +-----------+ > 1 row in set (0.02 sec) > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX > > > -----Original Message----- > > From: Andrew Gould [mailto:[EMAIL PROTECTED]] > > Sent: Saturday, May 11, 2002 7:51 AM > > To: [EMAIL PROTECTED] > > Subject: geometric mean aggregate function > > > > Has anyone out there developed a costum, aggregate > > function for calculating the geometric mean of > > populations? If not, would anyone be interested > in > > creating one? > > > > My development experience/skills is limited to SQL > and > > simple Python scripts; so I had difficulty > > understanding the documentation on creating > functions. > > > > Why _you_ might want this function: Geometric > mean is > > a good replacement for arithmetic mean when you > want > > to reduce the effects of extreme, infrequent > values > > without excluding the occurrences altogether. An > > example is Medicare's (US government health > insurance > > for the elderly) use of geometric mean to derive > > standard lengths of stay (days in the hospital) > for > > patients within Diagnostic Related Groups (DRGs). > > For companies calculating statistics for internal > > process improvement projects, this function would > be > > very useful because is describes what usually > happens > > better than the arithmetic mean (see below). > > > > Calculating geometric mean: > > Geometric mean is calculated by multiplying the > values > > of each occurrence together and raising the > resulting > > product by the power of 1 divided by the sample > size. > > Therefore, the geometric mean of 2 3, 4, 5, and 40 > > would be (2*3*4*5*40)^(1/5) = 5.448139855, which > is > > very different from the arithmetic mean of 10.8. > I > > certainly would have liked my school grades to be > > calculated this way! > > > > If anyone has created this function and would be > > willing to share it, I would greatly appreciate > it. > > If the developers on this list see value in adding > it > > as a standard function to MySQL, I think you will > be > > ahead of the pack as industries are becoming more > data > > savvy. > > > > Is this a fairly simple request; or would it have > been > > more appropriate to send this request to > > [EMAIL PROTECTED]? > > > > Thanks, > > > > Andrew Gould > > > > __________________________________________________ > > Do You Yahoo!? > > LAUNCH - Your Yahoo! Music Experience > > http://launch.yahoo.com > > > > > --------------------------------------------------------------------- > > 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 <mysql-unsubscribe- > > [EMAIL PROTECTED]> > > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- > 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 > __________________________________________________ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com --------------------------------------------------------------------- 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
