Re: Strict min and max aggregate functions

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;