You are right. Id however is presumed to be related to what is being averaged over, not a rowid. One would presume that the simple integers are just "demo values" and that in actual fact it contains a Modified Julian Date. If it does not contain an "offset in days from some base value" then both the question and answer are incorrect. If it is arbitrary rowid's, then your version is no more correct (just more complicated).
Performance will not be too bad if you have a proper covering index for the correlated subquery, and you have a covering index allowing in-order retrieval of the outer table and the inner correlating variable. You will have to pay for the computation no matter how you do it (or when, since you could store it in the tbl row itself and use a trigger to update it whenever tbl.sales_vol was updated or a row inserted/deleted. Where and how you want to pay for the computation is an application design question. --- () 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 Yuriy Kaminskiy > Sent: Sunday, 12 August, 2012 20:49 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] how to update the Moving average value > > Keith Medcalf wrote: > > You are right Klaas, it should be -2 not -3. You could always constrain id > to (MAXINT >= id >= 3-MAXINT) if you wanted to be sure there would not be an > arithmetic overflow. > > 1) s/MAXINT/INT64_MAX/; > 2) it is rather inefficient; > 3) it will break on ID discontinuity; and attempt to fix it - something like > SELECT id, sales_vol, > (SELECT avg(c.sales_vol) > FROM (SELECT b.sales_vol > FROM tbl b > WHERE b.id <= a.id ORDER BY id DESC LIMIT 3) c) AS mavg > FROM tbl a ORDER BY id ASC LIMIT -1 OFFSET 2; > will be HORRIBLY inefficient; > > I think calculating moving average and similar things in SQL is only good as > exercise or on small dataset. > > >> -----Original Message----- > >> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > >> boun...@sqlite.org] On Behalf Of Klaas V > >> Sent: Sunday, 12 August, 2012 12:49 > >> To: SQLite MailList > >> Subject: Re: [sqlite] how to update the Moving average value > >> > >> Keith Metcalf wrote: > >> > >> > >>> id Sales_vol mov_avg > >>> 1 1 > >>> 2 2 > >>> 3 3 =(1+2+3)/3 > >>> 4 5 =(2+3+5)/3 > >>> 5 4 =(3+5+4)/3 > >>> 6 2 =(5+4+2)/3 > >>> 7............ > >>> 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; > >> - 3 should be - 2. You're working with the last 4 days and might get an > error > >> if id=3 > >> :P > > _______________________________________________ > 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
