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 think as square root it such a common thing it will be worth it to be added to the standard SQLite functions. RBS On Sun, Oct 20, 2019 at 3:24 AM Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 oscillate > to terminate. It then scans those guesses looking for the one that is the > "closest" approximation to the actual square root. If you ask for the > square root of a negative number, you get null, and if subnormal division > gives a nan (NULL) that will also terminate the recursion. > > with guesses(findRootOf, guessRoot) > as ( > select ?1, > case when ?1 < 0 then null else ?1 / 2.0 end > union > select findRootOf, > (guessRoot + findRootOf / guessRoot) / 2.0 as newGuessRoot > from guesses > where guessRoot > 0.0 > ) > select guessRoot > from guesses > order by abs(guessRoot*guessRoot - findRootOf) > limit 1; > > Note that the parameter is used twice. Once to put in the table, and > again to compute the first "guess" (which is really to prime the guess with > NULL to prevent attempts to calculate the root of a negative number). > > guessRoot exactly matches the results of the sqrt function 76% of the > time, and is within 1 ULP 100% of the time. > guessRoot*guessRoot == findRootOf 48% of the time, within 1 ULP 94% of the > time, and within 2 ULP 100% of the time. > *based on 5,000,000 randomly generated positive real numbers > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > > _______________________________________________ > 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
