On Sun, Oct 20, 2019 at 4:36 PM Keith Medcalf <kmedc...@dessus.com> wrote:
> > On Sunday, 20 October, 2019 06:58, Petr Jakeš <petr.jakes....@gmail.com> > wrote: > > >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. > > You got it exactly. It really does not matter if one of your requested > points (x) happens to match an actual sample, as the calculated result from > the next sample (y) should match that value because it is interpolating > backwards -- it is also easier to always calculate the appropriate y for > the given x, than it is to figure out where the x samples are and fill in > the blanks. > > > Do you mind if I will mention you/your solution on my blog (in the time > > when I write post about power/water metering)? > > Not at all. > > > What about if I want 1 hour granity? (to plot a graph of daily > > consumption for example) > > This should work exactly the same way. You just need to generate the > *periods* so that they match the x values for which you want to calculate > readings. Since the *used* table reflects the lag between two adjacent > rows, you need your *periods* to include the last hour of the previous day > and perhaps the first hour of the next day (so 26 points, as it were), and > then filter it afterwards when you plot your graph. Note that this will be > a "snapshot" based graph, not an "average" based graph. > > This scheme is generally how Process Historians work for continuous > control. Once each minute (the exact offset into the minute does not > really matter nor does it need to be the same, it is just frequent > readings) the instantaneous value is collected and stored together with the > computed slope to the previous value. From this you can interpolate what > the instantaneous value was at exactly each minute boundary. This gives > you a series of polygons which are basically estimates of the shape of the > curve. The historian will generate the one minute "snapshot" values and > average those to get each hourly average. It can also compute the > "confidence" in the accuracy of that value based on the number of actual > samples in the interval (vs the number there ought to be). Industry > standard values are called "raw" which is the actual recorded data with no > interpolation, "snapshot" (point in time interpolated values), one minute > average (which is different from the snapshot since the value usually > changed sometime during the minute and the average is computed assuming > that the prior value was in effect up until the change, and the new value > afterwards, ie, that the readings are discrete -- or it may average the > start of minute and the end of minute snapshots depending on > configuration), six minute average on every 6 minute boundary (the average > of the 6 one minute snapshot values (at minute offset 0, 6, 12 ... in the > hour), and hourly average on every hour (the average of the 60 one minute > snapshot values). Intervals less than a day the y (timestamp) is the at > the end of the interval, and for intervals greater than a day the y > (timestamp) is the beginning of the interval. Sometimes the sample > frequency is increased beyond 1 minute, in which case the calculations are > all based on that sample frequency giving you a better approximation of the > curve. (It is all really nothing more than just using Eulers method to > collect samples which will allow an integral to be approximated). > Thank you for nice lesson. I was briefly googling about Process Historians. Thanks to your help I know, SQLite is powerful enough for my purposes (to store 9 water meters and 9 power meters readings). All depends, of course, about the time granularity of stored readings. In my case, one reading a day (water) and 144 readings a day (energy) per spot will be enough. > > > > > >> >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
