7.24.6 Examples of using window functions : Hitachi Advanced Database SQL Reference

(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) Examples of LAG specification

From the sales history table (SALESLIST), determine the amount of the purchase (PRE-PRICE) for row one row before (previous day) for each customer ID (USERID).

■ Example where RESPECT NULLS is specified

SELECT "USERID", "PUR-DATE", "PUR-PRICE",
        LAG("PUR-PRICE", 1, 0) RESPECT NULLS
            OVER(PARTITION BY "USERID" ORDER BY "PUR-DATE") AS "PRE-PRICE"
    FROM "SALESLIST"
    ORDER BY "USERID", "PUR-DATE"

Execution result example

[Figure]

[Explanation]

The PRE-PRICE column contains the value of the PUR-PRICE column one row before for each customer ID. Null values are stored even if the value in the PUR-PRICE column one row before is a null value.
In the case of the first row for each customer ID, the row one before is outside the set of ordered rows. Therefore, the PRE-PRICE column will contain 0 specified as the default value.

■Example where IGNORE NULLS is specified

SELECT "USERID", "PUR-DATE", "PUR-PRICE",
        LAG("PUR-PRICE", 1, 0) IGNORE NULLS
            OVER(PARTITION BY "USERID" ORDER BY "PUR-DATE") AS "PRE-PRICE"
    FROM "SALESLIST"
    ORDER BY "USERID", "PUR-DATE"

Execution result example

[Figure]

[Explanation]

The PRE-PRICE column contains the value of the PUR-PRICE column one row before for each customer ID. Null values are stripped out, so the PUR-PRICE column value is stored in the PUR-PRICE column one row before, excluding the row with the null value.

Note that if the PUR-PRICE column value is outside the set of ordered rows, except for the row with a null value, the PRE-PRICE column will contain 0, which is specified as the default value.
In the case of the first row for each customer ID, the row one before is outside the set of ordered rows. Therefore, the PRE-PRICE column will contain 0 specified as the default value.

To Page Top

(7) Examples of LEAD specification

From the sales history table (SALESLIST), determine the amount of the purchase (POST-PRICE) for the row one row back (the next day) for each customer ID (USERID).

■ Example where RESPECT NULLS is specified

SELECT "USERID", "PUR-DATE", "PUR-PRICE",
        LEAD("PUR-PRICE", 1, 0) RESPECT NULLS
            OVER(PARTITION BY "USERID" ORDER BY "PUR-DATE") AS "POST-PRICE"
    FROM "SALESLIST"
    ORDER BY "USERID", "PUR-DATE"

Execution result example

[Figure]

[Explanation]

The POST-PRICE column contains the value of the PUR-PRICE column one row after for each customer ID. Null values are stored even if the value in the PUR-PRICE column one row after is a null value.
In the case of the last row for each customer ID, the row one after is outside the set of ordered rows. Therefore, the POST-PRICE column will contain 0 specified as the default value.

■Example where IGNORE NULLS is specified

SELECT "USERID", "PUR-DATE", "PUR-PRICE",
        LEAD("PUR-PRICE", 1, 0) IGNORE NULLS
            OVER(PARTITION BY "USERID" ORDER BY "PUR-DATE") AS "POST-PRICE"
    FROM "SALESLIST"
    ORDER BY "USERID", "PUR-DATE"

Execution result example

[Figure]

[Explanation]

The POST-PRICE column contains the value of the PUR-PRICE column one row after for each customer ID. Null values are stripped out, so the PUR-PRICE column value is stored in the PUR-PRICE column one row after, excluding the row with the null value.

Note that if the PUR-PRICE column value is outside the set of ordered rows, except for the row after the row with a null value, the POST-PRICE column will contain 0, which is specified as the default value.
In the case of the last row for each customer ID, the row one after is outside the set of ordered rows. Therefore, the POST-PRICE column will contain 0 specified as the default value.

To Page Top

(8) 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