> select round(((select avg(close)
from test2 b
where b.tkid between a.tkid-12 and a.tkid) -
(select avg(close)
from test2 b
where b.tkid between a.tkid-26 and a.tkid)),2)
from test2 a
> could be select the result,but when update use this sql script:
> update test2
set diff=(select round(((select avg(close)
from test2 b
where b.tkid between a.tkid-12 and a.tkid) -
(select avg(close)
from test2 b
where b.tkid between a.tkid-26 and a.tkid)),2)
from test2 a)
should be
> update test2
set diff=round((select avg(close)
from test2 b
where b.tkid between test2.tkid-12 and test2.tkid) -
(select avg(close)
from test2 b
where b.tkid between test2.tkid-26 and test2.tkid),2)
if your select is correct.
Of course, your select simplifies to:
select -round((select avg(close)
from test2 b
where b.tkid between a.tkid-26 and a.tkid-13),2)
from test2 a
assuming that tkid is an integer.
Here is a more complicated example. Columns "Interval" are the UTC unix epoch
hour-ending (that is, the UTC hour ending timestamp/3600) and the integer
primary key of each table. Tables are populated with AESO pricing data. This
is my code, but the computed results are the same as those published by the
published of the source data: http://ets.aeso.ca/ There are a number of views
which generate all the various daily/monthly/yearly total/onpeak/offpeak
averages from the raw published data. This is just one example that computes
rolling averages (primarily to validate against the official published
numbers). Actual useful computed data is weighted averages or various types.
create view ActualHistory as
select esoYear as esoYear,
esoMonth as esoMonth,
esoDay as esoDay,
esoHour as esoHour,
Interval.Interval as Interval,
Forecast2.Price as Price2,
Forecast1.Price as Price1,
Forecast1.Demand as DemandF,
Forecast0.Price as Price0,
Forecast0.Demand as Demand0,
Actual.Price as Price,
Actual.Demand as Demand,
(select avg(Price)
from Actual
where Interval between Interval.Interval-23 and Interval.Interval)
as RAPrice1,
(select avg(Price, Demand)
from Actual
where Interval between Interval.Interval-23 and Interval.Interval)
as RWPrice1,
(select avg(Price)
from Actual
where Interval between Interval.Interval-24*30+1 and
Interval.Interval)
as RAPrice30,
(select avg(Price, Demand)
from Actual
where Interval between Interval.Interval-24*30+1 and
Interval.Interval)
as RWPrice30,
(select avg(Price)
from Actual
where Interval between Interval.Interval-24*91+1 and
Interval.Interval)
as RAPrice90,
(select avg(Price, Demand)
from Actual
where Interval between Interval.Interval-24*91+1 and
Interval.Interval)
as RWPrice90,
(select avg(Price)
from Actual
where Interval between Interval.Interval-24*365+1 and
Interval.Interval)
as RAPrice365,
(select avg(Price, Demand)
from Actual
where Interval between Interval.Interval-24*365+1 and
Interval.Interval)
as RWPrice365
from Interval natural join Actual
left outer join Forecast as Forecast2
on Forecast2.Interval=Interval.Interval
and Forecast2.Projected=Interval.Interval-2
left outer join Forecast as Forecast1
on Forecast1.Interval=Interval.Interval
and Forecast1.Projected=Interval.Interval-1
left outer join Forecast as Forecast0
on Forecast0.Interval=Interval.Interval
and Forecast0.Projected=Interval.Interval;
where avg(value, weight) is a custom aggregate computing (what would be
simplisticly defined as) sum(value*weight)/sum(weight) using a running
estimation of the weighted mean (running estimation avoids accumulation of
floating point errors for operations with widely disparate magnitudes -- not so
important here but very useful with other datasets).
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
