CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_STAT_FUNCS
Version 11.1
 
General
Source $ORACLE_HOME/rdbms/admin/dbmsstts.sql
First Available 10.1
Dependencies
DBMS_ASSERT DBMS_STAT_FUNCS_AUX PLITBLM
DBMS_OUTPUT DBMS_STAT_FUNCS_LIB  

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);
Security Model Execute is granted to PUBLIC
 
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

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

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

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

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

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

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
Functions
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [250 users online]    © 2010 psoug.org