Re: idea - new aggregates median, listagg

2009/12/15 David Fetter <david(at)fetter(dot)org>:
> On Tue, Dec 15, 2009 at 10:28:49PM +0100, Pavel Stehule wrote:
>> Hello
>>
>> I am looking on new feature - ORDER clause in aggregate, and I thing,
>> so we are able to effectively implement some non standard, but well
>> known aggregates.
>>
>> a) function median - it is relative frequent request - with usually
>> slow implementation
>
> Makes a lot of sense.  I suspect we'll have to provide several
> different medians, as there are several precise, useful, and
> conflicting definitions.  Some examples below:
>
> http://www.simple-talk.com/sql/t-sql-programming/median-workbench/

In this article the are two medians - statistical and financial. I am
for both. But only one can be named "median".

>
>> b) function listagg (it is analogy of group_concat from MySQL) - it
>> should simplify report generating and some other
>
> This is redundant, as it's equivalent to array_to_string(array_agg()).

when I implement it in orafce - the almost all code was parameters
checking. Implementation is trivial, because important part of work
was done with array_agg support. It is wrapper over stringInfo.

>
> If it's done as syntactic sugar over that, it's fine.  If it's a
> separate implementation, it's a bad idea.  Probably best as an
> optional module of some kind

I am not sure if implementation as syntactic sugar is best. It needs
parser changes. And final implementation will not be effective.
listagg should be faster - string operations are significantly
simpler than array op. What more - PostgreSQL cannot well wrap SRF and
aggregates. The implementation could be analogy with functions
generate_series and generate_subscripts. generate_subscripts is
redundant, but well implementation needs C coding. Implementation as
syntactic sugar is possible - it could be nice too - listagg is more
shorter than array_to_string(array_agg()) - but it has overhead with
array.

There could be some steps. First - implementation as wrapper over
arrays, second - if this functionality will be popular - native
implementation in C?

Regards
Pavel

>
> Cheers,
> David.
> --
> David Fetter <david(at)fetter(dot)org> http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com
> iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>