On Saturday, 19 October, 2019 18:26, Petr Jakeš <petr.jakes....@gmail.com> wrote:
>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. RateToPrior is the linear slope of the line which joins the current value to the prior value. Once you know the slope of that line, then for any point in time you merely need to find the next reading after that point in time and use the slope to calculate/interpolate what the value would have been at the time you are interested in (assuming that the value change is linear). Does this help? >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. -- 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
