Build SUM based daily record
- by ximarin
I have a problem building an aggregate function. Here's my problem:
I have a table like this
id action day isSum difference
1 ping 2012-01-01 1 500 (this is the sum of the differences from last year)
2 ping 2012-01-01 0 -2
3 ping 2012-01-02 0 1
4 ping 2012-01-03 0 -4
5 ping 2012-01-04 0 -2
6 ping 2012-01-05 0 3
7 ping 2012-01-06 0 2
8 ping 2012-01-01 1 0 (this is the sum of the differences from last year, now for pong)
9 pong 2012-01-01 0 -5
10 pong 2012-01-02 0 2
11 pong 2012-01-03 0 -2
12 pong 2012-01-04 0 -8
13 pong 2012-01-05 0 3
14 pong 2012-01-06 0 4
I now need to select the action, day and the summarized difference since 01-01 for every day, so that my result looks like this
action day total
ping 2012-01-01 498
ping 2012-01-02 499
ping 2012-01-03 495
ping 2012-01-04 493
ping 2012-01-05 496
ping 2012-01-06 498
pong 2012-01-01 - 5
pong 2012-01-02 - 3
pong 2012-01-03 - 5
pong 2012-01-04 -13
pong 2012-01-05 -10
pong 2012-01-06 - 6
How can I do this?
there a are a lot of datasets (~1 million), so the query needs to be pretty cheap. I don't know how the use sum to get daily sums for daily records depending on the action-column.
