Oracle Analytic Function

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. 

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.

In some cases an analytic function can run a hundred times faster than regular SQL that does the analytic with a subquery :-- 

 http://www.dba-oracle.com/t_OracleAnalyticFunctions.htm

 ********************************  
 Using Analytic Function

  ********************************

select    id,
    last_name,
    salary,
    sum(salary) over (order by id) cume_salary
from
    emp
    order by id;   Using AF: 0.84 seconds
Statistics
----------------------------
           1  recursive calls           0  db block gets
         290  consistent gets           0  physical reads
           0  redo size

 ********************************
-- Using regular SQL
********************************

select
    e1.id,
    e1.last_name,
    e1.salary,
   (select   sum(salary)   from   emp   where  id <= e1.id)    AS cume_salary
from
    emp e1
    order by id;

-- ********************************
-- Using regular SQL: over 8 minutes
-- ********************************

Statistics
----------------------------
       1  recursive calls
       0  db block gets
11200335  consistent gets –over 38,000 times as many
     255  physical reads
       0  redo size

Analytic Example

The  example makes the same calculation as the previous Aggregate example but returns the result for each employee within the department:

SELECT last_name, department_id, salary,
   MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
      OVER (PARTITION BY department_id) "Worst",
   MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
      OVER (PARTITION BY department_id) "Best"
    FROM employees
    ORDER BY department_id, salary;

LAST_NAME           DEPARTMENT_ID     SALARY      Worst       Best
------------------- ------------- ---------- ---------- ----------
Whalen                         10       4400       4400       4400
Fay                            20       6000       6000      13000
Hartstein                      20      13000       6000      13000
. . .
Gietz                         110       8300       8300      12000
Higgins                       110      12000       8300      12000
Grant                                   7000       7000       7000