7.23.6 Examples of using window functions

Organization of this subsection

(1) Examples where ROWS or RANGE is specified in the window frame clause
(2) Example using RANK
(3) Example using DENSE_RANK
(4) Example using CUME_DIST
(5) Example using ROW_NUMBER
(6) Example using PERCENTILE_CONT

(1) Examples where ROWS or RANGE is specified in the window frame clause

The following examples show SQL statements that determine a moving total, They illustrate the difference between specifying ROWS and RANGE in the window frame clause.

(a) Example using ROWS

SELECT "C1_SORTKEY", "C2_NUM",
        SUM("C2_NUM") OVER(ORDER BY "C1_SORTKEY"
                      ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS "ROWS_SUM"
    FROM "T1"
    ORDER BY "C1_SORTKEY", "C2_NUM"

Example of execution results

[Figure]

Explanation

In the above example, the values in C1_SORTKEY are arranged in ascending order when the window frame is set. The window frame is set such that the range of the window function extends from one row above the current row to the current row.
The ROWS_SUM column stores the sum of the values in column C2_NUM in the rows within the aggregation range.

(b) Example using RANGE

SELECT "C1_SORTKEY","C2_NUM",
        SUM("C2_NUM") OVER(ORDER BY "C1_SORTKEY"
                        RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS "RANGE_SUM"
    FROM "T1"
    ORDER BY "C1_SORTKEY","C2_NUM"

Example of execution results

[Figure]

Explanation

In the above example, the values in C1_SORTKEY are arranged in ascending order when the window frame is set. The window frame is set such that the range of the window function extends from the row for which the value of C1_SORTKEY is 1 less than the value at the current row, up to the row where it has the same value as the current row.
The RANGE_SUM column stores the sum of the values in column C2_NUM in the rows within the aggregation range.

To Page Top

(2) Example using RANK

Using the data in the salary table (SALARYLIST), this example ranks employees by salary (SALARY) within each job class (POSITION).

SELECT "EMPID","POSITION","SALARY",
        RANK() OVER(PARTITION BY "POSITION" ORDER BY "SALARY" DESC) AS "RANK"
    FROM "SALARYLIST"
    ORDER BY "POSITION","SALARY" DESC,"EMPID"

Example of execution results

[Figure]

To Page Top

(3) Example using DENSE_RANK

Using the data in the salary table (SALARYLIST), this example ranks employees by salary (SALARY) within each job class (POSITION).

SELECT "EMPID","POSITION","SALARY",
        DENSE_RANK() OVER(PARTITION BY "POSITION" ORDER BY "SALARY" DESC) AS "DENSE_RANK"
    FROM "SALARYLIST"
    ORDER BY "POSITION","SALARY" DESC,"EMPID"

Example of execution results

[Figure]

To Page Top

(4) Example using CUME_DIST

Using the data in the salary table (SALARYLIST), this example determines the relative positions of the employees' salaries (SALARY) within each job class (POSITION).

SELECT "EMPID","POSITION","SALARY",
        CUME_DIST() OVER(PARTITION BY "POSITION" ORDER BY "SALARY" DESC) AS "CUME_DIST"
    FROM "SALARYLIST"
    ORDER BY "POSITION","SALARY" DESC,"EMPID"

Example of execution results

[Figure]

To Page Top

(5) Example using ROW_NUMBER

Using the data in the salary table (SALARYLIST), this example determines the row numbers in descending order with respect to employee salary (SALARY) within each job class (POSITION).

SELECT "EMPID","POSITION","SALARY",
        ROW_NUMBER() OVER(PARTITION BY "POSITION" ORDER BY "SALARY" DESC) AS "ROW_NUMBER"
    FROM "SALARYLIST"
    ORDER BY "POSITION","SALARY" DESC,"EMPID"

Example of execution results

[Figure]

To Page Top

(6) Example using PERCENTILE_CONT

Using the data in the salary table (SALARYLIST), this example determines the median value (50^th percentile) of the employee salaries (SALARY) within each job class (POSITION).

SELECT "EMPID","POSITION","SALARY",
        PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY "SALARY")
            OVER(PARTITION BY "POSITION") AS "PERCENTILE_CONT"
    FROM "SALARYLIST"
    ORDER BY "POSITION","SALARY","EMPID"

Example of execution results

[Figure]

To Page Top