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 <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Bart Smissaert >Sent: Friday, 18 October, 2019 14:55 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >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 <n...@vbrichclient.com> 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
