Thread: 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