Re: [sqlite] Standard deviation last x entries

This is getting far away from SQLite now and moving to medical statistics. The thing is that the authors of this algorithm have taken a large amount of patient data and have calculated what the best predictors are to calculate the chance for the individual patient to have a heart attack or stroke

Re: [sqlite] Standard deviation last x entries

In my experience max(x) - min(x) isn't a great measure for this sort of thing, as it is ultimately dependent on outliers. Something like 75%-tile to 25%-tile (or other similar values) might make more sense. Ultimately, taking a Standard Deviation (or Variance) of a set of reading for a set of

Re: [sqlite] Standard deviation last x entries

Yes, could use something else than SD, but the mentioned calculator uses SD. Got this all working fine now. RBS On Sun, 20 Oct 2019, 13:31 Gabor Grothendieck, wrote: > Another approach is to question whether you really need the > standard deviation or if any measure of variation would do. > >

Re: [sqlite] Standard deviation last x entries

Another approach is to question whether you really need the standard deviation or if any measure of variation would do. You could use the range, max(x) - min(x) , and just report it as the range rather than the standard deviation. Also range/4 approximates the standard deviation (google the

Re: [sqlite] Standard deviation last x entries

Thanks for that and have tried this now (on Android app) and works fine. It is fast as well, although slightly slower than the previous version. I ran this on a column with 8000 values ranging from 0 to 1600 and this took about 140 milli-seconds on a fast Samsung S9 phone. Database is on a SD. I

Re: [sqlite] Standard deviation last x entries

Here is a recursive CTE that will calculate the square root to the best precision your processor math implementation is capable of. It limits the recursion by prohibiting the insertion of duplicate guesses by using UNION rather than UNION ALL, which will cause pathological cases that

Re: [sqlite] Standard deviation last x entries

ber, 2019 16:48 >To: SQLite mailing list >Subject: Re: [sqlite] Standard deviation last x entries > >> However, to optimize the calculation the following is more efficient > >I tested it, but didn't find it any faster. >Naming makes it a lot clearer though. > >RBS

Re: [sqlite] Standard deviation last x entries

> floating point numbers (the limit of computational precision in the last > binary place) > > -- > 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-us

Re: [sqlite] Standard deviation last x entries

Thanks, I did see that, but can't use this on Android. RBS On Fri, Oct 18, 2019 at 11:41 PM Gabor Grothendieck wrote: > There is a stdev function for sqlite here: > https://www.sqlite.org/contrib//download/extension-functions.c?get=25 > > On Wed, Oct 16, 2019 at 7:57 PM Olaf Schmidt wrote: >

Re: [sqlite] Standard deviation last x entries

There is a stdev function for sqlite here: https://www.sqlite.org/contrib//download/extension-functions.c?get=25 On Wed, Oct 16, 2019 at 7:57 PM Olaf Schmidt wrote: > > Am 12.10.2019 um 16:47 schrieb Bart Smissaert: > > Sorry, I forgot to tell that. It is date column with an integer number. > >

Re: [sqlite] Standard deviation last x entries

ng point numbers (the limit of computational precision in the last > binary place) > > -- > 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 On > >B

Re: [sqlite] Standard deviation last x entries

nticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Bart Smissaert >Sent: Friday, 18 October, 2019 14:55 >To: SQLite mailing list >Subject: Re: [sqlite] Standard deviation last x entries > >Hi Olaf, > >Could you tell me what thi

Re: [sqlite] Standard deviation last x entries

Hi Olaf, Could you tell me what this is doing: ,(x>0)*(y+x/y)/2 yi Especially the yi after the 2 How does the yi relate to the preceding bit? RBS On Fri, Oct 18, 2019 at 8:26 PM Olaf Schmidt wrote: > Am 18.10.2019 um 19:45 schrieb Bart Smissaert: > > > Regarding: > > > > With r(s2, s, i)

Re: [sqlite] Standard deviation last x entries

Have tested this and CTE pure SQL method is a lot faster (about 4 x) than my previous method in code: strSQL = "SELECT ID, SD_BP FROM QR3PARAMS" RS1 = cConn.SQL1.ExecQuery(strSQL) strSQL = "UPDATE QR3PARAMS SET SD_BP = ? WHERE ID = ?" cConn.BeginTransaction Do While RS1.NextRow

Re: [sqlite] Standard deviation last x entries

Hi Olaf, OK, thanks, that works fine indeed. > Well, as with any other (single-value-returning) Sub-Select It is just that saw SQL's where the update part came after the CTE, eg:

Re: [sqlite] Standard deviation last x entries

Am 18.10.2019 um 19:45 schrieb Bart Smissaert: Regarding: With r(s2, s, i) As (Select 2, 1, 1 Union All Select s2, (s2>0)*(s+s2/s)*.5, i+1 From r Where Abs(s2-s*s)>1e-12 Limit 32 ) Select s From r Order By i Desc Limit 1 How would this work if I wanted to update all the values in a table

Re: [sqlite] Standard deviation last x entries

Hi Olaf, Regarding: With r(s2, s, i) As (Select 2, 1, 1 Union All Select s2, (s2>0)*(s+s2/s)*.5, i+1 From r Where Abs(s2-s*s)>1e-12 Limit 32 ) Select s From r Order By i Desc Limit 1 How would this work if I wanted to update all the values in a table column to have the square root? RBS On

Re: [sqlite] Standard deviation last x entries

Hi Olaf, Tested all on the Windows app and works perfectly fine indeed. Thanks for that. RBS On Thu, Oct 17, 2019 at 12:56 AM Olaf Schmidt wrote: > Am 12.10.2019 um 16:47 schrieb Bart Smissaert: > > Sorry, I forgot to tell that. It is date column with an integer number. > > > > ID xValue

Re: [sqlite] Standard deviation last x entries

Ignore the mentioned problem, must have been an e-mail artefact and view creates fine. Will test all later. RBS On Thu, Oct 17, 2019 at 11:02 AM Bart Smissaert wrote: > Hi Olaf, > > Thanks, will try that out. > I have a slight problem with your first create view on Android (fine in my >

Re: [sqlite] Standard deviation last x entries

Hi Olaf, Thanks, will try that out. I have a slight problem with your first create view on Android (fine in my Windows desktop app), objecting to: T.ID, highlighting the dot. I tried with giving an alias T2 to the table in the subselect, but that made no difference. Will try later. RBS On Thu,

Re: [sqlite] Standard deviation last x entries

Am 12.10.2019 um 16:47 schrieb Bart Smissaert: Sorry, I forgot to tell that. It is date column with an integer number. ID xValue xDate 1 130 40123 1 120 41232 1 140 40582 1 100 40888 1 110 42541 2 140 41225 2 130 41589 2 150

Re: [sqlite] Standard deviation last x entries

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-

Re: [sqlite] Standard deviation last x entries

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: sqli

Re: [sqlite] Standard deviation last x entries

On > >Behalf Of Bart Smissaert > >Sent: Saturday, 12 October, 2019 08:09 > >To: General Discussion of SQLite Database >us...@mailinglists.sqlite.org> > >Subject: [sqlite] Standard deviation last x entries > > > >How do I get the standard deviation of the

Re: [sqlite] Standard deviation last x entries

>From: sqlite-users On >Behalf Of Richard Damon >Sent: Saturday, 12 October, 2019 09:33 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Standard deviation last x entries > >On 10/12/19 11:23 AM, Richard Damon wrote: >> On 10/12/19 10:08 AM, Bart Smissae

Re: [sqlite] Standard deviation last x entries

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

Re: [sqlite] Standard deviation last x entries

>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 xVa

Re: [sqlite] Standard deviation last x entries

Sorry, I forgot to tell that. It is date column with an integer number. ID xValue xDate 1 130 40123 1 120 41232 1 140 40582 1 100 40888 1 110 42541 2 140 41225 2 130 41589 2 150 40872 RBS On Sat, Oct 12, 2019 at 3:18 PM

Re: [sqlite] Standard deviation last x entries

On 10/12/2019 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. What do you mean by "last 4 entries"? What determines the order? How

[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

Re: [sqlite] STandard Deviation

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/18/2010 06:37 PM, Peter Haworth wrote: > Does Sqlite have a STDEV function? Don;t see it listed under the core > or aggregate functions. You can find that and many other functions in a contributed extension. See the bottom of

Re: [sqlite] STandard Deviation

Not in sqlite itself but stddev_samp and stddev_pop are available in the spatialite loadable extension. Be careful since they interchanged sample and population in one version of the extension. On Sun, Apr 18, 2010 at 9:37 PM, Peter Haworth wrote: > Does Sqlite have a

[sqlite] STandard Deviation

Does Sqlite have a STDEV function? Don;t see it listed under the core or aggregate functions. Thanks, Pete Haworth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users