2.15 Window function

Organization of this section
(1) Function
(2) Format
(3) Operands
(4) Notes
(5) Usage examples

(1) Function

The window function determines a result from a window frame that specifies a window associated with the window function. The function of the window function is described below.

In the window frame, specify the portion of a set of rows from which data is to be collected. In a version that supports only () for the window specification, the window frame indicates the entire range of a table derived as the result of a WHERE or FROM clause.

Table 2-27 Function of the window function

Window function typeExplanation
COUNT(*)Sets the number of rows to be input into the window frame.

(2) Format

window-function::=COUNT(*)
          OVER window-specification
window-specification::=()

(3) Operands

The following rules apply to the window function:

  1. The window function can be specified in a selection expression.
  2. The result data type of the window function COUNT(*) OVER() is INTEGER.
  3. An error results if overflow occurs during an operation, unless the overflow error suppression feature is set. For details about the operational results when overflow error suppression is specified, see 2.18 Operational results with overflow error suppression specified.
  4. The window function cannot be specified in the following locations:
    [Figure]Query expression body of an INSERT statement
    [Figure]Subquery
    [Figure]Derived table
    [Figure]Derived query expression of a view definition
    [Figure]Derived query expression inside a WITH clause
  5. The window function cannot be specified in a query specification, derived query expression, or query expression body that is the target of a set operation.
  6. When the window function is specified, a GROUP BY or HAVING clause cannot be specified.
  7. When the window function is specified in a selection expression, at least one selection expression must be specified, in addition to the window function.
  8. The window function cannot be specified in a scalar operation.
  9. When the window function is specified, a set function cannot be specified in the selection expression.

(4) Notes

  1. If the input to the function is an empty set, set function COUNT(*) outputs 0, but if the window function COUNT(*) OVER() is specified, no search result row is output.

(5) Usage examples

A usage example of the window function is described below. The table used in this example has the following structure:

[Figure]

  1. From the scores table (SCORES), determine numbers (ID), points (POINTS), and total (TOTAL), in descending order of the points.

   SELECT "ID", "POINTS", COUNT(*) OVER() AS "TOTAL"
     FROM "SCORES" ORDER BY "POINTS" DESC

[Figure]