Get sum of delta values per row

Question

I have a table with a field containing a time delta. The rows are guaranteed to be in temporal order, that means the row with id 1 represents an event which took place before the one represented in row 2. Now I want to do a query which returns me the absolute timestamp for every row.

Example data:

id timestamp_delta
1  0
2  22
3  5
4  10

I know the absolute timestamp of the first entry. Let's assume it's 100. Thus the resulting table I want to create with a query would be:

id timestamp_absolute
1  100
2  122
3  127
4  137

Looks simple, but I'm quite stuck with this task.

What I can do is read the delta of row n-1 by having the table in the from clause twice

select *
from badtable t1, badtable t2
where t2.id = t1.id-1

since I can rely on the order. However I'm not sure how to go from here. Is it possible to somehow just increment the value with each row? Even if a solution would result in O(N2) runtime behaviour by calculating the sum from the beginning for every row, that would be acceptable.

I guess it's not the way we should store this data. The reason why we chose to use delta was to reduce the amount of data to be transferred from mobile apps (worth it, even if just a few percent saving), for convenience just writing it to the server DB as is.

I'm curious to see how simple or complicated the best solution is going to be...

In case it matters, it's a MySQL DB.

You can check here http://stackoverflow.com/questions/11664142/cumulative-total-in-ms-sql-server — Ozan Deniz, Jun 21 '13 at 19:32
@OzanDeniz: That's specifically for SQLServer - better questions for MySQL would be http://stackoverflow.com/questions/664700 and http://stackoverflow.com/questions/8359077 . — , Jun 21 '13 at 19:39

Paul Fleming · Accepted Answer · 2013-06-22T17:41:31.400

1

You could do it with a self join like so: (freehand)

SELECT t1.id, SUM(t2.timestamp_delta) + 100
FROM badtable t1 JOIN badtable t2 ON t2.id <= t1.id 
GROUP BY t1.id

edited Jun 22 '13 at 17:41

answered Jun 21 '13 at 19:34

Paul Fleming

24,238
8
76
113

Of course, that simple! Obviously I need some more practice using the group by clause, somehow it's not so intuitive to me. Thanks! – didi_X8 Jun 21 '13 at 20:09
@didi_X8 My pleasure. If you're happy with the answer, please upvote. – Paul Fleming Jun 21 '13 at 20:15
@didi_X8 joins on inequalities are not obvious – Conrad Frix Jun 21 '13 at 22:22
@flem you probably need a + 100 in there [sqlfiddle](http://sqlfiddle.com/#!2/11df6/3) – Conrad Frix Jun 21 '13 at 22:24

Get sum of delta values per row

1 Answers1