This is not a sample, but all the systolic blood pressure values of all of our patients. All this has to do with the calculation of the QRisk3 score: https://qrisk.org/three/
RBS On Sat, Oct 12, 2019 at 5:34 PM Richard Damon <rich...@damon-family.org> wrote: > One thing to point out, it sounds like you are dealing with a sample, > and I think you want to get the estimated standard deviation of the > process, which says you really want to use the adjusted formula that > uses N-1 in the denominator, as the expected value of the standard > deviation of a sample is smaller than the standard deviation of the > process/population. > > One quick test is to think what you would do if you had just a single > point. Does this mean you really have a Standard Deviation of Zero (as > that is the SD of a population of one) or does it mean you don't have an > idea of what the SD of the population is. > > On 10/12/19 12:13 PM, Bart Smissaert wrote: > > 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 > > > -- > Richard Damon > > _______________________________________________ > 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
