How can I construct a update query to calculate and set a record field "latency" with the difference between "timestamps" by "deviceid"? Appears sqlite doesn't support lag and lead.
ie. I have a table with 1,000,000 + records collecting real time stats from many devices with many columns but the main ones of interest are 'timestamp', 'latency' and 'DeviceID'. 2012-05-01 13:12:11.103 Null 14356 2012-05-01 13:12:11.103 Null 14372 2012-05-01 13:12:11.103 Null 4356 2012-05-01 13:12:07.103 Null 14356 .... 2012-05-01 13:12:11.221 Null 14356 The data is collected inconsistently... many times out of sequence I want to update the latency field for each record with the latest previous timestamp for the same DeviceID. Run this update query every time new data is appended to the table. Periodically recalc the entire table if an out of sequence file is processed or at minimum recalc from the earliest out of sequence time to present. I can do this fairly easily in excel but am struggling in sql/sqlite. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
