TheGROUPING_IDfunction returns a 0 or 1 when given a single column. (In this case,it works just like theGROUPINGfunction.) If the return value is 0,indicating a detail record value for that particular column,then the column in question was not aggregated over (was not collapsed). If the function returns 1,then the column in question was aggregated over—any aggregates in theSELECTlist would have been computed over that entire column's set of values.GROUPING_IDdiffers fromGROUPING,in that you can send a list of columns and theGROUPING_IDfunction will treat the list as bits and return a decimal number. That means that the call toGROUPING_ID(a,b,c)might return any number between 0 and 7,because different 0/1 combinations are returned. Given that fact,we can use aCASEstatement in the query to see if the row is a detail row forDEPTNO,forJOB,for neither,or for both.

UsingGROUPING SETSin Listing 1,we asked forGROUP BYonly onDEPTNOand then only onJOB. So,that one query was like running the following query

select deptno,null,sum(sal) 
from emp group by deptno
union all
select null,job,sum(sal) 
from emp group by job;

Code Listing 1:Using GROUPING_ID with GROUPING SETS

. . . but without having to make two passes on theEMPtable,as would be the case with theUNION ALL.

In looking at the columns involved in the query in Listing 1,we can see that the functionGROUPING(column_name)shows us when a column is aggregated over or preserved as a detail record. WhenGROUPING(deptno) = 0,DEPTNOis preserved in the output. When it is 1,it is aggregated over. However,we have two columns in this set we are aggregating by,for a total of four possible 0/1 combinations. (In this query,only two are possible.) Using theGROUPING_IDfunction on this vector of columns,we can easily see what each row represents. I've also included the alternative,more verbose way to accomplish this—theBIN_TO_NUM()function,to which we can send a list of 0s and 1s and get back a decimal number as well. I'm pretty sure you'll agree thatGROUPING_ID(c1,c2,c3)is easier than the correspondingBIN_TO_NUMcall with threeGROUPINGcalls.

In Listing 2,we take a look atGROUP BY ROLLUP. A rollup by the two columnsDEPTNOandJOBwill produce

1. Detail records byDEPTNO,JOB(sum ofSALfor eachDEPTNO/JOBcombination).
2. A summary record for eachDEPTNOoverJOB(like a subtotal).
3. A summary record overDEPTNOandJOB—a single aggregate for the entire result. Listing 2 shows the query and the results.

Code Listing 2:Using GROUPING_ID with GROUP BY ROLLUP

A rollup is sort of like a running total report,andGROUPING_IDtells us when the rollups happened. So the data is sorted byDEPTNO,JOB,and we have subtotals byDEPTNO(aggregated overJOB) and byDEPTNO,JOB(aggregated over both) along with the details byDEPTNO/JOB.

As you can see in Listing 2,theGROUPING_IDfunction was useful in telling us when we were dealing with a rolled-up record and the level of detail we could expect in that record.

Last,we'll look atGROUP BY CUBE. CUBEis similar toROLLUP,in that you get the same three record types as shown in Listing 2 but also get all possible aggregations.CUBEgrouping byDEPTNOandJOBwill give you records by all of the following:

You get every possible aggregate. Listing 3 shows the Syntax and output and how to use theGROUPING_IDfunction to see what the level of detail is for each row. It is interesting to note thatGROUP BY CUBEproduces a superset of the rows we observed in the first query (in Listing 1). You could useGROUPING_IDwithCUBEto generate the same result set as the original grouping sets query. That is,adding

