Re: Calculating Median value
| От | Chandru Aroor |
|---|---|
| Тема | Re: Calculating Median value |
| Дата | |
| Msg-id | 1526435930.410638.1525626383262@mail.yahoo.com обсуждение исходный текст |
| Ответ на | Re: Calculating Median value (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: Calculating Median value
|
| Список | pgsql-novice |
Thanks Tom. You are actually correct. In talking with the business user, we need to get the 50th percentile, and picking the higher value for even number of members, so median as Excel calculates it is actually not appropriate. Will aggregate percentile_cont, or possibly percentile_disc do it?
On Sunday, May 6, 2018, 10:44:19 AM CDT, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Chandru Aroor <caroor@yahoo.com> writes:
> ... 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.
It's a user-defined aggregate. See
https://www.postgresql.org/docs/current/static/xaggr.html
array_append is a built-in function that, in this usage, would just serve
to collect all the input values into an array. array_median is not the
name of any built-in function, so it must be user-written code. What
I'd expect it to do, if it's trying to implement the usual definition of
median, is to sort the array and then take the middle element. Judging
from your description, it's not doing that.
If you're using PG 9.4 or later, you could skip trying to debug this
homegrown version of median and instead use the standard ordered-set
aggregate percentile_cont, or possibly percentile_disc. See
https://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES
regards, tom lane
> ... 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.
It's a user-defined aggregate. See
https://www.postgresql.org/docs/current/static/xaggr.html
array_append is a built-in function that, in this usage, would just serve
to collect all the input values into an array. array_median is not the
name of any built-in function, so it must be user-written code. What
I'd expect it to do, if it's trying to implement the usual definition of
median, is to sort the array and then take the middle element. Judging
from your description, it's not doing that.
If you're using PG 9.4 or later, you could skip trying to debug this
homegrown version of median and instead use the standard ordered-set
aggregate percentile_cont, or possibly percentile_disc. See
https://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES
regards, tom lane
В списке pgsql-novice по дате отправления: