On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf <kmedc...@dessus.com> wrote:
> 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? > If I understand it properly: - timestamps are considered as x values, readings as y values - *pwr* statement calculates slope of the line <https://en.wikipedia.org/wiki/Slope> (delta y / delta x) between all adjacent readings - *periods* statement calculates (fills in) the dates according to the requested granity - *readings* statement calculates readings from the next reading (next higher timestamp to the periods datestamp) - *used* statement calculates consumption (lag between two adjacent rows) My confusion was I didn't expect all values are calculated, not just returned directly from the database as I was almost doing. Your approach is GENIAL! Thank you. Do you mind if I will mention you/your solution on my blog (in the time when I write post about power/water metering)? What about if I want 1 hour granity? (to plot a graph of daily consumption for example) > >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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
