On Mon, Feb 13, 2012 at 11:51 AM, Steinar Midtskogen <stei...@latinitas.org> wrote: > [Scott Hess] >> 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. > > Yes, but that reduces sqlite to just a way to store data. It would be > nice to be able to use SQL and aggregate functions on the resulting > moving average (in particular max() and min()).
Well, SQLite isn't a data warehouse or analysis solution, for the most part it IS "just a way to store data." > Perhaps the moving average can be implemented as a virtual table? The virtual table could wrap another table, and manually include ORDER BY when querying for data from the backing table. The virtual table could provide a custom function, so it would look a lot like what you suggested, except with the part about having to create the virtual table in the first place. Given sufficient work, you could even potentially track the ordering of the outer query, and maybe expose the query optimization info from the underlying table. Probably a little involved for just this function, but if one was looking to implement an analysis tool which might have a bunch of other functions, it might make some sense. > One should think that the ability to calculate a moving average would > be a pretty common request. But people do it in their application > code instead? "lite", right? :-). But even being serious, I think this is a pretty low-volume use case. The current custom functions are pretty straight-forward, you can either convert a series of inputs into a series of outputs, or you can convert a series of inputs into a single output. In both cases the ordering of the inputs should not affect the final output(s), which makes things a lot cleaner to verify and test. This is consistent with SQL in general - in the abstract, results are not ordered, which allows the engine flexibility to optimize things, and ordering only required to happen at the point when you're reading out the results. [That last statement may not initially make sense. Think in terms of SQL as an expression of relational algebra. The only reason things even need to have an order is because of our primitive APIs.] -scott _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
