On 27 Mar 2012, at 9:47pm, Simon <turne...@gmail.com> wrote: > But I don't think aggregates is the key here... Basically, the kind of > function I need is something like this: > For each row, in this column, calculate the foobar result on all (or a > group of) the values of another column. > I think this is the definition of windowing (I just read a few lines on the > topic at the moment). > > 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. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
