Aggregate vs Analytic Functions

WHY USE ANALYTIC FUNCTIONS?

• Ability to see one row from another row in the results

• Avoid self-join queries

• Summary data in detail rows

• Slice and dice within the results

What’s the difference?

Aggregate (traditional)

Query often includes the keywords GROUP BY

Output is a single row (or one row per group with GROUP BY)

Analytic

Query often includes the keywords OVER ( some other stuff)

Does not change number of rows

What makes a function  analytic?

• Keyword OVER, followed by set of parentheses

SELECT deptno, ename, sal , COUNT  ( * )  OVER () ,  SUM   ( sal ) OVER ()
FROM scott.emp;

Returns one result for each record in the dataset. No grouping

TWO NEW FUNCTIONS

• LAG
•  LEAD

                Usage: LAG ( field_name, num_recs ) OVER ( )

These function are analytic only

SELECT  deptno, ename, sal , LAG ( ename ) OVER ( ORDER BY ename ) f1,
 LAG ( ename , 2) OVER( ORDER BY ename ) f2, 
 LEAD  ( ename ) OVER( ORDER BY ename DESC) f3,
LAG ( sal ) OVER( ORDER BY ename ) f4
FROM scott.emp
ORDER BY deptno, ename;

THREE NEW FUNCTIONS

• RANK
• DENSE_RANK 
• ROW_NUMBER

Usage: RANK ( ) OVER ( ORDER BY field_name )

WINDOWING :

Below 2 queries returns similar info. Because, first query represents DEFAULT WINDOWING.

select SUM(SAL) OVER ( ORDER BY ENAME ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )  FROM EMP;

select SUM(SAL) over ()
FROM EMP;

ROWS WINDOWING :  Restricts window by records based on ORDER BY

RANGE WINDOWING : Restricts window by a period of time or a value . References field used in ORDER BY

Eg:
select SAL,SUM(SAL) over ( order by SAL rows between 1 preceding and 1 following ) from EMP;select

SAL,SUM(SAL) over ( order by SAL range between 200 preceding and 200 following ) from EMP;