The inner query is called a correlated subquery. The query in ( ... ) is
executed for each row of the outer query. Correlated means that a value from
the "outer query" is used to constrain (in a where condition) the rows used
when computing the scalar result of the subquery.
> select dexin, code,new, (select avg(new) from tb1 where dexin between dexin-3
> and dexin)as mavg from tb1;
You have to alias the "same table name" in the correlated subquery to
differentiate it from the table in the outer query. In your example you refer
to "dexin" three times. Which comes from the outer query" and which comes from
the "inner query"? Although completely unnecessary, it is more clear if you
use indentation to make clear what is happening:
select dexin,
code,
new,
(select avg(new)
from tb1 innerTable
where innerTable.dexin between outerTable.dexin-2 and
outerTable.dexin) as mavg
from tb1 outerTable;
effectively, the correlated subquery is a subroutine which is executed (called)
for each row retrieved in the outer query and calculates the average over the
set of rows in innerTable that match the values computed from the value of
dexin in the outerTable.
In procedural code it does:
For each tb1 (known as outerTable) row
Get the values of dexin, code, new
Set mavg to 0
Set n = 0
For each tb1 (known as innerTable) row
if innerTable.dexin >= outerTable.dexin-2 and innerTable.dexin <=
outerTable.dexin
n++
mavg += innerTable.new
endif
endfor
set mavg = mavg / n
return the row (dexin, code, new, mavg)
endfor
What mavg calculates is dependant on the definition of dexin. If dexin
contains a Julian Day Number, then it will compute the moving average over
three days. If it contains unix epoch dates, it will return the moving average
over three seconds. If it contains the rowid, it will compute the moving
average over three rows (whatever they represent). Performance will be crap
for large datasets. Though if you have an index on (dexin, new) it won't be
too bad ...
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
> -----Original Message-----
> From: [email protected] [mailto:sqlite-users-
> [email protected]] On Behalf Of YAN HONG YE
> Sent: Sunday, 12 August, 2012 19:31
> To: [email protected]
> Subject: Re: [sqlite] how to update the Moving average value
>
> select dexin, code,new, (select avg(new) from tb1 where dexin between dexin-3
> and dexin)as mavg from tb1;
>
> the result is only one value,
> and table only one: tb1
> I don't know why have tbl b and tbl a
>
>
>
>
>
> >From: "Keith Medcalf" <[email protected]>
> >To: "General Discussion of SQLite Database" <[email protected]>
> >Subject: Re: [sqlite] how to update the Moving average value
> >Message-ID: <[email protected]>
> >Content-Type: text/plain; charset="us-ascii"
> >
> >
> >select id, sales_vol, (select avg(sales_vol) as mavg
> > from tbl b
> > where b.id between a.id - 3 and a.id)
> > from tbl a;
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
