From: Mischa Sandberg <ischamay.andbergsay@activestateway.com>
Reply-To: ischamay.andbergsay@activestateway.com
User-Agent: Mozilla Thunderbird 0.7.3 (X11/20040803)
MIME-Version: 1.0
Subject: Re: olympics ranking query
References: <41262968.2000507@zara.6.isreserved.com>
	<22979.1093020382@sss.pgh.pa.us>
	<412630C8.5000004@zara.6.isreserved.com>
	<28621.1093022468@sss.pgh.pa.us>
In-Reply-To: <28621.1093022468@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 37
Message-ID: <mqrVc.40516$fz2.32057@edtnps89>
Date: Fri, 20 Aug 2004 18:29:38 GMT
To: pgsql-sql@postgresql.org

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
>