Home » SQL & PL/SQL » SQL & PL/SQL » Newton-Raphson method with sql
Newton-Raphson method with sql Thu, 25 February 2010 02:19
 steffeli Messages: 112Registered: 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
 Michel Cadot Messages: 65088Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Why do you want to use a database to solve equation?

Regards
Michel
Re: Newton-Raphson method with sql [message #444992 is a reply to message #444989] Thu, 25 February 2010 02:30
 delna.sexy Messages: 941Registered: 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.

Delna
Re: Newton-Raphson method with sql [message #444993 is a reply to message #444989] Thu, 25 February 2010 02:34
 ayush_anand Messages: 417Registered: 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
 steffeli Messages: 112Registered: 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
 Michel Cadot Messages: 65088Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 steffeli Messages: 112Registered: 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
 ayush_anand Messages: 417Registered: 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
 _jum Messages: 515Registered: 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
 steffeli Messages: 112Registered: 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
 Michel Cadot Messages: 65088Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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:

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
 steffeli Messages: 112Registered: 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).

```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
 Michel Cadot Messages: 65088Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 steffeli Messages: 112Registered: 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
 _jum Messages: 515Registered: 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

```

[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
 Michel Cadot Messages: 65088Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 steffeli Messages: 112Registered: 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
 steffeli Messages: 112Registered: July 2006 Senior Member
Re: Newton-Raphson method with sql [message #445828 is a reply to message #445817] Thu, 04 March 2010 05:02
 _jum Messages: 515Registered: 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
 steffeli Messages: 112Registered: 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
 Michel Cadot Messages: 65088Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 _jum Messages: 515Registered: 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
 ayush_anand Messages: 417Registered: 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
 steffeli Messages: 112Registered: 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
 ayush_anand Messages: 417Registered: 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
 steffeli Messages: 112Registered: 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
 ayush_anand Messages: 417Registered: November 2008 Senior Member
Re: Newton-Raphson method with sql [message #445883 is a reply to message #445881] Thu, 04 March 2010 08:41
 ayush_anand Messages: 417Registered: 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
 steffeli Messages: 112Registered: 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
 ayush_anand Messages: 417Registered: 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
 ayush_anand Messages: 417Registered: 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
 Michel Cadot Messages: 65088Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
I (we) already gave him the link to the syntax (and much more) but as he said:

Quote:

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
 steffeli Messages: 112Registered: 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
 _jum Messages: 515Registered: 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: Wed Jul 26 00:03:20 CDT 2017

Total time taken to generate the page: 0.13169 seconds