Home » SQL & PL/SQL » SQL & PL/SQL » Newton-Raphson method with sql
Newton-Raphson method with sql [message #444989] Thu, 25 February 2010 02:19 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Dear all,

Is it possible to solve an equation with the newton-raphson method with sql?

I have an equation f(x)=1-a/x**2, f(x)'= 2a/x**3
and want to solve x for several a's

The newton-raphson methos uses the derivation as next approximation:

xn2 = xn1 - f(x)/f(x)'

The result should be like this:

create table temp_res (a integer, res number);
insert into temp_res values(1, 1);
insert into temp_res values(2, 1.41421288939285);
insert into temp_res values(3, 1.73205078513617);
insert into temp_res values(4, 1.99999999999617);
insert into temp_res values(5, 2.23606797658102);
insert into temp_res values(6, 2.4494897130809);
insert into temp_res values(7, 2.64575095945042);
insert into temp_res values(8, 2.82842712474403);


Is there an efficient solution?

Thanks, Stefan
Re: Newton-Raphson method with sql [message #444991 is a reply to message #444989] Thu, 25 February 2010 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to use a database to solve equation?
Do you ask your math teacher to keep your data?

Regards
Michel
Re: Newton-Raphson method with sql [message #444992 is a reply to message #444989] Thu, 25 February 2010 02:30 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
For different values of X, output will be one and the same.
So to store that data within database is not the way.
And as Michel sir said, is it necessary to store that static data in database?

Quote:
Is it possible to solve an equation with the newton-raphson method with sql?

Yes.

regads,
Delna
Re: Newton-Raphson method with sql [message #444993 is a reply to message #444989] Thu, 25 February 2010 02:34 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
f(x)/f(x)'= (1 - (1-a)/(2a))

x(n+1)=x(n) - (1 - (1-a)/(2a))

you will require value for x(1) and a to get further answers.

Oracle provides a good function of achieving the same (with more accuracy than Newton Ralphson offcourse)

SQL> with data as (select rownum val from dual connect by level<=8)
  2  select val,sqrt(val) from data
  3  /

       VAL  SQRT(VAL)
---------- ----------
         1          1
         2 1.41421356
         3 1.73205081
         4          2
         5 2.23606798
         6 2.44948974
         7 2.64575131
         8 2.82842712

8 rows selected.

[Updated on: Thu, 25 February 2010 02:40]

Report message to a moderator

Re: Newton-Raphson method with sql [message #444996 is a reply to message #444993] Thu, 25 February 2010 03:10 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
sqrt works for this equation, sure, but is there a general solution to solve non linear equations in oracle?
Re: Newton-Raphson method with sql [message #444999 is a reply to message #444996] Thu, 25 February 2010 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you can express it in algorithm: yes.

Regards
Michel
Re: Newton-Raphson method with sql [message #445000 is a reply to message #444999] Thu, 25 February 2010 03:25 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
sure, any non-linear equation using the newton raphson method
Re: Newton-Raphson method with sql [message #445001 is a reply to message #444996] Thu, 25 February 2010 03:27 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
sqrt works for this equation, sure, but is there a general solution to solve non linear equations in oracle?


Quote:
If you can express it in algorithm: yes.

Regards
Michel

To add to that I dont think in any language can solve equations unless you tell them what to do?
So you need to tell the language what to do with your data
Re: Newton-Raphson method with sql [message #445053 is a reply to message #444989] Thu, 25 February 2010 08:20 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
The model clause gives a possible solution (here a=8):
SELECT 'Stop bei i=' ||i||' a=' ||a|| ' Delta=' || d ||' f(x)=' ||f erg FROM dual
MODEL 
  DIMENSION BY (1 as na)
  MEASURES (0 as d, 1 as f, 0 as i, 8 as a)
  RULES ITERATE (3000) UNTIL ABS(PREVIOUS(d[1]) - d[1]) <1E-100
( 
 i[1] = ITERATION_NUMBER+1,
 --newton-raphson method: x(n+1)=x(n)-(1-a/x**2)/(2a/x**3)
 d[1] = (1-a[1]/POWER(f[1],2))/(2*a[1]/POWER(f[1],3)),
 f[1] = f[1]-d[1]
 );

ERG                                                                             
--------------------------------------------------------------------------------
Stop bei i=11 a=8 Delta=0 f(x)=2,82842712474619009760337744841939615714         
Re: Newton-Raphson method with sql [message #445133 is a reply to message #445053] Fri, 26 February 2010 02:17 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
excellent, thank you very much!
Re: Newton-Raphson method with sql [message #445135 is a reply to message #445133] Fri, 26 February 2010 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what I mean with "If you can express it in algorithm: yes".
Model clause can implement (almost) any algorithm.

You can learn a (little) bit more in SQL Reference:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/expressions011.htm#i1049969
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2171160

And more in Database Data Warehousing Guide:
Chapter 22 SQL for Modeling
although the examples are about data computing and not equation solving.

Regards
Michel

Re: Newton-Raphson method with sql [message #445722 is a reply to message #445135] Wed, 03 March 2010 10:02 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Dear all, I have another question regarding iterations within the model clause. Is it possible to write a for-loop in the iteration? I tried the code below, but the syntax seems to be wrong (don't mind the silly example in the for-loop).

Thanks for your help.

SELECT 'Stop bei x=' ||x||' i=' ||i||' a=' ||a|| ' Delta=' || d ||' f(x)=' ||f erg FROM dual
MODEL 
  DIMENSION BY (1 as na)
  MEASURES (0 as d, 1 as f, 0 as i, 8 as a, 2 as x)
  RULES ITERATE (3000) UNTIL ABS(PREVIOUS(d[1]) - d[1]) <1E-100
( 
 i[1] = ITERATION_NUMBER+1,
 d[1] = (1-a[1]/POWER(f[1],2))/(2*a[1]/POWER(f[1],3)),
 f[1] = f[1]-d[1],
 x[1] = for z in 2..9 loop
           x[1]=f[i]+z
	end loop
 )
Re: Newton-Raphson method with sql [message #445727 is a reply to message #445722] Wed, 03 March 2010 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you only set f[1], f[i] is null for i>1 and so the sum is always null.

If you can set f[i], you have to introduce a new measure containing n[i]=i and the formula to sum is something like:
x[ANY] = sum(f[na between 2 and 9])+sum(n[na between 2 and 9])

Regards
Michel
Re: Newton-Raphson method with sql [message #445777 is a reply to message #445727] Thu, 04 March 2010 01:52 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hi Michel, thank you for your feedback, I wrote f[i] instead of f[1], sorry, but anyway I don't get it.

I want to loop several times (e.g. from z=2 to z=9) within an interation step.

Something like this:

 x[1] = for z in 2..9 loop
           x[1]= f[1]+z
	end loop

or

 x[1] = sum[ for z between 2..9, x[1]=f[1]+z ]



Could anyone help me with the correct syntax?

Thanks, Stefan

Re: Newton-Raphson method with sql [message #445779 is a reply to message #445777] Thu, 04 March 2010 02:00 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
Here the correct syntax for 'FOR' in MODEL computing your square root with babylon method
SELECT 'SQRT at inc='||inc||' x=' ||x||' ' erg FROM dual
MODEL 
  DIMENSION BY (1 as inc)
  MEASURES (8 as x)
  RULES 
( 
  x[FOR inc FROM 2 TO 10 INCREMENT 1] =  1/2*(x[1]/x[cv()-1]+x[cv()-1])
 );

ERG
---------------------------------------------------------
SQRT at inc=1 x=8 
SQRT at inc=2 x=4,5 
SQRT at inc=3 x=3,13888888888888888888888888888888888889 
SQRT at inc=4 x=2,84378072763028515240904621435594886923 
SQRT at inc=5 x=2,8284685718801466821819618416725070015 
SQRT at inc=6 x=2,82842712504986412995671609183567406561 
SQRT at inc=7 x=2,82842712474619009761967942719569409644 
SQRT at inc=8 x=2,82842712474619009760337744841939615714 
SQRT at inc=9 x=2,82842712474619009760337744841939615714 
SQRT at inc=10 x=2,82842712474619009760337744841939615714 


Please contact the fine manual at model clause

[Updated on: Thu, 04 March 2010 02:04]

Report message to a moderator

Re: Newton-Raphson method with sql [message #445783 is a reply to message #445777] Thu, 04 March 2010 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Could anyone help me with the correct syntax?

1/ I gave you a correct syntax
2/ Please read the links I provided, you cannot, I repeat you cannot, guess the syntax you have to learn it.

Regards
Michel
Re: Newton-Raphson method with sql [message #445817 is a reply to message #445779] Thu, 04 March 2010 04:24 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
@_jum: thank you very much for your help, but I still don't get the code. I only need the last value of x (end of loop value). How can I do that?
Re: Newton-Raphson method with sql [message #445819 is a reply to message #445817] Thu, 04 March 2010 04:27 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
@Michel: unfortunately, there is nothing like this in your two links
Re: Newton-Raphson method with sql [message #445828 is a reply to message #445817] Thu, 04 March 2010 05:02 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
If You only need ONE value, You should use ITERATE.
Re: Newton-Raphson method with sql [message #445854 is a reply to message #445828] Thu, 04 March 2010 06:37 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
thank you _jum, but I don't know how I can integrate a iterate-code (x[1]) into another iterate code. Does anyone know how to write this code?

SELECT 'Stop bei x=' ||x||' i=' ||i||' a=' ||a|| ' Delta=' || d ||' f(x)=' ||f erg FROM dual
MODEL 
  DIMENSION BY (1 as na)
  MEASURES (0 as d, 1 as f, 0 as i, 8 as a, 2 as x)
  RULES ITERATE (3000) UNTIL ABS(PREVIOUS(d[1]) - d[1]) <1E-100
( 
 i[1] = ITERATION_NUMBER+1,
 d[1] = (1-a[1]/POWER(f[1],2))/(2*a[1]/POWER(f[1],3)),
 f[1] = f[1]-d[1],
 x[1] = for z in 2..9 loop   -|
           x[1]=f[1]+z        | how can I write this as an interation give that 
                                this is within another iteration step?
	end loop             -|
 )

[Updated on: Thu, 04 March 2010 07:46] by Moderator

Report message to a moderator

Re: Newton-Raphson method with sql [message #445865 is a reply to message #445854] Thu, 04 March 2010 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again, I gave you the syntax for this.

Regards
Michel
Re: Newton-Raphson method with sql [message #445869 is a reply to message #445865] Thu, 04 March 2010 07:56 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
Imo, if you ITERATE, you have only x[1],f[1] and no x[2], f[2]..., so I think it is not possible to use:
x[ANY] = sum(f[na between 2 and 9])

in a simple iteration.
Re: Newton-Raphson method with sql [message #445873 is a reply to message #445854] Thu, 04 March 2010 08:14 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SQL> ed
Wrote file afiedt.buf

  1  with at as (select rownum rn from dual connect by level <=10)
  2  SELECT rn,' f(x)=' ||f erg
  3  FROM at
  4  MODEL
  5    PARTITION BY (at.rn)
  6    DIMENSION BY (1 as na)
  7    MEASURES (0 as d, 1 as f, 0 as i, at.rn as a)
  8    RULES ITERATE (9)
  9  (
 10   i[1] = ITERATION_NUMBER+1,
 11   d[1] = (1-a[1]/POWER(f[1],2))/(2*a[1]/POWER(f[1],3)),
 12   f[1] = f[1]-d[1]
 13   )
 14* order by 1
SQL> /

        RN ERG
---------- ----------------------------------------------
         1  f(x)=1
         2  f(x)=1.41421356237309504880168872420969807857
         3  f(x)=1.73205080756887729352744634150587236694
         4  f(x)=2
         5  f(x)=2.23606797749978969640917366873127623544
         6  f(x)=2.44948974278317809819728407470589139196
         7  f(x)=2.64575131106459059050161575363926042571
         8  f(x)=2.82842712474619009760337744841939615714
         9  f(x)=3
        10  f(x)=3.16227766016837933199889354443271853371

10 rows selected.

SQL> 

[Updated on: Thu, 04 March 2010 08:15]

Report message to a moderator

Re: Newton-Raphson method with sql [message #445874 is a reply to message #445873] Thu, 04 March 2010 08:21 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
thank you ayush_anand, but this is only one iteration, I need an iteration within another iteration.
Re: Newton-Raphson method with sql [message #445876 is a reply to message #445874] Thu, 04 March 2010 08:23 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
But the question is why do you need that??
Re: Newton-Raphson method with sql [message #445878 is a reply to message #445876] Thu, 04 March 2010 08:25 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
no, the question is how I can iterate within another iteration step - in other words how can I calculate the sum of a function f(x) for x in 2..9 within another iteration step

[Updated on: Thu, 04 March 2010 08:28]

Report message to a moderator

Re: Newton-Raphson method with sql [message #445881 is a reply to message #445878] Thu, 04 March 2010 08:37 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_10002.htm#i2172805

Check it yourself Razz
Re: Newton-Raphson method with sql [message #445883 is a reply to message #445881] Thu, 04 March 2010 08:41 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
as

 
x[1] = for z in 2..9 loop   -|
           x[1]=f[1]+z        | how can I write this as an interation give that 
                                this is within another iteration step?
	end loop             -|


this is similar to


x[1] = f[1]+9
Re: Newton-Raphson method with sql [message #445892 is a reply to message #445883] Thu, 04 March 2010 09:23 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
@ayush_anand: you are totally right, it is a silly example (as I already wrote), but I need an iteration within another iteration step. Let't make a more realistic case. I can calculate x[1] as below, but this is obviously not a very smooth solution, I want to iterate or loop from 2 to 9 (since my real x[1] is a much more complicate formula and otherwise I'd have to write a lot of code).

SELECT 'Stop bei x=' ||x||' i=' ||i||' a=' ||a|| ' Delta=' || d ||' f(x)=' ||f erg FROM dual
MODEL 
  DIMENSION BY (1 as na)
  MEASURES (0 as d, 1 as f, 0 as i, 8 as a, 2 as x)
  RULES ITERATE (3000) UNTIL ABS(PREVIOUS(d[1]) - d[1]) <1E-100
( 
 i[1] = ITERATION_NUMBER+1,
 d[1] = (1-a[1]/POWER(f[1],2))/(2*a[1]/POWER(f[1],3)),
 f[1] = f[1]-d[1],
 -- x = z*x + power(2,z)   for z in 2..9  
 x[1] =  ( 2*x[1] + power(2,2) )
        +( 3*x[1] + power(2,3) )
        +( 4*x[1] + power(2,4) )
        +( 5*x[1] + power(2,5) )
        +( 6*x[1] + power(2,6) )
        +( 7*x[1] + power(2,7) )
        +( 8*x[1] + power(2,8) )
        +( 9*x[1] + power(2,9) )
)

[Updated on: Thu, 04 March 2010 09:28]

Report message to a moderator

Re: Newton-Raphson method with sql [message #445927 is a reply to message #445892] Thu, 04 March 2010 12:55 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
same as this
SQL> SELECT 'Stop bei x=' ||x||' i=' ||i||' a=' ||a|| ' Delta=' || d ||' f(x)=' ||f erg FROM dual
  2  MODEL 
  3    DIMENSION BY (1 as na)
  4    MEASURES (0 as d, 1 as f, 0 as i, 8 as a, 2 as x)
  5    RULES ITERATE (3000) UNTIL ABS(PREVIOUS(d[1]) - d[1]) <1E-100
  6  ( 
  7   i[1] = ITERATION_NUMBER+1,
  8   d[1] = (1-a[1]/POWER(f[1],2))/(2*a[1]/POWER(f[1],3)),
  9   f[1] = f[1]-d[1],
 10   -- x = z*x + power(2,z)   for z in 2..9  
 11   x[1] =  ( 2+3+4+5+6+7+8+9)*x[1] + power(2, ( 2+3+4+5+6+7+8+9))   
 12  )
 13  /

ERG
--------------------------------------------------------------------------------
Stop bei x=489588034328312985544036450304 i=11 a=8 Delta=0 f(x)=2.82842712474619
009760337744841939615714

[Updated on: Thu, 04 March 2010 12:56]

Report message to a moderator

Re: Newton-Raphson method with sql [message #445977 is a reply to message #445927] Fri, 05 March 2010 00:25 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
if you check the syntax of select statement you will get an idea

measure_column [ { { condition
                   | expr
                   | single_column_for_loop
                   }
                     [, { condition
                        | expr
                        | single_column_for_loop
                        }
                     ]...
                 | multi_column_for_loop
                 }
               ]

Note: The outer square brackets are part of the syntax.
      In this case, they do not indicate optionality.
Re: Newton-Raphson method with sql [message #445980 is a reply to message #445977] Fri, 05 March 2010 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I (we) already gave him the link to the syntax (and much more) but as he said:

Quote:
@Michel: unfortunately, there is nothing like this in your two links


No syntax and no examples in Oracle documentation.

Regards
Michel
Re: Newton-Raphson method with sql [message #445994 is a reply to message #445927] Fri, 05 March 2010 01:29 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
@ayush_anand: I know that it is possible to simplify this code

 x[1] =  ( 2*x[1] + power(2,2) )
        +( 3*x[1] + power(2,3) )
        +( 4*x[1] + power(2,4) )
        +( 5*x[1] + power(2,5) )
        +( 6*x[1] + power(2,6) )
        +( 7*x[1] + power(2,7) )
        +( 8*x[1] + power(2,8) )
        +( 9*x[1] + power(2,9) )


but as I already wrote, the real formula for x is much more complicate and I need a LOOP OR ITERATION WITHIN ANOTHER ITERATION step.

Thank you for the this

measure_column [ { { condition
                   | expr
                   | single_column_for_loop
                   }
                     [, { condition
                        | expr
                        | single_column_for_loop
                        }
                     ]...
                 | multi_column_for_loop
                 }
               ]

Note: The outer square brackets are part of the syntax.
      In this case, they do not indicate optionality.


but I still don't know how I can write the correct syntax for my problem above. I'd really appreciate your help!

[Updated on: Fri, 05 March 2010 01:30]

Report message to a moderator

Re: Newton-Raphson method with sql [message #446001 is a reply to message #445994] Fri, 05 March 2010 02:00 Go to previous message
_jum
Messages: 509
Registered: February 2008
Senior Member
As I statet before, I found no solution and it makes less sense to LOOP inside ITERATE.
Nor I think You can ITERATE inside ITERATE (no syntax, no example).
May be, you should use an outer CONNECT BY like @ayush_anand advised.

[Updated on: Fri, 05 March 2010 02:01]

Report message to a moderator

Previous Topic: Test
Next Topic: concat
Goto Forum:
  


Current Time: Sun Dec 11 08:11:13 CST 2016

Total time taken to generate the page: 0.12412 seconds