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
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 |