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
mysql select avg(test_val) from g_mean;
+---+
| avg(test_val) |
+---+
|46 |
+---+
1 row in set (0.00 sec)
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)
X
-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
