Обсуждение: FILTER clause for non-aggregate window functions
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
Andrey Zhidenkov
=?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
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