| General Information |
| Purpose |
The interface for the DBMS Statistical Functions Package (DBMS_STAT_FUNCS).
It provides procedures to do distribution fitting and to summarize numerical data. |
| Source |
$ORACLE_HOME/rdbms/admin/dbmsstts.sql |
| First Available |
10.1 |
| 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 |
|
|
| Security Model |
Crated with AUTHID CURRENT_USER. Execute is granted to PUBLIC. |
| 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
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;
/ |
|