having (grouping_id(deptno,job)=2  
    or (grouping_id(deptno,job)=1 

. . . to theGROUP BY CUBEquery would cause it to be the logical equivalent of theGROUPING SETSquery. But you shouldn't do that! If you need only some of the aggregates,useGROUPING SETSto get just the ones you need computed and avoid computing the others altogether. It would be fair to say thatGROUPING_IDdoesn't avoid multiple grouping functions butGROUPING SETSdoes. However,GROUPING_IDplays an important role in seeing what data is what.

Code Listing 3:Using GROUPING_ID with GROUP BY CUBE

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 explain
break on deptno skip 1
column ename format A6
column deptno format 999
column sal format 99999
column 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
/

Ename Deptno Sal Running Total Dept Total Seq
------ ------ ------ ------------- ---------- ----
CLARK 10 2450 2450 2450 1
KING 5000 7450 7450 2
MILLER 1300 8750 8750 3

ADAMS 20 1100 9850 1100 1
FORD 3000 12850 4100 2
JONES 2975 15825 7075 3
SCOTT 3000 18825 10075 4
SMITH 800 19625 10875 5

ALLEN 30 1600 21225 1600 1
BLAKE 2850 24075 4450 2
JAMES 950 25025 5400 3
MARTIN 1250 26275 6650 4
TURNER 1500 27775 8150 5
WARD 1250 29025 9400 6

Execution Plan
---------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   WINDOW (SORT)
   2    1     TABLE ACCESS (FULL) OF 'EMP'
Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1658  bytes sent via sql*Net to client
        503  bytes received via sql*Net from client
          2  sql*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

The example shows how to calculate a "Running Total"for the entire query. This is done using the entire ordered result set,viaSUM(sal) OVER (ORDER BY deptno,ename).
Further,we were able to compute a running totalwithin each department,a total that would be reset at the beginning of the next department. ThePARTITION BY deptnoin thatSUM(sal)caused this to happen,a partitioning clause was specified in the query in order to break the data up into groups.
TheROW_NUMBER()function is used tosequentially number the rowsreturned ineachgroup,according to our ordering criteria (a "Seq" column was added to in order to display this position).
The execution plan shows,that the whole query is very well performed with only 3 consistent gets,this can never be accomplished with standard sql or even PL/sql.

Top-N Queries

How can we get the Top-N records by some set of fields ?
Prior to having access to these analytic functions,questions of this nature were extremely difficult to answer. There are some problems with Top-N queries however; mostly in the way people phrase them. It is something to be careful about when designing reports. Consider this seemingly sensible request:
I would like the top three paid sales reps by department

Using the "traditional approach you can perform:
select * from
(your_query)
where rownum <= 10;

The problem with this question is that it is ambiguous because of repeated values,there might be four people who all make the same salary,what should we do then ?
Let's look at three examples,all use the well known table EMP.

Example 1

Let's look at what ROW_NUMBER can do. Here is an example query using ROW_NUMBER to assign an increasing number to each row in the EMP table after sorting by SAL DESC:

select ename,row_number() over (order by sal desc) rn
  from emp
  order by sal desc;

ENAME    SAL    RN
-----   ----    --  
 KING   5000     1
 FORD   3000     2
SCOTT   3000     3
JONES   2975     4
 .
 .
 .
 JAMES    950   13
 SMITH    800   14

I can apply a predicate to ROW_NUMBER after it is assigned. For example

select * from (select ename,row_number() over (order by sal desc) rn
                from emp)
  where rn <= 3
  order by sal desc;

ENAME    SAL    RN
-----   ----    --  
 KING   5000     1
SCOTT   3000     2
 FORD   3000     3

So,that demonstrates how to perform a top-nquery by using ROW_NUMBER and also points out a general issue with top-nqueries. If you look at that result,you see two rows with the value 3000. What if,in the EMP table,three people,instead of just two,had a salary of 3000? The result obtained by the above query would be ambiguous—I would get three records,but the records I retrieved would be somewhat random.We will analyze that on the example 2:

Another Example:
Sort the sales people by salary from greatest to least. Give the first three rows. If there are less then three people in a department,this will return less than three records.

set autotrace on explain
break on deptno skip 1

SELECT * FROM (SELECT deptno,ename,ROW_NUMBER()
OVER (PARTITION BY deptno
ORDER BY sal DESC)
Top3 FROM emp)
WHERE Top3 <= 3;

DEPTNO ENAME SAL TOP3
---------- ---------- ---------- ----------
10 KING 5000 1
CLARK 2450 2
MILLER 1300 3

20 SCOTT 3000 1
FORD 3000 2
JONES 2975 3

30 BLAKE 2850 1
ALLEN 1600 2
TURNER 1500 3

This query works by sorting each partition (or group,which is the deptno),in a descending order,based on the salary column and then assigning a sequential row number to each row in the group as it is processed. The use of a WHERE clause after doing this to get just the first three rows in each partition.

Example 2

Bearing this in mind,I can use other analytic functions to remove the ambiguity from example 1. They will do so,but the analytic functions might return more thannrows. In my opinion,when the attribute I order by is not unique,I want my query to return all of the relevant records—not just the firstnarbitrary ones. To that end,I can use the RANK and DENSE_RANK analytic functions. Let's take a look at what they do:

select ename,row_number() over (order by sal desc)rn,rank() over (order by sal desc)rnk,dense_rank() over (order by sal desc)drnk
   from emp
  order by sal desc;

ENAME    SAL   RN   RNK   DRNK
-----   ----   --   ---   ----
 KING   5000    1     1      1
 FORD   3000    2     2      2
SCOTT   3000    3     2      2
JONES   2975    4     4      3
BLAKE   2850    5     5      4
CLARK   2450    6     6      5
 .
 .
 .

The main things to note here are the following:

• ROW_NUMBER assigns contiguous,unique numbers from 1..Nto a result set.

RANK does not assign unique numbers—FORD and SCOTT tied for second place—nor does it assign contiguous numbers. No record was assigned the value of 3,because two people tied for second place,and no one came in third,according to RANK. DENSE_RANK,like RANK,does not assign unique numbers,but it does assign contiguous numbers. Even though two records tied for second place,there is a third-place record.

You can use RANK and DENSE_RANK in the same way you would use ROW_NUMBER to restrict the number of rows returned,but obvIoUsly you'll get subtly different results. For example

select * from (select ename,dense_rank() over (order by sal desc)drnk
                 from emp) 
  where drnk <= 3
  order by sal desc;

ENAME    SAL   DRNK   
-----   ----   ---- 
 KING   5000      1
SCOTT   3000      2
 FORD   3000      2
JONES   2975      3

That query returns "the set of people who make the top three salaries," which is likely the desired result. Getting the first three records from EMP after sorting by SAL is rather arbitrary,because using exactly the same set of data,simply inserted in different orders,you could observe different result sets with ROW_NUMBER (because SAL is not unique). Using DENSE_RANK,however,I don't get precisely three records but,instead,a repeatable (deterministic) result set. And I suspect that I retrieve the set the end user really meant to retrieve—the set of people making the top three salaries.

Another Example:
Give me the set of sales people who make the top 3 salaries - that is,find the set of distinct salary amounts,sort them,take the largest three,and give me everyone who makes one of those values.

SELECT * FROM (SELECT deptno,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal desc)
TopN FROM emp)
WHERE TopN <= 3
ORDER BY deptno,sal DESC;

DEPTNO ENAME SAL TOPN
---------- ---------- ---------- ----------
10 KING 5000 1
CLARK 2450 2
MILLER 1300 3

20 SCOTT 3000 1 <--- !
FORD 3000 1 <--- !
JONES 2975 2
ADAMS 1100 3

30 BLAKE 2850 1
ALLEN 1600 2
30 TURNER 1500 3

Here theDENSE_RANKfunction was used to get the top three salaries. We assigned the dense rank to the salary column and sorted it in a descending order.
The DENSE_RANK function computes the rank of a row in an ordered group of rows. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank.
The DENSE_RANK function does not skip numbers and will assign the same number to those rows with the same value. Hence,after the resultset is built in the inline view,we can simply select all of the rows with a dense rank of three or less,this gives us everyone who makes the top three salaries by department number.

Windows

The windowing clause gives us a way to define a slidingor anchored window of data,on which the analytic function will operate,within a group. Thedefault windowis an anchored window that simply starts at the first row of a group an continues to the current row.
We can set up windows based on two criteria:RANGES of data valuesorROWS offset from the current row. It can be said,that the existance of an ORDER BY in an analytic function will add a default window clause of RANGE UNBOUNDED PRECEDING. That says to get all rows in our partition that came before us as specified by the ORDER BY clause.
Let's look at an example with a sliding window within a group and compute the sum of the current row's SAL column plus the prevIoUs 2 rows in that group. If we need a report that shows the sum of the current employee's salary with the preceding two salaries within a departement,it would look like this.

break on deptno skip 1
column ename format A6
column deptno format 999
column sal format 99999

SELECT deptno "Deptno",ename "Ename",
SUM(SAL) OVER (PARTITION BY deptno
ORDER BY ename
ROWS 2 PRECEDING)"Sliding Total"
FROM emp
ORDER BY deptno,ename;

Deptno Ename Sal Sliding Total
------ ------ ------ -------------
10 CLARK 2450 2450
KING 5000 7450
MILLER 1300 8750

20 ADAMS 1100 1100
FORD 3000 4100
JONES 2975 7075 ^
SCOTT 3000 8975 |
SMITH 800 6775 \-- Sliding Window

30 ALLEN 1600 1600
BLAKE 2850 4450
JAMES 950 5400
MARTIN 1250 5050
TURNER 1500 3700
WARD 1250 4000

The partition clause makes the SUM (sal) be computed within each department,independent of the other groups. Tthe SUM (sal) is ' reset ' as the department changes. The ORDER BY ENAME clause sorts the data within each department by ENAME; this allowsthe window clause: ROWS 2 PRECEDING,to access the 2 rows prior to the current row in a groupin order to sum the salaries.
For example,if you note the SLIDING TOTAL value forSMITHis 6 7 7 5,which is the sum of 800,3000,and 2975. That was simply SMITH's row plus the salary from the preceding two rows in the window.

Range Windows

Range windows collect rows together based on a WHERE clause. If I say ' range 5 preceding ' for example,this will generate a sliding window that has the set of all preceding rows in the group such that they are within 5 units of the current row. These units may either be numeric comparisons or date comparisons and it is not valid to use RANGE with datatypes other than numbers and dates.

Example
Count the employees which where hired within the last 100 days preceding the own hiredate. The range window goes back 100 days from the current row's hiredate and then counts the rows within this range. The solution ist to use the following window specification:

COUNT(*) OVER (ORDER BY hiredate ASC RANGE 100 PRECEDING)

column ename heading "Name" format a8
column hiredate heading "Hired" format a10
column hiredate_pre heading "Hired-100" format a10
column cnt heading "Cnt" format 99

SELECT ename,hiredate,hiredate-100 hiredate_pre,
COUNT(*)OVER (ORDER BY hiredate ASC
RANGE 100 PRECEDING) cnt
FROM emp
ORDER BY hiredate ASC;

Name Hired Hired-100 Cnt
-------- ---------- ---------- ---
SMITH 17-DEC-80 08-SEP-80 1
ALLEN 20-FEB-81 12-NOV-80 2
WARD 22-FEB-81 14-NOV-80 3
JONES 02-APR-81 23-DEC-80 3
BLAKE 01-MAY-81 21-JAN-81 4
CLARK 09-JUN-81 01-MAR-81 3
TURNER 08-SEP-81 31-MAY-81 2
MARTIN 28-SEP-81 20-JUN-81 2
KING 17-NOV-81 09-AUG-81 3
JAMES 03-DEC-81 25-AUG-81 5
FORD 03-DEC-81 25-AUG-81 5
MILLER 23-JAN-82 15-OCT-81 4
SCOTT 09-DEC-82 31-AUG-82 1
ADAMS 12-JAN-83 04-OCT-82 2

We ordered the single partition by hiredate ASC. If we look for example at the row for CLARK we can see that his hiredate was 09-JUN-81,and 100 days prior to that is the date 01-MAR-81. If we look who was hired between 01-MAR-81 and 09-JUN-81,we find JONES (hired: 02-APR-81) and BLAKE (hired: 01-MAY-81). This are 3 rows including the current row,this is what we see in the column "Cnt" of CLARK's row.

Compute average salary for defined range

As an example,compute the average salary of people hired within 100 days before for each employee. The query looks like this:

column ename heading "Name" format a8
column hiredate heading "Hired" format a10
column hiredate_pre heading "Hired-100" format a10
column avg_sal heading "Avg-100" format 999999

SELECT ename,
AVG(sal) OVER (ORDER BY hiredate ASC
RANGE 100 PRECEDING) avg_sal
FROM emp
ORDER BY hiredate ASC;

Name Hired SAL Avg-100
-------- ---------- ---------- -------
SMITH 17-DEC-80 800 800
ALLEN 20-FEB-81 1600 1200
WARD 22-FEB-81 1250 1217
JONES 02-APR-81 2975 1942
BLAKE 01-MAY-81 2850 2169
CLARK 09-JUN-81 2450 2758
TURNER 08-SEP-81 1500 1975
MARTIN 28-SEP-81 1250 1375
KING 17-NOV-81 5000 2583
JAMES 03-DEC-81 950 2340
FORD 03-DEC-81 3000 2340
MILLER 23-JAN-82 1300 2563
SCOTT 09-DEC-82 3000 3000
ADAMS 12-JAN-83 1100 2050

Look at CLARK again,since we understand his range window within the group. We can see that the average salary of 2758 is equal to (2975+2850+2450)/3. This is the average of the salaries for CLARK and the rows preceding CLARK,those of JONES and BLAKE. The data must be sorted in ascending order.

Row Windows

Row Windows are physical units; physical number of rows,to include in the window. For example you can calculate the average salary of a given record with the (up to 5) employees hired before them or after them as follows:

set numformat 9999
SELECT ename,
AVG(sal)
OVER (ORDER BY hiredateASC ROWS 5 PRECEDING) AvgAsc,
COUNT(*)
OVER (ORDER BY hiredateASC ROWS 5 PRECEDING) CntAsc,
AVG(sal)
OVER (ORDER BY hiredateDESC ROWS 5 PRECEDING) AvgDes,
COUNT(*)
OVER (ORDER BY hiredateDESC ROWS 5 PRECEDING) CntDes
FROM emp
ORDER BY hiredate;

ENAME HIREDATE SAL AVGASC CNTASC AVGDES CNTDES
---------- --------- ----- ------ ------ ------ ------
SMITH 17-DEC-80 800 800 11988 6
ALLEN 20-FEB-81 1600 1200 22104 6
WARD 22-FEB-81 1250 1217 32046 6
JONES 02-APR-81 2975 1656 42671 6
BLAKE 01-MAY-81 2850 1895 52675 6
CLARK 09-JUN-81 2450 1988 62358 6
TURNER 08-SEP-81 1500 2104 6 2167 6
MARTIN 28-SEP-81 1250 2046 6 2417 6
KING 17-NOV-81 5000 2671 6 2392 6
JAMES 03-DEC-81 950 2333 6 1588 4
FORD 03-DEC-81 3000 2358 6 1870 5
MILLER 23-JAN-82 1300 2167 6 1800 3
SCOTT 09-DEC-82 3000 2417 6 2050 2
ADAMS 12-JAN-83 1100 2392 6 1100 1

The window consist of up to 6 rows,the current row and five rows " in front of " this row,where " in front of " is defined by the ORDER BY clause. With ROW partitions,we do not have the limitation of RANGE partition - the data may be of any type and the order by may include many columns. Notice,that we selected out a COUNT(*) as well. This is useful just to demonstrate how many rows went into making up a given average. We can see clearly that for ALLEN's record,the average salarycomputationfor people hired before him used only 2 records whereas the computation for salaries of people hired after him used 6.

Accessing Rows Around Your Current Row

Frequentlyyouwant to access data not only from the current row but the current row"in front of"or"behind"them. For example,let's say you need a report that shows,bydepartment allof the employees; their hire date;how many days before was the last hire; how many days after was thenext hire.
Using straight sql this query would bedifficultto write.Not only that but its performance would once again definitely be questionable. Theapproach I typically took in the past was either to"select a select"or write a PL/sql function thatwouldtake some data from the current row and"find"the prevIoUs and next rows data. This worked,butintroduce large overhead into both the development of the query and therun-time execution of the query.
Using analytic functions,this is easy and efficient to do.

set echo on
column deptno format 99 heading Dep
column ename format a6 heading Ename
column hiredate heading Hired
column last_hire heading LastHired
column days_last heading DaysLast
column next_hire heading NextHire
column days_next heading NextDays
break on deptno skip 1

SELECT deptno,
LAG(hiredate,1,NULL)
OVER (PARTITION BY deptno
ORDER BY hiredate,ename) last_hire,
hiredate - LAG(hiredate,NULL)
OVER (PARTITION BY deptno
ORDER BY hiredate,ename) days_last,
LEAD(hiredate,ename) next_hire,
LEAD(hiredate,ename) - hiredate days_next
FROM emp
ORDER BY deptno,hiredate;

Dep Ename Hired LastHired DaysLast NextHire NextDays
--- ------ --------- --------- -------- --------- --------
10 CLARK09-JUN-81 17-NOV-81 161
KING 17-NOV-8109-JUN-81 16123-JAN-82 67
MILLER23-JAN-8217-NOV-81 67

20 SMITH 17-DEC-80 02-APR-81 106
JONES 02-APR-81 17-DEC-80 106 03-DEC-81 245
FORD 03-DEC-81 02-APR-81 245 09-DEC-82 371
SCOTT 09-DEC-82 03-DEC-81 371 12-JAN-83 34
ADAMS 12-JAN-83 09-DEC-82 34

30 ALLEN 20-FEB-81 22-FEB-81 2
WARD 22-FEB-81 20-FEB-81 2 01-MAY-81 68
BLAKE 01-MAY-81 22-FEB-81 68 08-SEP-81 130
TURNER 08-SEP-81 01-MAY-81 130 28-SEP-81 20
MARTIN 28-SEP-81 08-SEP-81 20 03-DEC-81 66
JAMES 03-DEC-81 28-SEP-81 66

The LEAD and LAG routinescould be considered a wayto"index into your partitioned group". Usingthese functions you canaccess any individual row. Notice for example in the above printout,it showsthat therecord forKINGincludes the data (inboldredfont) from the prior row (LAST HIRE) and the nextrow(NEXT-HIRE). We canaccess the fields in records preceding or following the current record in anorderedpartition easily.