Обсуждение: BUG #13238: [FEATURE] - OVER ( [ ] ) support for Ordered-Set Aggregate Functions

The following bug has been logged on the website:

Bug reference:      13238
Logged by:          Vladimir Nicolici
Email address:      vladnc@gmail.com
PostgreSQL version: 9.4.1
Operating system:   Linux 3.13.0-32-generic #57-Ubuntu SMP
Description:

First, thanks for implementing the percentile functions, they are very
useful.

However, there is still room for improvement, since at the moment they don't
seem to be implemented as window functions.

According to the documentation and my testing this functions do not support
the "OVER" clause yet, for example this is the description of the
percentile_cont function:

http://www.postgresql.org/docs/devel/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE

percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)


However, SQL Server and Oracle Database support that:

>From https://msdn.microsoft.com/en-us/library/hh231473.aspx

PERCENTILE_CONT ( numeric_literal )
    WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )
    OVER ( [ <partition_by_clause> ] )

Same syntax supported by the Oracle Database:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions110.htm


Having that clause would be very useful, because at the moment you can't use
those functions in PostgreSQL without using group by.

So to work around this I have to write the same complex query twice, once
with group by and once without group by, and join the two queries on the
"partition by" expression.

I hate duplicating code, so I think that if there's an easy way to make them
window functions, it should be done.

Off topic, not having a bug tracker is a major annoyance for me, because I
don't want to subscribe to an entire mailing list just to be able to comment
on an issue or two.

I did that once, and the amount of "spam" I received as a result was
unbearable, so I'm not doing that again.

This is submitted using the bug form, and I'll search for this bug on Google
from time to time to see what happens.

Also, mailing lists in 2015? What about at least something like a forum
software, if a bug tracker is too much?

Also, I know this not really a bug, but the pgsql-hackers mailing list
doesn't have a "feature reporting form", so I had to use this.

vladnc@gmail.com wrote:

> Off topic, not having a bug tracker is a major annoyance for me, because I
> don't want to subscribe to an entire mailing list just to be able to comment
> on an issue or two.

You can write to pgsql-hackers@postgresql.org with your request without
subscribing.  Your email will get stalled for moderation, but these
should normally be released and posted within a couple of days;
everybody will normally CC you in their replies, and you can see the
entire thread in the web archives for those that don't.

> I did that once, and the amount of "spam" I received as a result was
> unbearable, so I'm not doing that again.

You know that gmail has filters that let you filter emails so that they
are "archived" automatically, i.e. not become part of your inbox, right?

> This is submitted using the bug form, and I'll search for this bug on Google
> from time to time to see what happens.

... or you can see the web archives,
https://www.postgresql.org/message-id/20150505131841.1095.56314%40wrigleys.postgresql.org

> Also, mailing lists in 2015? What about at least something like a forum
> software, if a bug tracker is too much?

You, sir, are a fine jester.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services