pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Mischa Sandberg <ischamay.andbergsay@activestateway.com>
To: pgsql-sql@postgresql.org
Subject: Re: olympics ranking query
Date: Fri, 20 Aug 2004 18:29:38 GMT
Message-ID: <mqrVc.40516$fz2.32057@edtnps89> (raw)
In-Reply-To: <28621.1093022468@sss.pgh.pa.us>
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>

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
> 



view thread (8+ messages)  latest in thread

Message-ID: <mqrVc.40516$fz2.32057@edtnps89>
Permalink:  ../mqrVc.40516$fz2.32057@edtnps89/
Also on:    postgresql.org/message-id/mqrVc.40516$fz2.32057@edtnps89

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: ischamay.andbergsay@activestateway.com
  Subject: Re: olympics ranking query
  In-Reply-To: <mqrVc.40516$fz2.32057@edtnps89>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox