Oracle: Create a normal distribution with a given mean and k-th percentile value
This note is intended to demonstrate how a normal distribution with a given mean (here: 5000) and a given k-th percentile value (here k=0.1 % and value = 10) can be created in Oracle using dbms_random.normal.
For this purpose, we need to somehow obtain the z-Score for k, for example with this calculator. It turns out, the sought value is 3.09023.
With these three values (z-Score, mean and k-th percentile value 10 (x), we create a table with one million records whose values for val follow this distribution:
exec dbms_random.seed(2808)
create table tq84_normal as
with param as (
select
3.09023 zScore, -- 0.1 % -- https://www.socscistatistics.com/tests/ztest/zscorecalculator.aspx / Calculate z from p
-- 1.28155 zScore, -- 10.0 %
-- 0.67449 zScore, -- 25.0 %
5000 mean,
10 x
from
dual
)
selectdbms_random.normal * (param.x - param.mean) / param.zScore + param.mean val
from
param, dual
connect by
level <= 1000000;
After creating the table, we check if the result corresponds to our criteria. It turns out that the mean is approximately 5000 and that indeed 0.1 % of val is smaller or equal to 10:
selectround(avg(val), 1) mean,
to_char(100 * count(case when val <= 10 then 1 end) / count(*), '90.00') || ' %' pct
from
tq84_normal;
--
-- MEAN PCT
-- ---------- --------
-- 5000.7 0.10 %