SQL Treasures.

The reason that I call this topic as treasure is that this feature was introduced in Oracle 8.1.6 ( a whiiiiiile back ) . Not many people are using ( to its fullest extent ) .

You would have guessed it by now . Yeah . It is analytics.

Analytics is one of the greatest additon to the SQL family . Though ,it is documented only in Datawarehousing guide . It is equally important in OLTP application .

It has ranking , reporting , statisticial  functions to name a few.

As an example , let us go to scott schema .

In this post , I will cover few basic analytical SQLs . Some of the analytical constructs has been introduced in SQL Server 2005.

Rest of the analytical functions in the subsequent posts.

Example 1: 

Usuage of ROW_NUMBER function .

This is to return  a running sequence number.

SQL> SELECT empno,
  2         ename,
  3         job,
  4         sal,
  5         deptno,
  6         row_number() over(ORDER BY sal DESC) rn
  7  FROM emp;

EMPNO ENAME      JOB             SAL DEPTNO         RN
----- ---------- --------- --------- ------ ----------
 7839 KING       PRESIDENT   5000.00     10          1
 7902 FORD       ANALYST     3000.00     20          2
 7788 SCOTT      ANALYST     3000.00     20          3
 7566 JONES      MANAGER     2975.00     20          4
 7698 BLAKE      MANAGER     2850.00     30          5
 7782 CLARK      MANAGER     2450.00     10          6
 7499 ALLEN      SALESMAN    1600.00     30          7
 7844 TURNER     SALESMAN    1500.00     30          8
 7934 MILLER     CLERK       1300.00     10          9
 7521 WARD       SALESMAN    1250.00     30         10
 7654 MARTIN     SALESMAN    1250.00     30         11
 7876 ADAMS      CLERK       1100.00     20         12
 7900 JAMES      CLERK        950.00     30         13
 7369 SMITH      CLERK        800.00     20         14

Example 2:

The following example has lot of functions .

1. LEAD and LAG will let us to look at the previous and next records's value .

2. RANK and DENSE_RANK let us to rank the particular record based on the column ( we specify ) . The difference between RANK and DENSE_RANK comes when there is a tie in the column value . DENSE_RANK does not skip the rank , where as the RANK does.

SQL> SELECT empno,
  2         ename,
  3         job,
  4         sal,
  5         deptno,
  6         row_number() over(PARTITION BY deptno ORDER BY sal ASC) rn,
  7         rank() over(PARTITION BY deptno ORDER BY sal ASC) rank,
  8         dense_rank() over(PARTITION BY deptno ORDER BY sal ASC) dense_rank,
  9         lag(sal) over(PARTITION BY deptno ORDER BY sal ASC) previous_sal,
 10         lead(sal) over(PARTITION BY deptno ORDER BY sal ASC) next_sal
 11  FROM emp
 12  ORDER BY deptno, sal
 13  /

EMPNO ENAME      JOB             SAL DEPTNO         RN       RANK DENSE_RANK PREVIOUS_SAL   NEXT_SAL
----- ---------- --------- --------- ------ ---------- ---------- ---------- ------------ ----------
 7934 MILLER     CLERK       1300.00     10          1          1          1                    2450
 7782 CLARK      MANAGER     2450.00     10          2          2          2         1300       5000
 7839 KING       PRESIDENT   5000.00     10          3          3          3         2450
 7369 SMITH      CLERK        800.00     20          1          1          1                    1100
 7876 ADAMS      CLERK       1100.00     20          2          2          2          800       2975
 7566 JONES      MANAGER     2975.00     20          3          3          3         1100       3000
 7788 SCOTT      ANALYST     3000.00     20          4          4          4         2975       3000
 7902 FORD       ANALYST     3000.00     20          5          4          4         3000
 7900 JAMES      CLERK        950.00     30          1          1          1                    1250
 7654 MARTIN     SALESMAN    1250.00     30          2          2          2          950       1250
 7521 WARD       SALESMAN    1250.00     30          3          2          2         1250       1500
 7844 TURNER     SALESMAN    1500.00     30          4          4          3         1250       1600
 7499 ALLEN      SALESMAN    1600.00     30          5          5          4         1500       2850
 7698 BLAKE      MANAGER     2850.00     30          6          6          5         1600

