Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1

    Unanswered: PL/SQL for calculate the Geometric Mean of data column!

    Hello,

    I would like to calculate the geometric mean of column data based on monthly recorded_date as following:

    Here is Geometric Mean Formula :
    Geometric Mean = ((X1)(X2)(X3)........(XN))^1/N
    where ^ stand for exponent.

    I'm newbie and totally not sure how to code PL/SQL Oracle for geometric mean aggregate function that can solve above issue.

    My detailed column data as shown below:

    Test | Value |Recorded_date
    -----------------------------
    1 | 7.8 | 3/1/2012
    1 |100.3 | 3/15/2012
    1 |48.9 | 3/30/2012
    1 |98.25 | 3/31/2012
    2 |58.3 | 3/2/2012
    2 |99.0 | 3/14/2012
    2 |45.6 | 3/19/2012
    2 |99.2 | 3/25/2012
    2 |100.4 | 3/26/2012
    2 |78.98 | 3/28/2012
    2 |98.99 | 3/29/2012
    2 |66.66 |3/30/2012
    2 | 88.77 |3/31/2012

    The expected result

    Test | GEO_MEAN(Value)
    =====================
    1 | ?
    2 | ?

    I really appreciate your time to work out on my issue. Thanks in advance.
    Last edited by avt2k6; 04-01-12 at 12:50.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try the follwing expression to calculate Geometric Mean in Oracle SQL.
    POWER( 10 , AVG( LOG(10 , value) ) )

    Explanation:
    Geometric Mean = ((X1)(X2)(X3)........(XN))^1/N
    LOG(base, Geometric Mean) = ( LOG(base, X1) + LOG(base, X2) + LOG(base, X3) + ....... + LOG(base, XN) ) / N
    LOG(base, Geometric Mean) = AVG( LOG(base, Xn) ) : Where n = 1, 2, 3, ... , N
    POWER(base, LOG(base, Geometric Mean) ) = POWER(base, AVG( LOG(base, Xn) ) ) : Where n = 1, 2, 3, ... , N
    Geometric Mean = POWER(base, AVG( LOG(base, Xn) ) ) : Where n = 1, 2, 3, ... , N
    Last edited by tonkuma; 04-01-12 at 14:06. Reason: Add Explanation.

  3. #3
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    Thank you so much for your time to explain the other Geometric Mean form via math conversions, so I can use Oracle functions to calculate the GM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •