> I think it is possible to create a custom aggregate function which would > work on a cross-join of the data to analyse. The cross-join makes all the > data available to each bucket (group by Date, for example), and each bucket > is basically one row of the whole data. The aggregate function would thus > work on all the data, and output its result in every row of the result > set. The aggregate function would be in charge of dealing its own window > (similar to the "varianceStep" function that ignores null values).
You're going to find a big distinction in what SQLite calls 'aggregate > functions'. The built-in ones are here: > > <http://www.sqlite.org/lang_aggfunc.html> > > and you can write your own very sophisticated ones. No problem with that: > good luck and have fun. But there are some thing that can't be done within > the grammar: functions which depend on the order of retrieved results, like > some kinds of the 'moving_average()' function you mentioned in your > original post. This is because SQL tables have no inherent order: you can > get all the values you want but you don't really know what order they'll > show up in. For that, you need your programming language, whatever it is. > I've been reading on how these were implemented in sqlite. This is how I had the idea to use a cross-join. But after thinking about it in the metro back home, I decided this was the least efficient approach. Because sqlite first assigns a series of rows to a bucket and then runs the aggregate function on that bucket's rows, and since I intend to provide all rows to all buckets (which represent all rows), then I get an optimization of log(n^2) or worst depending on how I implement my own calculations. The only advantage to such a waste of cpu would be in being able to call it in an sql query. And this advantage is only viable if my intuition of this whole project is worth it. But thanks for reminding the importance of not relying on order! It's something I might have tried to force using twisted queries! Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
