 Oracle Analytical SQL and Statistical Functions
Included in Oracle Database 12c is a compelling array of analytical capabilities and statistical functions accessible from through SQL. These include descriptive statistics, hypothesis testing, correlations analysis, test for distribution fit, cross tabs with Chi-square statistics, and analysis of variance (ANOVA). Oracle continues to expand its support for advanced analytics functionality in the database.
Highlighted Tips/Articles |  | SQL Pattern Matching
The new groundbreaking SQL pattern matching capabilities brings the simplicity and efficiency of the most common data analysis language to the process of identifying patterns within a data set. It provides a completely new native SQL syntax that has adopted the regular expression capabilities of Perl by implementing a core set of rules to define patterns in sequences (streams of rows). Learn about this exciting new functionality here. | Overview
The statistical functions in the database can be used in a variety of ways, for example, users can call Oracle's DBMS_STAT_FUNCS to obtain basic count, mean, max, min and standard deviation information for their dataset; or users can determine the strength of relationships using hypothesis testing statistics such as a t-test, f-test or ANOVA. Users are able to not only complete a wide range of statistics, but also include these results in more advanced SQL queries and analytical pipelines. Features include: - Ranking
- Windowing
- Reporting
- Linear Regression
- LAG/LEAD
- FIRST/LAST
- Inverse Percentile
- Hypothetical Rank and Distribution
- Pattern Matching
- Modeling
| - Descriptive statistics
- Hypothesis testing
- Correlations analysis (parametric and nonparametric)
- Cross Tabulations with Chi-square statistics
- ANOVA
- Test Distribution fit
- Pivoting Operations
- User Defined Aggregates
- Linear Algebra
| Unlike statistical software vendors, which charge separate license fees for these statistical capabilities, Oracle Database 12c includes this wide feature set with the database license. This means you can reduce your overall license costs for statistical analysis and eliminate the movement and staging of data to external systems to perform statistical analytics. Eliminating data movement and staging provides two core benefits: 1) it increases security since copies of the data need not reside in insecure environments and 2) it decreases time to results since the overhead associated with moving the data is eliminated. Because the statistical results are immediately available through SQL and integrated in Oracle Database, they can be immediately used across the entire Oracle stack - unleashing many more opportunities to leverage your results in spontaneous and unexpected ways.
The Oracle Advanced Analytics Option provides a combination of Oracle Data Mining and Oracle R Enterprise. Oracle Data Mining leverages the SQL based statistical functions through the Oracle Data Miner graphical user interface (GUI) extension to SQL Developer 3.1. R Enterprise also exposes the SQL based statistical functions through the open source R statistical programming language and pushes down computations to the database for significant performance gains. |
 Key Benefits of Oracle's In-Database SQL Analytics Here are key benefits provided by Oracle's in-database SQL functions: - Improved Query Speed - The processing optimizations supported by in-database analytics enable significantly better query performance. Actions which before required self-joins or complex procedural processing may now be performed in native SQL.
- Enhanced Developer Productivity - The functions enable developers to perform complex analyses with much clearer and more concise SQL code. Tasks which in the past required multiple SQL statements or the use of procedural languages can now be expressed using single SQL statements. The new SQL is quicker to formulate and maintain than the older approaches, resulting in greater productivity.
- Minimized Learning Effort - Through careful syntax design, the analytic functions minimize the need to learn new keywords.The syntax leverages existing aggregate functions, such as SUM and AVG, so that these well-understood keywords can be used in extended ways.
- Standardized Syntax - As part of the ANSI SQL standard and Oracle is working with vendors of query, reporting and OLAP products to assist them in exploiting analytic functions. In the past, several database vendors have offered proprietary extensions in the same areas as these functions. However, those extensions did not achieve major market share, and few software vendors adjusted their products to support them. In contrast, oracle's in-database analytic functions will be supported by a large number of independent software vendors.
|
Database
Printer View