From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Running tally |
Date: | 2003-10-11 18:43:38 |
Message-ID: | 200310111943.38792.dev@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | elein | 2003-10-11 19:49:00 | Re: Running tally |
Previous Message | Richard Huxton | 2003-10-11 11:55:08 | Re: [SQL] sql performance and cache |