On Sun, Oct 20, 2019 at 8:23 PM Petr Jakeš <petr.jakes....@gmail.com> wrote:
> > > 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. > 1 hour granularity works as expected... select julianday(strftime('%Y-%m-%d %H',(min(timestamp)), '-1 hour') || ':00:00.000') from pwr union all select julianday(datetime(timestamp, '+1 hour')) from periods where timestamp < (select max(timestamp) from pwr) > > > >> > >> > >> >> >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
