Re: FYI: geometric means in one step without custom functions - Mailing list pgsql-general

From Vincent Hikida
Subject Re: FYI: geometric means in one step without custom functions
Date
Msg-id 046201c349d3$30727380$210110ac@HOMEOFFICE
Whole thread Raw
In response to FYI: geometric means in one step without custom functions  (Andrew Gould <andrewgould@yahoo.com>)
List pgsql-general
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)
>


pgsql-general by date:

Previous
From: Mike Mascari
Date:
Subject: Re: select null + 0 question
Next
From: Martijn van Oosterhout
Date:
Subject: Re: Fw: select null + 0 question