On Mon, Jul 20, 2015 at 11:06 AM, Paul A Jungwirth <
p...@illuminatedcomputing.com> wrote:

> > The above implementation of "first" aggregate returns the first non-NULL
> item
> > value.
> I'm curious what advantages this approach has over these FIRST/LAST
> functions from the Wiki?:
>     https://wiki.postgresql.org/wiki/First/last_%28aggregate%29
> Also to get the "first non-null value" you can apply an ordering to
> just the aggregate function, e.g.:
>     select first(id order by start_time nulls last) from events;
> If you want speed you should probably write a C version.
> Is there something I'm missing?
> Also since we're on the hackers list is this a proposal to add these
> functions to core Postgres?
> Yours,
> Paul
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

If it is a proposal to add to core, I'd like to suggest a close cousin
function of first()/last():  only().     [1]

It would behave like first() but would throw an error if it encountered
more than one distinct value in the window.

This would be helpful in dependent grouping situations like this:
    select a.keyval, a.name_of_the thing, sum(b.metric_value) as
    from a
    join b on b.a_keyval = a.keyval
    group by a.keyval, a.name_of_the_thing

Now, everyone's made this optimization to reduce group-by overhead:
    select a.keyval, min(a.name_of_the_thing) as name_of_the_thing,
sum(b.metric_value) as metric_value
    from a
    join b on b.a_keyval = a.keyval
    group by a.keyval

Which works fine, but it's self-anti-documenting:
    - it implies that name of the thing *could* be different across rows
with the same keyval
    - it implies we have some business preference for names that are first
in alphabetical order.
    - it implies that the string has more in common with the summed metrics
(imagine this query has dozens of them) than the key values to the left.

Using first(a.name_of_the_thing) is less overhead than min()/max(), but has
the same issues listed above.

By using only(a.name_of_the_thing) we'd have a bit more clarity that the
author expected all of those values to be the same across the aggregate
window, and discovering otherwise was reason enough to fail the query.

*IF* we're considering adding these to core, I think that only() would be
just a slight modification of the last() implementation, and could be done
at the same time.

[1] I don't care what it gets named. I just want the functionality.

Reply via email to