Home » Databases » Oracle Analytic Functions

 
 

Oracle Analytic Functions

 

Oracle analytic functions compute an aggregate value based on a group of rows and provide the ability to reference values across multi-level aggregation and granular control of sort order in a subset of data.

OLAP queries perform multidimensional aggregation to support business decision-making processes. It is very important to make these statements maximally efficient. Conventional SQL queries usually perform multiple self-joins, leading to poor performance. Moreover, we often need aggregations at various levels, with aggregated and non-aggregated rows in the result set. Analytic functions return these both values without any self-joins. Window functions enable calculating cumulative and moving aggregates returning multiple rows for each group.

Understanding analytic functions will give you a new way of thinking and allow tuning complex SQL statements to make them highly efficient.
Oracle analytic functions video tutorial. 3 lessons, total length 39 minutes.

  1. Oracle Partition by clause (09:10)
    analytic function syntax, over (), over partition by, order by, nulls first, nulls last, row_number
    buy single lesson
  2. Oracle analytic functions (18:30)
    group by grouping sets, group by cube, group by rollup, over partition by, within group, accumulative sum, functions: rank, dense_rank, cume_dist, percent rank, ratio_to report, covar_pop, ntile, stddev, variance
    buy single lesson
  3. Window functions and performance comparison (11:20)
    windowing functions, unbounded preceding, unbounded following, current row, rows between, range between, first_value, last_value, lead function, lag function
    buy single lesson

Price 9.00 USD

Frequently Asked Questions

Free lesson:

Tags: , ,