Re: Gaussian Function

From: Ethan Post <post.ethan_at_gmail.com>
Date: Wed, 22 Jul 2020 23:59:52 -0500
Message-ID: <CAMNhnU1zeGVW_CwA_+UiZwqH3iGeTWc1VsHE38a2fT-1F9JXOQ_at_mail.gmail.com>



Thanks all! Appreciate the quick response here.

On Wed, Jul 22, 2020 at 7:17 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> 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
Received on Thu Jul 23 2020 - 06:59:52 CEST

Original text of this message