After long time I have set up development environment properly and I am able to start to study your queries.
I am lost. I don't either understand the first bunch of subqueries... ( What is returned in the "ratetoprior"? I have been pulling my hair over 3 hours trying to figure it out ... no clue what it means. 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) ) select * from pwr On Tue, Sep 3, 2019 at 1:11 AM Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users