Blog programisty w Oracle PL/SQL

Jest to blog eksperymentatora programisty w PL/SQL dla Oracle. Wszystkie kody tutaj zamieszczone mogą być dowolnie wykorzystywane i zmieniane. A jeśli Ktoś z Gości znajdzie błąd, będę niezwykle wdzięczny...
Zapisz

Szukaj na tym blogu

środa, 7 sierpnia 2024

Calculating Euler's number in Oracle SQL

 

I use the MODEL clause again to calculate the Euler's number.
Euler proved that e is the sum of the infinite series e = 1/0!  +1/1!  +1/2! +1/3!  +1/4! ........
There is one dimension - the iteration number.
The measures are:
  • E_EXP -the Euler's number calculated from the EXP function
  • FACTORIAL - value of the factorial function
  • E_COMPUTED - our estimate of the number e
  • DIFF -the difference between EXP(1) and the current estimate

My proposal is to change the precision in the stop condition
- the second parameter of the POWER function: 
UNTIL (DIFF[ITERATION_NUMBER] < POWER(10, -30))
----------------------------------------------------------- 

SELECT
       d              "Iteration number",
       FACTORIAL      "Factorial",
       E_EXP          "Euler's number from EXP",
       E_COMPUTED     "Euler's number calculated",
       DIFF           "Difference"
  FROM (SELECT 1 d FROM DUAL)
MODEL
    DIMENSION BY (0 d)
    MEASURES (CAST (0  AS NUMBER) AS E_EXP,
              CAST (1 AS NUMBER) AS FACTORIAL,
              CAST (1 AS NUMBER) AS E_COMPUTED,
              CAST (0 AS NUMBER) AS DIFF)
    RULES
    ITERATE (1000) UNTIL (DIFF[ITERATION_NUMBER] < POWER(10, -30))
    (
        FACTORIAL [ITERATION_NUMBER] =
            CASE
                WHEN ITERATION_NUMBER=0  THEN 1
                ELSE FACTORIAL[ITERATION_NUMBER- 1] * (ITERATION_NUMBER)
            END,
        E_EXP [ITERATION_NUMBER] =  
          CASE
            WHEN ITERATION_NUMBER= 0 THEN    
                EXP(1)
            WHEN ITERATION_NUMBER> 0 THEN
                E_EXP[ITERATION_NUMBER-1]
            END,
        E_COMPUTED [ITERATION_NUMBER] =
            CASE ITERATION_NUMBER
                WHEN 0 THEN 1
                ELSE
                      E_COMPUTED[ITERATION_NUMBER- 1]
                    + 1 / FACTORIAL[ITERATION_NUMBER]
            END,
        DIFF [ITERATION_NUMBER] =
            ABS (E_COMPUTED[ITERATION_NUMBER] - E_EXP[ITERATION_NUMBER]))