On Monday, 2 September, 2019 12:26, Petr Jakeš <[email protected]> wrote:
>Yes, you are right. The error is connected with the version of
>SQLite. Now I am trying to build DB Browser using SQLite version 3.29.0.
>Than I have to study your code. Your knowledge and SQL Windows
>functions are over my scope. Thank for the study material for next weekend :D
Here is another example for you to ponder that uses your original table and
requires one index. It builds the "pwr" view dynamically but uses the
ratetoprior to compute the instant readings and only needs one window query to
compute the usage from two readings. Performance is half way between the other
two examples:
create index if not exists power_jd on power (julianday(timestamp), total_kwh);
with pwr (timestamp, reading, ratetoprior) as
(
select julianday(timestamp),
total_kwh,
(select (c.total_kwh - p.total_kwh) / (julianday(c.timestamp) -
julianday(p.timestamp))
from power as p
where julianday(p.timestamp) < julianday(c.timestamp)
order by julianday(p.timestamp) desc
limit 1)
from power as c
order by julianday(timestamp)
),
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)
),
used (timestamp, kwh) as
(
select timestamp,
reading - lag(reading) over ()
from readings
)
select datetime(timestamp),
kwh
from used
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
