Analytic Functions in Oracle 8i and 9i

Overview
Analytic Functions, which have been available since Oracle 8.1.6, are designed to address such problems as "Calculate a running total", "Find percentages within a group", "Top-N queries", "Compute a moving average" and many more. Most of these problems can be solved using standard PL/SQL, however the performance is often not what it should be. Analytic Functions add extensions to the SQL language that not only make these operations easier to code; they make them faster than could be achieved with pure SQL or PL/SQL. These extensions are currently under review by the ANSI SQL committee for inclusion in the SQL specification.
How Analytic Functions Work ?
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.
The Syntax
The Syntax of analytic functions is rather straightforward in appearance
Analytic-Function(,,...)

OVER (

)

Analytic-Function
Specify the name of an analytic function, Oracle actually provides many analytic functions such as AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.
Arguments
Analytic functions take 0 to 3 arguments.
Query-Partition-Clause
The PARTITION BY clause logically breaks a single result set into N groups, according to the criteria set by the partition expressions. The words "partition" and "group" are used synonymously here. The analytic functions are applied to each group independently, they are reset for each group.
Order-By-Clause
The ORDER BY clause specifies how the data is sorted within each group (partition). This will definitely affect the outcome of any analytic function.
Windowing-Clause
The windowing clause gives us a way to define a sliding or anchored window of data, on which the analytic function will operate, within a group. This clause can be used to have the analytic function compute its value based on any arbitrary sliding or anchored window within a group
Example: Calculate a running Total
This example shows the cumulative salary within a departement row by row, with each row including a summation of the prior rows salary.
set autotrace traceonly explainbreak on deptno skip 1column ename format A6column deptno format 999column sal format 99999column seq format 999
SELECT ename "Ename", deptno "Deptno", sal "Sal",

SUM(sal) OVER (ORDER BY deptno, ename) "Running Total", SUM(SAL)

OVER (PARTITION BY deptno ORDER BY ename) "Dept Total", ROW_NUMBER()

OVER (PARTITION BY deptno ORDER BY ENAME) "Seq"

FROM emp

ORDER BY deptno, ename