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
