Skip site navigation (1) Skip section navigation (2)

Running totals

From: Bastiaan Olij <lists(at)basenlily(dot)nl>
To: pgsql-novice(at)postgresql(dot)org
Subject: Running totals
Date: 2010-08-31 01:19:20
Message-ID: 4C7C5898.4030900@basenlily.nl (view raw or flat)
Thread:
Lists: pgsql-novice
Hi All,

I was wondering if anyone had a neat trick to do the following. Right
now the only way I can think of is to go with cursors but I hate those.
I did manage to do this with a query but that became exponentially
slower the bigger the data set as I was counting the total up to the
record I'm updating in a subquery and the further down the dataset I
get, the bigger the reverse lookup.

In Sybase there was a trick where you update a variable that resulted in
a running total but you didn't have control over the order in which the
records are processed.

I have a table with the columns:
- Primary key
- Date
- Value
- Bucket_1
- Bucket_2
- Bucket_3

What I want is to store any value up to the sum of 1000 into bucket 1,
up to 2000 into bucket 2, and the remainder in bucket 3, by date. Date
does not have to be unique so the  primary key is used as a second sort.

So say my data from the start is:
1, 20100801, 400 , 0, 0, 0
2, 20100801, 300 , 0, 0, 0
3, 20100802, 100 , 0, 0, 0
4, 20100803, 500 , 0, 0, 0
5, 20100804, 800 , 0, 0, 0
6, 20100805, 300 , 0, 0, 0
7, 20100806, 400 , 0, 0, 0

I want to end up with:

1, 20100801, 400 , 400, 0, 0
2, 20100801, 300 , 300, 0, 0
3, 20100802, 100 , 100, 0, 0
4, 20100803, 500 , 200, 300, 0 <-- 400+300+100+500 brings us to 1300, so
we start using bucket 2
5, 20100804, 800 , 0, 700, 100 <-- 1300+800 brings us to 2100, so we
start using bucket 3
6, 20100805, 300 , 0, 0, 300
7, 20100806, 400 , 0, 0, 400

Any ideas?

-- 
Kindest Regards,

Bastiaan Olij
e-mail/MSN: bastiaan(at)basenlily(dot)me
web: http://www.basenlily.me
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij


Responses

pgsql-novice by date

Next:From: Jayadevan MDate: 2010-08-31 03:35:32
Subject: Re: Partitions and the optimizer.
Previous:From: Mladen GogalaDate: 2010-08-30 15:15:39
Subject: Partitions and the optimizer.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group