7.23.5 Rules and considerations pertaining to window functions

1. Window functions can be specified in selection expressions and ORDER BY clauses. However, when specifying a window function in an ORDER BY clause, the sort key of the ORDER BY clause must be identical to the sort key of the value expressions in the selection expression.

2. You can specify a maximum of eight window functions in a single query specification.

3. You cannot specify a window function, a subquery, or the RANDOMROW scalar function inside of a window function.

4. The execution result of the window function RANK, DENSE_RANK, or ROW_NUMBER will have the data type INTEGER. The execution result of CUME_DIST will have the data type DOUBLE PRECISION. For details about the data type of the execution result of a set function used as a window function, see the description of the set function in 7.22 Set functions.

5. The window function applies to the set of rows derived from the results of the table expression (the results of the FROM clause and WHERE clause). If there are no rows in the results of the table expression, the window function is not executed.

6. You cannot specify ROW and a window function at the same time.

7. If you specify a GROUP BY clause, HAVING clause, or set function, the grouping column must be a column specification that is not positioned as the aggregated argument of a window function or as a set function that is included in a window specification.

   Example:

   SELECT COUNT("C1") OVER(PARTITION BY SUM("C2")
                             ORDER BY "C1" RANGE UNBOUNDED PRECEDING)
       FROM "T1"
       GROUP BY "C1"

   In the SQL statement above, column C1, which is specified in the GROUP BY clause, is the grouping column. Because C1, which is specified in COUNT("C1") and ORDER BY "C1", is not the aggregated argument of a set function, it must be specified as the grouping column. On the other hand, C2, which is specified in SUM("C2"), is the aggregated argument of a set function, so it need not be specified as the grouping column.

8. When a window function is specified, a work table might be created. If the size of the work table DB area where the work table is to be created has not been estimated correctly, it might result in a performance degradation. For details about estimating the size of the work table DB area, see the HADB Setup and Operation Guide. For details about work tables, see Considerations when executing an SQL statement that creates work tables in the HADB Application Development Guide.

To Page Top