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@zara.6.isreserved.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
>