Re: ANY_VALUE aggregate

On Mon, Dec 5, 2022 at 8:46 PM Vik Fearing <vik(at)postgresfriends(dot)org> wrote:

> On 12/5/22 18:56, David G. Johnston wrote:
> > Also, maybe we should have any_value do something like compute a 50/50
> > chance that any new value seen replaces the existing chosen value,
> instead
> > of simply returning the first value all the time. Maybe even prohibit
> the
> > first value from being chosen so long as a second value appears.
>
> The spec says the result is implementation-dependent meaning we don't
> even need to document how it is obtained, but surely behavior like this
> would preclude future optimizations like the ones I mentioned?
>

So, given the fact that we don't actually want to name a function
first_value (because some users are readily confused as to when the concept
of first is actually valid or not) but some users do actually wish for this
functionality - and you are proposing to implement it here anyway - how
about we actually do document that we promise to return the first non-null
value encountered by the aggregate. We can then direct people to this
function and just let them know to pretend the function is really named
first_value in the case where they specify an order by. (last_value comes
for basically free with descending sorting).

>
> I once wrote a random_agg() for a training course that used reservoir
> sampling to get an evenly distributed value from the inputs. Something
> like that seems to be what you are looking for here. I don't see the
> use case for adding it to core, though.
>
>
The use case was basically what Tom was saying - I don't want our users
that don't understand the necessity of order by, and don't read the
documentation, to observe that we consistently return the first non-null
value and assume that this is what the function promises when we are not
making any such promise to them. As noted above, my preference at this
point would be to just make that promise.

David J.