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.
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 <[email protected]> wrote:
>
> On Monday, 2 September, 2019 12:26, Petr Jakeš <[email protected]>
> 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
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
