Included in every Oracle Database is a collection of basic statistical functions accessible via SQL. These include descriptive statistics, hypothesis testing, correlations analysis, test for distribution fits, cross tabs with Chi-square statistics, and analysis of variance (ANOVA). The basic statistical functions are implemented as SQL functions and leverage all the strengths of the Oracle Database. The SQL statistical functions work on Oracle tables and views and exploit all database parallelism, scalability, user privileges and security schemes. Hence the SQL statistical functions can be included and exposed within SQL queries, BI dashboards and embedded in real-time Applications. The SQL statistical functions can be used in a variety of ways. For example, users can call Oracle's SQL statistical functions to obtain mean, max, min, median, mode and standard deviation information for their data; or users can measure the correlations between attributes and measure the strength of relationships using hypothesis testing statistics such as a t-test, f-test or ANOVA. The SQL Aggregate functions return a single result row based on groups of rows, rather than on single rows while the SQL Analytical functions compute an aggregate value based on a group of rows.
SQL statistical functions include:
Correlations analysis (parametric and nonparametric)
Cross Tabulations with Chi-square statistics
Test Distribution fit
Window Aggregate functions
Reporting aggregate functions
STATS_T_TEST_INDEPU Example: The following example determines the significance of the difference between the average sales to men and women where the distributions are known to have significantly different (unpooled) variances:
SELECT SUBSTR(cust_income_level, 1, 22) income_level, AVG(DECODE(cust_gender, 'M', amount_sold, null)) sold_to_men, AVG(DECODE(cust_gender, 'F', amount_sold, null)) sold_to_women, STATS_T_TEST_INDEPU(cust_gender, amount_sold, 'STATISTIC', 'F') t_observed, STATS_T_TEST_INDEPU(cust_gender, amount_sold) two_sided_p_value FROM sh.customers c, sh.sales s WHERE c.cust_id = s.cust_id GROUP BY ROLLUP(cust_income_level) ORDER BY income_level, sold_to_men, sold_to_women, t_observed;
(See link below to SQL Language Reference for STATS_T_TEST_*)
Most statistical software vendors charge license fees for these statistical capabilities. Oracle includes them in every Oracle Database. Users can reduce annual license fees and perform the equivalent basic statistical functionality while keeping big data and analytics simple in a single, unified, consistent, scalable and secure Oracle Database platform. Because the statistical functions are native SQL functions, statistical results can be immediately used across the Oracle stack - unleashing many more opportunities to leverage your results in spontaneous and unexpected ways.
Additionally, Oracle Advanced Analytics'Oracle R Enterprise component exposes the SQL statistical functions through the R statistical programming language and allows R users to use R statistical functions e.g. Summary but then pushes down the R functions to the equivalent SQL statistical functions for avoidance of data movement and significant in-database performance gains. The SQL Developer Oracle Data Miner workflow GUI extension also leverages the SQL statistical functions in the Explore, Graph, SQL Query and Transform nodes.