> I suppose I could copy the data table into a temporary
> table ... documenataion quarantees AUTOINCREMENT fields
> to be "monotonically increasing" ...
"Monotonically increasing" does not mean that for every key k
there will be a key (k-1). In fact, if you ever delete a row in
an autoincrement table, you're guaranteed to have a "hole" in
your keys.
You don't really need a manufactured key. Since your arithmetic
depends on the timestamp being unique, you can use the timestamp
as your primary key; but you should define the key to enforce
existence and uniqueness:
create table bytes (ts int primary key, bytes int);
insert into bytes (ts, bytes) values (1121484797, 11234);
insert into bytes (ts, bytes) values (1121484923, 14583);
insert into bytes (ts, bytes) values (1121485008, 19267);
insert into bytes (ts, bytes) values (1121484801, 12789);
insert into bytes (ts, bytes) values (1121485051, 23005);
insert into bytes (ts, bytes) values (1121485832, 28436);
select datetime(ts, 'unixepoch'), bytes from bytes
order by ts;
||
\/
datetime(ts, 'unixepoch') bytes
------------------------- ----------
2005-07-16 03:33:17 11234
2005-07-16 03:33:21 12789
2005-07-16 03:35:23 14583
2005-07-16 03:36:48 19267
2005-07-16 03:37:31 23005
2005-07-16 03:50:32 28436
select datetime(ts, 'unixepoch'), bytes from bytes
where datetime(ts, 'unixepoch') between
'2005-07-16 03:35:01' and '2005-07-16 03:40:00'
order by ts;
||
\/
datetime(ts, 'unixepoch') bytes
------------------------- ----------
2005-07-16 03:35:23 14583
2005-07-16 03:36:48 19267
2005-07-16 03:37:31 23005
select datetime(t2.ts, 'unixepoch') 'time',
(0.0+t2.bytes-t1.bytes)/(t2.ts-t1.ts) 'byte rate'
from bytes t1, bytes t2
where t1.ts =
(select max(t3.ts) from bytes t3 where t3.ts < t2.ts)
and datetime(t2.ts, 'unixepoch') between
'2005-07-16 03:35:01' and '2005-07-16 03:40:00'
order by t2.ts ;
||
\/
time byte rate
------------------- ----------------
2005-07-16 03:35:23 14.7049180327869
2005-07-16 03:36:48 55.1058823529412
2005-07-16 03:37:31 86.9302325581395
But SQL is not designed to do sequential processing of data.
You'll probably get better performance and more readable code if
you retrieve your data set with SQL
select bytes, ts from bytes
where datetime(ts, 'unixepoch') between
'2005-07-16 03:35:01' and '2005-07-16 03:40:00'
order by ts;
and use your host language to do the arithmetic.
Regards
