Calculating Median value

From: Chandru Aroor <caroor(at)yahoo(dot)com>
To: "pgsql-novice(at)lists(dot)postgresql(dot)org" <pgsql-novice(at)lists(dot)postgresql(dot)org>
Subject: Calculating Median value
Date: 2018-05-06 13:19:31
Message-ID: 113513307.352934.1525612771808@mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,
I am trying to debug a function that was written by someone else.  As part of what this function does, it calculates the median value of a values passed on to it as a single column.  The value that it return is wrong. The user written function, lets call it calculate median_room_rate. It selects a list of qualifying values and uses median to determine the median.  At first I thought this was a inbuilt SQL function such as min and max. After many hours of trying to troubleshoot I discovered median might be a user written or an extension (I still don't know) function. My pgAdmin browser was not enabled to display the Aggregates Object where I found it.  median is defines as follows:
CREATE AGGREGATE public.median(anyelement) (  SFUNC=array_append,  STYPE=anyarray,  FINALFUNC=array_median I will be honest. I have no clue how that works or what it is supposed to do.  

As an example, I am passing on two values 0.1303 and 0.0757. These are being cast as  According to online calculators and Excel I am supposed to get back 0.1030. However, the median function is returning 1.0757 which totally does not make sense.
I tried to create my own median function in the Aggregates Object thinking I will pass in a numeric[] object, but I am unable to select any State Function or Final Function in the UI.
 I suppose I can write my own function, but not sure how/what.
Any help is mightily appreciated. 

Chandru

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2018-05-06 15:43:58 Re: Calculating Median value
Previous Message Andreas Kretschmer 2018-05-03 19:27:24 Re: Postgres warm standby with delay