Window (Analytical) Functions

Window functions (also known as analytical functions) are a kind of aggregation, but one that does not “reduce” a group into a single row.The columns of aggregated data are mixed with the query result set.

The window functions are used with the OVER clause.They may appear only in the SELECT list, or the ORDER BY clause of a query.

Firebird window functions may be partitioned and ordered.

Window functions are available in DSQL and PSQL.Availability in ESQL is not tracked by this Language Reference.

Syntax

<window_function> ::=
    <aggregate-function> OVER <window-name-or-spec>
  | <window-function-name> ([<value-expression> [, <value-expression> ...]])
    OVER <window-name-or-spec>

<aggregate-function> ::=
  !! See Aggregate Functions !!

<window-name-or-spec> ::=
  (<window-specification-details>) | existing_window_name

<window-function-name> ::=
    <ranking-function>
  | <navigational-function>

<ranking-function> ::=
    RANK | DENSE_RANK | PERCENT_RANK | ROW_NUMBER
  | CUME_DIST | NTILE

<navigational-function>
  LEAD | LAG | FIRST_VALUE | LAST_VALUE | NTH_VALUE

<window-specification-details> ::=
  [existing-window-name]
    [<window-partition-clause>]
    [<order-by-clause>]
    [<window-frame-clause>]

<window-partition-clause> ::=
  PARTITION BY <value-expression> [, <value-expression> ...]

<order-by-clause> ::=
  ORDER BY <sort-specification [, <sort-specification> ...]

<sort-specification> ::=
  <value-expression> [<ordering-specification>] [<null-ordering>]

<ordering-specification> ::=
    ASC  | ASCENDING
  | DESC | DESCENDING

<null-ordering> ::=
    NULLS FIRST
  | NULLS LAST

<window-frame-clause> ::= { RANGE | ROWS } <window-frame-extent>

<window-frame-extent> ::=
    <window-frame-start>
  | <window-frame-between>

<window-frame-start> ::=
    UNBOUNDED PRECEDING
  | <value-expression> PRECEDING
  | CURRENT ROW

<window-frame-between> ::=
  BETWEEN { UNBOUNDED PRECEDING | <value-expression> PRECEDING
          | CURRENT ROW | <value-expression> FOLLOWING }
  AND { <value-expression> PRECEDING | CURRENT ROW
      | <value-expression> FOLLOWING | UNBOUNDED FOLLOWING }

Table 1. Window Function Arguments
Argument	Description
value-expression	Expression.May contain a table column, constant, variable, expression, scalar or aggregate function.Window functions are not allowed as an expression.
aggregate-function	An aggregate function used as a window function
existing-window-name	A named window defined using the `WINDOW` clause of the current query specification.

Aggregate Functions as Window Functions

All aggregate functions — including FILTER clause — can be used as window functions, by adding the OVER clause.

Imagine a table EMPLOYEE with columns ID, NAME and SALARY, and the need to show each employee with their respective salary and the percentage of their salary over the payroll.

A normal query could achieve this, as follows:

select
    id,
    department,
    salary,
    salary / (select sum(salary) from employee) portion
  from employee
  order by id;

Results

id  department  salary  portion
--  ----------  ------  ----------
1   R & D        10.00      0.2040
2   SALES        12.00      0.2448
3   SALES         8.00      0.1632
4   R & D         9.00      0.1836
5   R & D        10.00      0.2040

The query is repetitive and lengthy to run, especially if EMPLOYEE happens to be a complex view.

The same query could be specified in a much faster and more elegant way using a window function:

select
    id,
    department,
    salary,
    salary / sum(salary) OVER () portion
  from employee
  order by id;

Here, sum(salary) over () is computed with the sum of all SALARY from the query (the EMPLOYEE table).