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 New Approximate Count Distinct Function
Oracle Database now includes a new and optimized SQL function, APPROX_COUNT_DISTINCT().

The need to count distinct values is a common operation in today's data analysis. Optimizing the processing time and resource consumption by orders of magnitude while providing almost exact results speeds up any existing processing and enables new levels of analytical insight. For more information please refer to the Oracle Database SQL Language Reference, click 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.


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
  • Inverse Percentile
  • Hypothetical Rank and Distribution
  • Pattern Matching
  • Modeling
  • Advanced aggregations
  • User defined functions

Technical Information

red-arrow-box SQL — the natural language for analysis (Whitepaper)
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)



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
red-arrow-box Database X-Series - SQL Pattern Matching

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


  Oracle Data Warehousing and SQL Analytics Blog


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

Database 12c Innovation Online Forum RHS banner