Statistic Calculation – DBMS_STAT_FUNCS

Currently I’m working on a project dealing with lot of statistic calculations particularly that of quartiles. First, I have no idea what it is. Thanks to Wiki for explanation. Now the tough part is to do the actual calculation. Fortunately, Oracle provides DBMS_STAT_FUNCS which is exactly what I’m looking for.

List of available calculations –

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

Sample –

set serveroutput on;
DECLARE
sfsumtype dbms_stat_funcs.summaryType;
BEGIN
dbms_stat_funcs.summary(p_ownername=>'SCHEMA_NAME',
                     p_tablename=>'TABLE_NAME',
                     p_columnname=>'COLUMN_NAME',
                     s=>sfsumtype);
dbms_output.put_line('Quantile 5   => ' || sfsumtype.quantile_5);
dbms_output.put_line('Quantile 25  => ' || sfsumtype.quantile_25);
dbms_output.put_line('Median       => ' || sfsumtype.median);
dbms_output.put_line('Quantile 75  => ' || sfsumtype.quantile_75);
dbms_output.put_line('Quantile 95  => ' || sfsumtype.quantile_95);
END;
/