1 package dbms_stat_funcs authid current_user is
2
3 TYPE n_arr IS VARRAY(5) of NUMBER;
4 TYPE num_table IS TABLE of NUMBER;
5
6 TYPE summaryType IS RECORD (
7 count NUMBER,
8 min NUMBER,
9 max NUMBER,
10 range NUMBER,
11 mean NUMBER,
12 cmode num_table,
13 variance NUMBER,
14 stddev NUMBER,
15 quantile_5 NUMBER,
16 quantile_25 NUMBER,
17 median NUMBER,
18 quantile_75 NUMBER,
19 quantile_95 NUMBER,
20 plus_x_sigma NUMBER,
21 minus_x_sigma NUMBER,
22 extreme_values num_table,
23 top_5_values n_arr,
24 bottom_5_values n_arr);
25
26 -----------------------------------------------------------------------
27 -- SUMMARY
28 -- Summarizes a numerical column of a table.
29 -- The input parameters are the table name, the column name and the
30 -- name of the owner of the table, as well as the sigma value, which
31 -- defaults to 3.
32 -- The output is a record of type summaryType, which contains the
33 -- following information for the set of numbers in the column: the
34 -- count, minimum value, maximum value, range, mean, mode, variance,
35 -- standard deviation, five quantile values (5%, 25%, 50% or median,
36 -- 75% and 95%), the value of the mean plus (and minus) sigma times
37 -- the standard deviation, the set of extreme values (defined as the
38 -- ones that fall outside of the plus/minus sigma values), the top 5
39 -- and the bottom 5 values.
40 -----------------------------------------------------------------------
41 procedure SUMMARY(p_ownername IN varchar2,
42 p_tablename IN varchar2,
43 p_columnname IN varchar2,
44 p_sigma_value IN number := 3,
45 s OUT NOCOPY SummaryType);
46
47 -----------------------------------------------------------------------
48 -- NORMAL_DIST_FIT
49 -- Calculates how well the data in the input column fits a normal
50 -- distribution. The p_test_type input parameter can be one of the
51 -- following: 'SHAPIRO_WILKS' (the default), 'KOLMOGOROV_SMIRNOV',
52 -- 'ANDERSON_DARLING' or 'CHI_SQUARED'. The output of this procedure
53 -- is the significance of the fit.
54 -- mean is the location parameter
55 -- stddev is the scale parameter
56 -----------------------------------------------------------------------
57 procedure NORMAL_DIST_FIT(
58 ownername IN varchar2,
59 tablename IN varchar2,
60 columnname IN varchar2,
61 test_type IN varchar2 DEFAULT 'SHAPIRO_WILKS',
62 mean IN OUT number,
63 stdev IN OUT number,
64 sig OUT number);
65
66 -----------------------------------------------------------------------
67 -- UNIFORM_DIST_FIT
68 -- Calculates how well the data in the input column fits a uniform
69 -- distribution. The p_test_type input parameter can be one of the
70 -- following: 'KOLMOGOROV_SMIRNOV' (the default), 'ANDERSON_DARLING'
71 -- or 'CHI_SQUARED'. The var_type input parameter can be
72 -- 'CONTINUOUS' (the default) or 'DISCRETE'. The output of this
73 -- procedure is the significance of the fit.
74 -- paramA is location parameter
75 -- paramB is scale parameter
76 -- For the continuous case, the probability density function
77 -- is f(x)= 1 / (B - A) and the cumulative distribution function
78 -- is F(x) = (x - A) / (B - A)
79 -----------------------------------------------------------------------
80 procedure UNIFORM_DIST_FIT(
81 ownername IN varchar2,
82 tablename IN varchar2,
83 columnname IN varchar2,
84 var_type IN varchar2 DEFAULT 'CONTINUOUS',
85 test_type IN varchar2 DEFAULT 'KOLMOGOROV_SMIRNOV',
86 paramA IN OUT number,
87 paramB IN OUT number,
88 sig OUT number);
89
90 -----------------------------------------------------------------------
91 -- POISSON_DIST_FIT
92 -- Calculates how well the data in the input column fits a Poisson
93 -- distribution. The p_test_type input parameter can be one of the
94 -- following: 'KOLMOGOROV_SMIRNOV' (the default) or 'ANDERSON_DARLING'
95 -- The output of this procedure is the significance of the fit.
96 -- lambda is the shape parameter.
97 -----------------------------------------------------------------------
98 procedure POISSON_DIST_FIT(
99 ownername IN varchar2,
100 tablename IN varchar2,
101 columnname IN varchar2,
102 test_type IN varchar2 DEFAULT 'KOLMOGOROV_SMIRNOV',
103 lambda IN OUT number,
104 sig OUT number);
105
106 -----------------------------------------------------------------------
107 -- WEIBULL_DIST_FIT
108 -- Calculates how well the data in the input column fits a Weibull
109 -- distribution. The p_test_type input parameter can be one of the
110 -- following: 'KOLMOGOROV_SMIRNOV' (the default), 'ANDERSON_DARLING'
111 -- or 'CHI_SQUARED'. The output of this procedure is the significance
112 -- of the fit.
113 -- alpha is the scale parameter.
114 -- mu is the location parameter.
115 -- beta is the slope/shape parameter.
116 -----------------------------------------------------------------------
117 procedure WEIBULL_DIST_FIT(
118 ownername IN varchar2,
119 tablename IN varchar2,
120 columnname IN varchar2,
121 test_type IN varchar2 DEFAULT 'KOLMOGOROV_SMIRNOV',
122 alpha IN OUT number,
123 mu IN OUT number,
124 beta IN OUT number,
125 sig OUT number);
126
127 -----------------------------------------------------------------------
128 -- EXPONENTIAL_DIST_FIT
129 -- Calculates how well the data in the input column fits an exponential
130 -- distribution. The p_test_type input parameter can be one of the
131 -- following: 'KOLMOGOROV_SMIRNOV' (the default), 'ANDERSON_DARLING'
132 -- or 'CHI_SQUARED'. The output of this procedure is the significance
133 -- of the fit.
134 -- lambda is the scale parameter
135 -- mu is the location parameter
136 -----------------------------------------------------------------------
137 procedure EXPONENTIAL_DIST_FIT(
138 ownername IN varchar2,
139 tablename IN varchar2,
140 columnname IN varchar2,
141 test_type IN varchar2 DEFAULT 'KOLMOGOROV_SMIRNOV',
142 lambda IN OUT number,
143 mu IN OUT number,
144 sig OUT number);
145
146 end;