Oracle DBMS_STAT_FUNCS
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Provides procedures to that perform distribution fitting for numerical data summarization.
AUTHID CURRENT_USER
Data Types TYPE n_arr IS VARRAY(5) OF NUMBER;

TYPE num_table IS TABLE of NUMBER;

TYPE summaryType IS RECORD (
count           NUMBER,
min             NUMBER,
max             NUMBER,
range           NUMBER,
mean            NUMBER,
cmode           num_table,
variance        NUMBER,
stddev          NUMBER,
quantile_5      NUMBER,
quantile_25     NUMBER,
median          NUMBER,
quantile_75     NUMBER,
quantile_95     NUMBER,
plus_x_sigma    NUMBER,
minus_x_sigma   NUMBER,
extreme_values  num_table,
top_5_values    n_arr,
bottom_5_values n_arr);
Dependencies
DBMS_ASSERT DBMS_STAT_FUNCS_AUX PLITBLM
DBMS_OUTPUT DBMS_STAT_FUNCS_LIB  
Documented Yes
First Available 10.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source $ORACLE_HOME/rdbms/admin/dbmsstts.sql
Subprograms
EXPONENTIAL_DIST_FIT POISSON_DIST_FIT UNIFORM_DIST_FIT
NORMAL_DIST_FIT SUMMARY WEIBULL_DIST_FIT
 
EXPONENTIAL_DIST_FIT
Tests how well a sample of values fits an exponential distribution Test types: 'CHI_SQUARED', 'KOLMOGOROV_SMIRNOV' or 'ANDERSON_DARLING'

dbms_stat_funcs.exponential_dist_fit(
ownername  IN     VARCHAR2,
tablename  IN     VARCHAR2,
columnname IN     VARCHAR2,
test_type  IN     VARCHAR2 DEFAULT 'KOLMOGOROV_SMIRNOV',
lambda     IN OUT NUMBER,
mu         IN OUT NUMBER,
sig           OUT NUMBER);
conn sh/sh@pdbdev

SELECT MIN(amount_sold) FROM sales;

SELECT MAX(amount_sold) FROM sales;

set serveroutput on

DECLARE
  sig    NUMBER;
 lambda NUMBER := 1;
 mu     NUMBER := 1;
BEGIN
  dbms_stat_funcs.exponential_dist_fit('SH', 'SALES', 'AMOUNT_SOLD', 'KOLMOGOROV_SMIRNOV', lambda, mu, sig);
  dbms_output.put_line(sig);
END;
/
 
NORMAL_DIST_FIT
Tests how well a sample of values fits a normal distribution Test types: 'CHI_SQUARED', 'KOLMOGOROV_SMIRNOV', 'ANDERSON_DARLING' or 'SHAPIRO_WILKS'

dbms_stat_funcs.normal_dist_fit(
ownername  IN     VARCHAR2,
tablename  IN     VARCHAR2,
columnname IN     VARCHAR2,
test_type  IN     VARCHAR2 DEFAULT 'SHAPIRO_WILKS',
mean       IN OUT NUMBER,
stdev      IN OUT NUMBER,
sig           OUT NUMBER);
conn sh/sh@pdbdev

SELECT MIN(amount_sold) FROM sales;

SELECT MAX(amount_sold) FROM sales;

set serveroutput on

DECLARE
 sig   NUMBER;
 mean  NUMBER := 1;
 stdev NUMBER := 1;
BEGIN
  dbms_stat_funcs.normal_dist_fit('SH', 'SALES', 'AMOUNT_SOLD', 'SHAPIRO_WILKS', mean, stdev, sig);
  dbms_output.put_line(sig);
END;
/

DECLARE
  sig   NUMBER;
 mean  NUMBER := 1;
 stdev NUMBER := 1;
BEGIN
  dbms_stat_funcs.normal_dist_fit('SH', 'SALES', 'AMOUNT_SOLD', 'CHI_SQUARED', mean, stdev, sig);
  dbms_output.put_line(sig);
END;
/
 
POISSON_DIST_FIT
Tests how well a sample of values fits a Poisson distribution Test types: 'KOLMOGOROV_SMIRNOV' or 'ANDERSON_DARLING'

dbms_stat_funcs.poisson_dist_fit(
ownername  IN     VARCHAR2,
tablename  IN     VARCHAR2,
columnname IN     VARCHAR2,
test_type  IN     VARCHAR2 DEFAULT 'KOLMOGOROV_SMIRNOV',
lambda     IN OUT NUMBER,
sig           OUT NUMBER);
conn sh/sh@pdbdev

SELECT MIN(amount_sold) FROM sales;

SELECT MAX(amount_sold) FROM sales;

set serveroutput on

DECLARE
  sig   NUMBER;
 lambda NUMBER := 1;
