Statistical Analysis

Contact Us or call 1-877-932-8228
Statistical Analysis

Statistical Analysis

Oracle SQL provides an extensive array of statistical capabilities available through functions that can be called in the context of SQL statements. PL/SQL provides even more statistical power through the DBMS_STAT_FUNCS package.

Code Sample:

Specialized-Topics/Demos/summary_statistics_functions.sql
select 
	lpad(TO_CHAR(min(salary),'9,999.99'), 25) as "min", 
	lpad(TO_CHAR(max(salary),'999,999,999.99'), 25) as "max", 
	lpad(TO_CHAR(avg(salary),'9,999.99'), 25) as "avg",
	lpad(TO_CHAR(variance(salary),'999,999,999.99'), 25) as "var",
	lpad(TO_CHAR(stddev(salary),'9,999.99'), 25) as "stddev"
from employees
/

Code Explanation

This demonstration illustrates how to retrieve basic summary statistics using SQL.

Code Sample:

Specialized-Topics/Demos/summary_statistics.sql
DECLARE
 sig   NUMBER := 3; 
 s     dbms_stat_funcs.SummaryType;

BEGIN
  dbms_stat_funcs.summary
          ('HR', 'EMPLOYEES', 'SALARY', sig, s);

  dbms_output.put_line('Min:      ' ||
                       lpad(TO_CHAR(s.min,'9,999.99'), 25));
  dbms_output.put_line('Max:      ' ||
                       lpad(TO_CHAR(s.max,'999,999,999.99'), 25));
  dbms_output.put_line('Mean:     ' ||
                       lpad(TO_CHAR(s.mean,'9,999.99'), 25));
  dbms_output.put_line('Variance: ' ||
                    lpad(TO_CHAR(s.variance,'999,999,999.99'), 25));
  dbms_output.put_line('Std Dev:  ' ||
                       lpad(TO_CHAR(s.stddev,'9,999.99'), 25));
END;
/

Code Explanation

The DBMS_STAT_FUNCS package can be used to retrieve the same information as a single type.

The package goes beyond these simple descriptive calculations. Data can be plotted in a way that suggests a shape: plot of employee salary data There are several procedures in the package that quantify the degree that a data set conforms to an identified distribution.

Next