M. Fioretti <[EMAIL PROTECTED]> wrote: > A few examples of the kind of queries I'd > like to (learn how to) optimize first: > > - search of strings in text fields (both sub-words and whole words)
Are you familiar with SQLite full-text search (FTS) extension? http://www.sqlite.org/cvstrac/wiki?p=FtsUsage > - calculation of moving average of a floating field, eg if a table is > like this: > > MONTH SALES_TOTAL > 2007-01 500 > 2007-02 1059.3 > ... > 2008-06 439 > 2008-07 605.52 > > what is the fastest SQLITE3 query to read that table and return > another table with the average sales over the last 12 months: > > 2008-05 (total sales from 2007-06 to 2008-05)/12 > 2008-06 (total sales from 2007-07 to 2008-06)/12 > 2008-07 (total sales from 2007-08 to 2008-07)/12 Any solution in pure SQL is going to be awkward. Personally, I'd run a simple query like select MONTH, SALES_TOTAL from Sales order by MONTH and, as I iterate through the resultset, keep a queue and a running sum of the last 12 totals. If you insist, something like this should work (but not very fast): select s1.MONTH, avg(s2.SALES_TOTAL) from Sales s1 join Sales s2 on (s2.MONTH between strftime('%Y-%m', s1.MONTH || '-01', '-11 months') and s1.MONTH) group by s1.MONTH -- optional, if you don't want partial averages at the beginning -- HAVING count(*)=12 order by s1.MONTH; Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
