agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFILTER clause for non-aggregate window functions
3+ messages / 2 participants
[nested] [flat]
* FILTER clause for non-aggregate window functions
@ 2017-12-12 07:54 Андрей Жиденков <pensnarik@gmail.com>
2017-12-12 15:35 ` Re: FILTER clause for non-aggregate window functions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Андрей Жиденков @ 2017-12-12 07:54 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: FILTER clause for non-aggregate window functions
2017-12-12 07:54 FILTER clause for non-aggregate window functions Андрей Жиденков <pensnarik@gmail.com>
@ 2017-12-12 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-12-12 16:53 ` Re: FILTER clause for non-aggregate window functions Андрей Жиденков <pensnarik@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2017-12-12 15:35 UTC (permalink / raw)
To: Андрей Жиденков <pensnarik@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
=?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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: FILTER clause for non-aggregate window functions
2017-12-12 07:54 FILTER clause for non-aggregate window functions Андрей Жиденков <pensnarik@gmail.com>
2017-12-12 15:35 ` Re: FILTER clause for non-aggregate window functions Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-12-12 16:53 ` Андрей Жиденков <pensnarik@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Андрей Жиденков @ 2017-12-12 16:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@lists.postgresql.org
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:
> =?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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-12-12 16:53 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-12-12 07:54 FILTER clause for non-aggregate window functions Андрей Жиденков <pensnarik@gmail.com>
2017-12-12 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-12-12 16:53 ` Андрей Жиденков <pensnarik@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox