Re: [sqlite] Schema updates across threads in WAL & multithread mode
Hi D. Richard Hipp. Sorry for the late reply. I had some email settings wrong somewhere, and I didn't realize I had a reply until I remembered this and checked the archives months later. You had written: >> >> Is there something we need to do proactively to ensure that schema update >> appears immediately from other threads? >> > >When a database connection has a read transaction open, it continues >to see a snapshot of the database as it existed when the read >transaction was first opened. Outside changes to the database, >including schema changes, are invisible to the connection holding the >transaction. This is the "I" in "ACID". > >As soon as you release the read transaction and start another, all >changes will immediately become visible. > >If you are not deliberately holding a read transaction open, perhaps >you are doing so accidentally by failing to sqlite3_reset() or >sqlite3_finalize() a prepared statement. You can perhaps figure out >which statement that is by running: > > SELECT sql FROM sqlite_stmt WHERE busy; That is very interesting. We definitely don't expect a read transaction to be open at that point. This is super helpful. I'll check on this next week. Thank you! Ben On Fri, Aug 16, 2019 at 12:49 PM Ben Asher wrote: > To clarify, we add a column on our writer connection, and then "SELECT * > FROM table" on the reader connection does not include the column that was > added. > > Ben > > On Fri, Aug 16, 2019 at 11:32 AM Ben Asher wrote: > >> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a >> schema update (adding a column to a table) on our writer connection, but >> then the schema update isn't immediately available on the read-only >> connections that we use on other threads, which causes a crash in our >> application (app expects the column to exist at that point). I've verified >> that the column does indeed get added, and everything works fine after >> restarting the application (i.e. all connections loaded fresh pickup the >> schema update). >> >> Is there something we need to do proactively to ensure that schema update >> appears immediately from other threads? >> >> Some notes about our setup: >> >> sqlite 3.27.2 >> Using multithread mode (SQLITE_OPEN_NOMUTEX) >> Using WAL mode >> >> Thanks! >> >> Ben >> > > > -- > Ben > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema updates across threads in WAL & multithread mode
Hi Simon. Sorry for the late reply. I had some email settings wrong somewhere, and I didn't realize I had a reply until I remembered this and checked the archives months later. You had written: >Can I ask the maximum number of columns you expect to exist in that table ? I'm working up to trying to convince you to add a row to something instead, but I want to make sure you're doing what I think you're doing. > >Other people may be able to answer your question. It's a small number of columns– less than 10. The table already has data, and we added the column with a default value. Thanks! Ben On Fri, Aug 16, 2019 at 12:49 PM Ben Asher wrote: > To clarify, we add a column on our writer connection, and then "SELECT * > FROM table" on the reader connection does not include the column that was > added. > > Ben > > On Fri, Aug 16, 2019 at 11:32 AM Ben Asher wrote: > >> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a >> schema update (adding a column to a table) on our writer connection, but >> then the schema update isn't immediately available on the read-only >> connections that we use on other threads, which causes a crash in our >> application (app expects the column to exist at that point). I've verified >> that the column does indeed get added, and everything works fine after >> restarting the application (i.e. all connections loaded fresh pickup the >> schema update). >> >> Is there something we need to do proactively to ensure that schema update >> appears immediately from other threads? >> >> Some notes about our setup: >> >> sqlite 3.27.2 >> Using multithread mode (SQLITE_OPEN_NOMUTEX) >> Using WAL mode >> >> Thanks! >> >> Ben >> > > > -- > Ben > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema updates across threads in WAL & multithread mode
Hi José. Sorry for the late reply. I had some email settings wrong somewhere, and I didn't realize I had a reply until I remembered this and checked the archives months later. You had asked: > Are you using BEGIN and END before and after the schema update? Yes that's correct. We are doing the schema updates inside of an explicit transaction. Thanks! Ben On Fri, Aug 16, 2019 at 12:49 PM Ben Asher wrote: > To clarify, we add a column on our writer connection, and then "SELECT * > FROM table" on the reader connection does not include the column that was > added. > > Ben > > On Fri, Aug 16, 2019 at 11:32 AM Ben Asher wrote: > >> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a >> schema update (adding a column to a table) on our writer connection, but >> then the schema update isn't immediately available on the read-only >> connections that we use on other threads, which causes a crash in our >> application (app expects the column to exist at that point). I've verified >> that the column does indeed get added, and everything works fine after >> restarting the application (i.e. all connections loaded fresh pickup the >> schema update). >> >> Is there something we need to do proactively to ensure that schema update >> appears immediately from other threads? >> >> Some notes about our setup: >> >> sqlite 3.27.2 >> Using multithread mode (SQLITE_OPEN_NOMUTEX) >> Using WAL mode >> >> Thanks! >> >> Ben >> > > > -- > Ben > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count Rows Inserted into FTS5 Index w/ External Content Table
I should have included this in the first place. Here are the queries used
to setup this table and an example:
CREATE TABLE text (text TEXT NONNULL);
CREATE VIRTUAL TABLE text_fts_index USING fts5(text, content=text,
content_rowid=rowid, prefix='2 3 4 5 6 7 8 9 10', tokenize='unicode61');
INSERT INTO text VALUES ('hello');
-- I was hoping the following would return 0, but it returns 1, presumably
because it's getting the answer from the external content table
SELECT COUNT(content_rowid) FROM text_fts_index;
Thanks again!
Ben
On Fri, Oct 18, 2019 at 4:31 PM Ben Asher wrote:
> Hello! I'm trying to write some code to keep an external content table in
> sync with the index. To do this, I need to be able to get some state about
> the index: either how many rows have been inserted so far or the max rowid
> that has been inserted into the index. However, if I try to run queries
> (where "fts" is the name of the FTS5 virtual table) like "SELECT COUNT(*)
> FROM fts" or "SELECT MAX(rowid) FROM fts", the result always ends up being
> an answer as if I had run those queries on the external content table. Is
> there some other way I can query the state in this case?
>
> Thanks!
>
> Ben
>
--
Ben
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Count Rows Inserted into FTS5 Index w/ External Content Table
Hello! I'm trying to write some code to keep an external content table in sync with the index. To do this, I need to be able to get some state about the index: either how many rows have been inserted so far or the max rowid that has been inserted into the index. However, if I try to run queries (where "fts" is the name of the FTS5 virtual table) like "SELECT COUNT(*) FROM fts" or "SELECT MAX(rowid) FROM fts", the result always ends up being an answer as if I had run those queries on the external content table. Is there some other way I can query the state in this case? Thanks! Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Standard deviation last x entries
Note that you might need to change the 2.22044604925031e-16 constant to 2 ULPs (4.44089209850063e-16) in order to get around some pathological rounding cases. It probably will not be noticeable faster since you are only saving a few machine cycles per iteration. It might be noticeable unless you are running the calculation thousands or millions of times. -- 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 >Behalf Of Bart Smissaert >Sent: Friday, 18 October, 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 > >On Fri, Oct 18, 2019 at 10:56 PM Keith Medcalf >wrote: > >> >> We are calculating the square root using Newtons Method of successive >> approximation >> >> https://pages.mtu.edu/~shene/COURSES/cs201/NOTES/chap04/sqrt.html >> >> (x>0)*(y+x/y)/2 yi >> >> is computing the new guess of the square root of x based on the last >guess >> y and calling that calculation yi (the AS keyword is omitted). The >(x>0) >> part is to make sure that x is always a positive number otherwise the >new >> guess will be 0. This means that the attempt to find the square root >of a >> negative number (or 0) will always return 0 for each of two iterations >thus >> meeting the stop condition because abs(0 - 0) is less than any positive >> number. >> >> To use a CTE with better named variables one would use: >> >> with guesses(findRootOf, guessRoot, iteration) >> as ( >> select ?, 1.0, 1 >> union all >> select findRootOf, (findRootOf > 0) * (guessRoot + findRootOf >/ >> guessRoot) / 2.0 as newGuess, iteration + 1 >>from guesses >> where abs(newGuess - guessRoot) > 1e-12 >> limit 32 >> ) >> select guessRoot >> from guesses >> order by iteration desc >>limit 1; >> >> However, to optimize the calculation the following is more efficient >> (since it only calculates the new guess once each iteration, and >computes >> to the limit of precision). We are substituting calculating the guess >> twice and its difference from a constant (9 operations) with computing >the >> actual precision (6 operations): >> >> with guesses(findRootOf, guessRoot, iteration) >> as ( >> select ?, 1.0, 1 >> union all >> select findRootOf, (findRootOf > 0) * (guessRoot + findRootOf >/ >> guessRoot) / 2.0, iteration + 1 >>from guesses >> where abs(guessRoot*guessRoot - findRootOf) > >> (2.22044604925031e-16 * FindRootOf) >> limit 32 >> ) >> select guessRoot >> from guesses >> order by iteration desc >>limit 1; >> >> where 2.22044604925031e-16 is the epsilon of IEEE754 double precision >> 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-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 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) 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? >> >> >> >> Well, as with any other (single-value-returning) Sub-Select >> >> (which was enclosed in parentheses)... >> >> >> >> E.g. >> >> "Update MyTable Set MyCol = (Select 1)" >> >> would update MyCol with the value 1 across the whole table... >> >> >> >> Same thing basically (only "a bit larger") with a CTE-based >> >> "Single-Value-SubSelect"... >> >> >> >> BTW, I've updated and tuned the thing a bit (which should now >> >> offer more precision, and ~20% more performance as well): >> >> >> >> With r (x, y, i) As ( >> >>Select ?, 1, 1 Union All >> >>Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 >Limit >> >32 >> >> ) Select y From r Order By i Desc Limit 1 >> >> >> >> Note the question-marked "Parameter" for the "squared Input-Value" >> >> (in the first Select Statement of the CTEs triple). >> >> >> >> Integrated into an Update-Query it could look this
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 On Fri, Oct 18, 2019 at 10:56 PM Keith Medcalf wrote: > > We are calculating the square root using Newtons Method of successive > approximation > > https://pages.mtu.edu/~shene/COURSES/cs201/NOTES/chap04/sqrt.html > > (x>0)*(y+x/y)/2 yi > > is computing the new guess of the square root of x based on the last guess > y and calling that calculation yi (the AS keyword is omitted). The (x>0) > part is to make sure that x is always a positive number otherwise the new > guess will be 0. This means that the attempt to find the square root of a > negative number (or 0) will always return 0 for each of two iterations thus > meeting the stop condition because abs(0 - 0) is less than any positive > number. > > To use a CTE with better named variables one would use: > > with guesses(findRootOf, guessRoot, iteration) > as ( > select ?, 1.0, 1 > union all > select findRootOf, (findRootOf > 0) * (guessRoot + findRootOf / > guessRoot) / 2.0 as newGuess, iteration + 1 >from guesses > where abs(newGuess - guessRoot) > 1e-12 > limit 32 > ) > select guessRoot > from guesses > order by iteration desc >limit 1; > > However, to optimize the calculation the following is more efficient > (since it only calculates the new guess once each iteration, and computes > to the limit of precision). We are substituting calculating the guess > twice and its difference from a constant (9 operations) with computing the > actual precision (6 operations): > > with guesses(findRootOf, guessRoot, iteration) > as ( > select ?, 1.0, 1 > union all > select findRootOf, (findRootOf > 0) * (guessRoot + findRootOf / > guessRoot) / 2.0, iteration + 1 >from guesses > where abs(guessRoot*guessRoot - findRootOf) > > (2.22044604925031e-16 * FindRootOf) > limit 32 > ) > select guessRoot > from guesses > order by iteration desc >limit 1; > > where 2.22044604925031e-16 is the epsilon of IEEE754 double precision > 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-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 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) 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? > >> > >> Well, as with any other (single-value-returning) Sub-Select > >> (which was enclosed in parentheses)... > >> > >> E.g. > >> "Update MyTable Set MyCol = (Select 1)" > >> would update MyCol with the value 1 across the whole table... > >> > >> Same thing basically (only "a bit larger") with a CTE-based > >> "Single-Value-SubSelect"... > >> > >> BTW, I've updated and tuned the thing a bit (which should now > >> offer more precision, and ~20% more performance as well): > >> > >> With r (x, y, i) As ( > >>Select ?, 1, 1 Union All > >>Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit > >32 > >> ) Select y From r Order By i Desc Limit 1 > >> > >> Note the question-marked "Parameter" for the "squared Input-Value" > >> (in the first Select Statement of the CTEs triple). > >> > >> Integrated into an Update-Query it could look this way: > >> > >> Update MyTable Set MySquareRoot = ( -- SubSelect-Opening-Paren... > >> With r (x, y, i) As ( > >> Select MySquaredValue, 1, 1 Union All > >> Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit > >32 > >> ) Select y From r Order By i Desc Limit 1 > >> ) -- SubSelect-Closing-Paren... > >> > >> HTH > >> > >> Olaf > >> > >> ___ > >> 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
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: > > > > 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 40872 > > > > Because SQLite on Android has at least CTEs available, > > (since Android 5 or so), I thought I make an attempt using them... > > > > For your above sample-set I've created a table Test this way > > (with slightly changed Column-Names + different IDs with "gaps" > > + a few more sample-records, to cover especially n=1 sets): > > > > Create Table Test(ID, xVal, xDat); > > > > Insert Into Test Values(3, 130, 40123); > > Insert Into Test Values(3, 120, 41232); > > Insert Into Test Values(3, 140, 40582); > > Insert Into Test Values(3, 100, 40888); > > Insert Into Test Values(3, 110, 42541); > > > > Insert Into Test Values(5, 140, 41225); > > Insert Into Test Values(5, 130, 41589); > > Insert Into Test Values(5, 150, 40872); > > > > Insert Into Test Values(6, 110, 41225); > > Insert Into Test Values(6, 115, 41227); > > > > Insert Into Test Values(9, 105, 41225); > > > > After the above Table-Creation + Inserts, one should > > define a "Base-View" first, which is then able to select > > appropriate aggregates (respecting your "max-4" condition): > > > > Create View vw_StdDev_Max4_Aggregates As > > Select ID, Avg(xVal) a1, Avg(xVal*xVal) a2, Count(*) n From Test T > > Where xDat In (Select xDat From Test Where T.ID=ID Order By xDat Desc > > Limit 4) > > Group By ID; > > > > Only this view above has to be adjusted, according to your > > real table- and column-names, but the following two "derived views", > > will work after these changes as well, as long as you leave the > > above view-name - and the field-names: (ID, a1, a2, n) intact. > > > > So what remains, is two more view-defs (one for the population-StdDev - > > and one with a few slight changes, to cover the sample-StdDev as well. > > > > Create View vw_StdDev_Samples As > > Select ID, n n_SampleSize, (a2-a1*a1)*n/(n-1) "s^2", ( > >With r(s2, s, i) As (Select (a2-a1*a1)*n/(n-1), 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 > > ) s From vw_StdDev_Max4_Aggregates Where n>1 > > Union All > > Select ID, n, Null, Null From vw_StdDev_Max4_Aggregates Where n=1; > > > > Create View vw_StdDev_Populations As > > Select ID, n n_PopulationSize, (a2-a1*a1) "sigma^2", ( > >With r(s2, s, i) As (Select (a2-a1*a1), 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 > > ) sigma From vw_StdDev_Max4_Aggregates Where n>1 > > Union All > > Select ID, n, 0, 0 From vw_StdDev_Max4_Aggregates Where n=1; > > > > > > Ok, here the result I get on my test-table, when the 3 views > > are in place, and the following gets executed: > > > > Select ID, n_PopulationSize, sigma From vw_StdDev_Populations; > > ID n sigma > > > > 3 4 14.7901994577491 > > 5 3 8.16496580927733 > > 6 2 2.5 > > 9 1 0 > > > > > > Select ID, n_SampleSize, s From vw_StdDev_Samples; > > ID n s > > > > 3 4 17.0782512765993 > > 5 3 10.1 > > 6 2 3.53553390593274 > > 9 1 null > > > > Please note the returned null (vs. the real 0) in the last result > > (done, because "unbiased estimates" are undefined for sample-size == 1) > > > > Finally for those interested, the CTE-based "iterated Square-Root", > > cut out for an isolated calculation of sqrt(2). > > > > 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 > > > > The max. iterations are limited to 32 - though for input-values > > in a "typical range", the iteration will usually exit earlier... > > In case of the example above -> sqrt(2), the iteration-counter > > i reached only 6 (as can be seen, when you include i alongside s > > in the final Select. > > > > 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 i, s From r Order By i Desc Limit 1 > > > > HTH > > > > Olaf > > > > > > > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > >
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. > > > > 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 > > Because SQLite on Android has at least CTEs available, > (since Android 5 or so), I thought I make an attempt using them... > > For your above sample-set I've created a table Test this way > (with slightly changed Column-Names + different IDs with "gaps" > + a few more sample-records, to cover especially n=1 sets): > > Create Table Test(ID, xVal, xDat); > > Insert Into Test Values(3, 130, 40123); > Insert Into Test Values(3, 120, 41232); > Insert Into Test Values(3, 140, 40582); > Insert Into Test Values(3, 100, 40888); > Insert Into Test Values(3, 110, 42541); > > Insert Into Test Values(5, 140, 41225); > Insert Into Test Values(5, 130, 41589); > Insert Into Test Values(5, 150, 40872); > > Insert Into Test Values(6, 110, 41225); > Insert Into Test Values(6, 115, 41227); > > Insert Into Test Values(9, 105, 41225); > > After the above Table-Creation + Inserts, one should > define a "Base-View" first, which is then able to select > appropriate aggregates (respecting your "max-4" condition): > > Create View vw_StdDev_Max4_Aggregates As > Select ID, Avg(xVal) a1, Avg(xVal*xVal) a2, Count(*) n From Test T > Where xDat In (Select xDat From Test Where T.ID=ID Order By xDat Desc > Limit 4) > Group By ID; > > Only this view above has to be adjusted, according to your > real table- and column-names, but the following two "derived views", > will work after these changes as well, as long as you leave the > above view-name - and the field-names: (ID, a1, a2, n) intact. > > So what remains, is two more view-defs (one for the population-StdDev - > and one with a few slight changes, to cover the sample-StdDev as well. > > Create View vw_StdDev_Samples As > Select ID, n n_SampleSize, (a2-a1*a1)*n/(n-1) "s^2", ( >With r(s2, s, i) As (Select (a2-a1*a1)*n/(n-1), 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 > ) s From vw_StdDev_Max4_Aggregates Where n>1 > Union All > Select ID, n, Null, Null From vw_StdDev_Max4_Aggregates Where n=1; > > Create View vw_StdDev_Populations As > Select ID, n n_PopulationSize, (a2-a1*a1) "sigma^2", ( >With r(s2, s, i) As (Select (a2-a1*a1), 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 > ) sigma From vw_StdDev_Max4_Aggregates Where n>1 > Union All > Select ID, n, 0, 0 From vw_StdDev_Max4_Aggregates Where n=1; > > > Ok, here the result I get on my test-table, when the 3 views > are in place, and the following gets executed: > > Select ID, n_PopulationSize, sigma From vw_StdDev_Populations; > ID n sigma > > 3 4 14.7901994577491 > 5 3 8.16496580927733 > 6 2 2.5 > 9 1 0 > > > Select ID, n_SampleSize, s From vw_StdDev_Samples; > ID n s > > 3 4 17.0782512765993 > 5 3 10.1 > 6 2 3.53553390593274 > 9 1 null > > Please note the returned null (vs. the real 0) in the last result > (done, because "unbiased estimates" are undefined for sample-size == 1) > > Finally for those interested, the CTE-based "iterated Square-Root", > cut out for an isolated calculation of sqrt(2). > > 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 > > The max. iterations are limited to 32 - though for input-values > in a "typical range", the iteration will usually exit earlier... > In case of the example above -> sqrt(2), the iteration-counter > i reached only 6 (as can be seen, when you include i alongside s > in the final Select. > > 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 i, s From r Order By i Desc Limit 1 > > HTH > > Olaf > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Standard deviation last x entries
> and calling that calculation yi (the AS keyword is omitted). Ah, indeed, simple! Thanks for that explanation. Will study this and try it out. RBS On Fri, Oct 18, 2019 at 10:56 PM Keith Medcalf wrote: > > We are calculating the square root using Newtons Method of successive > approximation > > https://pages.mtu.edu/~shene/COURSES/cs201/NOTES/chap04/sqrt.html > > (x>0)*(y+x/y)/2 yi > > is computing the new guess of the square root of x based on the last guess > y and calling that calculation yi (the AS keyword is omitted). The (x>0) > part is to make sure that x is always a positive number otherwise the new > guess will be 0. This means that the attempt to find the square root of a > negative number (or 0) will always return 0 for each of two iterations thus > meeting the stop condition because abs(0 - 0) is less than any positive > number. > > To use a CTE with better named variables one would use: > > with guesses(findRootOf, guessRoot, iteration) > as ( > select ?, 1.0, 1 > union all > select findRootOf, (findRootOf > 0) * (guessRoot + findRootOf / > guessRoot) / 2.0 as newGuess, iteration + 1 >from guesses > where abs(newGuess - guessRoot) > 1e-12 > limit 32 > ) > select guessRoot > from guesses > order by iteration desc >limit 1; > > However, to optimize the calculation the following is more efficient > (since it only calculates the new guess once each iteration, and computes > to the limit of precision). We are substituting calculating the guess > twice and its difference from a constant (9 operations) with computing the > actual precision (6 operations): > > with guesses(findRootOf, guessRoot, iteration) > as ( > select ?, 1.0, 1 > union all > select findRootOf, (findRootOf > 0) * (guessRoot + findRootOf / > guessRoot) / 2.0, iteration + 1 >from guesses > where abs(guessRoot*guessRoot - findRootOf) > > (2.22044604925031e-16 * FindRootOf) > limit 32 > ) > select guessRoot > from guesses > order by iteration desc >limit 1; > > where 2.22044604925031e-16 is the epsilon of IEEE754 double precision > 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-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 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) 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? > >> > >> Well, as with any other (single-value-returning) Sub-Select > >> (which was enclosed in parentheses)... > >> > >> E.g. > >> "Update MyTable Set MyCol = (Select 1)" > >> would update MyCol with the value 1 across the whole table... > >> > >> Same thing basically (only "a bit larger") with a CTE-based > >> "Single-Value-SubSelect"... > >> > >> BTW, I've updated and tuned the thing a bit (which should now > >> offer more precision, and ~20% more performance as well): > >> > >> With r (x, y, i) As ( > >>Select ?, 1, 1 Union All > >>Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit > >32 > >> ) Select y From r Order By i Desc Limit 1 > >> > >> Note the question-marked "Parameter" for the "squared Input-Value" > >> (in the first Select Statement of the CTEs triple). > >> > >> Integrated into an Update-Query it could look this way: > >> > >> Update MyTable Set MySquareRoot = ( -- SubSelect-Opening-Paren... > >> With r (x, y, i) As ( > >> Select MySquaredValue, 1, 1 Union All > >> Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit > >32 > >> ) Select y From r Order By i Desc Limit 1 > >> ) -- SubSelect-Closing-Paren... > >> > >> HTH > >> > >> Olaf > >> > >> ___ > >> 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 >
Re: [sqlite] Standard deviation last x entries
We are calculating the square root using Newtons Method of successive approximation https://pages.mtu.edu/~shene/COURSES/cs201/NOTES/chap04/sqrt.html (x>0)*(y+x/y)/2 yi is computing the new guess of the square root of x based on the last guess y and calling that calculation yi (the AS keyword is omitted). The (x>0) part is to make sure that x is always a positive number otherwise the new guess will be 0. This means that the attempt to find the square root of a negative number (or 0) will always return 0 for each of two iterations thus meeting the stop condition because abs(0 - 0) is less than any positive number. To use a CTE with better named variables one would use: with guesses(findRootOf, guessRoot, iteration) as ( select ?, 1.0, 1 union all select findRootOf, (findRootOf > 0) * (guessRoot + findRootOf / guessRoot) / 2.0 as newGuess, iteration + 1 from guesses where abs(newGuess - guessRoot) > 1e-12 limit 32 ) select guessRoot from guesses order by iteration desc limit 1; However, to optimize the calculation the following is more efficient (since it only calculates the new guess once each iteration, and computes to the limit of precision). We are substituting calculating the guess twice and its difference from a constant (9 operations) with computing the actual precision (6 operations): with guesses(findRootOf, guessRoot, iteration) as ( select ?, 1.0, 1 union all select findRootOf, (findRootOf > 0) * (guessRoot + findRootOf / guessRoot) / 2.0, iteration + 1 from guesses where abs(guessRoot*guessRoot - findRootOf) > (2.22044604925031e-16 * FindRootOf) limit 32 ) select guessRoot from guesses order by iteration desc limit 1; where 2.22044604925031e-16 is the epsilon of IEEE754 double precision 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-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 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) 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? >> >> Well, as with any other (single-value-returning) Sub-Select >> (which was enclosed in parentheses)... >> >> E.g. >> "Update MyTable Set MyCol = (Select 1)" >> would update MyCol with the value 1 across the whole table... >> >> Same thing basically (only "a bit larger") with a CTE-based >> "Single-Value-SubSelect"... >> >> BTW, I've updated and tuned the thing a bit (which should now >> offer more precision, and ~20% more performance as well): >> >> With r (x, y, i) As ( >>Select ?, 1, 1 Union All >>Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit >32 >> ) Select y From r Order By i Desc Limit 1 >> >> Note the question-marked "Parameter" for the "squared Input-Value" >> (in the first Select Statement of the CTEs triple). >> >> Integrated into an Update-Query it could look this way: >> >> Update MyTable Set MySquareRoot = ( -- SubSelect-Opening-Paren... >> With r (x, y, i) As ( >> Select MySquaredValue, 1, 1 Union All >> Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit >32 >> ) Select y From r Order By i Desc Limit 1 >> ) -- SubSelect-Closing-Paren... >> >> HTH >> >> Olaf >> >> ___ >> 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
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) 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? > > Well, as with any other (single-value-returning) Sub-Select > (which was enclosed in parentheses)... > > E.g. > "Update MyTable Set MyCol = (Select 1)" > would update MyCol with the value 1 across the whole table... > > Same thing basically (only "a bit larger") with a CTE-based > "Single-Value-SubSelect"... > > BTW, I've updated and tuned the thing a bit (which should now > offer more precision, and ~20% more performance as well): > > With r (x, y, i) As ( >Select ?, 1, 1 Union All >Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit 32 > ) Select y From r Order By i Desc Limit 1 > > Note the question-marked "Parameter" for the "squared Input-Value" > (in the first Select Statement of the CTEs triple). > > Integrated into an Update-Query it could look this way: > > Update MyTable Set MySquareRoot = ( -- SubSelect-Opening-Paren... > With r (x, y, i) As ( > Select MySquaredValue, 1, 1 Union All > Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit 32 > ) Select y From r Order By i Desc Limit 1 > ) -- SubSelect-Closing-Paren... > > HTH > > Olaf > > ___ > 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
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 cConn.SQL1.ExecNonQuery2(strSQL, Array As String(Sqrt(RS1.GetDouble2(1)), RS1.GetInt2(0))) Loop cConn.EndTransaction This is B4A code on Android with the SQLCipher driver. 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) 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? > > Well, as with any other (single-value-returning) Sub-Select > (which was enclosed in parentheses)... > > E.g. > "Update MyTable Set MyCol = (Select 1)" > would update MyCol with the value 1 across the whole table... > > Same thing basically (only "a bit larger") with a CTE-based > "Single-Value-SubSelect"... > > BTW, I've updated and tuned the thing a bit (which should now > offer more precision, and ~20% more performance as well): > > With r (x, y, i) As ( >Select ?, 1, 1 Union All >Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit 32 > ) Select y From r Order By i Desc Limit 1 > > Note the question-marked "Parameter" for the "squared Input-Value" > (in the first Select Statement of the CTEs triple). > > Integrated into an Update-Query it could look this way: > > Update MyTable Set MySquareRoot = ( -- SubSelect-Opening-Paren... > With r (x, y, i) As ( > Select MySquaredValue, 1, 1 Union All > Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit 32 > ) Select y From r Order By i Desc Limit 1 > ) -- SubSelect-Closing-Paren... > > HTH > > Olaf > > ___ > 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
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: https://stackoverflow.com/questions/27637278/how-to-remove-duplicate-rows-with-cte-when-partitioning-by-another-tables-colum WITH cte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY Created) AS Rnk FROM @Item AS i JOIN @ItemType AS it ON i.ItemTypeId = it.Id ) DELETE FROM cte WHERE Rnk > 1; 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) 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? > > Well, as with any other (single-value-returning) Sub-Select > (which was enclosed in parentheses)... > > E.g. > "Update MyTable Set MyCol = (Select 1)" > would update MyCol with the value 1 across the whole table... > > Same thing basically (only "a bit larger") with a CTE-based > "Single-Value-SubSelect"... > > BTW, I've updated and tuned the thing a bit (which should now > offer more precision, and ~20% more performance as well): > > With r (x, y, i) As ( >Select ?, 1, 1 Union All >Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit 32 > ) Select y From r Order By i Desc Limit 1 > > Note the question-marked "Parameter" for the "squared Input-Value" > (in the first Select Statement of the CTEs triple). > > Integrated into an Update-Query it could look this way: > > Update MyTable Set MySquareRoot = ( -- SubSelect-Opening-Paren... > With r (x, y, i) As ( > Select MySquaredValue, 1, 1 Union All > Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit 32 > ) Select y From r Order By i Desc Limit 1 > ) -- SubSelect-Closing-Paren... > > HTH > > Olaf > > ___ > 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
Re: [sqlite] Excel Pivot Table
Forgot to mention the most important feature of spreadsheet datasources: Once you linked your e.g. pivot to a datasource, you always can refresh it (usually Ritgh Click/Refresh), after a DB data change. Kind Regards, Alek ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 column to have the square root? Well, as with any other (single-value-returning) Sub-Select (which was enclosed in parentheses)... E.g. "Update MyTable Set MyCol = (Select 1)" would update MyCol with the value 1 across the whole table... Same thing basically (only "a bit larger") with a CTE-based "Single-Value-SubSelect"... BTW, I've updated and tuned the thing a bit (which should now offer more precision, and ~20% more performance as well): With r (x, y, i) As ( Select ?, 1, 1 Union All Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit 32 ) Select y From r Order By i Desc Limit 1 Note the question-marked "Parameter" for the "squared Input-Value" (in the first Select Statement of the CTEs triple). Integrated into an Update-Query it could look this way: Update MyTable Set MySquareRoot = ( -- SubSelect-Opening-Paren... With r (x, y, i) As ( Select MySquaredValue, 1, 1 Union All Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit 32 ) Select y From r Order By i Desc Limit 1 ) -- SubSelect-Closing-Paren... HTH Olaf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Excel Pivot Table
Hi Dennis,
On 2019-10-17 02:11, Harris, Dennis wrote:
I sure this has been asked a 100 times but what is the best practice to get
data from SQLITE to Excel? I would like to have a pivot table that updates
upon open.
Excel and it's leading open source alternative - LibreOffice Calc, both
have build-in DB/XML datasource facilities [*]. In LibreOffice user
interface should be sufficient. For Excel 2003 (the only version I have)
I am using VBA Macro, pasted bellow.
Testing:
1.Install ODBC driver for SQLite
This is the leading OSS driver, developed by Mr.Cristian Werner:
http://www.ch-werner.de/sqliteodbc/
2.Save module text bellow as vb.base somewhere, then import it into your
workbook (Alt-F11, File/Import file) [**]
3.Name a new sheet in your workbook "sqlite". Paste the following 4
lines in column A starting from A1
```
c:\path\to\your\database.sqlite
tables_and_views select name, 'select * from ' || name sql, type from
sqlite_master where type in ('table', 'view') order by name
```
4.Select Cell A3 (containing tables_and_views), press Ctrl+Shift+d.
Expected result is a table (name, sql) placed from A6.
5.In any cell like A6 - which contains name (suitable for a Excel
identifier - sheet-name, QueryTable name, etc) and sql in the adjacent
cell you can press Ctrl-d for table and Ctrl-t from pivot.
Step 4. is optional (just for illustration of what is expected on step
5.). From step 3. only the path placed in A1 is mandatory.
Hope this mess still work in your Excel version :-)
Kind Regards,
Alek
[*] IMHO, spreadsheet applications should be used primarily for
browsing/pivoting of data based on DB/XML datasources, not as
development place, where the user tries with a bunch of fragile formulas
and macros to achieve the same result as of few lines SQL.
[**] If you want these macros to be available in all workbooks, import
them in a hidden workbook placed in the path, specified at
"Tools/Options/General/At startup" (Usually persnal.xls) or something
similar in your version of Excel.
db.bas:
---
Attribute VB_Name = "db"
Option Explicit
Private Function sheet_db_name(sheet As Excel.Worksheet)
Select Case ActiveSheet.name
Case "sqlite"
sheet_db_name = sheet.range("a1")
Case "mysql-sample"
sheet_db_name = sheet.range("a1")
Case Else
sheet_db_name = ""
End Select
End Function
Private Function connection_string(connection_key As String, Optional
db_name As String) As String
Select Case connection_key
Case "sqlite"
Let connection_string = _
"ODBC;DRIVER={SQLite3 ODBC Driver};DATABASE=" & db_name
Case "dsn-sample"
Let connection_string = _
"ODBC;DSN=sample;Uid=aUsername;Pwd=aPassword"
Case "mysql-sample"
Let connection_string = _
"ODBC;DRIVER={MySQL ODBC 5.1
Driver};SERVER=127.0.0.1;Port=3306;" _
& "DATABASE=" & db_name & ";USER=aPassword;OPTION=3;"
Case Else
Let connection_string = _
"OLEDB;Provider=SQLOLEDB; Server=127.0.0.1,1433; " & _
"User ID=aUsername; Password=aPassword; Initial
Catalog=" & connection_key
End Select
End Function
Private Function sheet_get(name As String) As Excel.Worksheet
Dim sel As Object
Dim sheet As Excel.Worksheet
For Each sel In ActiveWorkbook.Sheets
If sel.name = name Then
Set sheet_get = sel
Exit Function
End If
Next
End Function
Private Sub db_fetch( _
sql As String, connection_key As String, _
range_at As Excel.range, fetch_name As String, _
Optional db_name As String = "", _
Optional as_pivot As Boolean = False _
)
Dim sheet As Excel.Worksheet
Dim db_connection_string As String
Let db_connection_string = connection_string(connection_key, db_name)
Set sheet = range_at.Worksheet
If Not Err Then
If as_pivot Then
Dim cache As Excel.PivotCache
Set cache = ActiveWorkbook.PivotCaches.Add(xlExternal)
With cache
.Connection = db_connection_string
.CommandType = xlCmdSql
.CommandText = sql
End With
Dim pivot As PivotTable
Set pivot = sheet.PivotTables.Add(cache, range_at, fetch_name)
Let pivot.DisplayImmediateItems = True
Else
With sheet.QueryTables.Add(db_connection_string, range_at, sql)
.name = fetch_name
.FieldNames = True
.RowNumbers = False
.MaintainConnection = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
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 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 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 > > Because SQLite on Android has at least CTEs available, > (since Android 5 or so), I thought I make an attempt using them... > > For your above sample-set I've created a table Test this way > (with slightly changed Column-Names + different IDs with "gaps" > + a few more sample-records, to cover especially n=1 sets): > > Create Table Test(ID, xVal, xDat); > > Insert Into Test Values(3, 130, 40123); > Insert Into Test Values(3, 120, 41232); > Insert Into Test Values(3, 140, 40582); > Insert Into Test Values(3, 100, 40888); > Insert Into Test Values(3, 110, 42541); > > Insert Into Test Values(5, 140, 41225); > Insert Into Test Values(5, 130, 41589); > Insert Into Test Values(5, 150, 40872); > > Insert Into Test Values(6, 110, 41225); > Insert Into Test Values(6, 115, 41227); > > Insert Into Test Values(9, 105, 41225); > > After the above Table-Creation + Inserts, one should > define a "Base-View" first, which is then able to select > appropriate aggregates (respecting your "max-4" condition): > > Create View vw_StdDev_Max4_Aggregates As > Select ID, Avg(xVal) a1, Avg(xVal*xVal) a2, Count(*) n From Test T > Where xDat In (Select xDat From Test Where T.ID=ID Order By xDat Desc > Limit 4) > Group By ID; > > Only this view above has to be adjusted, according to your > real table- and column-names, but the following two "derived views", > will work after these changes as well, as long as you leave the > above view-name - and the field-names: (ID, a1, a2, n) intact. > > So what remains, is two more view-defs (one for the population-StdDev - > and one with a few slight changes, to cover the sample-StdDev as well. > > Create View vw_StdDev_Samples As > Select ID, n n_SampleSize, (a2-a1*a1)*n/(n-1) "s^2", ( >With r(s2, s, i) As (Select (a2-a1*a1)*n/(n-1), 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 > ) s From vw_StdDev_Max4_Aggregates Where n>1 > Union All > Select ID, n, Null, Null From vw_StdDev_Max4_Aggregates Where n=1; > > Create View vw_StdDev_Populations As > Select ID, n n_PopulationSize, (a2-a1*a1) "sigma^2", ( >With r(s2, s, i) As (Select (a2-a1*a1), 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 > ) sigma From vw_StdDev_Max4_Aggregates Where n>1 > Union All > Select ID, n, 0, 0 From vw_StdDev_Max4_Aggregates Where n=1; > > > Ok, here the result I get on my test-table, when the 3 views > are in place, and the following gets executed: > > Select ID, n_PopulationSize, sigma From vw_StdDev_Populations; > ID n sigma > > 3 4 14.7901994577491 > 5 3 8.16496580927733 > 6 2 2.5 > 9 1 0 > > > Select ID, n_SampleSize, s From vw_StdDev_Samples; > ID n s > > 3 4 17.0782512765993 > 5 3 10.1 > 6 2 3.53553390593274 > 9 1 null > > Please note the returned null (vs. the real 0) in the last result > (done, because "unbiased estimates" are undefined for sample-size == 1) > > Finally for those interested, the CTE-based "iterated Square-Root", > cut out for an isolated calculation of sqrt(2). > > 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 > > The max. iterations are limited to 32 - though for input-values > in a "typical range", the iteration will usually exit earlier... > In case of the example above -> sqrt(2), the iteration-counter > i reached only 6 (as can be seen, when you include i alongside s > in the final Select. > > 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 i, s From r Order By i Desc Limit 1 > > HTH > > Olaf > > > > ___ > 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
Re: [sqlite] [EXTERNAL] Re: Limit on number of columns in SQLite table
If you should happen to have the need for storing columns that each have only a small set of possible values, maybe you would be better off looking into FastBit, which has coulmn-oriented storage -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Mitar Gesendet: Donnerstag, 17. Oktober 2019 15:46 An: SQLite mailing list Betreff: Re: [sqlite] [EXTERNAL] Re: Limit on number of columns in SQLite table Hi! Thanks for this input. So you are saying that sqlite3_column 100k times per row is slow, but retrieving 100k rows to construct one "original" row will be faster? So not sure if I understand why reading and decoding cells in over multiple columns is so much slower than reading and decoding cells in over multiple rows? Mitar On Thu, Oct 17, 2019 at 3:38 PM Hick Gunter wrote: > > I have the impression that you still do not grasp the folly of a 100k column > schema. > > See the example below, which only has 6 fields. As you can see, each field > requires a Column opcode and arguments (about 10 bytes) and a "register" to > hold the value (48 bytes), which for 100k columns uses about 5.5Megabytes to > retrieve a row from the database. It ill also involve SQLite decoding 100k > field values and your application calling sqlite3_column interface 100k times > for each and every row, which yield an expected performance of about 2 rows > per second. Can you afford to use that much memory and time? > > asql> create temp table genes (id integer primary key, name char, f1 > asql> char, f2 char, f3 char, f4 char); .explain explain select * from > asql> genes; > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 13000 Start at 13 > 1 OpenRead 0 2 1 6 00 root=2 iDb=1; genes > 2 Explain2 0 0 SCAN TABLE genes 00 > 3 Rewind 0 12000 > 4 Rowid 0 1 000 r[1]=rowid > 5 Column 0 1 200 r[2]=genes.name > 6 Column 0 2 300 r[3]=genes.f1 > 7 Column 0 3 400 r[4]=genes.f2 > 8 Column 0 4 500 r[5]=genes.f3 > 9 Column 0 5 600 r[6]=genes.f4 > 10 ResultRow 1 6 000 output=r[1..6] > 11Next 0 4 001 > 12Halt 0 0 000 > 13Transaction1 0 1 0 01 usesStmtJournal=0 > 14Goto 0 1 000 > > -Ursprüngliche Nachricht- > Von: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von > Mitar > Gesendet: Donnerstag, 17. Oktober 2019 15:11 > An: SQLite mailing list > Betreff: [EXTERNAL] Re: [sqlite] Limit on number of columns in SQLite > table > > Hi! > > On Thu, Oct 17, 2019 at 3:04 PM Eric Grange wrote: > > my suggestion would be to store them as JSON in a blob, and use the > > JSON functions of SQLite to extract the data > > JSON has some crazy limitations like by standard it does not support full > floating point spec, so NaN and infinity cannot be represented there. So JSON > is really no a great format when you want to preserve as much of the input as > possible (like, integers, floats, text, and binary). SQLite seems to be spot > on in this regard. > > But yes, if there would be some other standard to SQLite and supported format > to embed, that approach would be useful. Like composite value types. > > > Mitar > > -- > http://mitar.tnode.com/ > https://twitter.com/mitar_m > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > Gunter Hick | Software Engineer | Scientific Games International GmbH > | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 > | (O) +43 1 80100 - 0 > > May be privileged. May be confidential. Please delete if not the addressee. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- http://mitar.tnode.com/ https://twitter.com/mitar_m ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien,
Re: [sqlite] [EXTERNAL] Re: Limit on number of columns in SQLite table
If you need to perform queries over the 100k rows, then normalization is the only practical way and reconstructing a row will similarly slow. But you have stated that you use case is "retrieving complete rows". In this case, SQLite does not need to know the 100k details of the row. Keep your data in whatever format they come in - which is another requirement you have expressed. All you have to extract from the 100k details is the columns that you need to identify the row(s) you want back. Create table gene_data (sample_id integer primary key, name char, raw_data blob); Anything else you need to do with the row data goes into your application. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Mitar Gesendet: Donnerstag, 17. Oktober 2019 15:46 An: SQLite mailing list Betreff: Re: [sqlite] [EXTERNAL] Re: Limit on number of columns in SQLite table Hi! Thanks for this input. So you are saying that sqlite3_column 100k times per row is slow, but retrieving 100k rows to construct one "original" row will be faster? So not sure if I understand why reading and decoding cells in over multiple columns is so much slower than reading and decoding cells in over multiple rows? Mitar On Thu, Oct 17, 2019 at 3:38 PM Hick Gunter wrote: > > I have the impression that you still do not grasp the folly of a 100k column > schema. > > See the example below, which only has 6 fields. As you can see, each field > requires a Column opcode and arguments (about 10 bytes) and a "register" to > hold the value (48 bytes), which for 100k columns uses about 5.5Megabytes to > retrieve a row from the database. It ill also involve SQLite decoding 100k > field values and your application calling sqlite3_column interface 100k times > for each and every row, which yield an expected performance of about 2 rows > per second. Can you afford to use that much memory and time? > > asql> create temp table genes (id integer primary key, name char, f1 > asql> char, f2 char, f3 char, f4 char); .explain explain select * from > asql> genes; > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 13000 Start at 13 > 1 OpenRead 0 2 1 6 00 root=2 iDb=1; genes > 2 Explain2 0 0 SCAN TABLE genes 00 > 3 Rewind 0 12000 > 4 Rowid 0 1 000 r[1]=rowid > 5 Column 0 1 200 r[2]=genes.name > 6 Column 0 2 300 r[3]=genes.f1 > 7 Column 0 3 400 r[4]=genes.f2 > 8 Column 0 4 500 r[5]=genes.f3 > 9 Column 0 5 600 r[6]=genes.f4 > 10 ResultRow 1 6 000 output=r[1..6] > 11Next 0 4 001 > 12Halt 0 0 000 > 13Transaction1 0 1 0 01 usesStmtJournal=0 > 14Goto 0 1 000 > > -Ursprüngliche Nachricht- > Von: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von > Mitar > Gesendet: Donnerstag, 17. Oktober 2019 15:11 > An: SQLite mailing list > Betreff: [EXTERNAL] Re: [sqlite] Limit on number of columns in SQLite > table > > Hi! > > On Thu, Oct 17, 2019 at 3:04 PM Eric Grange wrote: > > my suggestion would be to store them as JSON in a blob, and use the > > JSON functions of SQLite to extract the data > > JSON has some crazy limitations like by standard it does not support full > floating point spec, so NaN and infinity cannot be represented there. So JSON > is really no a great format when you want to preserve as much of the input as > possible (like, integers, floats, text, and binary). SQLite seems to be spot > on in this regard. > > But yes, if there would be some other standard to SQLite and supported format > to embed, that approach would be useful. Like composite value types. > > > Mitar > > -- > http://mitar.tnode.com/ > https://twitter.com/mitar_m > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > Gunter Hick | Software Engineer | Scientific Games International GmbH > | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 > | (O) +43 1 80100 - 0 > > May be privileged. May be confidential. Please delete if not the addressee. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org >
