Running totals using windowed functions

[Category: Programming and Databases] [link] [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
values ('Fishing','Tackle Box',20)
       ,('Camping','Sleeping Bag',50)
-- 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.

comments powered by Disqus