On Sat, Mar 8, 2014 at 2:24 AM, big stone <stonebi...@gmail.com> wrote: > Ooups ! > > Thanks to the awesome posts about "RPAD/LPAD", I understood that I could > already create a "sqrt()" function for SQLite3 in interpreted python. >
Yes, that discussion was inspiring :) Looking at your task I also played with cte version of sqrt. Based on the "guessing" approach from one of the answers from http://stackoverflow.com/questions/3581528/how-is-the-square-root-function-implemented the following query finally worked. /* :value=12345 */ with recursive sqrt(depth, val, guess) as ( select 1, :value, Cast(:value as Float)/2 UNION ALL select depth + 1, val as newval, ((guess + val/guess)/2) as newguess from sqrt where abs(newguess - guess) > 1e-308 and depth < 100 ) select guess from sqrt order by depth desc limit 1 but I could not overcome some pecularities of float numbers so depth < 100 here is for cases when comparison fails to stop. Also for CTE queries in general I wonder whether there is another faster way to get the last row of the query (in natural executing order), so order by depth can be replaced by something else. I suspect ordering here triggers temporary storage. I tested this function as "expression function" implemented based on that thread and an average speed of this one is about 4000 sqrt operations / second on a mobile Intel i3. Not so fast, but if one desperately needs one, then it would be ok. Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
