Other Statistical Functions - Data Warehousing

Oracle introduces a set of SQL statistical functions and a statistics package, DBMS_STAT_FUNCS. This section lists some of the new functions along with basic syntax. See PL/SQL Packages and Types Reference for detailed information about the DBMS_STAT_FUNCS package and Oracle Database SQL Reference for syntax and semantics.

Descriptive Statistics
You can calculate the following descriptive statistics:

  • Median of a Data Set
  • Median (expr) [OVER (query_partition_clause)]

  • Mode of a Data Set

STATS_MODE (expr)

Hypothesis Testing - Parametric Tests
You can calculate the following descriptive statistics:

  • One-Sample T-Test
  • STATS_T_TEST_ONE (expr1, expr2 (a constant) [, return_value])

  • Paired-Samples T-Test
  • STATS_T_TEST_PAIRED (expr1, expr2 [, return_value])

  • Independent-Samples T-Test. Pooled Variances
  • STATS_T_TEST_INDEP (expr1, expr2 [, return_value])

  • Independent-Samples T-Test, Unpooled Variances
  • STATS_T_TEST_INDEPU (expr1, expr2 [, return_value])

  • The F-Test
  • STATS_F_TEST (expr1, expr2 [, return_value])

  • One-Way ANOVA
  • STATS_ONE_WAY_ANOVA (expr1, expr2 [, return_value])

Crosstab Statistics
You can calculate crosstab statistics using the following syntax:

STATS_CROSSTAB (expr1, expr2 [, return_value])

Can return any one of the following:

  • Observed value of chi-squared
  • Significance of observed chi-squared
  • Degree of freedom for chi-squared
  • Phi coefficient, Cramer's V statistic
  • Contingency coefficient
  • Cohen's Kappa

Hypothesis Testing - Non-Parametric Tests
You can calculate hypothesis statistics using the following syntax:

STATS_BINOMIAL_TEST (expr1, expr2, p [, return_value])

  • Binomial Test/Wilcoxon Signed Ranks Test
  • STATS_WSR_TEST (expr1, expr2 [, return_value])

  • Mann-Whitney Test
  • STATS_MW_TEST (expr1, expr2 [, return_value])

  • Kolmogorov-Smirnov Test
  • STATS_KS_TEST (expr1, expr2 [, return_value])

Non-Parametric Correlation
You can calculate the following parametric statistics:

  • Spearman's rho Coefficient
  • CORR_S (expr1, expr2 [, return_value])

  • Kendall's tau-b Coefficient
  • CORR_K (expr1, expr2 [, return_value])

In addition to the functions, this release has a new PL/SQL package, DBMS_STAT_FUNCS. It contains the descriptive statistical function SUMMARY along with functions to support distribution fitting. The SUMMARY function summarizes a numerical column of a table with a variety of descriptive statistics. The five distribution fitting functions support normal, uniform, Weibull, Poisson, and exponential distributions.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Data Warehousing Topics