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

Reply via email to