From: elein <elein@varlena.com>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Running tally
Date: Sat, 11 Oct 2003 12:49:00 -0700
Message-ID: <20031011124900.G6483@cookie.varlena.com> (raw)
In-Reply-To: <3F7E9956.30804@familyhealth.com.au>
References: <3F7E9956.30804@familyhealth.com.au>
You can use plpythonu (or tcl or C or R) to do running
sums. For plpythonu, you must initialize the SD[]
by calling it first with the proper argument.
create or replace function runsum(int,int)
returns int as
'
if args[0] == 1:
SD["currval"] = 0
return SD["currval"]
else:
try:
SD["currval"] += args[1]
except:
SD["currval"] = args[1]
return SD["currval"]
' language 'plpython';
select runsum(1,0);
select num, runsum(0,num) from tallytable;
Variations on this technique are discussed on
General Bits http://www.varlena.com/GeneralBits
under the Tidbits area listing talks from OSCON2003.
elein@varlena.com
webstat=# select runsum(0,code), code, doc from temp_rawlogs;
n Sat, Oct 04, 2003 at 05:56:38PM +0800, Christopher Kings-Lynne wrote:
> Hi guys,
>
> If I have a table that is just a single column full of numbers, how can
> I select all the rows from the table with a second column that is the
> running tally so far down the result set?
>
> eg:
>
> Num Tally so far
> 0.3 0.3
> 1.2 1.5
> 2.0 3.5
> ...
>
> Does this require PL/PgSQL coding? If so, how do you actually construct
> an arbitrary row for returning? The docs are somewhat unclear on this.
>
> Chris
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
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: elein@varlena.com, chriskl@familyhealth.com.au
Subject: Re: Running tally
In-Reply-To: <20031011124900.G6483@cookie.varlena.com>
* 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