I have a simple data acquisition application that reads a byte counter and records it in an sqlite3 table. The table is defined as:
create table data (timestamp, bytecount); The 'timestamp' field is the output of the standard c-library seconds-since-the-epoch time() function and the 'bytecount' field is the value of the byte counter at that time. The data is populated by a small routine that samples the input on an irregular time interval and inserts the timestamp and bytecount into the table resulting in rows like this: 1121484797|11234 1121484923|14583 1121485008|19267 1121484801|12789 1121485051|23005 1121485832|28436 etc. (Notice that the timestamps are not ordered at the time of data collection.) I am attempting to work out an SQL query that will return a row-set which is the data rate calculated between time-ordered samples. I.e. (bytes(i+1)-bytes(i)) / (timestamp(i+1)-timestamp(i)) where i is the row number from a sub-query that orders the rows by timestamp. How do I use SQL to "walk" through a table like this? Is there a better table design for this type of application? I've been searching the web on such topics like "sql calculation between rows" but I'm not having much success. The best hint I've found is to construct a join between the table and itself but I can't see how to do that in a way that offsets one side of the join relative to the other side. Thanks very much, Bill
