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