This is a great technique. It is especially useful in finance for
compounded interest for problems like the following
total return = ((1+janReturn)(1+febReturn)(1+marReturn))-1
I first learned it from an MBA in finance when I was looking over a
spreadsheet that she wrote.
Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"
www.UrbanaSoft.com
----- Original Message -----
From: "Andrew Gould" <andrewgould@yahoo.com>
To: "Postgres Mailing List" <pgsql-general@postgresql.org>
Sent: Sunday, July 06, 2003 8:38 AM
Subject: [GENERAL] FYI: geometric means in one step without custom functions
> A long time ago, I emailed this list about calculating
> a geometric mean in PostgreSQL. Creating a custom
> function didn't work because the process of
> multiplying the values from each record resulted in
> numbers that exceeded the limits for the size of a
> number very quickly when dealing with large
> populations.
>
> I have learned, since, that you can achieve the same
> end by replacing certain steps with log functions.
> (Someone who is very good at math showed me this -- I
> just tested the results and wrote the sql.) This
> method has 2 great benefits:
>
> 1. The method pushes the limits of deriving geometric
> mean calculations considerably.
> 2. The default installation of PostgreSQL has
> everything needed to perform the calculation.
>
> The sql statement below calculates the geometric mean
> of the lengths of stay (gm_los) for patients, grouped
> by diagnostic related group and fiscal year.
>
> The population (cases) and average length of stay
> (avg_los) are also reported.
>
> Note 1. Make sure you are calculating geometric mean
> on a data type that has values to the right of the
> decimal point.
>
> Note 2. You cannot use a log function on a value <= 0.
> Thus, I filtered for los > 0.
>
> select drg_no, fy, count(pt_id) as cases,
> avg(los) as avg_los,
> exp(sum(ln(los::real)) * (1.0/count(pt_id))) as gm_los
>
> from case_current where los > 0
> group by drg_no, fy;
>
> Have fun!
>
> Andrew Gould
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>