Message-ID: <ffddcf31-d170-9cf0-f6b6-985cd9e5be46@postgresfriends.org>
Date: Tue, 6 Dec 2022 04:46:37 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: ANY_VALUE aggregate
Content-Language: en-US
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <5cff866c-10a8-d2df-32cb-e9072e6b04a2@postgresfriends.org>
 <CAKFQuwau9pyMB=4S-RUEZ6_Nf_=BuSVb+7FKhjJqPyaSROjreQ@mail.gmail.com>
From: Vik Fearing <vik@postgresfriends.org>
In-Reply-To: 
 <CAKFQuwau9pyMB=4S-RUEZ6_Nf_=BuSVb+7FKhjJqPyaSROjreQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/ffddcf31-d170-9cf0-f6b6-985cd9e5be46%40postgresfriends.org>
Precedence: bulk

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?

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 for ANY_VALUE is compliance with the standard.
-- 
Vik Fearing