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
