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). > > >> >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