Example 3:

NTILE let us to divide the results in equal height . 

In the following example , the result set is divided into three equal parts . 

I find  NTILE  very useful  in do it yourself (DIY) parallel-zing   jobs . In DIY jobs , you could divvy up the result set and send it to parallel jobs either using DBMS_JOB / DBMS_SCHDULER.

SQL> SELECT empno, ename, job, sal, deptno, ntile(3) over(ORDER BY sal) ntile
  2  FROM emp
  3  ORDER BY sal
  4  /

EMPNO ENAME      JOB             SAL DEPTNO      NTILE
----- ---------- --------- --------- ------ ----------
 7369 SMITH      CLERK        800.00     20          1
 7900 JAMES      CLERK        950.00     30          1
 7876 ADAMS      CLERK       1100.00     20          1
 7521 WARD       SALESMAN    1250.00     30          1
 7654 MARTIN     SALESMAN    1250.00     30          1
 7934 MILLER     CLERK       1300.00     10          2
 7844 TURNER     SALESMAN    1500.00     30          2
 7499 ALLEN      SALESMAN    1600.00     30          2
 7782 CLARK      MANAGER     2450.00     10          2
 7698 BLAKE      MANAGER     2850.00     30          2
 7566 JONES      MANAGER     2975.00     20          3
 7788 SCOTT      ANALYST     3000.00     20          3
 7902 FORD       ANALYST     3000.00     20          3
 7839 KING       PRESIDENT   5000.00     10          3

Example 4: 

The first look at the following example may be little bit intimidating . If you look at the second example , it may be clear .

In the latter example , we get the ratio of the salaries in DEPT 10 . The total of sal is 8750 , out of which CLARK's salary is 2450 ( 28% of the department's total salary ).

It helped us in one scenario , where the requirement was to save the report into an excel spreadsheet format .
This report was developed using PowerBuilder . In PowerBuilder , we can save the contents of the datawindow into an excel spreadsheet . The developer has done in the ratio calculations in the front end ( computed column in PB lingo ) . When the data window was saved as an excel , the calculation did not make into an excel ( as only  the result set of  the SQL was saved ) . In this case , the following function came in handy.

SQL> SELECT empno,
  2         ename,
  3         job,
  4         deptno,
  5         sal,
  6         ratio_to_report(sal) over() rr_whole,
  7         ratio_to_report(sal) over(PARTITION BY deptno) rr_deptno
  8  FROM emp
  9  /

EMPNO ENAME      JOB       DEPTNO       SAL   RR_WHOLE  RR_DEPTNO
----- ---------- --------- ------ --------- ---------- ----------
 7782 CLARK      MANAGER       10   2450.00 0.08440999       0.28
 7839 KING       PRESIDENT     10   5000.00 0.17226528 0.57142857
 7934 MILLER     CLERK         10   1300.00 0.04478897 0.14857142
 7566 JONES      MANAGER       20   2975.00 0.10249784 0.27356321
 7902 FORD       ANALYST       20   3000.00 0.10335917 0.27586206
 7876 ADAMS      CLERK         20   1100.00 0.03789836 0.10114942
 7369 SMITH      CLERK         20    800.00 0.02756244 0.07356321
 7788 SCOTT      ANALYST       20   3000.00 0.10335917 0.27586206
 7521 WARD       SALESMAN      30   1250.00 0.04306632 0.13297872
 7844 TURNER     SALESMAN      30   1500.00 0.05167958 0.15957446
 7499 ALLEN      SALESMAN      30   1600.00 0.05512489 0.17021276
 7900 JAMES      CLERK         30    950.00 0.03273040 0.10106382
 7698 BLAKE      MANAGER       30   2850.00 0.09819121 0.30319148
 7654 MARTIN     SALESMAN      30   1250.00 0.04306632 0.13297872



SQL> SELECT empno,
  2         ename,
  3         job,
  4         deptno,
  5         sal,
  6         ratio_to_report(sal) over(PARTITION BY deptno) rr_deptno
  7  FROM emp
  8  Where deptno = 10
  9  /

