Table of Contents |

## 4.3 ANSI SQL Window FunctionsSQL2003
allows for a
Window, or analytic, functions are similar to standard aggregate functions in that they operate on multiple rows, or groups of rows, within the result set returned from a query. However, the groups of rows that a window function operates on are defined not by a GROUP BY clause, but by partitioning and windowing clauses. Furthermore, the order within these groups is defined by an ordering clause, but that order only affects function evaluation, and has no effect on the order in which rows are returned by the query.
## 4.3.1 SQL2003's Window SyntaxSQL2003 specifies the following syntax for window functions:
## 4.3.2 Oracle's Window SyntaxOracle's window function syntax is as follows:
## 4.3.3 DB2's Window SyntaxDB2's syntax is similar to Oracle's. For OLAP, ranking, and numbering functions, DB2 allows the following syntax:
When aggregate functions (e.g.
## 4.3.4 PartitioningPartitioning the rows operated on by the partition clause is similar
to using the
The following results illustrate the effects of partitioning by ODD. The sum of the even numbers is 2 (0+2), and the sum of the odd numbers is 4 (1+3). The second column of the result set reports the sum of all values in the partition to which that row belongs. Yet all the detail rows are returned. The query provides summary results in the context of detail rows:
Not using a partitioning clause at all will sum all of the numbers in the NUM column for each row returned by the query. In effect, the entire result set is treated as a single, large partition:
## 4.3.5 OrderingYou specify the order of the rows on which an analytic function
operates using the
As you can see, the ORDER BY clauses in the
window function invocations affect the ordering of the rows in the
respective partitions when those functions are evaluated. Thus,
## 4.3.6 Grouping or WindowingMany analytic functions also allow you to specify a virtual, moving
window surrounding a row within a partition. You do this using the
The following, Oracle-based example uses the
SUM to calculate a running sum of the values in
the first column. No partitioning clause is used, so each invocation
of SUM operates over the entire result set.
However, the ORDER BY clause sorts the rows for
SUM in ascending order of NUM's value, and the
BETWEEN clause (which is the windowing clause)
causes each invocation of SUM to include values
for NUM only up through the current
row. Each successive invocation of SUM includes
yet another value for NUM, in order,
from the lowest value of NUM to the
greatest:
This example's a bit too easy, as the order of the final result set happens to match the order of the running total. That doesn't need to be the case. The following example generates the same results, but in a different order. You can see that the running total values are appropriate for each value of NUM, but the rows are presented in a different order than before. The result set ordering is completely independent of the ordering used for window function calculations:
## 4.3.7 List of Window FunctionsSQL2003 specifies that any aggregate function may also be used as a window function. Both Oracle and DB2 largely follow the standard in that respect, so you'll find that you can take just about any aggregate function (certainly the standard ones) and apply to it the window function syntax described in the preceding sections. In addition to the aggregate functions, SQL2003 defines the window functions described in the following sections. Only Oracle and DB2 currently implement these functions. All examples use the following table and data, which is a variation on the ODD_NUMS table used earlier to illustrate the concepts of partitioning, ordering, and grouping:
Platform-specific window functions for Oracle (there are none for DB2) are included in the lists found under Section 4.5 later in this chapter.
Calculates the cumulative distribution, or relative rank, of the current row to other rows in the same partition. The calculation for a given row is as follows: number of peer or preceding rows / number of rows in partition Because the result for a given row depends on the number of rows preceding that row in the same partition, it's important to always specify an ORDER BY clause when invoking this function. ## SQL2003 SyntaxCUME_DIST( ) OVER { ## DB2DB2 does not support the ## OracleOracle does not allow the framing portion of the windowing syntax.
Oracle requires the CUME_DIST( ) OVER ([ ## ExampleThe following Oracle-based example uses
Following is an explanation of the calculation behind the rank for the row in which NUM=0: Because of the ORDER BY clause, the rows in the partition are ordered as follows:
- NUM=0
- NUM=2
- NUM=4
There are no rows preceding NUM=0. There is one row that is a peer of NUM=0, and that is the NUM=0 row itself. Thus, the divisor is 1. There are three rows in the partition as a whole, making the dividend 3. The result of 1/3 is.33 repeating, as shown in the example output.
Assigns a rank to each row in a partition, which should be ordered in
some manner. The rank for a given row is computed by counting the
number of rows preceding the row in question, and then adding 1 to
the result. Rows with duplicate ## SQL2003 SyntaxDENSE_RANK( ) OVER { ## DB2DB2 requires the clause:framingDENSE_RANK( ) OVER ([ ## OracleOracle also requires the clause:framingDENSE_RANK( ) OVER ([ ## ExampleCompare the results from the following Oracle-based example to those
shown in the section on the
The two rows where NUM=3 are both ranked at #3. The next higher row is ranked at #4. Rank numbers are not skipped, hence the term "dense."
Assigns a rank to each row in a partition, which should be ordered in
some manner. The rank for a given row is computed by counting the
number of rows preceding the row in question, and then adding 1 to
the result. Rows with duplicate ## SQL2003 SyntaxRANK( ) OVER { ## DB2DB2 requires the clause:framingRANK( ) OVER ([ ## OracleOracle also requires the clause:framingRANK( ) OVER ([ ## ExampleThe following Oracle-based example uses the NUM column to rank the rows in the test4 table:
Because both rows where NUM=3 rank the same at #4, the next higher row will be ranked at #6. The #5 rank is skipped.
Computes the relative rank of a row by dividing that row's rank less 1 by the number of rows in the partition, also less 1: (rank - 1) / (rows - 1) Compare this calculation to that used for
## SQL2003 SyntaxPERCENT_RANK( ) OVER ({ ## DB2DB2 does not support the ## OracleOracle requires the clause:framingPERCENT_RANK( ) OVER ([ ## ExampleThe following, Oracle-based example assigns a relative rank to values of NUM, partitioning the data on the ODD column:
Following is an explanation of the calculation behind the rank for the row in which NUM=2: Row NUM=2 is the second row in its partition; thus, it ranks #2. Subtract 1 from 2 to get a divisor of 1. The dividend is the total number of rows in the partition, or 3. Subtract 1 from 3 to get a dividend of 2. The result of 1/3 is.33 repeating, as shown in the example.
Assigns a unique number to each row in a partition. ## SQL2003 SyntaxROW_NUMBER( ) OVER ({ ## DB2DB2 does not allow the clause optional:orderingROW_NUMBER( ) OVER ([ ## OracleOracle requires the clause:framingROW_NUMBER( ) OVER ([ ## Example
5 1 4 |

Table of Contents |