Re: Gaussian Function

Hi Ethan,

Yes, that's pretty easy since dbms_random.normal is a gaussian function with mean=0 and variance=1.
Look at $ORACLE_HOME/rdbms/admin/dbmsrand.sql: $ grep 'FUNCTION normal' -b3 $ORACLE_HOME/rdbms/admin/dbmsrand.sql

9597-
9598-    -- Random numbers in a normal distribution.
9646-    -- Pilfered from Knuth volume 2.
9683:    FUNCTION normal RETURN NUMBER PARALLEL_ENABLE is
9738-                    -- 38 decimal places: Mean 0, Variance 1
9799-        v1  NUMBER;
9819-        v2  NUMBER;

So without min and max it's very easy: dbms_random.normal()*mean + variance And with min/max it's a bit longer:
https://gist.github.com/xtender/e65fd2cb0e82ce071150ecd517e0834a

create or replace function random_gauss(p_mean number:=0, p_dev number:=1, p_min number:=null, p_max number:=null)
  return number
as

    res number;
    function gauss return number as
    begin

        return dbms_random.normal()*p_dev + p_mean;     end;
begin

    res:=gauss();
    while not res between p_min and p_max loop

        res:=gauss();
    end loop;
    return res;
end;
/
SQL> select random_gauss(3,4,1,5) g from dual connect by level<=10;

         G

4.86149705
3.62906844
2.55838621
1.38483471
2.02562505
2.70431185
1.56148613
1.46706992
3.69866968
1.30428931

10 rows selected.

On Thu, Jul 23, 2020 at 2:42 AM Michael D O'Shea/Woodward Informatics Ltd < woodwardinformatics_at_strychnine.co.uk> wrote:

> There’s likely something proven that matches this requirement in the
> *org.apache.commons.math3.distribution* namespace Ethan. If you really
> need to do this within the database you could import with the loadjava util
> (I’ve used it since 8i and it still exists as of 12.2), create new
> functions "as language java name 'whatever(….)‘", and redline the cpu that
> way ;-/
>
> Mike
>
> Woodward Informatics Ltd
> http://www.strychnine.co.uk
>
>
>
> Am 23.07.2020 um 00:24 schrieb Ethan Post <post.ethan_at_gmail.com>:
>
> Anyone know if Oracle provides anything like this guass function (Python)?
> Is this fairly simply to port perhaps?
>
>
> https://stackoverflow.com/questions/16471763/generating-numbers-with-gaussian-function-in-a-range-using-python
>
>
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l