The Standard Deviation of the Population is the Square Root of the mean of the second order differences.
For your input values, you calculate the mean. Then you calculate the mean of the square of the difference between each value and the mean of the values. Then you take the square root of that. You can also compute it using a running calculation (generally more accurate on IEEE754 hardware). So just retrieve the values you are interested in and do the calculations in your application. That is, if you only want FOUR values, then write a query that returns only those FOUR values, and use those to computer the answer. While you can do all the calculations (including the Square Root) in SQL, it is likely to be horrendously slow. However, before you can do *ANYTHING* else, you need to be able to demonstrate that you can write a select statement that retrieves the values you want to work on, only the values you want to work on, and nothing but the values you want to work on. Having 47,000 values and sauing that you would like to find the standard deviation of some set of four of them is entertaining, but not useful in any way. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Bart Smissaert >Sent: Saturday, 12 October, 2019 08:09 >To: General Discussion of SQLite Database <sqlite- >us...@mailinglists.sqlite.org> >Subject: [sqlite] Standard deviation last x entries > >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 >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
