pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRunning tally
3+ messages / 3 participants
[nested] [flat]
* Running tally
@ 2003-10-04 09:56 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 2 replies; 3+ messages in thread
From: Christopher Kings-Lynne @ 2003-10-04 09:56 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Running tally
@ 2003-10-11 18:43 Richard Huxton <dev@archonet.com>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2003-10-11 18:43 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-sql
On Saturday 04 October 2003 10:56, 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.
Did you post this days ago, or is your/my clock wrong - it reads 2003-10-04 in
my mailer?
Anyway, you could do it with a sub-query, but performance will be less than
great with a large result-set.
SELECT * FROM runtot ;
idx | num
-----+-----
1 | 10
2 | 20
3 | 30
CREATE FUNCTION runtot_sum(int4) RETURNS int4 AS 'SELECT sum(num)::int4 FROM
runtot WHERE idx <= $1' LANGUAGE 'SQL';
SELECT idx, num, runtot_sum(idx) FROM runtot ORDER BY idx;
idx | num | runtot_sum
-----+-----+------------
1 | 10 | 10
2 | 20 | 30
3 | 30 | 60
Procedural is the way to go if you have a large result set. Stephan Szabo has
written some good notes on set-returning functions:
http://techdocs.postgresql.org/guides/SetReturningFunctions
Basically, define a type:
CREATE TYPE run_tot_type AS (
a whatever,
b whatever,
run_tot whatever
);
Then:
CREATE FUNCTION my_run_tot() RETURNS SETOF run_tot_type AS...
Accumulate your values in a record-type variable and use RETURN NEXT to issue
each row.
HTH
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Running tally
@ 2003-10-11 19:49 elein <elein@varlena.com>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
1 sibling, 0 replies; 3+ messages in thread
From: elein @ 2003-10-11 19:49 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-10-11 19:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-04 09:56 Running tally Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-10-11 18:43 ` Richard Huxton <dev@archonet.com>
2003-10-11 19:49 ` elein <elein@varlena.com>
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