Get sum of delta values per row [duplicate]

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.

@OzanDeniz: That's specifically for SQLServer - better questions for MySQL would be stackoverflow.com/questions/664700 and stackoverflow.com/questions/8359077 . — user359040
– user359040, Commented Jun 21, 2013 at 19:39

Paul Fleming · Accepted Answer · 2013-06-22 17:41:31Z

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, 2013 at 17:41

answered Jun 21, 2013 at 19:34

Paul Fleming

24.6k8 gold badges79 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

didi_X8 Over a year ago

Of course, that simple! Obviously I need some more practice using the group by clause, somehow it's not so intuitive to me. Thanks!

Paul Fleming Over a year ago

@didi_X8 My pleasure. If you're happy with the answer, please upvote.

Conrad Frix Over a year ago

@didi_X8 joins on inequalities are not obvious

Conrad Frix Over a year ago

@flem you probably need a + 100 in there sqlfiddle

Collectives™ on Stack Overflow

Get sum of delta values per row [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related