Re: onlyvalue aggregate (was: First Aggregate Funtion?)

On 10/28/15 5:53 PM, Pavel Stehule wrote:
> what is use case for this function and why it should be in core?

Corey had one example in his email, but I can offer another one which 
came up this week at $work.  The query looked something like this:

SELECT a, sum(amount), onlyvalue(rolling_count)
FROM
(    SELECT a, amount, count(*) OVER (ORDER BY a) AS rolling_count    FROM tbl
) ss
GROUP BY a;

We know that all the values for the column are going to be the same 
value for every "a", so we could use min() or max().  But the advantage 
of "onlyvalue" is that it actually checks that, so if someone went and 
changed the window frame to do something slightly different, the query 
would blow up instead of silently returning the (now likely incorrect) 
minimum or maximum value.  It's also self-documenting for the reader of 
such queries.

In my experience this problem comes up often enough that it would be 
make sense to have this aggregate in core.


.m