Running totals using windowed functions
[Category: Programming and Databases] [Date: 2013-08-14 17:39:10]
Many modern databases support windowed aggregate functions. One of the most useful applications of these are computing running totals for either entire data sets or by a sub-grouping. On older database systems, one was reduced to while loops, cursors, CTE's, or perhaps very inefficient nested select queries to generate running totals. Thankfully, there are better options now. The below example is in PostgreSQL (supported 8.4+). A similar strategy works in Microsoft SQL but only in 2012+.
-- create data drop table if exists pricedata; create temp table pricedata (category text,item text,price float8); insert into pricedata (category,item,price) values ('Fishing','Tackle Box',20) ,('Camping','Sleeping Bag',50) ,('Baseball','Bat',40) ,('Fishing','Rod',100) ,('Fishing','Bait',5) ,('Camping','Stove',30); -- use windowed aggregate function with over () select category,item ,sum(price) over (partition by category order by item) totalprice from pricedata;
We get a logical result of:
category item totalprice Baseball Bat 40 Camping Sleeping Bag 50 Camping Stove 80 Fishing Bait 5 Fishing Rod 105 Fishing Tackle Box 125
It's fast, convenient, and pretty powerful.