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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of YAN HONG YE > Sent: Sunday, 12 August, 2012 19:31 > To: sqlite-users@sqlite.org > 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" <kmedc...@dessus.com> > >To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > >Subject: Re: [sqlite] how to update the Moving average value > >Message-ID: <545265ad8b7cfb45b4f7f9160b8cc...@mail.dessus.com> > >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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users