On Mon, Feb 13, 2012 at 9:24 AM, Steinar Midtskogen <stei...@latinitas.org> wrote: > [Peter Aronson] >> (2) You can associate data with an argument to a regular user-defined >> function using sqlite3_set_auxdata() and sqlite3_get_auxdata() as long >> as the value of the argument is static. If you don't normally have a >> static argument to your function, you can add one (say a string >> MAVG'). I actually used this approach with some application generated >> SQL in my current project at one point. > > Thanks. I'm intending to write a function so I can do: > > SELECT unix_time, mavg(value, unix_time, <period>) FROM tab; > > assuming: > > CREATE TABLE tab (value REAL, unix_time INTEGER, PRIMARY KEY (unix_time)); > > So I assume that your second approach could work, since the third > argument to mavg() (the period, window size in seconds) is static, > e.g. mavg(value, unix_time, 86400) will give me the moving daily > average.
I think you're making unwarranted assumptions about the order your custom function will be called. Even if you added "ORDER BY" to the end of the query, that wouldn't necessarily order the calls to your custom function. Even if you find a workaround which allows you to implement something in the current version of SQLite, it wouldn't necessarily work in a future version. Unfortunately, I can't offhand think of a reasonable solution for you, I think I'd just use the SELECT to generate the data, while calculating the moving average in my application code. -scott _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
