Of course, what we are emulating here is called a "Process Historian", common
examples being PHD and PI. So, if you make a few minor adjustments, you can
make this run just about as fast as a "designed for purpose" Process Historian.
The changes are that you need to store the data in an "economical format". I
have chosen to store the timestamp as a floating-point julianday number. You
also need to calculate and store the slope to the previous engineering value
each time you store a value. You must also insert the data in chronological
order and you may not update a value once it has been inserted.
This is about 500 times (or more) faster than using the table you created. The
following work the same as the previous example but uses triggers to enforce
the constraints and calculate the slope for new engineering values stored. And
you only need to change the '+1 day' to whatever interval you want to use.
This will load the data into the pwr table from your existing table.
drop table pwr;
create table pwr
(
timestamp float primary key,
reading float not null,
ratetoprior float
) without rowid;
create trigger pwr_ins_stamp before insert on pwr
begin
select raise(ABORT, 'Data insertion must be in chronological order')
where new.timestamp < (select max(timestamp) from pwr);
end;
create trigger pwr_ins_slope after insert on pwr
begin
update pwr
set ratetoprior = (new.reading - (select reading
from pwr
where timestamp < new.timestamp
order by timestamp desc
limit 1))
/
(new.timestamp - (select timestamp
from pwr
where timestamp < new.timestamp
order by timestamp desc
limit 1))
where timestamp = new.timestamp;
end;
create trigger pwr_upd_error after update of timestamp, reading on pwr
begin
select raise(ABORT, 'Data update prohibited');
end;
insert into pwr (timestamp, reading)
select julianday(timestamp),
total_kwh
from power
order by julianday(timestamp);
select datetime(timestamp),
kwh
from (
with periods (timestamp) as
(
select julianday(date(min(timestamp), '-1 day') || ' 23:59:59.999')
from pwr
union all
select julianday(datetime(timestamp, '+1 day'))
from periods
where timestamp < (select max(timestamp) from pwr)
),
readings (timestamp, reading) as
(
select timestamp,
(select reading - (b.timestamp - p.timestamp) * ratetoprior
from pwr as b
where b.timestamp >= p.timestamp
limit 1) as reading
from periods as p
where timestamp between (select min(timestamp) from pwr)
and (select max(timestamp) from pwr)
)
select timestamp,
reading - lag(reading) over () as kwh
from readings
)
where kwh is not null;
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
