Oracle DBMS_STAT_FUNCS Statistical Functions


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

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