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
