Re: [sqlite] Calculation between rows?

[Kurt Welgehausen]

> 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:1711234
2005-07-16 03:33:2112789
2005-07-16 03:35:2314583
2005-07-16 03:36:4819267
2005-07-16 03:37:3123005
2005-07-16 03:50:3228436

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:2314583
2005-07-16 03:36:4819267
2005-07-16 03:37:3123005

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

Re: [sqlite] Calculation between rows?

[William Trenker]

On 7/17/05, Darren Duncan <[EMAIL PROTECTED]> wrote:
> Assuming there actually is a meta-data function like ROW_NUMBER(),
> which is the ordinal value of the row in the query result, you can
> just do something like this:
> 
> SELECT 
> FROM (
>SELECT ROW_NUMBER() AS myrownum, sq.*
>FROM  AS sq
> ) AS a FULL OUTER JOIN (
>SELECT ROW_NUMBER + 1 AS myrownum, ...
>FROM  AS sq
> ) AS b ON b.myrownum = a.myrownum
> ...
> 

I suppose I could copy the data table into a temporary table with an
AUTOINCREMENT field.  (The row-set used to create the temporary
table's contents would be ordered appropriately.) The SQLite
documenataion quarantees AUTOINCREMENT fields to be "monotonically
increasing".  I'll try that out using the ideas you've given me.

Thanks for the insight, Darren.

Cheers,
Bill

Re: [sqlite] Calculation between rows?

[Darren Duncan]

At 4:27 PM -0400 7/17/05, William Trenker wrote:

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.


Assuming there actually is a meta-data function like ROW_NUMBER(), 
which is the ordinal value of the row in the query result, you can 
just do something like this:


SELECT 
FROM (
  SELECT ROW_NUMBER() AS myrownum, sq.*
  FROM  AS sq
) AS a FULL OUTER JOIN (
  SELECT ROW_NUMBER + 1 AS myrownum, ...
  FROM  AS sq
) AS b ON b.myrownum = a.myrownum
...

So you do the join between the subquery and itself, and the "+1" 
causes a single row offset in the join.  Note you may have to UNION 
ALL a single row to the top or bottom of each subquery in order to 
prevent losing the first/last row.


-- Darren Duncan