Postgres Pro
Downloads
Home   >   mailing lists

Thread: FILTER clause for non-aggregate window functions

FILTER clause for non-aggregate window functions

From
Андрей Жиденков
Date:
I need to find the first date value in widow which meets certain conditions. In my case I use min() function with CASE like this:

SELECT min(CASE WHEN <conditions> THEN <date_field> end) OVER (PARTITION BY .. ORDER BY <date_field> ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)

But this is too slow mainly because of min() should read all tuples in window, I guess. So I tried to use first_value() function like this:

SELECT first_value(<date_field>) FILTER(WHERE <conditions>) OVER (PARTITION BY .. ORDER BY <date_field> ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)

But I got this error:

FILTER is not implemented for non-aggregate window functions

Why FILTER is not implemented for non-aggregate functions? Is there some restrictions in PostgreSQL executor core or maybe this behavior will lead to some conflicts? Is there a way to find needed value without scanning all tuples in window? Any help would be greatly appreciated.

--
Andrey Zhidenkov

Re: FILTER clause for non-aggregate window functions

From
Tom Lane
Date:
=?UTF-8?B?0JDQvdC00YDQtdC5INCW0LjQtNC10L3QutC+0LI=?= <pensnarik@gmail.com> writes:
> Why FILTER is not implemented for non-aggregate functions?

The SQL standard only describes FILTER for aggregates.  I'm not sure
offhand whether it'd be semantically sensible for window functions.
In any case, seems like most of the cases where you'd want it can be
expressed equally well by putting the restriction into WHERE.

            regards, tom lane

Re: FILTER clause for non-aggregate window functions

From
Андрей Жиденков
Date:
I could not put the restrictions in WHERE because I want them to be applied only to the window frame, not to all selected rows.

On 12 Dec. 2017 18:35, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
\xC1\xBA\xFFАндрей Жидем\xAF\xFDков <pensnarik@gmail.com> writes:
> Why FILTER is not implemented for non-aggregate functions?

The SQL standard only describes FILTER for aggregates.  I'm not sure
offhand whether it'd be semantically sensible for window functions.
In any case, seems like most of the cases where you'd want it can be
expressed equally well by putting the restriction into WHERE.

                        regards, tom lane