Jeff Janes schrieb am 19.11.2016 um 22:12:
I need "strict" MIN and MAX aggregate functions, meaning they return
NULL upon any NULL input, and behave like the built-in aggregates if
none of the input values are NULL.
This doesn't seem like an outlandish thing to want, and I'm surprised
I can't find other discussion of it. Perhaps because none of the
words here are very effective as search terms as they are so
individually common.
I've hit upon a solution that works, but it is both ugly and slow
(about 50 fold slower than the built-ins; for my current purpose this
is not a big problem but I would love it to be faster if that could
be done easily).
This is not really pretty as well, but might be faster:
select a,
case when group_count = nn_count then min_b end as min_b
from (
select a,
min(b) as min_b,
count(b) as nn_count,
count(*) as group_count
from x
group by a
) t;
As the expensive part is the group by I wouldn't expect the additional aggregates to make a big difference.
Alternatively:
select a, case when no_nulls then min_b end as min_b
from (
select a,
min(b) as min_b,
bool_and(b is not null) as no_nulls
from x
group by a
) t;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
