Oracle DBMS_STAT_FUNCS
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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
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@pdborcl

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@pdborcl

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@pdborcl

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@pdborcl

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@pdborcl

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@pdborcl

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
Packages

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-2014 Daniel A. Morgan All Rights Reserved