> Generally speaking, analytical functions (aka windowing functions [1]) > would appear to be the most useful for your endeavor. > > Sadly, SQLite doesn't provide anything like this out-of-the-box. >
I wasn't aware of the term. Thanks! I'll be able to google on that now! ;) And here are examples of custom functions, including stdev, variance, mode, > median, lower_quartile, upper_quartile, etc: > > http://www.sqlite.org/contrib/download/extension-functions.c?get=25 > This example file is seriously awesome! I'll practice a few things, thanks! 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). Although this seems a possible path, I don't feel it's a very appealing path. But I'll think about it and maybe I can come up with something. Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
