12c database logo

Oracle Analytical SQL Features and Functions


Included in Oracle Database 12c is a compelling array of analytical features and functions that are accessible through SQL. With each release of its database Oracle continues to expand its support for advanced in-database analytics functionality. This means you can reduce your overall license costs for many types of analytical processing and eliminate the movement and staging of data to external systems to perform analysis. Eliminating data movement and staging increases the security of your data and increases the speed at which you can deliver results.

Highlighted Tips/Articles

red-arrow-box 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.

Key Benefits of Oracle's In-Database SQL Analytics

The key benefits provided by Oracle’s in-database analytical functions and features are:
1. Enhanced Developer Productivity - enable developers to perform complex analyses with much clearer and more concise SQL code. Complex tasks can now be expressed using single SQL statement which is quicker to formulate and maintain, resulting in greater productivity.

2. Improved Query Speed - 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.

3. Improved Manageability - ability to access a consolidated view of all data types and sources is simplified when applications share a common relational environment rather than a mix of calculation engines with incompatible data structures.

4. Minimized Learning Effort - SQL analytic functions minimize the need to learn new keywords because the syntax leverages existing well-understood keywords.

5. Industry standards based syntax - Oracle's features conform to ANSI SQL standard and are supported by a large number of independent software vendors.



Overview

The in-database analytical functions features that are embedded inside the Oracle Database can be used to answer a wide variety of business problems. Developers and business users can access a wide range of analytic features and combine their results with other SQL queries and analytical pipelines to gain deeper insights. Oracle's Database includes the following features:
  • Ranking
  • Windowing
  • Reporting Aggregates
  • LAG/LEAD
  • FIRST/LAST
  • Inverse Percentile
  • Hypothetical Rank and Distribution
  • Pattern Matching
  • Modeling
  • Advanced aggregations
  • User defined functions

Technical Information

red-arrow-box Oracle Database Data Warehousing Guide—SQL Analysis and Reporting Documentation
red-arrow-box Oracle Database SQL Language Reference—T-test Statistical Functions
red-arrow-box Oracle Statistical Functions Overview - Presentation (PDF, 0.6MB)
red-arrow-box Patterns Everywhere - Find then fast! (Whitepaper)
red-arrow-box Patterns Everywhere - Find then fast! (Apple iBook for iPad and iPad Mini)
red-arrow-box SQL - the best development language for Big Data (OOW 2013)
red-arrow-box Analyze This! Analytical Power in SQL, More Than You Ever Dreamed Of (OOW 2012)
red-arrow-box Analytical SQL in Oracle Database 12c (Whitepaper)
red-arrow-box Using pattern matching and spatial analytics to track fraud (OOW 2013 - Keynote demo)

Training

Oracle-by-Example

red-arrow-box OBE: SQL Pattern Matching
red-arrow-box OBE: SQL Model Clause


Oracle Learning Library

red-arrow-box Pattern Matching
red-arrow-box Sessionization with SQL Pattern Matching
   red-arrow-box Video - Sessionization with SQL
red-arrow-box Introduction to SQL Analytics


OTN Virtual Developer Day

red-arrow-box SQL Pattern Matching Workshop
(Webcast, slides and hands-on lab)
red-arrow-box Download Database 12c VM for workshop

Data Sheets

red-arrow-box SQL Analytics Data Sheet

Blogs

  Oracle Data Warehousing and SQL Analytics Blog

Articles

red-arrow-box Oracle Magazine - Technology: SQL 101 by Melanie Caffrey is a senior development manager at Oracle.

Related Technologies

red-arrow-box Oracle BI DW Technology Center  Oracle Advanced Analytics Option
red-arrow-box Oracle OLAP  Oracle Statistics
red-arrow-box SQL Developer red-arrow-box PL/SQL

In-Memory Replay Banner