BEGIN
  dbms_stat_funcs.poisson_dist_fit('SH', 'SALES', 'AMOUNT_SOLD', 'KOLMOGOROV_SMIRNOV', lambda, sig);
  dbms_output.put_line(sig);
END;
/
 
SUMMARY
Summarizes a numerical column of a table dbms_stat_funcs.summary(
ownername   IN         VARCHAR2,
tablename   IN         VARCHAR2,
columnname  IN         VARCHAR2,
sigma_value IN         NUMBER := 3,
s           OUT NOCOPY SummaryType);
conn sh/sh@pdbdev

SELECT MIN(amount_sold) FROM sales;

SELECT MAX(amount_sold) FROM sales;

set serveroutput on

DECLARE
  sig   NUMBER := 3;
 s     dbms_stat_funcs.SummaryType;
BEGIN
  dbms_stat_funcs.summary('SH', 'SALES', 'AMOUNT_SOLD', sig, s);

  dbms_output.put_line('Min: ' || TO_CHAR(s.min));
  dbms_output.put_line('Max: ' || TO_CHAR(s.max));
  dbms_output.put_line('Mean: ' || TO_CHAR(s.mean));
  dbms_output.put_line('Variance: ' || TO_CHAR(s.variance));
  dbms_output.put_line('Std Dev: ' || TO_CHAR(s.stddev));
END;
/
 
UNIFORM_DIST_FIT
Tests how well a sample of values fits a uniform distribution Var types: 'CONTINUOUS', 'DISCRETE'
Test types: 'CHI_SQUARED', 'KOLMOGOROV_SMIRNOV' or 'ANDERSON_DARLING'

dbms_stat_funcs.uniform_dist_fit(
ownername  IN     VARCHAR2,
tablename  IN     VARCHAR2,
columnname IN     VARCHAR2,
var_type   IN     VARCHAR2 DEFAULT 'CONTINUOUS',
test_type  IN     VARCHAR2 DEFAULT 'KOLMOGOROV_SMIRNOV',
paramA     IN OUT NUMBER,
paramB     IN OUT NUMBER,
sig           OUT NUMBER);
conn sh/sh@pdbdev

SELECT MIN(amount_sold) FROM sales;

SELECT MAX(amount_sold) FROM sales;

set serveroutput on

DECLARE
 sig   NUMBER;
 alpha NUMBER := 1;
 beta  NUMBER := 1;
 ttype VARCHAR2(20) := 'CHI_SQUARED';
BEGIN
  dbms_stat_funcs.uniform_dist_fit('SH', 'SALES', 'AMOUNT_SOLD', 'CONTINUOUS', ttype, alpha, beta, sig);
  dbms_output.put_line(sig);
END;
/

DECLARE
  sig   NUMBER;
  alpha NUMBER := 1;
 beta  NUMBER := 1000;
 ttype VARCHAR2(20) := 'CHI_SQUARED';
BEGIN
  dbms_stat_funcs.uniform_dist_fit('SH', 'SALES', 'AMOUNT_SOLD', 'CONTINUOUS', ttype, alpha, beta, sig);
  dbms_output.put_line(sig);
END;
/
 
WEIBULL_DIST_FIT
Tests how well a sample of values fits a Weibull distribution Test types: 'CHI_SQUARED', 'KOLMOGOROV_SMIRNOV' or 'ANDERSON_DARLING'

dbms_stat_funcs.weibull_dist_fit(
ownername  IN     VARCHAR2,
tablename  IN     VARCHAR2,
columnname IN     VARCHAR2,
test_type  IN     VARCHAR2 DEFAULT 'KOLMOGOROV_SMIRNOV',
alpha      IN OUT NUMBER,
mu         IN OUT NUMBER,
beta       IN OUT NUMBER,
sig           OUT NUMBER);
conn sh/sh@pdbdev

SELECT MIN(amount_sold) FROM sales;

SELECT MAX(amount_sold) FROM sales;

set serveroutput on

DECLARE
 sig NUMBER;
 alpha NUMBER := 1;
 mu    NUMBER := -1;
 beta  NUMBER := 1;
BEGIN
  dbms_stat_funcs.weibull_dist_fit('SH', 'SALES', 'AMOUNT_SOLD', 'KOLMOGOROV_SMIRNOV', alpha, mu, beta, sig);
  dbms_output.put_line(sig);
END;
/

DECLARE
  sig NUMBER;
  alpha NUMBER := 500;
 mu    NUMBER := -1;
 beta  NUMBER := 1;
BEGIN
  dbms_stat_funcs.weibull_dist_fit('SH', 'SALES', 'AMOUNT_SOLD', 'KOLMOGOROV_SMIRNOV', alpha, mu, beta, sig);
  dbms_output.put_line(sig);
END;
/

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_STAT_FUNCS_AUX
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx