From: Kyle Bateman <kyle@actarg.com>
To: pgsql-sql@postgresql.org
Subject: Re: olympics ranking query
Date: Thu, 29 Mar 2007 15:33:27 -0600
Message-ID: <460C30A7.2020409@actarg.com> (raw)
Several years ago someone posted a question about how to achieve a
running total of columns in sql. I have been trying to find a solution
to a similar problem for some time and just came up with something that
works great for me so I thought I'd post it for the benefit of the list.
The problem is to produce a query like the following:
select date,amount,run_sum(amount) from ledger order by date;
Date Amount Sum
----------- ---------- ---------
2007-Jan-01 10.00 10.00
2007-Jan-02 20.00 30.00
2007-Jan-05 5.00 35.00
2007-Jan-10 -3.00 32.00
.
.
.
Using pl/tcl, I made the following function:
#Keep a running sum on a column where tag and trans are used to keep the
#results distinct from other calls to the same function
#Usage: run_sum(value,tag,trans)
#-------------------------------------------
function run_sum(numeric,text,text) {} {
returns numeric language pltcl called on null input as $$
if {![info exists GD(sum.$2.$3)]} {
set GD(sum.$2.$3) 0.00
}
if {[argisnull 1]} {
return $GD(sum.$2.$3)
} else {
return [set GD(sum.$2.$3) [expr $GD(sum.$2.$3) + $1]]
}
$$;}
Then I added a wrapper function to automatically produce a separate
namespace for each statement instance that uses the query:
#Keep a running sum on a column where tag is used to keep the results
distinct
#Automatically keeps results distinct for each separate statement
#Usage: run_sum(value,tag)
#-------------------------------------------
function run_sum(numeric,text) {run_sum(numeric,text,text)} {
returns numeric language sql as $$
select run_sum($1,$2,statement_timestamp()::text);
$$;}
Now you can do:
select date,amount,run_sum(amount,'amount') from ledger;
to get an initial result. The only problem is now ordering the data.
If you just put an 'order by' clause on the end, you don't get what you
might expect because the ordering happens after the function has
produced its result. So I do the following to order and sum it correctly:
select date,amount,run_sum(amount,'amount') from (select * from ledger
order by date) ss;
The use of the "tag" parameter allows you to use this on multiple
columns such as:
select date,debit,credit,
run_sum(debit,'debit')::numeric(14,2) as debits,
run_sum(credit,'credit')::numeric(14,2) as credits
from (select * from ledger order by date) ss;
Enjoy,
Kyle
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: kyle@actarg.com
Subject: Re: olympics ranking query
In-Reply-To: <460C30A7.2020409@actarg.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