Oracle Analytic Functions 1.0

This article provides a clear, thorough concept of analytic functions and its various options by a series of simple yet concept building examples. The article is intended for SQL coders, who for might be not be using analytic functions due to unfamiliarity with its cryptic syntax or uncertainty about its logic of operation. Often I see that people tend to reinvent the feature provided by analytic functions by native join and sub-query SQL. This article assumes familiarity with basic Oracle SQL, sub-query, join and group function from the reader. Based on that familiarity, it builds the concept of analytic functions through a series of examples.

It is true that whatever an analytic function does can be done by native SQL, with join and sub-queries. But the same routine done by analytic function is always faster, or at least as fast, when compared to native SQL. Moreover, I am not considering here the amount of time that is spent in coding the native SQLs, testing, debugging and tuning them.

The general syntax of analytic function is:

Function(arg1,…, argn) OVER ( [PARTITION BY <…>] [ORDER BY <….>] [<window_clause.

Example 1:

select customer_id, cp.OUTSTANDING_BALANCE OUTS, ROW_NUMBER() OVER(

ORDER BY cp.OUTSTANDING_BALANCE desc

NULLS LAST) asda

from Customer_Outstanding cp

RESULT

CUSTOMER_ID  OUTS     RO_NUM

000600003501    61649    1

000600003487    61649    2

000600000845    60096    3

000600005052    60096    4

000600004174    60096    5

000600002413    60096    6

 

Example 2:

select BRANCH_ID, BRANCH , COUNT(DISTINCT CUSTOMER_ID) CUST,

  ROW_NUMBER() OVER( ORDER BY COUNT(DISTINCT CUSTOMER_ID) desc NULLS LAST) asda

from Customer_Balance cp

WHERE PRODUCT_ID = 1 — AND ROW_NUMBER() <= 10

GROUP BY BRANCH_ID, BRANCH

RESULT

BRANCH_ID  BRANCH  CUST  ROWNUM

1              Mian Wali-01      4470       1

2              Lahore-01            4146       2

3              Lahore-02            3799       3

4              Kher Pur-01        3663       4

5              Mir Pur-01           3653       5

6              Shikar Pur            3642       6

 

Example 3:

SELECT empno, deptno, sal,

RANK() OVER (PARTITION BY deptno

ORDER BY sal DESC NULLS LAST) RANK,

DENSE_RANK() OVER (PARTITION BY

deptno ORDER BY sal DESC NULLS

LAST) DENSE_RANK

FROM emp

WHERE deptno IN (10, 20)

ORDER BY 2, RANK;

RESULT:

EMP   dpt             SAL    RANK   DENSE_RANK

7839       10           5000       1              1

7782       10           2450       2              2

7934       10           1300       3              3

7788       20           3000       1              1

7902       20           3000       1              1

7566       20           2975       3              2

7876       20           1100       4              3

7369       20           800         5              4

 

Example 4:

SELECT deptno, empno, sal,

LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) NEXT_LOWER_SAL,

LAG(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) PREV_HIGHER_SAL

FROM emp

WHERE deptno IN (10, 20)

ORDER BY deptno, sal DESC;

RESULT:

DEPTNO  EMPNO   SAL NEXT_LOWER_SAL PREV_HIGHER_SAL

——- —— —– ————– —————

     10   7839  5000           2450               0

     10   7782  2450           1300            5000

     10   7934  1300              0            2450

     20   7788  3000           3000               0

     20   7902  3000           2975            3000

     20   7566  2975           1100            3000

     20   7876  1100            800            2975

     20   7369   800              0            1100

 

Example 5:

SELECT empno, deptno, hiredate, hiredate – FIRST_VALUE(hiredate)

OVER (PARTITION BY deptno ORDER BY hiredate) DAY_GAP

FROM emp

WHERE deptno IN (20, 30)

ORDER BY deptno, DAY_GAP;

RESULT:

EMPNO  DEPTNO HIREDATE DAY_GAP

7369  20    12/17/1980  0

7566  20    4/2/1981    106

7902  20    12/3/1981   351

7788  20    4/19/1987   2314

7876  20    5/23/1987   2348

7499  30    2/20/1981   0

7521  30    2/22/1981   2

7698  30    5/1/1981    70

7844  30    9/8/1981    200

7654  30    9/28/1981   220

7900  30    12/3/1981   286

 

Example 7:

SELECT deptno, empno, sal,

Count(*) OVER (PARTITION BY deptno ORDER BY sal RANGE

BETWEEN UNBOUNDED PRECEDING AND (sal/2) PRECEDING) CNT_LT_HALF,

COUNT(*) OVER (PARTITION BY deptno ORDER BY sal RANGE

BETWEEN (sal/2) FOLLOWING AND UNBOUNDED FOLLOWING) CNT_MT_HALF

FROM emp

WHERE deptno IN (20, 30)

ORDER BY deptno, sal

RESULT:

DEPTNO  EMPNO   SAL CNT_LT_HALF CNT_MT_HALF

——- —— —– ———– ———–

     20   7369   800           0           3

     20   7876  1100           0           3

     20   7566  2975           2           0

     20   7788  3000           2           0

     20   7902  3000           2           0

     30   7900   950           0           3

     30   7521  1250           0           1

     30   7654  1250           0           1

     30   7844  1500           0           1

     30   7499  1600           0           1

     30   7698  2850           3           0

 

Advertisements

About Oracle Technology Blogs
Oracle Application Development

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: