On Monday, 2 September, 2019 12:26, Petr Jakeš <petr.jakes....@gmail.com> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users