Thanks, I do know how to calculate the SD in code, but I thought in this particular case it might be faster to do this in SQL. Only problem is the square root and for that reason I will test this in code as well and see how it compares with SQL.
I found a way to get the one from last step, so that is without doing the final square root step. Note that this has to do with a number of systolic blood pressure readings and I am actually taking the last 6 and that is useful as due to treatment the SD often will be less for the later values: UPDATE QR3PARAMS SET AVG_BP = (SELECT AVG(SYST) FROM (SELECT SYSTOLIC AS SYST FROM BP WHERE ID = QR3PARAMS.ID ORDER BY ENTRY_DATE DESC LIMIT 6)) UPDATE QR3PARAMS SET DEV_BP = (SELECT AVG(SYST * SYST) FROM (SELECT SYSTOLIC - QR3PARAMS.AVG_BP AS SYST FROM BP WHERE ID = QR3PARAMS.ID ORDER BY ENTRY_DATE DESC LIMIT 6)) RBS On Sat, Oct 12, 2019 at 4:21 PM Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
