Обсуждение: Running tally

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

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

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