On 10/12/19 11:23 AM, Richard Damon wrote: > 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)) > > (Sorry about the messed up subject, reposting to fix it)
-- Richard Damon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
