Обсуждение: Running totals

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@basenlily.me
web: http://www.basenlily.me
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij

In response to Bastiaan Olij :
>
> 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?

If you on 8.4, then yes. This code isn't correct, but i think, you can
see the way:

test=*# select * from olij;
 id | value
----+-------
  1 |   400
  2 |   300
  3 |   100
  4 |   500
  5 |   800
  6 |   300
  7 |   400
(7 rows)

select *,
  case when sum(value) over (order by id) < 1000
    then value
    else case when sum(value) over (order by id) - value < 1000
      then value + 1000 - sum(value) over (order by id)
      else 0
    end
  end as bucket1,
  case when sum(value) over (order by id) between 1000 and 2000
    then case when sum(value) over (order by id) - value < 1000
      then sum(value) over (order by id) - 1000
      else value
    end
    else 0 end as bucket2,
  case when sum(value) over (order by id) between 2000 and 3000
    then value else 0 end as bucket3 from olij order by id;


 id | value | bucket1 | bucket2 | bucket3
----+-------+---------+---------+---------
  1 |   400 |     400 |       0 |       0
  2 |   300 |     300 |       0 |       0
  3 |   100 |     100 |       0 |       0
  4 |   500 |     200 |     300 |       0
  5 |   800 |       0 |       0 |     800
  6 |   300 |       0 |       0 |     300
  7 |   400 |       0 |       0 |     400
(7 rows)


Again, it's NOT correct, i haven't time yet, but i hope you can see the
way...


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99