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.
--