Re: [sqlite] how to update the Moving average value
Earlier this year I was bothering this list with many questions about the sqlite virtual table support, because I needed (among other things) an efficient way to compute moving averages. And here is the result, free for anyone to play with: http://voksenlia.net/sqlite3/interpolate.c Rather than to average the last X values, it assumes that a table has a timestamp associated with the values that will be averaged, and what's going to be calculated is the average for a given period. The average of angles is also supported. It's a module which will take existing tables as input and create a virtual table with new columns for the moving average. It also allows you to look up any timestamp regardless of whether that timestamp exists in the underlying table(s). The returned value will be interpolated. It was written to be efficient on big datasets, but may become slow if used with virtual tables as input (or used recursively). My main use for this is to access weather data that I have stored. For instance, I have temperature (temp_out) stored somewhat irregularly at roughly 5 minute intervals, and I can now look up temp_out for any timestamp. Also, if I want the moving 24h temperature, I specify that in the virtual table declaration, and I can look up a new column temp_out__avg which will give me the moving average and it will work even if there are gaps in the data. More details are described in the comments of the C file. The module allowed me to make a web interface for my weather data: http://voksenlia.net/met/data/plot.php (Norwegian only) -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 Cordiali saluti/Vriendelijke groeten/Kind regards, Klaas V http://innocentisart.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to update the Moving average value
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. --- () 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 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 Cordiali saluti/Vriendelijke groeten/Kind regards, Klaas V http://innocentisart.net ___ 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
Re: [sqlite] how to update the Moving average value
On 12-08-2012 20:49, Klaas V wrote: Keith Metcalf wrote: id Sales_vol mov_avg 11 22 33 =(1+2+3)/3 45 =(2+3+5)/3 54 =(3+5+4)/3 62 =(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 Why 'might' there be an error? It perfectly valid to write: . where b.id between 0 and 3) or . where b.id between -1 and 2) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
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 11 22 33 =(1+2+3)/3 45 =(2+3+5)/3 54 =(3+5+4)/3 62 =(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
Re: [sqlite] how to update the Moving average value
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
Re: [sqlite] how to update the Moving average value
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 11 22 33 =(1+2+3)/3 45 =(2+3+5)/3 54 =(3+5+4)/3 62 =(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
[sqlite] how to update the Moving average value
I hace many record of product database,and wanna count the moving average value of sales_vol of last 3 days(id) of value, how to write sql command? 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 ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to update the Moving average value
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; --- () 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 ??? Sent: Saturday, 11 August, 2012 22:46 To: sqlite-users@sqlite.org Subject: [sqlite] how to update the Moving average value I hace many record of product database,and wanna count the moving average value of sales_vol of last 3 days(id) of value, how to write sql command? 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 ... ___ 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
