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);
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);