On 10/12/19 10:08 AM, Bart Smissaert wrote: > How do I get the standard deviation of the last 4 entries (there could be > less than 4) of an integer column grouped by an integer ID entry in another > column in the same table. > > So data could be like this: > > ID xValue > ---------------- > 1 130 > 1 120 > 1 140 > 1 100 > 1 110 > 2 140 > 2 130 > 2 150 > > I know that SQLite doesn't have the SQRT function (and I can't make a UDF > as I am doing this in an Android phone app) but I can do this last step in > code. > > RBS
I may not be the best at forming the SQL to do this, but here is the general idea I would use. First use a query with ORDER BY DECR and LIMIT to get the last 4 items, and select the value, and a computer column of value2 = value * value. Then over this result, do a GROUP BY to compute N = count(value), S = sum(value), S2 =sum(value2) You can then compute the variance (standard deviation squared) from the equation Variance = S2 / N - (S / N) * (S / N) This equation assumes that you are working with the full population and not just a sample, if you data is to be considered a sample you need to make a small adjustment in the formula to Estimated Variance of Popultion = S2 / (N-1) - S * S / (N * (N-1)) -- Richard Damon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
