Using Analytic Functions in Oracle

Provided by Chaitanya Susarla <chaitanyasusarla at yahoo.com>

This article is just to show some quick yet simple examples of how to use analytic functions in ORACLE. Though It doesn't cover all the analytical functions available, I am just focussing on trivial functions we use in daily sql queries and to understand the way analytics work for them.

All the following examples are tested on ORACLE Version 10.2.0.3.0.

What are analytic functions?
Answer:

(From Oracle documentation of 10g Release2(10.2))

(url: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#sthref965)

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

Do not worry If you don't understand the defintion from Oracle documentation. For any first timers, it is tough to grasp it quickly. After going through the following examples, probably you will have idea of what they are, so that you can revisit the definition and/or URL once again to get complete understanding of how they work and what they are meant for.

Now let us start.....

Pages: 1 · 2 · 3 · 4 · 5 · 6 · 7 · 8 · 9