FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 Aggregate FunctionsSystem Packages 

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).

Partitioning

Like aggregate functions, that may operate alone or in relation to a group, window functions may also operate on a group, which is called a “partition”.

Syntax
<window function>(...) OVER (PARTITION BY <expr> [, <expr> ...])

Aggregation over a group could produce more than one row, so the result set generated by a partition is joined with the main query using the same expression list as the partition.

Continuing the EMPLOYEE example, instead of getting the portion of each employee’s salary over the all-employees total, we would like to get the portion based on the employees in the same department:

select
    id,
    department,
    salary,
    salary / sum(salary) OVER (PARTITION BY department) portion
  from employee
  order by id;
Results
id  department  salary  portion
--  ----------  ------  ----------
1   R & D        10.00      0.3448
2   SALES        12.00      0.6000
3   SALES         8.00      0.4000
4   R & D         9.00      0.3103
5   R & D        10.00      0.3448

Ordering

The ORDER BY sub-clause can be used with or without partitions.The ORDER BY clause within OVER specifies the order in which the window function will process rows.This order does not have to be the same as the order rows appear in the output.

There is an important concept associated with window functions: for each row there is a set of rows in its partition called the window frame.By default, when specifying ORDER BY, the frame consists of all rows from the beginning of the partition to the current row and rows equal to the current ORDER BY expression.Without ORDER BY, the default frame consists of all rows in the partition.

As a result, for standard aggregate functions, the ORDER BY clause produces partial aggregation results as rows are processed.

Example
select
    id,
    salary,
    sum(salary) over (order by salary) cumul_salary
  from employee
  order by salary;
Results
id  salary  cumul_salary
--  ------  ------------
3     8.00          8.00
4     9.00         17.00
1    10.00         37.00
5    10.00         37.00
2    12.00         49.00

Then cumul_salary returns the partial/accumulated (or running) aggregation (of the SUM function).It may appear strange that 37.00 is repeated for the ids 1 and 5, but that is how it should work.The ORDER BY keys are grouped together, and the aggregation is computed once (but summing the two 10.00).To avoid this, you can add the ID field to the end of the ORDER BY clause.

It’s possible to use multiple windows with different orders, and ORDER BY parts like ASC/DESC and NULLS FIRST/LAST.

With a partition, ORDER BY works the same way, but at each partition boundary the aggregation is reset.

All aggregation functions can use ORDER BY, except for LIST().