EMPNO ENAME      JOB       DEPTNO       SAL  RR_DEPTNO
----- ---------- --------- ------ --------- ----------
 7782 CLARK      MANAGER       10   2450.00       0.28
 7839 KING       PRESIDENT     10   5000.00 0.57142857
 7934 MILLER     CLERK         10   1300.00 0.14857142

Transposing the data

Before Oracle 11g and SQL Server 2005 , transposing the data was bit cumbersome .

Transpong the data - if we want to transpose the columns into rows .

For example , if a person has multiple phone numbers and if we want to show the phone numbers on one record ... we could use PIVOT function .

To transpose rows into columns , we would use UNPIVOT .

SQL> Create table person
2 ( personid integer ,
3 telephonenumber varchar2(50) ) ;
Table created


SQL> insert into person values ( 100015 , 2484775248);
1 row inserted

SQL> insert into person values( 100015 , 9085014258);
1 row inserted

SQL> insert into person values( 100015 , 2015579964);
1 row inserted


SQL> insert into person values( 100010 , 7328225687);
1 row inserted


SQL> insert into person values( 100010 , 5328225687);


1 row inserted

Before 11g ...

SELECT personid ,
MAX(DECODE( rn ,1 , telephonenumber)) Phone_1 ,
MAX(DECODE( rn ,2 , telephonenumber)) Phone_2 ,
MAX(DECODE( rn ,3 , telephonenumber) ) Phone_3
FROM
(SELECT personid ,
telephonenumber ,
row_number() over ( partition BY personid order by personid) rn
FROM person
WHERE personid IN (100015 , 100010)
)
GROUP BY personid
 
 
In Oracle 11g / SQL Server 2005 .
 
SQL> SELECT *

2 FROM (SELECT personid , telephonenumber , row_number() over ( partition by personid order by personid) rn
3 FROM person
4 WHERE personid IN (100015 , 100010
5 ) )pivot(max(telephonenumber) for rn IN (1 ,2 , 3 ))
6 /




PERSONID 1 2 3


--------------------------------------- -------------------------

100010 7328225687 5328225687


100015 9085014258 2484775248 2015579964


In the above SQL , we know the maximum number of the columns to pivoted before hand.
If the number of columns ( to be pivoted) is dynamic , you would use PIVOT ... for XML.

Analytics 2.0

Jumping ahead from basic analytics to analytics 2.0 ( new feature in 11g R2) 

One simple yet  neat , nicer addition to analytics family in 11g R2.
There is a new analytic function called LISTAGG  , where I could concatenate the list of the data values in a
particular group .

Prior to this release , we would have used hierarchical  query ... SYS_PATH to a achieve this result set.


Let us proceed with a simple example


SQL> Create table  t as select * from all_objects;

Table created

SQL>
SQL>         Select owner , object_type , listagg(object_name , '~')
  2           within group
  3           (order by object_name )
  4           from t
  5           where owner in ('SCOTT' , 'OUTLN')
  6           group by owner , object_type
  7           order by object_type
  8  ;

OWNER                          OBJECT_TYPE         LISTAGG(OBJECT_NAME,'~')WITHIN
------------------------------ ------------------- --------------------------------------------------------------------------------
OUTLN                          INDEX               OL$HNT_NUM~OL$NAME~OL$NODE_OL_NAME~OL$SIGNATURE
SCOTT                          INDEX               PK_DEPT~PK_EMP
OUTLN                          PROCEDURE           ORA$GRANT_SYS_SELECT
OUTLN                          TABLE               OL$~OL$HINTS~OL$NODES
SCOTT                          TABLE               BONUS~DEPT~EMP~SALGRADE


Note : ~ is the delimiter

Bear in mind , the result set is of datatype varchar2( unless otherwise the columns defined in the group are RAW) , so you may be hit the limitation of varchar2(4000).

SQL>        Select owner , object_type , listagg(object_name , '~')
  2           within group
  3           (order by object_name )
  4           from t
  5           where owner in ('SCOTT' , 'SYS')
  6           group by owner , object_type
  7           order by object_type
  8  ;

Select owner , object_type , listagg(object_name , '~')
         within group
         (order by object_name )
         from t
         where owner in ('SCOTT' , 'SYS')
         group by owner , object_type
         order by object_type

ORA-01489: result of string concatenation is too long

Have a good time with analytcs.
Please refer to http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm for additional informaiton .