Re: olympics ranking query

That 'running aggregate' notion comes up quite regularily,
and it has a number of nonintuitive holes, to do with
what happens when your ordering allows for ties.
ASTRID had it, RedBrick SQL had it, the latest MSSQL
has it ... not necessarily a recommendation.

Tom Lane wrote:
> David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> writes:
>
>>This is not quite the same. The ranks are sequential, but they skip, so
>>as to match the number of participating countries.
>
>
> Oh, I missed that bit.
>
> What you really want here is a "running sum" function, that is
>
> SELECT running_sum(numranker) as rank, * FROM
> (same subselect as before) ss;
>
> There is no such thing in standard SQL, because it's fundamentally
> dependent on the assumption of the input data coming in a particular
> order, which is Evil Incarnate according to the relational worldview.
> But it's not too hard to do in PLs that allow persistent state.
> I recall Elein having exhibited one in plpython(?) not too long ago
> --- you might find it on techdocs or varlena.com.
>
> You could brute-force it with a subselect (essentially "SUM(numranker)
> over all rows that should precede this one") but that would involve
> recomputing the whole subselect for each output row, which doesn't seem
> very attractive.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>